from pathlib import Path
import sys

import psycopg2


ROOT_DIR = Path(__file__).resolve().parents[1]
CONN_DIR = ROOT_DIR / "conn"
API_DIR = ROOT_DIR / "api"
for path in (CONN_DIR, API_DIR):
    if str(path) not in sys.path:
        sys.path.insert(0, str(path))

import config
from ai_index import ensure_ai_schema


BOT_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS users (
    tid BIGINT PRIMARY KEY,
    username TEXT,
    surname TEXT,
    status CHAR(1) NOT NULL DEFAULT 'P',
    user_admin BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP NOT NULL DEFAULT now(),
    CHECK (status IN ('P', 'A'))
);

CREATE TABLE IF NOT EXISTS filters (
    fid BIGSERIAL PRIMARY KEY,
    tid BIGINT NOT NULL REFERENCES users(tid) ON DELETE CASCADE,
    sort CHAR(1) NOT NULL,
    sort_value TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    CHECK (sort IN ('A', 'S', 'C', 'P')),
    UNIQUE (tid, sort, sort_value)
);

CREATE INDEX IF NOT EXISTS filters_tid_idx ON filters (tid);
CREATE INDEX IF NOT EXISTS filters_sort_idx ON filters (sort);

CREATE TABLE IF NOT EXISTS tickets (
    fid BIGINT NOT NULL REFERENCES filters(fid) ON DELETE CASCADE,
    ticket BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (fid, ticket)
);

CREATE INDEX IF NOT EXISTS tickets_ticket_idx ON tickets (ticket);
"""


def ensure_bot_schema():
    connection = psycopg2.connect(**config.POSTGRES_CONFIG)
    cursor = connection.cursor()
    cursor.execute(BOT_SCHEMA_SQL)
    connection.commit()
    cursor.close()
    connection.close()


if __name__ == "__main__":
    ensure_bot_schema()
    ensure_ai_schema()
    print("PostgreSQL schema is ready")