"""Grocery list SQLite schema and CRUD.

Tables:
- grocery_list: Committed items from recipes
- recipe_temp_state: Ephemeral toggle state (5-min TTL)
"""

import json
import logging
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timedelta
from pathlib import Path

from icarus.core.config.staging import DATA_DIR

DB_PATH = DATA_DIR / "icarus.db"


# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------

SCHEMA = """
CREATE TABLE IF NOT EXISTS grocery_list (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item TEXT NOT NULL,
    normalized_item TEXT,
    quantity TEXT,
    recipe_source TEXT,
    recipe_url TEXT,
    requested_by TEXT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending'
);

CREATE TABLE IF NOT EXISTS recipe_temp_state (
    recipe_id TEXT PRIMARY KEY,
    user_id INTEGER,
    chat_id INTEGER,
    message_id INTEGER,
    title TEXT,
    source_url TEXT,
    ingredients_json TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_grocery_status ON grocery_list(status);
CREATE INDEX IF NOT EXISTS idx_grocery_added_by ON grocery_list(requested_by);
CREATE INDEX IF NOT EXISTS idx_grocery_recipe ON grocery_list(recipe_source);
CREATE INDEX IF NOT EXISTS idx_temp_expires ON recipe_temp_state(expires_at);
"""


def _get_connection() -> sqlite3.Connection:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Initialize tables. Called on startup."""
    with _get_connection() as conn:
        conn.executescript(SCHEMA)


def _clean_expired():
    """Remove expired temp state entries."""
    with _get_connection() as conn:
        conn.execute(
            "DELETE FROM recipe_temp_state WHERE expires_at < datetime('now')"
        )
        conn.commit()


# ---------------------------------------------------------------------------
# Grocery List CRUD
# ---------------------------------------------------------------------------

def add_items(items: list[dict], recipe_source: str, recipe_url: str | None, requested_by: str) -> int:
    """Add items to grocery list.

    Args:
        items: List of dicts with 'text', optionally 'quantity'
        recipe_source: Recipe title
        recipe_url: Source URL
        requested_by: User name/ID

    Returns:
        Number of items added.
    """
    with _get_connection() as conn:
        for item in items:
            conn.execute(
                """
                INSERT INTO grocery_list (item, normalized_item, quantity, recipe_source, recipe_url, requested_by)
                VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    item["text"],
                    _normalize_item(item["text"]),
                    item.get("quantity"),
                    recipe_source,
                    recipe_url,
                    requested_by,
                ),
            )
        conn.commit()
    return len(items)


def list_items(status: str | None = None, requested_by: str | None = None) -> list[dict]:
    """List grocery items with optional filters."""
    query = "SELECT * FROM grocery_list WHERE 1=1"
    params = []
    if status:
        query += " AND status = ?"
        params.append(status)
    if requested_by:
        query += " AND requested_by = ?"
        params.append(requested_by)
    query += " ORDER BY added_at DESC"

    with _get_connection() as conn:
        rows = conn.execute(query, params).fetchall()
        return [dict(r) for r in rows]


def update_status(item_id: int, status: str) -> bool:
    """Update item status."""
    valid = {"pending", "in_cart", "purchased", "skipped"}
    if status not in valid:
        return False
    with _get_connection() as conn:
        cur = conn.execute(
            "UPDATE grocery_list SET status = ? WHERE id = ?",
            (status, item_id),
        )
        conn.commit()
        return cur.rowcount > 0


def clear_list(requested_by: str | None = None) -> int:
    """Clear items. If requested_by provided, only their items."""
    with _get_connection() as conn:
        if requested_by:
            cur = conn.execute(
                "DELETE FROM grocery_list WHERE requested_by = ?",
                (requested_by,),
            )
        else:
            cur = conn.execute("DELETE FROM grocery_list")
        conn.commit()
        return cur.rowcount


def _normalize_item(text: str) -> str:
    """Normalize item text for deduplication."""
    import re
    # Remove quantities, units, adjectives
    text = re.sub(r"^\d+[\d\s./-]*\s*(cups?|tbsp|tsp|lbs?|oz|g|kg|ml|l|large|small|medium)\s*", "", text, flags=re.I)
    text = re.sub(r"\([^)]*\)", "", text)
    text = re.sub(r"\s+", " ", text).strip().lower()
    return text


# ---------------------------------------------------------------------------
# Temp State CRUD
# ---------------------------------------------------------------------------

def save_temp_state(
    recipe_id: str,
    user_id: int,
    chat_id: int,
    message_id: int,
    title: str,
    source_url: str | None,
    ingredients: list[dict],
) -> None:
    """Save temporary toggle state with 5-minute TTL."""
    _clean_expired()
    expires = datetime.now() + timedelta(minutes=5)

    with _get_connection() as conn:
        conn.execute(
            """
            INSERT OR REPLACE INTO recipe_temp_state
            (recipe_id, user_id, chat_id, message_id, title, source_url, ingredients_json, expires_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                recipe_id,
                user_id,
                chat_id,
                message_id,
                title,
                source_url,
                json.dumps(ingredients),
                expires.isoformat(),
            ),
        )
        conn.commit()


def get_temp_state(recipe_id: str) -> dict | None:
    """Retrieve temp state if not expired."""
    _clean_expired()

    with _get_connection() as conn:
        row = conn.execute(
            "SELECT * FROM recipe_temp_state WHERE recipe_id = ? AND expires_at > datetime('now')",
            (recipe_id,),
        ).fetchone()

        if not row:
            return None

        return {
            "recipe_id": row["recipe_id"],
            "user_id": row["user_id"],
            "chat_id": row["chat_id"],
            "message_id": row["message_id"],
            "title": row["title"],
            "source_url": row["source_url"],
            "ingredients": json.loads(row["ingredients_json"]),
        }


def update_temp_state(recipe_id: str, ingredients: list[dict]) -> None:
    """Update ingredients in temp state."""
    with _get_connection() as conn:
        conn.execute(
            "UPDATE recipe_temp_state SET ingredients_json = ? WHERE recipe_id = ?",
            (json.dumps(ingredients), recipe_id),
        )
        conn.commit()


def delete_temp_state(recipe_id: str) -> None:
    """Delete temp state."""
    with _get_connection() as conn:
        conn.execute("DELETE FROM recipe_temp_state WHERE recipe_id = ?", (recipe_id,))
        conn.commit()