import sqlite3
import os

DB_PATH = "/opt/ai-os/products/ceo/brand-monitoring/data/brand-monitoring.db"

def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 1. raw_items
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS raw_items (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        source_id   TEXT NOT NULL,
        url         TEXT NOT NULL UNIQUE,
        title       TEXT,
        snippet     TEXT,
        published   TEXT,  -- ISO 8601
        fetched_at  TEXT,
        brand_id    TEXT,
        lang        TEXT,
        raw_json    TEXT   -- toàn bộ payload gốc
    );
    """)
    
    # 2. mentions
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS mentions (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        raw_item_id     INTEGER REFERENCES raw_items(id),
        brand_id        TEXT NOT NULL,
        url_canonical   TEXT,
        title_clean     TEXT,
        published       TEXT,
        source_tier     TEXT,  -- A, B, C, D
        relevance_score INTEGER,
        sentiment       TEXT,  -- positive, neutral, negative, ambiguous
        is_crisis       INTEGER DEFAULT 0,
        crisis_reason   TEXT,
        dedup_hash      TEXT,
        processed_at    TEXT
    );
    """)
    
    # 3. alerts
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS alerts (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        mention_id  INTEGER REFERENCES mentions(id),
        brand_id    TEXT,
        alert_type  TEXT,  -- hot_alert, review_manual
        sent_at     TEXT,
        channel     TEXT,  -- telegram, email, etc.
        status      TEXT   -- sent, failed, acknowledged
    );
    """)
    
    # 4. reports
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS reports (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        brand_id        TEXT,
        report_type     TEXT,  -- daily, weekly
        period_start    TEXT,
        period_end      TEXT,
        generated_at    TEXT,
        file_path       TEXT,
        hot_alert_count INTEGER,
        negative_count  INTEGER,
        neutral_count   INTEGER,
        positive_count  INTEGER
    );
    """)
    
    conn.commit()
    conn.close()
    print("Database initialized successfully at:", DB_PATH)

if __name__ == "__main__":
    init_db()