import os
import json
from typing import List, Optional, Dict, Any

import psycopg2
from psycopg2.extras import RealDictCursor
from dotenv import load_dotenv
from pathlib import Path

# Load .env from the backend folder explicitly
BASE_DIR = Path(__file__).resolve().parent
load_dotenv(BASE_DIR / ".env")

# Local/dev: DATABASE_URL
DATABASE_URL = os.getenv("DATABASE_URL")

# K8s: explicit env vars from Secret
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")


def _build_dsn() -> str:
    # 1. אם יש DATABASE_URL – משתמשים בזה (לוקאל)
    if DATABASE_URL:
        print("[DB] Using DATABASE_URL:", DATABASE_URL.replace(DB_PASSWORD or "", "***") if DB_PASSWORD else DATABASE_URL)
        return DATABASE_URL

    # 2. אחרת – עובדים עם DB_HOST/DB_* (בקוברנטיס)
    if DB_HOST and DB_NAME and DB_USER and DB_PASSWORD:
        dsn = (
            f"dbname={DB_NAME} user={DB_USER} password={DB_PASSWORD} "
            f"host={DB_HOST} port={DB_PORT}"
        )
        print("[DB] Using explicit env vars DSN (masked):",
              f"dbname={DB_NAME} user={DB_USER} password=*** host={DB_HOST} port={DB_PORT}")
        return dsn

    raise RuntimeError(
        "No DB configuration found. Set DATABASE_URL or DB_HOST/DB_NAME/DB_USER/DB_PASSWORD."
    )


def get_conn():
    dsn = _build_dsn()
    return psycopg2.connect(dsn, cursor_factory=RealDictCursor)


def list_recipes_db(user_id: Optional[int] = None) -> List[Dict[str, Any]]:
    """List recipes visible to the user. If user_id is None, only show public recipes."""
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            if user_id is None:
                # Not authenticated - only public recipes
                cur.execute(
                    """
                    SELECT r.id, r.name, r.meal_type, r.time_minutes,
                           r.tags, r.ingredients, r.steps, r.image, r.made_by, r.user_id,
                           r.visibility, u.display_name as owner_display_name
                    FROM recipes r
                    LEFT JOIN users u ON r.user_id = u.id
                    WHERE r.visibility = 'public'
                    ORDER BY r.id
                    """
                )
            else:
                # Authenticated - show public, own recipes, friends' recipes
                cur.execute(
                    """
                    SELECT r.id, r.name, r.meal_type, r.time_minutes,
                           r.tags, r.ingredients, r.steps, r.image, r.made_by, r.user_id,
                           r.visibility, u.display_name as owner_display_name
                    FROM recipes r
                    LEFT JOIN users u ON r.user_id = u.id
                    WHERE r.visibility = 'public'
                       OR r.user_id = %s
                       OR (r.visibility = 'friends' AND EXISTS (
                           SELECT 1 FROM friendships f 
                           WHERE f.user_id = %s AND f.friend_id = r.user_id
                       ))
                    ORDER BY r.id
                    """,
                    (user_id, user_id)
                )
            rows = cur.fetchall()
        return rows
    finally:
        conn.close()

def update_recipe_db(recipe_id: int, recipe_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    """
    עדכון מתכון קיים לפי id.
    recipe_data: name, meal_type, time_minutes, tags, ingredients, steps, image, made_by, visibility
    """
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE recipes
                SET name = %s,
                    meal_type = %s,
                    time_minutes = %s,
                    tags = %s,
                    ingredients = %s,
                    steps = %s,
                    image = %s,
                    made_by = %s,
                    visibility = %s
                WHERE id = %s
                RETURNING id, name, meal_type, time_minutes, tags, ingredients, steps, image, made_by, user_id, visibility
                """,
                (
                    recipe_data["name"],
                    recipe_data["meal_type"],
                    recipe_data["time_minutes"],
                    json.dumps(recipe_data.get("tags", [])),
                    json.dumps(recipe_data.get("ingredients", [])),
                    json.dumps(recipe_data.get("steps", [])),
                    recipe_data.get("image"),
                    recipe_data.get("made_by"),
                    recipe_data.get("visibility", "public"),
                    recipe_id,
                ),
            )
            row = cur.fetchone()
        conn.commit()
        return row
    finally:
        conn.close()


def delete_recipe_db(recipe_id: int) -> bool:
    """
    מחיקת מתכון לפי id. מחזיר True אם נמחק, False אם לא נמצא.
    """
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                DELETE FROM recipes
                WHERE id = %s
                """,
                (recipe_id,),
            )
            deleted = cur.rowcount > 0
        conn.commit()
        return deleted
    finally:
        conn.close()


def create_recipe_db(recipe_data: Dict[str, Any]) -> Dict[str, Any]:
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO recipes (name, meal_type, time_minutes, tags, ingredients, steps, image, made_by, user_id, visibility)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id, name, meal_type, time_minutes, tags, ingredients, steps, image, made_by, user_id, visibility
                """,
                (
                    recipe_data["name"],
                    recipe_data["meal_type"],
                    recipe_data["time_minutes"],
                    json.dumps(recipe_data.get("tags", [])),
                    json.dumps(recipe_data.get("ingredients", [])),
                    json.dumps(recipe_data.get("steps", [])),
                    recipe_data.get("image"),
                    recipe_data.get("made_by"),
                    recipe_data.get("user_id"),
                    recipe_data.get("visibility", "public"),
                ),
            )
            row = cur.fetchone()
        conn.commit()
        return row
    finally:
        conn.close()


def get_recipes_by_filters_db(
    meal_type: Optional[str],
    max_time: Optional[int],
    user_id: Optional[int] = None,
) -> List[Dict[str, Any]]:
    conn = get_conn()
    try:
        query = """
            SELECT r.id, r.name, r.meal_type, r.time_minutes,
                   r.tags, r.ingredients, r.steps, r.image, r.made_by, r.user_id,
                   r.visibility, u.display_name as owner_display_name
            FROM recipes r
            LEFT JOIN users u ON r.user_id = u.id
            WHERE 1=1
        """
        params: List = []

        # Visibility filter
        if user_id is None:
            query += " AND r.visibility = 'public'"
        else:
            query += """ AND (
                r.visibility = 'public'
                OR r.user_id = %s
                OR (r.visibility = 'friends' AND EXISTS (
                    SELECT 1 FROM friendships f 
                    WHERE f.user_id = %s AND f.friend_id = r.user_id
                ))
            )"""
            params.extend([user_id, user_id])

        if meal_type:
            query += " AND r.meal_type = %s"
            params.append(meal_type.lower())

        if max_time:
            query += " AND r.time_minutes <= %s"
            params.append(max_time)

        with conn.cursor() as cur:
            cur.execute(query, params)
            rows = cur.fetchall()

        return rows
    finally:
        conn.close()