"""
Storage Layer — SQLite database for JARVIS state tracking.
Tracks: comments posted, opportunities found, daily metrics.
"""

import sqlite3
import os
import json
from datetime import datetime, timedelta
from config import DB_PATH


def get_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    _init_tables(conn)
    return conn


def _init_tables(conn):
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS comments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            post_id TEXT NOT NULL,
            subreddit TEXT NOT NULL,
            post_title TEXT,
            post_url TEXT,
            comment_text TEXT,
            is_brand_mention INTEGER DEFAULT 0,
            karma_earned INTEGER DEFAULT 0,
            status TEXT DEFAULT 'drafted',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            posted_at TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS opportunities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            post_id TEXT UNIQUE NOT NULL,
            subreddit TEXT NOT NULL,
            title TEXT,
            url TEXT,
            author TEXT,
            score INTEGER DEFAULT 0,
            num_comments INTEGER DEFAULT 0,
            opportunity_score REAL DEFAULT 0,
            intent_level TEXT,
            matched_keywords TEXT,
            brand_mention_worthy INTEGER DEFAULT 0,
            status TEXT DEFAULT 'new',
            discovered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            post_created_at TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_comments_date ON comments(created_at);
        CREATE INDEX IF NOT EXISTS idx_comments_sub ON comments(subreddit);
        CREATE INDEX IF NOT EXISTS idx_opps_status ON opportunities(status);
        CREATE INDEX IF NOT EXISTS idx_opps_score ON opportunities(opportunity_score DESC);
    """)
    conn.commit()


# ── Opportunity operations ──────────────────────────────────

def save_opportunity(opp: dict):
    conn = get_db()
    try:
        conn.execute("""
            INSERT INTO opportunities 
                (post_id, subreddit, title, url, author, score, num_comments,
                 opportunity_score, intent_level, matched_keywords, 
                 brand_mention_worthy, post_created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(post_id) DO UPDATE SET
                score = excluded.score,
                num_comments = excluded.num_comments,
                opportunity_score = excluded.opportunity_score
        """, (
            opp["post_id"], opp["subreddit"], opp["title"], opp["url"],
            opp.get("author", ""), opp.get("score", 0), opp.get("num_comments", 0),
            opp.get("opportunity_score", 0), opp.get("intent_level", "low"),
            json.dumps(opp.get("matched_keywords", [])),
            1 if opp.get("brand_mention_worthy") else 0,
            opp.get("post_created_at")
        ))
        conn.commit()
    finally:
        conn.close()


def get_opportunities(status="new", limit=20):
    conn = get_db()
    try:
        rows = conn.execute("""
            SELECT * FROM opportunities 
            WHERE status = ? 
            ORDER BY opportunity_score DESC 
            LIMIT ?
        """, (status, limit)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def update_opportunity_status(post_id, status):
    conn = get_db()
    try:
        conn.execute("UPDATE opportunities SET status = ? WHERE post_id = ?", (status, post_id))
        conn.commit()
    finally:
        conn.close()


def is_already_found(post_id):
    conn = get_db()
    try:
        row = conn.execute("SELECT 1 FROM opportunities WHERE post_id = ?", (post_id,)).fetchone()
        return row is not None
    finally:
        conn.close()


# ── Comment operations ──────────────────────────────────────

def save_comment(post_id, subreddit, post_title, post_url, comment_text, is_brand_mention=False, status="drafted"):
    conn = get_db()
    try:
        conn.execute("""
            INSERT INTO comments 
                (post_id, subreddit, post_title, post_url, comment_text, 
                 is_brand_mention, status, posted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            post_id, subreddit, post_title, post_url, comment_text,
            1 if is_brand_mention else 0, status,
            datetime.now().isoformat() if status == "posted" else None
        ))
        conn.commit()
    finally:
        conn.close()


def mark_comment_posted(post_id):
    conn = get_db()
    try:
        conn.execute("""
            UPDATE comments SET status = 'posted', posted_at = ? 
            WHERE post_id = ? AND status = 'drafted'
        """, (datetime.now().isoformat(), post_id))
        update_opportunity_status(post_id, "engaged")
        conn.commit()
    finally:
        conn.close()


def has_commented_on(post_id):
    conn = get_db()
    try:
        row = conn.execute(
            "SELECT 1 FROM comments WHERE post_id = ? AND status = 'posted'", (post_id,)
        ).fetchone()
        return row is not None
    finally:
        conn.close()


# ── Stats operations ────────────────────────────────────────

def get_today_stats():
    conn = get_db()
    today = datetime.now().strftime("%Y-%m-%d")
    try:
        total = conn.execute(
            "SELECT COUNT(*) FROM comments WHERE status='posted' AND DATE(posted_at) = ?", (today,)
        ).fetchone()[0]

        brand = conn.execute(
            "SELECT COUNT(*) FROM comments WHERE status='posted' AND is_brand_mention=1 AND DATE(posted_at) = ?", (today,)
        ).fetchone()[0]

        subs = conn.execute(
            "SELECT DISTINCT subreddit FROM comments WHERE status='posted' AND DATE(posted_at) = ?", (today,)
        ).fetchall()

        return {
            "date": today,
            "total_comments": total,
            "brand_mentions": brand,
            "subs_active": [r[0] for r in subs],
            "helpful_ratio": (total - brand) / brand if brand > 0 else total,
        }
    finally:
        conn.close()


def get_all_time_stats():
    conn = get_db()
    try:
        total = conn.execute("SELECT COUNT(*) FROM comments WHERE status='posted'").fetchone()[0]
        brand = conn.execute("SELECT COUNT(*) FROM comments WHERE status='posted' AND is_brand_mention=1").fetchone()[0]
        
        sub_breakdown = conn.execute("""
            SELECT subreddit, COUNT(*) as cnt, MAX(posted_at) as last_active
            FROM comments WHERE status='posted'
            GROUP BY subreddit ORDER BY cnt DESC
        """).fetchall()

        return {
            "total_comments": total,
            "brand_mentions": brand,
            "helpful_ratio": (total - brand) / brand if brand > 0 else total,
            "sub_breakdown": [dict(r) for r in sub_breakdown],
        }
    finally:
        conn.close()


def get_sub_last_activity():
    conn = get_db()
    try:
        rows = conn.execute("""
            SELECT subreddit, MAX(posted_at) as last_active, COUNT(*) as total
            FROM comments WHERE status='posted'
            GROUP BY subreddit
        """).fetchall()
        return {r["subreddit"]: {"last_active": r["last_active"], "total": r["total"]} for r in rows}
    finally:
        conn.close()


def get_recent_comments(limit=10):
    conn = get_db()
    try:
        rows = conn.execute("""
            SELECT * FROM comments WHERE status='posted'
            ORDER BY posted_at DESC LIMIT ?
        """, (limit,)).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()
