#!/bin/bash

# Script to setup database
# Pre-requisit: The file /etc/xivo_list should contain the list of XiVOs ip address (one ip per line) 

IP_FILE="/etc/xivo_list"

if [[ ! -f "$IP_FILE" ]]; then
    echo "IP file not found: $IP_FILE"
    exit 1
fi

# Read all IPs from file
mapfile -t IP_LIST < "$IP_FILE"

if [[ "${#IP_LIST[@]}" -lt 1 ]]; then
    echo "The IP file must contain at least one IP address."
    exit 1
fi

export PGPASSWORD=postgres  # Change if needed

# Prepare the SQL body
SQL=$(cat <<EOF
CREATE DATABASE kamailio;
CREATE ROLE kam WITH LOGIN PASSWORD 'kam';
GRANT CONNECT ON DATABASE kamailio TO kam;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kam;

\\c kamailio

DROP TABLE IF EXISTS "users";
CREATE TABLE "public"."users" (
    "name" character varying NOT NULL,
    "password" character varying NOT NULL
);

DROP TABLE IF EXISTS "xivo_dispatcher";
DROP SEQUENCE IF EXISTS xivo_dispatcher_id_seq;
CREATE SEQUENCE xivo_dispatcher_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

CREATE TABLE "public"."xivo_dispatcher" (
    "id" integer DEFAULT nextval('xivo_dispatcher_id_seq') NOT NULL,
    "setid" integer DEFAULT '0' NOT NULL,
    "destination" character varying NOT NULL,
    "flags" integer DEFAULT '0' NOT NULL,
    "priority" integer DEFAULT '0' NOT NULL,
    "attrs" character varying NOT NULL,
    "description" character varying NOT NULL
);

DROP TABLE IF EXISTS "version";
CREATE TABLE "public"."version" (
    "table_name" character varying NOT NULL,
    "table_version" integer DEFAULT '0' NOT NULL
);

TRUNCATE "version";
INSERT INTO "version" ("table_name", "table_version") VALUES ('xivo_dispatcher', 1);

GRANT SELECT ON ALL TABLES IN SCHEMA public TO kam;

CREATE EXTENSION IF NOT EXISTS dblink;

TRUNCATE users;
EOF
)

# Append user imports via dblink
for ip in "${IP_LIST[@]}"; do
SQL+="

INSERT INTO users(name, password)
SELECT remote_table.name, remote_table.password
    FROM dblink('dbname=asterisk port=5432
            host=${ip} user=asterisk password=proformatique',
    'SELECT name, secret, category from usersip') AS remote_table(name varchar, password varchar, category varchar) WHERE category=''user'';
"
done

# Truncate dispatcher and insert each destination
SQL+="

TRUNCATE \"xivo_dispatcher\";
"

id=1
for ip in "${IP_LIST[@]}"; do
SQL+="INSERT INTO \"xivo_dispatcher\" (\"id\", \"setid\", \"destination\", \"flags\", \"priority\", \"attrs\", \"description\") VALUES
(${id}, 1, 'sip:${ip}:5060', 0, 10, '', 'XIVO${id}');"$'\n'
((id++))
done

# Execute the generated SQL
psql -h localhost -U postgres <<EOF
$SQL
EOF

unset PGPASSWORD

