"""Add full-IP admin diagnostics, managed security rules, and analytics display preferences."""
import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).resolve().parents[1] / "instance" / "inventory.sqlite3"


def add_column(conn, table, column, ddl):
    cols = {row[1] for row in conn.execute(f"PRAGMA table_info({table})")}
    if column not in cols:
        conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {ddl}")


def migrate(db_path=DB_PATH):
    conn = sqlite3.connect(db_path)
    add_column(conn, "site_settings", "analytics_show_full_ip", "INTEGER DEFAULT 0")
    add_column(conn, "site_settings", "analytics_include_zero_seconds", "INTEGER DEFAULT 1")
    add_column(conn, "analytics_sessions", "ip_full", "TEXT")
    add_column(conn, "analytics_events", "ip_full", "TEXT")
    add_column(conn, "security_events", "source_ip_full", "TEXT")
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS security_rules (
        id INTEGER PRIMARY KEY,
        rule_type TEXT NOT NULL,
        rule_value TEXT NOT NULL,
        reason TEXT,
        is_enabled INTEGER DEFAULT 1,
        expires_at TEXT,
        hit_count INTEGER DEFAULT 0,
        last_hit_at TEXT,
        created_at TEXT,
        updated_at TEXT,
        UNIQUE(rule_type, rule_value)
    );
    CREATE INDEX IF NOT EXISTS idx_security_rules_type_enabled ON security_rules(rule_type, is_enabled);
    """)
    conn.execute("UPDATE site_settings SET analytics_show_full_ip=0 WHERE analytics_show_full_ip IS NULL")
    conn.execute("UPDATE site_settings SET analytics_include_zero_seconds=1 WHERE analytics_include_zero_seconds IS NULL")
    seeds = [
        ("approved_host", "cleanagrisales.com", "Primary production domain"),
        ("approved_host", "www.cleanagrisales.com", "WWW production domain"),
        ("blocked_domain", "paginademanuseamento.com", "Unauthorized proxy/clone"),
        ("blocked_ip", "216.252.233.168", "Unauthorized proxy/clone server"),
    ]
    for rule in seeds:
        conn.execute("INSERT OR IGNORE INTO security_rules(rule_type,rule_value,reason,is_enabled,created_at,updated_at) VALUES(?,?,?,1,datetime('now'),datetime('now'))", rule)
    conn.commit()
    conn.close()

if __name__ == "__main__":
    migrate()