67 lines
1.7 KiB
Python
67 lines
1.7 KiB
Python
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")
|