"""Add analytics v2 sessions/events and lead attribution without deleting legacy data."""
import sqlite3
from datetime import datetime, timezone

LEAD_COLUMNS = {
    "visitor_id": "TEXT", "analytics_session_token": "TEXT", "landing_page": "TEXT",
    "first_referrer": "TEXT", "first_visit_at": "TEXT", "views_before_lead": "INTEGER DEFAULT 0",
    "engaged_seconds_before_lead": "INTEGER DEFAULT 0", "attribution_country": "TEXT",
    "attribution_language": "TEXT", "attribution_device": "TEXT", "analytics_user_agent": "TEXT",
    "analytics_ip_hash": "TEXT",
}


def columns(conn, table):
    return {row[1] for row in conn.execute(f"PRAGMA table_info({table})")}


def migrate(db_path):
    conn = sqlite3.connect(db_path)
    try:
        existing = columns(conn, "leads")
        for name, ddl in LEAD_COLUMNS.items():
            if name not in existing:
                conn.execute(f"ALTER TABLE leads ADD COLUMN {name} {ddl}")
        settings = columns(conn, "site_settings")
        if "analytics_enabled" not in settings:
            conn.execute("ALTER TABLE site_settings ADD COLUMN analytics_enabled INTEGER DEFAULT 1")
        if "analytics_reliable_since" not in settings:
            conn.execute("ALTER TABLE site_settings ADD COLUMN analytics_reliable_since TEXT")
        now = datetime.now(timezone.utc).replace(microsecond=0).isoformat()
        conn.execute("UPDATE site_settings SET analytics_enabled=1 WHERE analytics_enabled IS NULL")
        conn.execute("UPDATE site_settings SET analytics_reliable_since=? WHERE analytics_reliable_since IS NULL OR analytics_reliable_since=''", (now,))
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS analytics_sessions (
            id INTEGER PRIMARY KEY, session_token TEXT UNIQUE, visitor_id TEXT,
            started_at TEXT, last_seen_at TEXT, landing_page TEXT, first_referrer TEXT,
            last_referrer TEXT, country_code TEXT, language_code TEXT, device_type TEXT,
            is_bot INTEGER DEFAULT 0, bot_reason TEXT, pageview_count INTEGER DEFAULT 0,
            engaged_seconds INTEGER DEFAULT 0, max_scroll_depth INTEGER DEFAULT 0,
            visit_number INTEGER DEFAULT 1, first_listing_id INTEGER, last_listing_id INTEGER,
            chatbot_opened INTEGER DEFAULT 0, chatbot_messages INTEGER DEFAULT 0,
            form_started INTEGER DEFAULT 0, whatsapp_clicks INTEGER DEFAULT 0,
            phone_clicks INTEGER DEFAULT 0, email_clicks INTEGER DEFAULT 0,
            lead_count INTEGER DEFAULT 0, user_agent TEXT, ip_hash TEXT
        );
        CREATE TABLE IF NOT EXISTS analytics_events (
            id INTEGER PRIMARY KEY, session_token TEXT, visitor_id TEXT, event_type TEXT,
            path TEXT, endpoint TEXT, listing_id INTEGER, language_code TEXT,
            country_code TEXT, referrer TEXT, event_value TEXT, event_data_json TEXT,
            engaged_seconds INTEGER DEFAULT 0, scroll_depth INTEGER DEFAULT 0,
            is_bot INTEGER DEFAULT 0, bot_reason TEXT, device_type TEXT,
            user_agent TEXT, ip_hash TEXT, created_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_analytics_events_created ON analytics_events(created_at);
        CREATE INDEX IF NOT EXISTS idx_analytics_events_session ON analytics_events(session_token);
        CREATE INDEX IF NOT EXISTS idx_analytics_events_listing ON analytics_events(listing_id);
        CREATE INDEX IF NOT EXISTS idx_analytics_events_type ON analytics_events(event_type);
        CREATE INDEX IF NOT EXISTS idx_analytics_sessions_visitor ON analytics_sessions(visitor_id);
        CREATE VIEW IF NOT EXISTS analytics_human_events AS SELECT * FROM analytics_events WHERE is_bot=0;
        """)
        conn.commit()
    finally:
        conn.close()

if __name__ == "__main__":
    import sys
    migrate(sys.argv[1])