import psycopg2.extras
from datetime import datetime, timedelta
from config import get_local_db_connection

# ───────── DB HELPER ───────── #
def db_get_videos(username: str = None, start=None, end=None):
    """
    Fetch videos, optionally filtered by username and created_at date range.
    `start` / `end` can be date or datetime (UTC). End is inclusive by day.
    """
    conn, cur = get_local_db_connection()
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    query = """
        SELECT video_id, username, site AS platform,
               filepath, size, duration, gender,
               created_at, updated_at, thumbnail
        FROM videos
        WHERE status = 'active'
    """
    params = []

    if username:
        query += " AND username = %s"
        params.append(username)

    if start is not None:
        # Normalize to datetime midnight if date
        if hasattr(start, "hour") is False:
            start = datetime.combine(start, datetime.min.time())
        query += " AND created_at >= %s"
        params.append(start)

    if end is not None:
        # Make end inclusive by bumping 1 day and using '<'
        if hasattr(end, "hour") is False:
            end = datetime.combine(end, datetime.min.time())
        end_exclusive = end + timedelta(days=1)
        query += " AND created_at < %s"
        params.append(end_exclusive)

    query += " ORDER BY created_at DESC"

    cur.execute(query, params)
    rows = cur.fetchall()
    cur.close(); conn.close()
    return [dict(r) for r in rows]

def db_get_video(video_id: str):
    conn, cur = get_local_db_connection()
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("""
        SELECT
            video_id, username, site AS platform,
            filepath, size, duration, gender,
            created_at, updated_at, thumbnail
        FROM videos
        WHERE video_id = %s
    """, (video_id,))
    row = cur.fetchone()
    cur.close(); conn.close()
    return dict(row) if row else None

def db_get_recent(page: int, per_page: int):
    offset = (page - 1) * per_page
    conn, cur = get_local_db_connection()
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    cur.execute("""
        SELECT
            video_id, username, site AS platform,
            filepath, size, duration, gender,
            created_at, updated_at, thumbnail
        FROM videos
        WHERE status != 'missing'
        ORDER BY created_at DESC NULLS LAST, updated_at DESC NULLS LAST
        LIMIT %s OFFSET %s
    """, (per_page, offset))
    rows = [dict(r) for r in cur.fetchall()]

    cur.execute("SELECT COUNT(*) FROM videos WHERE status != 'missing'")
    total = cur.fetchone()[0]

    cur.close(); conn.close()
    return rows, total