"""
Tests for Nutricore database initialization and data
"""

import pytest
import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
import database


@pytest.fixture(autouse=True)
def setup_db(tmp_path):
    database.DB_PATH = str(tmp_path / "test.db")
    database.init_db()
    yield


def test_db_has_12_products():
    conn = database.get_db()
    count = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
    conn.close()
    assert count == 12


def test_db_has_3_categories():
    conn = database.get_db()
    cats = conn.execute("SELECT DISTINCT category FROM products").fetchall()
    conn.close()
    assert len(cats) == 3


def test_db_has_fruit_powders():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE category = 'Fruit Powders'").fetchall()
    conn.close()
    assert len(rows) == 5


def test_db_has_vegetable_powders():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE category = 'Vegetable Powders'").fetchall()
    conn.close()
    assert len(rows) == 4


def test_db_has_green_leaf_powders():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE category = 'Green Leaf Powders'").fetchall()
    conn.close()
    assert len(rows) == 3


def test_products_have_prices():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE price IS NULL OR price <= 0").fetchall()
    conn.close()
    assert len(rows) == 0


def test_products_have_images():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE image IS NULL OR image = ''").fetchall()
    conn.close()
    assert len(rows) == 0


def test_products_have_health_goals():
    conn = database.get_db()
    rows = conn.execute("SELECT * FROM products WHERE health_goals IS NULL OR health_goals = ''").fetchall()
    conn.close()
    assert len(rows) == 0


def test_moringa_is_superfood():
    conn = database.get_db()
    row = conn.execute("SELECT * FROM products WHERE name = 'Moringa Powder'").fetchone()
    conn.close()
    assert row is not None
    assert row["price"] == 900
    assert "immunity" in row["health_goals"]


def test_banana_powder_energy():
    conn = database.get_db()
    row = conn.execute("SELECT * FROM products WHERE name = 'Banana Powder'").fetchone()
    conn.close()
    assert row is not None
    assert row["price"] == 850
    assert "energy" in row["health_goals"]


def test_orders_table_exists():
    conn = database.get_db()
    row = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='orders'").fetchone()
    conn.close()
    assert row is not None


def test_delivery_info_has_major_cities():
    assert "lahore" in database.DELIVERY_INFO
    assert "karachi" in database.DELIVERY_INFO
    assert "islamabad" in database.DELIVERY_INFO


def test_recipes_exist_for_all_products():
    from database import RECIPES
    assert len(RECIPES) == 12
    for product_id in range(1, 13):
        assert product_id in RECIPES, f"No recipe for product {product_id}"