#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import fdb
from datetime import datetime

# === CONFIG ===
DB_PATH = r"U:\Medicus 3\data\MEDICUS.FDB"
USER = "sysdba"
PASSWORD = "masterkey"

# output file (timestamped)
OUTFILE = f"firebird_schema_{datetime.now():%Y%m%d_%H%M%S}.txt"

# === CONNECT ===
con = fdb.connect(
    dsn=DB_PATH,
    user=USER,
    password=PASSWORD,
    charset='WIN1250'
)
cur = con.cursor()

with open(OUTFILE, "w", encoding="utf-8") as f:
    f.write(f"Firebird schema overview for: {DB_PATH}\n\n")

    # === GET ALL USER TABLES ===
    cur.execute("""
        SELECT r.rdb$relation_name
        FROM rdb$relations r
        WHERE r.rdb$system_flag = 0
          AND r.rdb$view_blr IS NULL
        ORDER BY r.rdb$relation_name
    """)
    tables = [t[0].strip() for t in cur.fetchall()]

    for table in tables:
        f.write("=" * 80 + "\n")
        f.write(f"TABLE: {table}\n")
        f.write("-" * 80 + "\n")

        # --- COLUMNS ---
        cur.execute(f"""
            SELECT
                rf.rdb$field_name,
                f.rdb$field_type,
                f.rdb$field_sub_type,
                f.rdb$field_length,
                f.rdb$field_scale,
                coalesce(rf.rdb$null_flag, 0),
                rf.rdb$default_source,
                rf.rdb$description
            FROM rdb$relation_fields rf
            JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
            WHERE rf.rdb$relation_name = '{table}'
            ORDER BY rf.rdb$field_position
        """)

        f.write("Columns:\n")
        for row in cur.fetchall():
            name = row[0].strip()
            dtype = row[1]
            subtype = row[2]
            length = row[3]
            scale = row[4]
            notnull = bool(row[5])
            default = (row[6] or "").strip() if row[6] else ""
            desc = (row[7] or "").strip() if row[7] else ""
            f.write(f"  - {name:<20} type={dtype} sub={subtype} len={length} "
                    f"scale={scale} notnull={notnull} default={default} desc={desc}\n")

        # --- INDEXES ---
        cur.execute(f"""
            SELECT
                i.rdb$index_name,
                i.rdb$unique_flag,
                i.rdb$index_type,
                s.rdb$field_name
            FROM rdb$indices i
            JOIN rdb$index_segments s ON i.rdb$index_name = s.rdb$index_name
            WHERE i.rdb$relation_name = '{table}'
            ORDER BY i.rdb$index_name, s.rdb$field_position
        """)
        idx = cur.fetchall()
        if idx:
            f.write("Indexes:\n")
            for row in idx:
                f.write(f"  - {row[0].strip():<30} field={row[3].strip()} unique={bool(row[1])} type={row[2]}\n")
        else:
            f.write("Indexes: none\n")

        # --- PRIMARY & FOREIGN KEYS ---
        cur.execute(f"""
            SELECT
                rc.rdb$constraint_name,
                rc.rdb$constraint_type,
                seg.rdb$field_name
            FROM rdb$relation_constraints rc
            JOIN rdb$index_segments seg ON rc.rdb$index_name = seg.rdb$index_name
            WHERE rc.rdb$relation_name = '{table}'
            ORDER BY rc.rdb$constraint_name
        """)
        cons = cur.fetchall()
        if cons:
            f.write("Constraints:\n")
            for row in cons:
                f.write(f"  - {row[1].strip():<15} {row[0].strip()} → {row[2].strip()}\n")
        else:
            f.write("Constraints: none\n")

        f.write("\n")

con.close()
print(f"✅ Schema saved to: {OUTFILE}")