"""
Database operations for opportunities table.

This module handles all DB interactions for persisting Layer 3/3.5/4 analysis results.
Designed to be imported into analyze.py and video_generation.py.
"""

import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from typing import Dict, Any, Optional, List

DB_PATH = Path(__file__).parent / "output" / "trends_history.db"


def run_migration():
    """Run the opportunities table migration if not already applied."""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Check if opportunities table exists
    cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table' AND name='opportunities'
    """)
    if cursor.fetchone():
        conn.close()
        return False  # Already migrated

    # Read and execute migration
    migration_path = Path(__file__).parent / "migrations" / "001_add_opportunities_table.sql"
    if migration_path.exists():
        migration_sql = migration_path.read_text()
        # SQLite doesn't support multiple statements in execute(), split them
        for statement in migration_sql.split(';'):
            # Remove comment lines and get actual SQL
            lines = statement.split('\n')
            code_lines = [l for l in lines if l.strip() and not l.strip().startswith('--')]
            clean_statement = '\n'.join(code_lines).strip()

            if clean_statement:
                try:
                    cursor.execute(clean_statement)
                except sqlite3.OperationalError as e:
                    # Ignore "duplicate column" errors from ALTER TABLE
                    if "duplicate column" not in str(e).lower():
                        raise
        conn.commit()
        print("[DB] Migration 001 applied: opportunities table created")
    else:
        raise FileNotFoundError(f"Migration file not found: {migration_path}")

    conn.close()
    return True


# =============================================================================
# CHANNEL ANALYSIS RUNS
# =============================================================================

def create_analysis_run(
    channel_id: str,
    items_analyzed: int = 0,
    clusters_formed: int = 0,
    layer2_passed: int = 0
) -> int:
    """
    Create a new channel_analysis_runs record.

    Returns the new run ID.
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    cursor.execute("""
        INSERT INTO channel_analysis_runs
        (channel_id, run_date, items_analyzed, clusters_formed, layer2_passed, status)
        VALUES (?, ?, ?, ?, ?, 'running')
    """, (channel_id, now, items_analyzed, clusters_formed, layer2_passed))

    run_id = cursor.lastrowid
    conn.commit()
    conn.close()

    return run_id


def update_analysis_run(
    run_id: int,
    opportunities_found: int = None,
    status: str = None,
    error_message: str = None
):
    """Update an existing channel_analysis_runs record."""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    updates = []
    params = []

    if opportunities_found is not None:
        updates.append("opportunities_found = ?")
        params.append(opportunities_found)

    if status is not None:
        updates.append("status = ?")
        params.append(status)
        if status in ('completed', 'failed'):
            updates.append("completed_at = ?")
            params.append(datetime.now(timezone.utc).isoformat())

    if error_message is not None:
        updates.append("error_message = ?")
        params.append(error_message)

    if updates:
        params.append(run_id)
        cursor.execute(
            f"UPDATE channel_analysis_runs SET {', '.join(updates)} WHERE id = ?",
            params
        )
        conn.commit()

    conn.close()


def get_latest_analysis_run(channel_id: str) -> Optional[Dict[str, Any]]:
    """Get the most recent analysis run for a channel."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute("""
        SELECT * FROM channel_analysis_runs
        WHERE channel_id = ?
        ORDER BY id DESC LIMIT 1
    """, (channel_id,))

    row = cursor.fetchone()
    conn.close()

    if row:
        return dict(row)
    return None


# =============================================================================
# OPPORTUNITIES - INSERT (Layer 3)
# =============================================================================

def insert_opportunity(
    channel_id: str,
    analysis_run_id: int,
    opportunity: Dict[str, Any],
    prompt_version: str = "v3"
) -> int:
    """
    Insert a new opportunity from Layer 3 analysis.

    Returns the new opportunity ID.
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    # Extract core fields
    cluster_id = opportunity.get("cluster_id")
    theme = opportunity.get("theme", "")
    suggested_title = opportunity.get("suggested_title", "")
    source_item_count = opportunity.get("topic_count", len(opportunity.get("topics_in_cluster", [])))
    verdict = opportunity.get("verdict", "")
    weighted_score = opportunity.get("weighted_score", 0)

    # Extract scores breakdown
    scores = opportunity.get("scores", {})
    scores_json = json.dumps(scores) if scores else None

    # Bundle remaining analysis fields into analysis_json
    analysis_fields = [
        "confusion_analysis", "existing_content", "why_existing_fails",
        "missing_angle", "target_audience", "opening_hook",
        "critical_success_factor", "structure", "verdict_reasoning",
        "topics_in_cluster", "cluster_topics", "sources", "merged_from"
    ]
    analysis_data = {k: opportunity.get(k) for k in analysis_fields if k in opportunity}
    analysis_json = json.dumps(analysis_data) if analysis_data else None

    cursor.execute("""
        INSERT INTO opportunities (
            channel_id, analysis_run_id, cluster_id,
            theme, suggested_title, source_item_count, verdict, weighted_score,
            scores_json, analysis_json,
            research_status, video_gen_status,
            prompt_version, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?)
    """, (
        channel_id, analysis_run_id, cluster_id,
        theme, suggested_title, source_item_count, verdict, weighted_score,
        scores_json, analysis_json,
        prompt_version, now
    ))

    opp_id = cursor.lastrowid
    conn.commit()
    conn.close()

    return opp_id


def insert_opportunities_batch(
    channel_id: str,
    analysis_run_id: int,
    opportunities: List[Dict[str, Any]],
    prompt_version: str = "v3"
) -> List[int]:
    """
    Insert multiple opportunities in a single transaction.

    Returns list of new opportunity IDs.
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()
    opp_ids = []

    analysis_fields = [
        "confusion_analysis", "existing_content", "why_existing_fails",
        "missing_angle", "target_audience", "opening_hook",
        "critical_success_factor", "structure", "verdict_reasoning",
        "topics_in_cluster", "cluster_topics", "sources", "merged_from"
    ]

    for opp in opportunities:
        cluster_id = opp.get("cluster_id")
        theme = opp.get("theme", "")
        suggested_title = opp.get("suggested_title", "")
        source_item_count = opp.get("topic_count", len(opp.get("topics_in_cluster", [])))
        verdict = opp.get("verdict", "")
        weighted_score = opp.get("weighted_score", 0)

        scores = opp.get("scores", {})
        scores_json = json.dumps(scores) if scores else None

        analysis_data = {k: opp.get(k) for k in analysis_fields if k in opp}
        analysis_json = json.dumps(analysis_data) if analysis_data else None

        cursor.execute("""
            INSERT INTO opportunities (
                channel_id, analysis_run_id, cluster_id,
                theme, suggested_title, source_item_count, verdict, weighted_score,
                scores_json, analysis_json,
                research_status, video_gen_status,
                prompt_version, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?)
        """, (
            channel_id, analysis_run_id, cluster_id,
            theme, suggested_title, source_item_count, verdict, weighted_score,
            scores_json, analysis_json,
            prompt_version, now
        ))

        opp_ids.append(cursor.lastrowid)

    conn.commit()
    conn.close()

    return opp_ids


# =============================================================================
# OPPORTUNITIES - UPDATE (Layer 3.5 Research)
# =============================================================================

def update_opportunity_research(
    opportunity_id: int,
    research_status: str,
    research_data: Dict[str, Any] = None
):
    """
    Update an opportunity with Layer 3.5 research results.

    Args:
        opportunity_id: DB ID of the opportunity
        research_status: 'completed', 'failed', or 'skipped'
        research_data: Dict containing research_report_content, metadata, etc.
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    research_json = json.dumps(research_data) if research_data else None

    cursor.execute("""
        UPDATE opportunities
        SET research_status = ?, research_json = ?, updated_at = ?
        WHERE id = ?
    """, (research_status, research_json, now, opportunity_id))

    conn.commit()
    conn.close()


def update_opportunity_research_by_cluster(
    analysis_run_id: int,
    cluster_id: int,
    research_status: str,
    research_data: Dict[str, Any] = None
):
    """
    Update opportunity research by cluster_id (for when we don't have DB ID).

    This is used when research runs after initial Layer 3 insert.
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    research_json = json.dumps(research_data) if research_data else None

    cursor.execute("""
        UPDATE opportunities
        SET research_status = ?, research_json = ?, updated_at = ?
        WHERE analysis_run_id = ? AND cluster_id = ?
    """, (research_status, research_json, now, analysis_run_id, cluster_id))

    conn.commit()
    conn.close()


# =============================================================================
# OPPORTUNITIES - UPDATE (Layer 4 Video Generation)
# =============================================================================

def update_opportunity_video_gen(
    opportunity_id: int,
    video_gen_status: str,
    video_concepts: Dict[str, Any] = None,
    production_spec: Dict[str, Any] = None,
    asset_specs: Dict[str, Any] = None
):
    """
    Update an opportunity with Layer 4 video generation results.

    Args:
        opportunity_id: DB ID of the opportunity
        video_gen_status: 'completed', 'failed', or 'skipped'
        video_concepts: Layer 4a output
        production_spec: Layer 4b output
        asset_specs: Layer 4c output
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    concepts_json = json.dumps(video_concepts) if video_concepts else None
    spec_json = json.dumps(production_spec) if production_spec else None
    assets_json = json.dumps(asset_specs) if asset_specs else None

    cursor.execute("""
        UPDATE opportunities
        SET video_gen_status = ?,
            video_concepts_json = ?,
            production_spec_json = ?,
            asset_specs_json = ?,
            updated_at = ?
        WHERE id = ?
    """, (video_gen_status, concepts_json, spec_json, assets_json, now, opportunity_id))

    conn.commit()
    conn.close()


def update_opportunity_video_gen_by_cluster(
    analysis_run_id: int,
    cluster_id: int,
    video_gen_status: str,
    video_concepts: Dict[str, Any] = None,
    production_spec: Dict[str, Any] = None,
    asset_specs: Dict[str, Any] = None
):
    """
    Update opportunity video gen by cluster_id (for when we don't have DB ID).
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    now = datetime.now(timezone.utc).isoformat()

    concepts_json = json.dumps(video_concepts) if video_concepts else None
    spec_json = json.dumps(production_spec) if production_spec else None
    assets_json = json.dumps(asset_specs) if asset_specs else None

    cursor.execute("""
        UPDATE opportunities
        SET video_gen_status = ?,
            video_concepts_json = ?,
            production_spec_json = ?,
            asset_specs_json = ?,
            updated_at = ?
        WHERE analysis_run_id = ? AND cluster_id = ?
    """, (video_gen_status, concepts_json, spec_json, assets_json, now, analysis_run_id, cluster_id))

    conn.commit()
    conn.close()


# =============================================================================
# OPPORTUNITIES - QUERY
# =============================================================================

def get_opportunities_for_run(
    analysis_run_id: int,
    verdict_filter: List[str] = None
) -> List[Dict[str, Any]]:
    """Get all opportunities from a specific analysis run."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = "SELECT * FROM opportunities WHERE analysis_run_id = ?"
    params = [analysis_run_id]

    if verdict_filter:
        placeholders = ','.join('?' * len(verdict_filter))
        query += f" AND verdict IN ({placeholders})"
        params.extend(verdict_filter)

    query += " ORDER BY weighted_score DESC"

    cursor.execute(query, params)
    rows = cursor.fetchall()
    conn.close()

    return [dict(row) for row in rows]


def get_latest_opportunities(
    channel_id: str,
    limit: int = 50,
    verdict_filter: List[str] = None
) -> List[Dict[str, Any]]:
    """Get opportunities from the latest analysis run for a channel."""
    latest_run = get_latest_analysis_run(channel_id)
    if not latest_run:
        return []

    return get_opportunities_for_run(
        latest_run['id'],
        verdict_filter=verdict_filter
    )


def get_opportunity_by_id(opportunity_id: int) -> Optional[Dict[str, Any]]:
    """Get a single opportunity by its DB ID."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM opportunities WHERE id = ?", (opportunity_id,))
    row = cursor.fetchone()
    conn.close()

    if row:
        result = dict(row)
        # Parse JSON fields
        for json_field in ['scores_json', 'analysis_json', 'research_json',
                           'video_concepts_json', 'production_spec_json', 'asset_specs_json']:
            if result.get(json_field):
                try:
                    result[json_field] = json.loads(result[json_field])
                except json.JSONDecodeError:
                    pass
        return result
    return None


def get_opportunities_needing_research(
    channel_id: str = None,
    analysis_run_id: int = None,
    limit: int = 10
) -> List[Dict[str, Any]]:
    """Get opportunities that haven't had research run yet."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = "SELECT * FROM opportunities WHERE research_status = 'pending'"
    params = []

    if channel_id:
        query += " AND channel_id = ?"
        params.append(channel_id)

    if analysis_run_id:
        query += " AND analysis_run_id = ?"
        params.append(analysis_run_id)

    # Prioritize by verdict and score
    query += """
        ORDER BY
            CASE verdict
                WHEN 'HIGH_PRIORITY' THEN 1
                WHEN 'WORTH_MAKING' THEN 2
                WHEN 'CONDITIONAL' THEN 3
                ELSE 4
            END,
            weighted_score DESC
        LIMIT ?
    """
    params.append(limit)

    cursor.execute(query, params)
    rows = cursor.fetchall()
    conn.close()

    return [dict(row) for row in rows]


def get_opportunities_needing_video_gen(
    channel_id: str = None,
    analysis_run_id: int = None,
    require_research: bool = True,
    limit: int = 10
) -> List[Dict[str, Any]]:
    """Get opportunities that need video generation."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = "SELECT * FROM opportunities WHERE video_gen_status = 'pending'"
    params = []

    if require_research:
        query += " AND research_status = 'completed'"

    if channel_id:
        query += " AND channel_id = ?"
        params.append(channel_id)

    if analysis_run_id:
        query += " AND analysis_run_id = ?"
        params.append(analysis_run_id)

    query += """
        ORDER BY
            CASE verdict
                WHEN 'HIGH_PRIORITY' THEN 1
                WHEN 'WORTH_MAKING' THEN 2
                WHEN 'CONDITIONAL' THEN 3
                ELSE 4
            END,
            weighted_score DESC
        LIMIT ?
    """
    params.append(limit)

    cursor.execute(query, params)
    rows = cursor.fetchall()
    conn.close()

    return [dict(row) for row in rows]


# =============================================================================
# STATISTICS
# =============================================================================

def get_channel_opportunity_stats(channel_id: str) -> Dict[str, Any]:
    """Get statistics for a channel's opportunities."""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Total opportunities
    cursor.execute(
        "SELECT COUNT(*) FROM opportunities WHERE channel_id = ?",
        (channel_id,)
    )
    total = cursor.fetchone()[0]

    # By verdict
    cursor.execute("""
        SELECT verdict, COUNT(*)
        FROM opportunities
        WHERE channel_id = ?
        GROUP BY verdict
    """, (channel_id,))
    by_verdict = dict(cursor.fetchall())

    # By research status
    cursor.execute("""
        SELECT research_status, COUNT(*)
        FROM opportunities
        WHERE channel_id = ?
        GROUP BY research_status
    """, (channel_id,))
    by_research = dict(cursor.fetchall())

    # By video gen status
    cursor.execute("""
        SELECT video_gen_status, COUNT(*)
        FROM opportunities
        WHERE channel_id = ?
        GROUP BY video_gen_status
    """, (channel_id,))
    by_video = dict(cursor.fetchall())

    # Analysis runs count
    cursor.execute(
        "SELECT COUNT(*) FROM channel_analysis_runs WHERE channel_id = ?",
        (channel_id,)
    )
    runs_count = cursor.fetchone()[0]

    conn.close()

    return {
        "total_opportunities": total,
        "by_verdict": by_verdict,
        "by_research_status": by_research,
        "by_video_gen_status": by_video,
        "analysis_runs": runs_count
    }