#!/usr/bin/env python
"""
Fix missing utils_notification table and related columns.
Connects directly via pymysql — no Django, no .env needed.
Run on the production server: python fix_missing_tables.py
"""
import sys
import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='xygbfpsg_graz',
    password='j.ez-xy6##y.rllB',
    database='xygbfpsg_loans',
    charset='utf8mb4',
)
print("Connected to database.")


def table_exists(cur, name):
    cur.execute(
        "SELECT COUNT(*) FROM information_schema.tables "
        "WHERE table_schema = DATABASE() AND table_name = %s", (name,)
    )
    return cur.fetchone()[0] > 0


def column_exists(cur, table, col):
    if not table_exists(cur, table):
        return False
    cur.execute(
        "SELECT COUNT(*) FROM information_schema.columns "
        "WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s",
        (table, col)
    )
    return cur.fetchone()[0] > 0


def run(cur, sql, label):
    try:
        cur.execute(sql)
        conn.commit()
        print(f"  + {label}")
        return True
    except Exception as e:
        conn.rollback()
        print(f"  ! {label}: {e}")
        return False


cur = conn.cursor()

print("\n[1] utils_notification table...")
if not table_exists(cur, 'utils_notification'):
    run(cur, """
        CREATE TABLE `utils_notification` (
            `id` char(32) NOT NULL PRIMARY KEY,
            `notification_type` varchar(50) NOT NULL,
            `title` varchar(200) NOT NULL,
            `message` longtext NOT NULL,
            `priority` varchar(20) NOT NULL,
            `created_at` datetime(6) NOT NULL,
            `read_at` datetime(6) NULL,
            `action_url` varchar(200) NULL,
            `expires_at` datetime(6) NULL,
            `icon` varchar(50) NOT NULL,
            `user_id` char(32) NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """, "Created utils_notification")
    run(cur, "CREATE INDEX `utils_notif_user_idx` ON `utils_notification` (`user_id`, `created_at` DESC)", "Index user_id")
    run(cur, "CREATE INDEX `utils_notif_type_idx` ON `utils_notification` (`notification_type`)", "Index notification_type")
    run(cur, "CREATE INDEX `utils_notif_read_idx` ON `utils_notification` (`read_at`)", "Index read_at")
else:
    print("  Table already exists")

print("\n[2] loan_app_id...")
if not column_exists(cur, 'utils_notification', 'loan_app_id'):
    run(cur, "ALTER TABLE `utils_notification` ADD COLUMN `loan_app_id` char(32) NULL", "Added loan_app_id")
else:
    print("  Already exists")

print("\n[3] related_loan_id...")
if not column_exists(cur, 'utils_notification', 'related_loan_id'):
    run(cur, "ALTER TABLE `utils_notification` ADD COLUMN `related_loan_id` char(32) NULL", "Added related_loan_id")
    run(cur, "ALTER TABLE `utils_notification` ADD KEY `utils_notif_loan_idx` (`related_loan_id`)", "Index related_loan_id")
else:
    print("  Already exists")

print("\n[4] Portfolio fields...")
for col, ddl in [
    ('alert_data',      "ADD COLUMN `alert_data` json NULL"),
    ('action_required', "ADD COLUMN `action_required` tinyint(1) NOT NULL DEFAULT 0"),
    ('resolved_at',     "ADD COLUMN `resolved_at` datetime(6) NULL"),
    ('resolved_by_id',  "ADD COLUMN `resolved_by_id` char(32) NULL"),
]:
    if not column_exists(cur, 'utils_notification', col):
        run(cur, f"ALTER TABLE `utils_notification` {ddl}", f"Added {col}")
    else:
        print(f"  {col} already exists")

print("\n--- Verification ---")
cur.execute("SHOW COLUMNS FROM `utils_notification`")
cols = [r[0] for r in cur.fetchall()]
print(f"Columns: {cols}")
missing = [c for c in ['id', 'related_loan_id', 'loan_app_id', 'user_id'] if c not in cols]
if missing:
    print(f"FAILED - still missing: {missing}")
    sys.exit(1)
else:
    print("SUCCESS - error is resolved.")

cur.close()
conn.close()