#!/bin/bash

# File containing one IP per line
IP_FILE="/etc/xivo_list"

# PostgreSQL connection
PGUSER="postgres"
PGHOST="localhost"
PGPASSWORD="kam"  # Change if needed

export PGPASSWORD

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

# Read IPs into array
mapfile -t IP_LIST < "$IP_FILE"
if [[ "${#IP_LIST[@]}" -lt 1 ]]; then
    echo "ERROR: No IPs found in $IP_FILE"
    exit 1
fi

# SQL preamble — setup DB and schema
SQL=$(cat <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'kamailio') THEN
      CREATE DATABASE kamailio;
   END IF;
END
\$\$;

-- connect to kamailio DB
\\c kamailio

DO \$\$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'kam') THEN
      CREATE ROLE kam WITH LOGIN PASSWORD 'kam';
   END IF;
END
\$\$;

GRANT CONNECT ON DATABASE kamailio TO kam;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kam;

-- Tables
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    name VARCHAR NOT NULL,
    password VARCHAR NOT NULL
);

DROP TABLE IF EXISTS xivo_dispatcher;
DROP SEQUENCE IF EXISTS xivo_dispatcher_id_seq;
CREATE SEQUENCE xivo_dispatcher_id_seq START 1;

CREATE TABLE xivo_dispatcher (
    id INTEGER DEFAULT nextval('xivo_dispatcher_id_seq') NOT NULL,
    setid INTEGER DEFAULT 0 NOT NULL,
    destination VARCHAR NOT NULL,
    flags INTEGER DEFAULT 0 NOT NULL,
    priority INTEGER DEFAULT 0 NOT NULL,
    attrs VARCHAR NOT NULL,
    description VARCHAR NOT NULL
);

DROP TABLE IF EXISTS version;
CREATE TABLE version (
    table_name VARCHAR 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 dblink import per IP
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 WHERE category=''user''')
AS remote_table(name VARCHAR, password VARCHAR, category VARCHAR);
"
done

# Append dispatcher rows
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
psql -h "$PGHOST" -U "$PGUSER" -d postgres <<EOF
$SQL
EOF

# Cleanup
unset PGPASSWORD

