import sqlite3
from pathlib import Path

DB_PATH = Path("instance/inventory.sqlite3")

LOCAL_HOSTS = [
    "46cars.ca",
    "www.46cars.ca",
    "localhost",
    "127.0.0.1",
    "::1",
]

conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()

# Confirm table exists
tables = [r[0] for r in cur.execute(
    "SELECT name FROM sqlite_master WHERE type='table'"
).fetchall()]

if "security_rules" not in tables:
    raise SystemExit("security_rules table does not exist in this DB.")

cols_info = cur.execute("PRAGMA table_info(security_rules)").fetchall()
cols = [r[1] for r in cols_info]

print("security_rules columns:")
print(cols)

# Detect actual column names
type_col = "rule_type" if "rule_type" in cols else None
value_col = "rule_value" if "rule_value" in cols else None

if not type_col or not value_col:
    raise SystemExit("Could not find rule_type/rule_value columns in security_rules.")

status_col = None
for candidate in ("is_active", "enabled", "is_enabled", "active"):
    if candidate in cols:
        status_col = candidate
        break

reason_col = "reason" if "reason" in cols else ("notes" if "notes" in cols else None)
created_col = "created_at" if "created_at" in cols else None
updated_col = "updated_at" if "updated_at" in cols else None

for host in LOCAL_HOSTS:
    existing = cur.execute(
        f"""
        SELECT id FROM security_rules
        WHERE {type_col} = 'approved_host'
          AND LOWER({value_col}) = LOWER(?)
        LIMIT 1
        """,
        (host,),
    ).fetchone()

    if existing:
        updates = []
        params = []

        if status_col:
            updates.append(f"{status_col} = 1")
        if reason_col:
            updates.append(f"{reason_col} = ?")
            params.append("Local development approved host")
        if updated_col:
            updates.append(f"{updated_col} = datetime('now')")

        if updates:
            params.append(existing[0])
            cur.execute(
                f"UPDATE security_rules SET {', '.join(updates)} WHERE id = ?",
                params,
            )

        print(f"Updated existing approved host: {host}")
        continue

    insert_cols = [type_col, value_col]
    insert_vals = ["approved_host", host]

    if status_col:
        insert_cols.append(status_col)
        insert_vals.append(1)

    if reason_col:
        insert_cols.append(reason_col)
        insert_vals.append("Local development approved host")

    if created_col:
        insert_cols.append(created_col)
        insert_vals.append("__SQL_NOW__")

    if updated_col:
        insert_cols.append(updated_col)
        insert_vals.append("__SQL_NOW__")

    placeholders = []
    params = []

    for val in insert_vals:
        if val == "__SQL_NOW__":
            placeholders.append("datetime('now')")
        else:
            placeholders.append("?")
            params.append(val)

    cur.execute(
        f"""
        INSERT INTO security_rules ({", ".join(insert_cols)})
        VALUES ({", ".join(placeholders)})
        """,
        params,
    )

    print(f"Inserted approved host: {host}")

conn.commit()
conn.close()

print("Done. Restart Flask, then login locally.")