#!/usr/bin/env python3
"""Initialize SQLite database for AI use case collector."""
import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), '..', 'data', 'ai_usecases.db')

def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS scenes (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        original_url TEXT UNIQUE,
        source_platform TEXT,
        discovery_date TEXT DEFAULT (datetime('now')),
        categories TEXT,
        tags TEXT,
        key_technologies TEXT,
        innovation_score INTEGER DEFAULT 0,
        is_new INTEGER DEFAULT 1,
        llm_summary TEXT,
        raw_content TEXT,
        last_updated TEXT DEFAULT (datetime('now'))
    )''')
    c.execute('''CREATE TABLE IF NOT EXISTS collection_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        run_date TEXT DEFAULT (datetime('now')),
        source TEXT,
        items_found INTEGER DEFAULT 0,
        items_new INTEGER DEFAULT 0,
        status TEXT
    )''')
    conn.commit()
    conn.close()
    print(f"✅ Database initialized at {os.path.abspath(DB_PATH)}")

if __name__ == '__main__':
    init_db()