"""
models.py — Database schema (init_db) and seed data (seed_db).
"""
import sys
from db import get_db, q, run, today, now, hash_pw


# ── DB Schema ─────────────────────────────────────────────────────────────────
def init_db():
    db = get_db()
    db.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'cashier',
        line TEXT,
        outlet_id INTEGER,
        pay_period TEXT DEFAULT 'monthly',
        pay_amount REAL DEFAULT 0,
        active INTEGER DEFAULT 1,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS outlets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        location TEXT,
        till_number TEXT,
        active INTEGER DEFAULT 1
    );
    CREATE TABLE IF NOT EXISTS ingredients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        unit TEXT NOT NULL,
        cost_per_unit REAL NOT NULL DEFAULT 0,
        reorder_level REAL DEFAULT 5,
        category TEXT DEFAULT 'ingredient'
    );
    CREATE TABLE IF NOT EXISTS stock (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ingredient_id INTEGER NOT NULL,
        quantity REAL DEFAULT 0,
        last_updated TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
    );
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        category TEXT NOT NULL,
        unit TEXT DEFAULT 'portion',
        price REAL NOT NULL,
        active INTEGER DEFAULT 1
    );
    CREATE TABLE IF NOT EXISTS recipes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER NOT NULL,
        ingredient_id INTEGER NOT NULL,
        qty_used REAL NOT NULL,
        yields INTEGER NOT NULL DEFAULT 1,
        note TEXT,
        FOREIGN KEY (product_id) REFERENCES products(id),
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
    );
    CREATE TABLE IF NOT EXISTS production_targets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        product_id INTEGER NOT NULL,
        target_qty INTEGER DEFAULT 0,
        submitted_by INTEGER,
        locked INTEGER DEFAULT 0,
        FOREIGN KEY (product_id) REFERENCES products(id),
        UNIQUE (date, product_id)
    );
    CREATE TABLE IF NOT EXISTS production_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        product_id INTEGER NOT NULL,
        portions_made INTEGER DEFAULT 0,
        finished INTEGER DEFAULT 0,
        wastage INTEGER DEFAULT 0,
        expected_output INTEGER DEFAULT 0,
        recorded_by INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS requisitions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        for_date TEXT NOT NULL,
        requested_by INTEGER NOT NULL,
        req_type TEXT DEFAULT 'kitchen',
        status TEXT DEFAULT 'pending',
        notes TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (requested_by) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS requisition_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        requisition_id INTEGER NOT NULL,
        ingredient_id INTEGER NOT NULL,
        qty_requested REAL NOT NULL,
        FOREIGN KEY (requisition_id) REFERENCES requisitions(id),
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
    );
    CREATE TABLE IF NOT EXISTS stock_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
        outlet_id INTEGER,
        product_id INTEGER NOT NULL,
        event_type TEXT NOT NULL,
        qty_in REAL DEFAULT 0,
        qty_out REAL DEFAULT 0,
        reference TEXT,
        reason TEXT,
        recorded_by INTEGER,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS outlet_receipts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        qty_received REAL NOT NULL,
        received_by INTEGER,
        received_at TEXT DEFAULT CURRENT_TIMESTAMP,
        dispatch_ref TEXT,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS cashier_submissions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        cashier_id INTEGER NOT NULL,
        submitted_at TEXT DEFAULT CURRENT_TIMESTAMP,
        cash_declared REAL DEFAULT 0,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (cashier_id) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS submission_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        submission_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        qty_remaining INTEGER DEFAULT 0,
        FOREIGN KEY (submission_id) REFERENCES cashier_submissions(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS cash_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        event_type TEXT NOT NULL,
        amount REAL NOT NULL,
        note TEXT,
        recorded_by INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id)
    );
    CREATE TABLE IF NOT EXISTS outlet_mpesa (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        amount REAL NOT NULL,
        note TEXT,
        recorded_by INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id)
    );
    CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        category TEXT NOT NULL,
        amount REAL NOT NULL,
        tx_cost REAL DEFAULT 0,
        note TEXT,
        receipt_path TEXT,
        recorded_by INTEGER,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS ingredient_receipts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        ingredient_id INTEGER,
        amount REAL NOT NULL,
        tx_cost REAL DEFAULT 0,
        note TEXT,
        receipt_path TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS mpesa_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER,
        phone TEXT,
        amount REAL NOT NULL,
        mpesa_ref TEXT UNIQUE,
        till_number TEXT,
        sms_status TEXT DEFAULT 'pending',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id)
    );
    CREATE TABLE IF NOT EXISTS clock_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        event_type TEXT NOT NULL,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
    );
    CREATE TABLE IF NOT EXISTS day_snapshots (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER,
        snapshot_type TEXT NOT NULL,
        recorded_by INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        note TEXT,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id)
    );
    CREATE TABLE IF NOT EXISTS sale_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        qty INTEGER NOT NULL DEFAULT 1,
        cashier_id INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS shifts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        cashier_id INTEGER NOT NULL,
        shift_type TEXT DEFAULT 'morning',
        started_at TEXT DEFAULT CURRENT_TIMESTAMP,
        ended_at TEXT,
        handover_note TEXT,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (cashier_id) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS custom_roles (
        role_key TEXT PRIMARY KEY,
        display_name TEXT NOT NULL,
        permissions TEXT DEFAULT '',
        notes TEXT,
        created_by INTEGER,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS customer_orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        outlet_id INTEGER NOT NULL,
        waiter_id INTEGER NOT NULL,
        chef_id INTEGER,
        table_ref TEXT,
        status TEXT DEFAULT 'pending',
        notes TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (outlet_id) REFERENCES outlets(id),
        FOREIGN KEY (waiter_id) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS customer_order_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        qty INTEGER NOT NULL DEFAULT 1,
        notes TEXT,
        FOREIGN KEY (order_id) REFERENCES customer_orders(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );
    CREATE TABLE IF NOT EXISTS inventory_ledger (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        item_id INTEGER NOT NULL,
        item_type TEXT NOT NULL DEFAULT 'product',
        movement_type TEXT NOT NULL,
        quantity REAL NOT NULL,
        unit TEXT,
        reference_type TEXT,
        reference_id INTEGER,
        created_by INTEGER,
        notes TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS waste_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        outlet_id INTEGER,
        item_id INTEGER NOT NULL,
        item_type TEXT NOT NULL DEFAULT 'product',
        qty REAL NOT NULL,
        reason TEXT,
        cost_estimate REAL DEFAULT 0,
        recorded_by INTEGER,
        recorded_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS daily_reconciliation (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        outlet_id INTEGER NOT NULL,
        opening_stock REAL DEFAULT 0,
        received_stock REAL DEFAULT 0,
        remaining_stock REAL DEFAULT 0,
        expected_sold REAL DEFAULT 0,
        cash_declared REAL DEFAULT 0,
        mpesa_total REAL DEFAULT 0,
        total_collected REAL DEFAULT 0,
        variance REAL DEFAULT 0,
        is_locked INTEGER DEFAULT 0,
        locked_by INTEGER,
        locked_at TEXT,
        notes TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(date, outlet_id)
    );
    CREATE TABLE IF NOT EXISTS expense_categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        is_default INTEGER DEFAULT 0
    );
    """)
    # Schema migrations — add columns if they don't exist yet
    try:
        db.execute("ALTER TABLE products ADD COLUMN wastage_threshold_pct REAL DEFAULT 10")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    try:
        db.execute("ALTER TABLE outlet_mpesa ADD COLUMN mpesa_ref TEXT")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    try:
        db.execute("ALTER TABLE production_records ADD COLUMN line TEXT")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # Per-chef production records: add chef_id column so two chefs don't collide
    try:
        db.execute("ALTER TABLE production_records ADD COLUMN chef_id INTEGER")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # Time-slot columns for 5 production windows (3AM, 7AM, 11AM, 3PM, 7PM)
    # ── ingredient_receipts: add missing columns ─────────────────────────────
    for col_def in [
        ('quantity',   'ALTER TABLE ingredient_receipts ADD COLUMN quantity REAL DEFAULT 0'),
        ('supplier',   'ALTER TABLE ingredient_receipts ADD COLUMN supplier TEXT'),
        ('invoice_no', 'ALTER TABLE ingredient_receipts ADD COLUMN invoice_no TEXT'),
    ]:
        try:
            db.execute(col_def[1]); db.commit()
        except Exception as _mig_e:
            import sys; print(f"MIGRATION ({col_def[0]}): {_mig_e}", file=sys.stderr)

    for slot_col in ('slot_3am','slot_7am','slot_11am','slot_3pm','slot_7pm'):
        try:
            db.execute(f"ALTER TABLE production_records ADD COLUMN {slot_col} INTEGER DEFAULT 0")
            db.commit()
        except Exception as _mig_e:
            import sys; print(f"MIGRATION WARNING ({slot_col}): {_mig_e}", file=sys.stderr)
    # outlet_receipts: track how many were originally dispatched for discrepancy detection
    try:
        db.execute("ALTER TABLE outlet_receipts ADD COLUMN qty_dispatched REAL DEFAULT 0")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # Daily outlet assignments: managers assign cashiers to outlets per day
    try:
        db.execute("""CREATE TABLE IF NOT EXISTS outlet_assignments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            outlet_id INTEGER NOT NULL,
            cashier_id INTEGER NOT NULL,
            assigned_by INTEGER,
            assigned_at TEXT DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(date, outlet_id, cashier_id)
        )""")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # Products: sellable flag — only sellable products appear in dispatch/cashier
    # 'ingredient' category items added to store do NOT appear in dispatch
    # Rule: products table = sellable menu items; ingredients table = raw materials
    # This was always the design; the issue is items added to ingredients table
    # appearing in dispatch. No schema change needed — just UI logic.
    try:
        db.execute("ALTER TABLE requisitions ADD COLUMN reviewed_at TEXT")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    try:
        db.execute("ALTER TABLE requisitions ADD COLUMN reviewed_by INTEGER")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    try:
        db.execute("ALTER TABLE requisitions ADD COLUMN review_note TEXT")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # Audit / activity log table
    try:
        db.execute("""CREATE TABLE IF NOT EXISTS activity_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            action TEXT NOT NULL,
            entity TEXT,
            entity_id INTEGER,
            old_value TEXT,
            new_value TEXT,
            ip TEXT,
            recorded_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )""")
        db.commit()
    except Exception as _mig_e:
        import sys; print(f"MIGRATION WARNING: {_mig_e}", file=sys.stderr)
    # ── login_attempts table (DB-backed, multi-worker safe) ─────────────────
    try:
        db.execute("""CREATE TABLE IF NOT EXISTS login_attempts (
            identifier TEXT PRIMARY KEY,
            attempt_count INTEGER DEFAULT 0,
            window_start REAL,
            locked_until REAL
        )""")
        db.commit()
    except Exception as e:
        import sys; print(f"MIGRATION: login_attempts table: {e}", file=sys.stderr)

    # ── Migration: verbose failure logging (was: silent pass) ─────────────────
    # All subsequent ALTER TABLE migrations log failures to stderr instead of swallowing them.

    # ── Performance indexes ───────────────────────────────────────────────────
    indexes = [
        "CREATE INDEX IF NOT EXISTS idx_orders_status       ON customer_orders(status)",
        "CREATE INDEX IF NOT EXISTS idx_orders_date         ON customer_orders(created_at)",
        "CREATE INDEX IF NOT EXISTS idx_orders_waiter       ON customer_orders(waiter_id)",
        "CREATE INDEX IF NOT EXISTS idx_stock_events_outlet ON stock_events(outlet_id, product_id)",
        "CREATE INDEX IF NOT EXISTS idx_stock_events_date   ON stock_events(timestamp)",
        "CREATE INDEX IF NOT EXISTS idx_users_role          ON users(role)",
        "CREATE INDEX IF NOT EXISTS idx_users_active        ON users(active)",
        "CREATE INDEX IF NOT EXISTS idx_subs_outlet_date    ON cashier_submissions(outlet_id, submitted_at)",
        "CREATE INDEX IF NOT EXISTS idx_req_status          ON requisitions(status)",
        "CREATE INDEX IF NOT EXISTS idx_req_for_date        ON requisitions(for_date)",
        "CREATE INDEX IF NOT EXISTS idx_prod_date_chef      ON production_records(date, chef_id)",
        "CREATE INDEX IF NOT EXISTS idx_ingredients_cat     ON ingredients(category)",
        "CREATE INDEX IF NOT EXISTS idx_products_active     ON products(active)",
        "CREATE INDEX IF NOT EXISTS idx_mpesa_outlet_date   ON mpesa_transactions(outlet_id, created_at)",
        "CREATE INDEX IF NOT EXISTS idx_expenses_date       ON expenses(date)",
        "CREATE INDEX IF NOT EXISTS idx_sale_events_outlet  ON sale_events(outlet_id, recorded_at)",
        "CREATE INDEX IF NOT EXISTS idx_ledger_date         ON inventory_ledger(date, item_id)",
        "CREATE INDEX IF NOT EXISTS idx_ledger_type         ON inventory_ledger(movement_type)",
        "CREATE INDEX IF NOT EXISTS idx_waste_date          ON waste_logs(date, outlet_id)",
        "CREATE INDEX IF NOT EXISTS idx_recon_date          ON daily_reconciliation(date, outlet_id)",
    ]
    for idx_sql in indexes:
        try:
            db.execute(idx_sql)
        except Exception as _idx_e:
            import sys; print(f"INDEX WARNING: {idx_sql[:60]}... => {_idx_e}", file=sys.stderr)
    db.commit()

def seed_db():
    db = get_db()
    if q("SELECT id FROM users LIMIT 1"): return

    # Outlets first
    run("INSERT INTO outlets (name,location,till_number) VALUES (?,?,?)", ('Shiba Main','Main Branch','174379'))
    run("INSERT INTO outlets (name,location,till_number) VALUES (?,?,?)", ('Shiba Express','CBD Branch','174380'))

    staff = [
        # name, email, password, role, line, outlet_id, pay_period, pay_amount
        ('General Manager', 'gm@shiba.co.ke',   'gm123',    'general_manager', None,              None, 'monthly', 120000),
        ('Alice Wanjiku',   'alice@shiba.co.ke', 'alice123', 'manager',         'Operations',      None, 'monthly',  85000),
        ('James Mwangi',    'james@shiba.co.ke', 'james123', 'chef',            'Main Kitchen',    1,    'monthly',  45000),
        ('Grace Njeri',     'grace@shiba.co.ke', 'grace123', 'chef',            'Pastry & Snacks', 1,    'monthly',  42000),
        ('Tom Otieno',      'tom@shiba.co.ke',   'tom123',   'cashier',         None,              1,    'daily',      800),
        ('Amina Wanjiru',   'amina@shiba.co.ke', 'amina123', 'cashier',         None,              1,    'daily',      800),
        ('Kevin Kamau',     'kevin@shiba.co.ke', 'kevin123', 'cashier',         None,              2,    'daily',      800),
        ('Faith Achieng',   'faith@shiba.co.ke', 'faith123', 'cashier',         None,              2,    'daily',      800),
        ('David Njoroge',   'david@shiba.co.ke', 'david123', 'waiter',          None,              1,    'daily',      700),
        ('Mary Kamau',      'mary@shiba.co.ke',  'mary123',  'waiter',          None,              2,    'daily',      700),
    ]
    for name, email, password, role, line, outlet_id, pay_period, pay_amount in staff:
        run("INSERT INTO users (name,email,password,role,line,outlet_id,pay_period,pay_amount) VALUES (?,?,?,?,?,?,?,?)",
            (name, email, hash_pw(password), role, line, outlet_id, pay_period, pay_amount))

    # ── INGREDIENTS ──
    ingredients = [
        # Proteins
        ('Beef Patty (frozen)',      'piece',  80,  'ingredient'),
        ('Chicken Breast',           'kg',    650,  'ingredient'),
        ('Chicken Thigh/Leg',        'kg',    550,  'ingredient'),
        ('Beef Mince',               'kg',    700,  'ingredient'),
        ('Sausage/Hotdog',           'piece',  60,  'ingredient'),
        # Dairy / Eggs
        ('Eggs',                     'piece',  18,  'ingredient'),
        ('Milk',                     'L',      80,  'ingredient'),
        ('Cream / Fresh Cream',      'L',     350,  'ingredient'),
        ('Mozzarella/Cheese Slice',  'piece',  40,  'ingredient'),
        ('Cheddar Cheese',           'kg',    900,  'ingredient'),
        ('Butter',                   'kg',    400,  'ingredient'),
        # Vegetables
        ('Tomatoes',                 'kg',     80,  'ingredient'),
        ('Onions',                   'kg',     60,  'ingredient'),
        ('Capsicum / Bell Pepper',   'kg',    150,  'ingredient'),
        ('Garlic',                   'kg',    300,  'ingredient'),
        ('Ginger',                   'kg',    350,  'ingredient'),
        ('Cabbage',                  'kg',     40,  'ingredient'),
        ('Spinach',                  'kg',     60,  'ingredient'),
        ('Sukuma Wiki',              'kg',     50,  'ingredient'),
        ('Spring Onions/Dhania',     'bunch',  20,  'ingredient'),
        ('Mushrooms',                'kg',    400,  'ingredient'),
        ('Plantain (Ndizi)',         'kg',    120,  'ingredient'),
        ('Matoke (Green Banana)',    'kg',     80,  'ingredient'),
        ('Bhajia Mix / Chickpea Flour','kg',  120,  'ingredient'),
        ('Potato',                   'kg',     70,  'ingredient'),
        # Starches / Grains
        ('Ugali Flour (Maize)',      'kg',     50,  'ingredient'),
        ('White Rice',               'kg',    120,  'ingredient'),
        ('Pasta (Spaghetti)',        'kg',    180,  'ingredient'),
        ('Penne Pasta',              'kg',    180,  'ingredient'),
        ('Wheat Flour',              'kg',     65,  'ingredient'),
        ('Bread Rolls / Buns',       'piece',  30,  'ingredient'),
        # Sauces
        ('Tomato Sauce / Passata',   'L',     200,  'ingredient'),
        ('BBQ Sauce',                'bottle',350,  'ingredient'),
        ('Mayonnaise',               'kg',    300,  'ingredient'),
        ('Ketchup',                  'bottle',200,  'ingredient'),
        ('Mustard',                  'bottle',300,  'ingredient'),
        ('Soy Sauce',                'bottle',300,  'ingredient'),
        ('Worcestershire Sauce',     'bottle',350,  'ingredient'),
        # Spices
        ('Salt',                     'kg',     30,  'ingredient'),
        ('Black Pepper',             'kg',    600,  'ingredient'),
        ('Paprika',                  'kg',    600,  'ingredient'),
        ('Mixed Herbs',              'kg',    800,  'ingredient'),
        ('Cumin',                    'kg',    700,  'ingredient'),
        ('Chilli Powder',            'kg',    500,  'ingredient'),
        ('Garlic Powder',            'kg',    700,  'ingredient'),
        ('Oregano',                  'kg',    900,  'ingredient'),
        ('Thyme',                    'kg',    900,  'ingredient'),
        ('Curry Powder',             'kg',    600,  'ingredient'),
        ('Sugar',                    'kg',    120,  'ingredient'),
        ('Brown Sugar',              'kg',    150,  'ingredient'),
        ('Baking Powder',            'kg',    400,  'ingredient'),
        ('Cooking Oil',              'L',     280,  'ingredient'),
        # Beverages
        ('Tea Leaves',               'kg',    700,  'ingredient'),
        ('Coffee (Instant)',         'kg',   2000,  'ingredient'),
        ('Lemon',                    'piece',  10,  'ingredient'),
        ('Honey',                    'kg',    800,  'ingredient'),
        ('Milk (Tea)',               'L',      80,  'ingredient'),
        # Other kitchen
        ('Maziwa Lala / Sour Milk',  'L',      90,  'ingredient'),
        ('Coconut Milk',             'L',     160,  'ingredient'),
        ('Beef Stock / Bouillon',    'piece',  20,  'ingredient'),
        ('Cream Cheese',             'kg',    900,  'ingredient'),
        ('Pancetta / Bacon',         'kg',    900,  'ingredient'),
        ('Parmesan Cheese',          'kg',   1200,  'ingredient'),
        ('Mukimo Greens (Peas/Potato/Spinach mix)','kg', 90, 'ingredient'),
        ('Dawa Mix (ginger/honey/lemon)','portion',50, 'ingredient'),
        ('Lettuce / Salad Leaves',   'kg',    150,  'ingredient'),
        ('Cucumber',                 'kg',     80,  'ingredient'),
        ('Carrots',                  'kg',     70,  'ingredient'),
        # Kitchen consumables
        ('LPG Gas Cylinder (13kg)',  'cylinder', 3200, 'consumable'),
        ('Charcoal',                 'bag',    800,  'consumable'),
        ('Aluminum Foil',            'roll',   350,  'consumable'),
        ('Cling Film',               'roll',   250,  'consumable'),
        ('Gloves (box)',             'box',    200,  'consumable'),
        ('Cleaning Detergent',       'bottle', 300,  'consumable'),
        # Outlet consumables
        ('Packaging Bags (small)',   'pack',   150,  'consumable'),
        ('Packaging Bags (large)',   'pack',   250,  'consumable'),
        ('Paper Cups (250ml)',       'pack',   200,  'consumable'),
        ('Tumblers (plastic)',       'piece',   25,  'consumable'),
        ('Straws (pack)',            'pack',    80,  'consumable'),
        ('Serviettes / Napkins',     'pack',    60,  'consumable'),
        ('Takeaway Boxes',           'pack',   300,  'consumable'),
        ('Spoons/Forks (disposable)','pack',   120,  'consumable'),
    ]
    for name, unit, cost, cat in ingredients:
        iid = run("INSERT INTO ingredients (name,unit,cost_per_unit,reorder_level,category) VALUES (?,?,?,?,?)",
                  (name, unit, cost, 5, cat))
        run("INSERT INTO stock (ingredient_id, quantity) VALUES (?,?)", (iid, 20))

    # ── PRODUCTS ──
    products = [
        ('Beef Burger',                     'Burgers & Hotdogs', 'portion', 400),
        ('Chicken Burger',                  'Burgers & Hotdogs', 'portion', 400),
        ('Plain Hotdog',                    'Burgers & Hotdogs', 'portion', 200),
        ('Hotdog with Cheese',              'Burgers & Hotdogs', 'portion', 400),
        ('Grilled Chicken',                 'Chicken',           'portion', 450),
        ('Pan-fried Chicken Mushroom Sauce','Chicken',           'portion', 450),
        ('Bolognese Pasta',                 'Pasta',             'portion', 350),
        ('Spaghetti Bolognese',             'Pasta',             'portion', 350),
        ('Spaghetti Carbonara',             'Pasta',             'portion', 400),
        ('Stir-fried Carbonara',            'Pasta',             'portion', 400),
        ('Ugali',                           'Staples',           'portion', 100),
        ('White Rice',                      'Staples',           'portion', 150),
        ('Vegetable Rice',                  'Staples',           'portion', 200),
        ('Chips',                           'Potatoes',          'portion', 250),
        ('Potato Wedges',                   'Potatoes',          'portion', 300),
        ('Cheese Chips Masala',             'Potatoes',          'portion', 300),
        ('Salted Potatoes',                 'Potatoes',          'portion', 300),
        ('Mashed Potatoes',                 'Potatoes',          'portion', 300),
        ('Roasted Potatoes',                'Potatoes',          'portion', 250),
        ('Spinach',                         'Vegetables',        'portion', 100),
        ('Kachumbari',                      'Vegetables',        'portion', 100),
        ('Sukuma Wiki',                     'Vegetables',        'portion', 100),
        ('Cabbage',                         'Vegetables',        'portion', 100),
        ('Mukimo',                          'Vegetables',        'portion', 250),
        ('Chapati',                         'Bread & Snacks',    'piece',    50),
        ('Mandazi',                         'Bread & Snacks',    'piece',    20),
        ('Bhajia',                          'Bread & Snacks',    'portion', 200),
        ('Plantain',                        'Sides',             'portion', 300),
        ('Matoke',                          'Sides',             'portion', 250),
        ('Kenyegi Veggie',                  'Sides',             'portion', 150),
        ('Black Tea',                       'Beverages',         'cup',      30),
        ('White Tea',                       'Beverages',         'cup',      60),
        ('Black Coffee',                    'Beverages',         'cup',      50),
        ('Dawa',                            'Beverages',         'cup',     200),
        ('Lemon Tea',                       'Beverages',         'cup',      80),
        ('Glass of Milk',                   'Beverages',         'glass',   100),
    ]
    for name, cat, unit, price in products:
        run("INSERT INTO products (name,category,unit,price) VALUES (?,?,?,?)", (name, cat, unit, price))

    # ── RECIPES ──
    def ing(name):
        r = q("SELECT id FROM ingredients WHERE name=?", (name,), one=True)
        return r['id'] if r else None
    def prd(name):
        r = q("SELECT id FROM products WHERE name=?", (name,), one=True)
        return r['id'] if r else None

    recipes = {
        'Beef Burger':    [(ing('Beef Patty (frozen)'),1,1,'patty'),(ing('Bread Rolls / Buns'),1,1,'bun'),(ing('Lettuce / Salad Leaves'),0.02,1,'lettuce'),(ing('Tomatoes'),0.05,1,'tomato'),(ing('Mayonnaise'),0.02,1,'mayo'),(ing('Ketchup'),0.02,1,'ketchup')],
        'Chicken Burger': [(ing('Chicken Breast'),0.18,1,'chicken'),(ing('Bread Rolls / Buns'),1,1,'bun'),(ing('Lettuce / Salad Leaves'),0.02,1,'lettuce'),(ing('Mayonnaise'),0.02,1,'mayo'),(ing('Cooking Oil'),0.02,1,'oil')],
        'Plain Hotdog':   [(ing('Sausage/Hotdog'),1,1,'sausage'),(ing('Bread Rolls / Buns'),1,1,'bun'),(ing('Ketchup'),0.02,1,'ketchup')],
        'Hotdog with Cheese': [(ing('Sausage/Hotdog'),1,1,'sausage'),(ing('Bread Rolls / Buns'),1,1,'bun'),(ing('Mozzarella/Cheese Slice'),1,1,'cheese'),(ing('Ketchup'),0.02,1,'ketchup')],
        'Grilled Chicken':[(ing('Chicken Thigh/Leg'),0.25,1,'chicken'),(ing('Garlic'),0.01,1,'garlic'),(ing('Mixed Herbs'),0.005,1,'herbs'),(ing('Cooking Oil'),0.02,1,'oil')],
        'Chips':          [(ing('Potato'),0.25,1,'potato'),(ing('Cooking Oil'),0.05,1,'oil'),(ing('Salt'),0.004,1,'salt')],
        'Ugali':          [(ing('Ugali Flour (Maize)'),0.2,2,'flour')],
        'White Rice':     [(ing('White Rice'),0.1,1,'rice'),(ing('Salt'),0.003,1,'salt')],
        'Black Tea':      [(ing('Tea Leaves'),0.004,1,'tea'),(ing('Sugar'),0.01,1,'sugar')],
        'White Tea':      [(ing('Tea Leaves'),0.004,1,'tea'),(ing('Milk (Tea)'),0.1,1,'milk'),(ing('Sugar'),0.01,1,'sugar')],
        'Black Coffee':   [(ing('Coffee (Instant)'),0.005,1,'coffee'),(ing('Sugar'),0.008,1,'sugar')],
        'Chapati':        [(ing('Wheat Flour'),0.067,1,'flour'),(ing('Cooking Oil'),0.01,1,'oil'),(ing('Salt'),0.002,1,'salt')],
        'Mandazi':        [(ing('Wheat Flour'),0.05,1,'flour'),(ing('Cooking Oil'),0.005,1,'oil'),(ing('Sugar'),0.008,1,'sugar')],
    }
    for pname, items in recipes.items():
        pid = prd(pname)
        if not pid: continue
        for ing_id, qty, yields, note in items:
            if ing_id:
                run("INSERT INTO recipes (product_id,ingredient_id,qty_used,yields,note) VALUES (?,?,?,?,?)",
                    (pid, ing_id, qty, yields, note))

    run("INSERT OR IGNORE INTO settings (key,value) VALUES (?,?)", ('business_name','Shiba Meals'))
    run("INSERT OR IGNORE INTO settings (key,value) VALUES (?,?)", ('contact','+254700000000'))  # TODO: update via Admin → Settings
    # Default expense categories
    default_cats = ['Transport','Utilities','Rent','Supplies','Maintenance','Salaries','Fuel','Miscellaneous']
    for cat in default_cats:
        run("INSERT OR IGNORE INTO expense_categories (name, is_default) VALUES (?,1)", (cat,))
    db.commit()

def ledger_entry(item_id, movement_type, quantity, item_type='product',
                  reference_type=None, reference_id=None, notes=None, unit=None):
    """Write an immutable inventory ledger entry. Never update — only insert."""
    try:
        uid = session.get('user_id') if session else None
        run("""INSERT INTO inventory_ledger
               (date, item_id, item_type, movement_type, quantity, unit,
                reference_type, reference_id, created_by, notes)
               VALUES (?,?,?,?,?,?,?,?,?,?)""",
            (today(), item_id, item_type, movement_type, quantity, unit,
             reference_type, reference_id, uid, notes))
    except Exception as _e:
        import sys; print(f"LEDGER write failed: {_e}", file=sys.stderr)  # must not crash main flow

# ── Calculations ───────────────────────────────────────────────────────────────
def calc_expected_revenue(outlet_id, date_str):
    products = q("SELECT id FROM products WHERE active=1")
    total = 0.0
    for p in products:
        pid = p['id']
        ti = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                  WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                  AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id,pid,date_str,date_str),one=True)['t']
        if ti == 0: continue
        wt = q("""SELECT COALESCE(SUM(qty_out),0) as t FROM stock_events
                  WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                  AND event_type='WASTAGE'""",(outlet_id,pid,date_str,date_str),one=True)['t']
        last_sub = q("""SELECT si.qty_remaining FROM submission_items si
                        JOIN cashier_submissions cs ON si.submission_id=cs.id
                        WHERE cs.outlet_id=? AND si.product_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day')
                        ORDER BY cs.submitted_at DESC LIMIT 1""",(outlet_id,pid,date_str),one=True)
        remaining = last_sub['qty_remaining'] if last_sub else 0
        sold = max(0, ti - wt - remaining)
        price = q("SELECT price FROM products WHERE id=?", (pid,), one=True)
        if price: total += sold * price['price']
    return total

def calc_ingredient_cost(date_str):
    reqs = q("SELECT id FROM requisitions WHERE for_date=? AND status='approved'", (date_str,))
    total = 0.0
    for r in reqs:
        items = q("""SELECT ri.qty_requested, i.cost_per_unit
                     FROM requisition_items ri JOIN ingredients i ON ri.ingredient_id=i.id
                     WHERE ri.requisition_id=?""",(r['id'],))
        for item in items:
            total += item['qty_requested'] * item['cost_per_unit']
    return total

def cost_per_unit(product_id):
    recipe = q("""SELECT r.qty_used, r.yields, i.cost_per_unit
                  FROM recipes r JOIN ingredients i ON r.ingredient_id=i.id
                  WHERE r.product_id=?""",(product_id,))
    return sum((row['qty_used']/row['yields']) * row['cost_per_unit'] for row in recipe)