my-recipes/backend/db_utils.py

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() -> List[Dict[str, Any]]:
    conn = get_conn()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT id, name, meal_type, time_minutes,
                       tags, ingredients, steps, image, made_by
                FROM recipes
                ORDER BY 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
    """
    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
                WHERE id = %s
                RETURNING id, name, meal_type, time_minutes, tags, ingredients, steps, image, made_by
                """,
                (
                    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_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)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id, name, meal_type, time_minutes, tags, ingredients, steps, image, made_by
                """,
                (
                    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"),
                ),
            )
            row = cur.fetchone()
        conn.commit()
        return row
    finally:
        conn.close()


def get_recipes_by_filters_db(
    meal_type: Optional[str],
    max_time: Optional[int],
) -> List[Dict[str, Any]]:
    conn = get_conn()
    try:
        query = """
            SELECT id, name, meal_type, time_minutes,
                   tags, ingredients, steps, image, made_by
            FROM recipes
            WHERE 1=1
        """
        params: List = []

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

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

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

        return rows
    finally:
        conn.close()