# -*- coding: utf-8 -*-
"""
Populates the POS database with realistic demo data for customer presentations.

Generates:
  - 1 manager account  (username: manager / PIN: 1234 / password: password)
  - 4 waiters with real Greek names
  - 2 table groups with 13 tables total
  - A full menu: 3 categories, ~20 products with costs
  - 120 days of backdated history with realistic time distribution
  - Online orders (source="online") scattered through history
  - Order cancellations (~10% of items)
  - Site notes and todos
  - Contacts (suppliers) and expenses over the period
  - Customers and tabs
  - Scheduled shifts for the next 3 weeks

Usage:
    python demo_seed.py

Must be run from the local_backend directory. Designed to run on a clean DB
(after wipe_database.py), but safe to run multiple times — checks for the
DEMO guard before inserting anything.
"""
import json
import random
import sys
from datetime import datetime, timedelta, date, timezone

import bcrypt
from sqlalchemy import text

from database import engine, Base, SessionLocal

import models.user
import models.table
import models.printer
import models.product
import models.customers    # noqa: F401 — must be before models.order (orders.customer_id FK)
import models.order
import models.business_day
import models.shift
import models.settings
import models.flag
import models.message
import models.notes        # noqa: F401 — registers tables with Base.metadata
import models.expenses     # noqa: F401
import models.tabs         # noqa: F401
import models.schedule     # noqa: F401
import models.waste        # noqa: F401
import models.reservation  # noqa: F401

from models.user import User, WaiterZone
from models.table import TableGroup, Table
from models.product import Category, Product
from models.order import Order, OrderItem, OrderWaiter, OrderAuditLog
from models.business_day import BusinessDay
from models.shift import WaiterShift
from models.notes import SiteNote, SiteTodo
from models.expenses import Contact, Expense, ExpensePayment
from models.customers import Customer
from models.tabs import Tab, TabEntry, TabPayment
from models.schedule import ScheduledShift

Base.metadata.create_all(bind=engine)

# ── Config ────────────────────────────────────────────────────────────────────

DEMO_DAYS = 120         # how many past days to generate (~4 months)
ORDERS_PER_DAY = (18, 35)   # min/max orders per business day

# ── Helpers ───────────────────────────────────────────────────────────────────

def _utc(dt: datetime) -> datetime:
    if dt.tzinfo is None:
        return dt.replace(tzinfo=timezone.utc)
    return dt


def _hash_pin(pin: str) -> str:
    return bcrypt.hashpw(pin.encode(), bcrypt.gensalt()).decode()


def _hash_password(pw: str) -> str:
    return bcrypt.hashpw(pw.encode(), bcrypt.gensalt()).decode()


def _audit(db, order_id, event_type, waiter_id=None, item_ids=None,
           amount=None, payment_method=None, note=None, created_at=None):
    log = OrderAuditLog(
        order_id=order_id,
        event_type=event_type,
        waiter_id=waiter_id,
        item_ids=json.dumps(item_ids) if item_ids is not None else None,
        amount=amount,
        payment_method=payment_method,
        note=note,
    )
    if created_at:
        log.created_at = _utc(created_at)
    db.add(log)


def _random_time_in_day(day_date: date, hour_weights: list) -> datetime:
    """
    Returns a random datetime in day_date, biased toward peak hours.
    hour_weights: list of (hour, weight) tuples covering the operating hours.
    """
    hours = [h for h, _ in hour_weights]
    weights = [w for _, w in hour_weights]
    h = random.choices(hours, weights=weights)[0]
    m = random.randint(0, 59)
    return datetime(day_date.year, day_date.month, day_date.day, h, m)


# Operating hours weighted toward lunch (12-14) and dinner peaks (19-22),
# with lighter morning coffee traffic (10-11) and afternoon lulls (15-18).
HOUR_WEIGHTS = [
    (10, 3), (11, 5),
    (12, 12), (13, 15), (14, 10),
    (15, 4), (16, 3), (17, 4),
    (18, 6), (19, 14), (20, 16), (21, 13), (22, 8),
]

# Day-of-week multipliers (Mon=0 … Sun=6) — weekends busier
DOW_MULTIPLIER = {0: 0.75, 1: 0.75, 2: 0.80, 3: 0.85, 4: 1.00, 5: 1.20, 6: 1.10}

# ── Menu definition ───────────────────────────────────────────────────────────
# (category_name, color, [(product_name, price, cost), ...])
MENU = [
    ("Ορεκτικά", "#f97316", [
        ("Τζατζίκι",        4.50, 1.20),
        ("Ταραμοσαλάτα",    4.50, 1.10),
        ("Χωριάτικη Σαλάτα",7.50, 2.00),
        ("Κεφτεδάκια",      8.00, 2.80),
        ("Σαγανάκι",        6.50, 2.20),
        ("Χούμους",         5.00, 1.40),
    ]),
    ("Κυρίως Πιάτα", "#ef4444", [
        ("Μπριζόλα Χοιρινή",    12.00, 4.50),
        ("Μπριζόλα Μοσχαρίσια", 16.00, 7.00),
        ("Σουβλάκι Κοτόπουλο",  10.00, 3.20),
        ("Μουσακάς",            11.00, 3.80),
        ("Παστίτσιο",           10.00, 3.50),
        ("Σπαγγέτι Μπολονέζ",   9.50, 3.00),
        ("Γύρος Χοιρινός",      8.50, 2.60),
    ]),
    ("Ποτά & Αναψυκτικά", "#3b82f6", [
        ("Νερό 500ml",       1.00, 0.20),
        ("Αναψυκτικό",       2.50, 0.60),
        ("Μπύρα Φιάλη",      4.00, 1.20),
        ("Κρασί (ποτήρι)",   4.50, 1.00),
        ("Καφές Ελληνικός",  2.50, 0.45),
        ("Φραπέ",            3.00, 0.50),
        ("Χυμός Πορτοκάλι",  3.50, 0.90),
    ]),
]

# Weighted product picker: drinks ordered ~40% of the time, mains ~35%, starters ~25%
CATEGORY_WEIGHTS = [0.25, 0.35, 0.40]

WAITER_NAMES = [
    ("Νίκος",   "Νίκος Παπαδόπουλος",  "demo_nikos",   "2201"),
    ("Μαρία",   "Μαρία Κωνσταντίνου",  "demo_maria",   "3302"),
    ("Πέτρος",  "Πέτρος Αναστασίου",   "demo_petros",  "4403"),
    ("Έλενα",   "Έλενα Μιχαηλίδου",    "demo_elena",   "5504"),
]

PAYMENT_METHODS = ["cash", "cash", "cash", "card", "card"]   # 60% cash / 40% card

CANCEL_REASONS = [
    "Λάθος παραγγελία",
    "Ο πελάτης άλλαξε γνώμη",
    "Το προϊόν δεν είναι διαθέσιμο",
    "Λάθος τραπέζι",
]

# ── Main ──────────────────────────────────────────────────────────────────────

db = SessionLocal()

try:
    # ── Guard: abort if demo data already present ─────────────────────────────
    existing = db.query(User).filter(User.username == "demo_nikos").first()
    if existing:
        print("Demo data already present (found demo_nikos user). Nothing to do.")
        print("Run  python wipe_database.py  first if you want a fresh seed.")
        sys.exit(0)

    print("Creating demo data...")

    # ── Manager account ───────────────────────────────────────────────────────
    manager = db.query(User).filter(User.username == "manager").first()
    if not manager:
        manager = User(
            username="manager",
            pin_hash=_hash_pin("1234"),
            password_hash=_hash_password("password"),
            role="manager",
            full_name="Manager",
            is_active=True,
        )
        db.add(manager)
        db.flush()
        print("  created manager  (username: manager / PIN: 1234 / password: password)")
    else:
        if not manager.password_hash:
            manager.password_hash = _hash_password("password")
            db.flush()
        print("  manager account already exists — updated password to 'password'")

    # ── Waiters ───────────────────────────────────────────────────────────────
    waiters = []
    for nickname, full_name, username, pin in WAITER_NAMES:
        w = db.query(User).filter(User.username == username).first()
        if not w:
            w = User(
                username=username,
                pin_hash=_hash_pin(pin),
                role="waiter",
                full_name=full_name,
                nickname=nickname,
                is_active=True,
            )
            db.add(w)
            db.flush()
        waiters.append(w)
    print(f"  created {len(waiters)} waiters")

    # ── Table groups & tables ─────────────────────────────────────────────────
    group_configs = [
        ("Κεντρική Αίθουσα", "Κ", "#6366f1", list(range(1, 9))),    # tables 1-8
        ("Βεράντα",          "Β", "#22c55e", list(range(1, 6))),     # tables 1-5
    ]
    all_tables = []
    for g_name, g_prefix, g_color, table_numbers in group_configs:
        grp = db.query(TableGroup).filter(TableGroup.name == g_name).first()
        if not grp:
            grp = TableGroup(name=g_name, prefix=g_prefix, color=g_color, sort_order=len(all_tables))
            db.add(grp)
            db.flush()
        for n in table_numbers:
            tbl = db.query(Table).filter(Table.group_id == grp.id, Table.number == n).first()
            if not tbl:
                tbl = Table(number=n, group_id=grp.id, is_active=True)
                db.add(tbl)
                db.flush()
            all_tables.append(tbl)
    print(f"  created {len(all_tables)} tables across {len(group_configs)} groups")

    # ── Waiter zone assignments ───────────────────────────────────────────────
    for w in waiters:
        existing_zone = db.query(WaiterZone).filter(
            WaiterZone.waiter_id == w.id, WaiterZone.group_id.is_(None)
        ).first()
        if not existing_zone:
            db.add(WaiterZone(waiter_id=w.id, group_id=None))
    db.flush()

    # ── Menu ──────────────────────────────────────────────────────────────────
    categories = []
    all_products = []
    category_product_map = []

    for sort_idx, (cat_name, cat_color, items) in enumerate(MENU):
        cat = db.query(Category).filter(Category.name == cat_name).first()
        if not cat:
            cat = Category(name=cat_name, color=cat_color, sort_order=sort_idx)
            db.add(cat)
            db.flush()
        categories.append(cat)

        cat_products = []
        for prod_sort, (prod_name, prod_price, prod_cost) in enumerate(items):
            p = db.query(Product).filter(Product.name == prod_name, Product.category_id == cat.id).first()
            if not p:
                p = Product(
                    name=prod_name,
                    category_id=cat.id,
                    base_price=prod_price,
                    cost_simple=prod_cost,
                    is_available=True,
                    lifecycle_status="active",
                    sort_order=prod_sort,
                )
                db.add(p)
                db.flush()
            else:
                p.cost_simple = prod_cost
                db.flush()
            cat_products.append(p)
            all_products.append(p)
        category_product_map.append(cat_products)

    print(f"  created {len(categories)} categories with {len(all_products)} products (with costs)")

    # ── Contacts (suppliers & utilities) ─────────────────────────────────────
    contact_defs = [
        ("Κρεοπωλείο Παπαδόπουλος", "supplier",  "+30 22310 41234", None,            "Πιερία 12, Κατερίνη",     "Κύριος προμηθευτής κρέατος"),
        ("Αλιεύματα Κόκκαλης",      "supplier",  "+30 22310 55678", None,            "Λιμάνι, Κατερίνη",        "Φρέσκα ψάρια και θαλασσινά"),
        ("Χονδρικό Ελαιόλαδο",      "supplier",  "+30 23310 22100", "oil@agrofarm.gr","Αγροτική Οδός 5",         "Ελαιόλαδο και τυρί χύμα"),
        ("Αναψυκτικά ΑΕΒΕ",         "supplier",  "+30 210 9012345", "orders@aeve.gr", "Βιομηχανική Ζώνη, Αθήνα","Αναψυκτικά, νερά, μπύρες"),
        ("ΔΕΗ",                     "utility",   "11500",            None,            None,                     "Ηλεκτρισμός"),
        ("ΕΥΔΑΠ / Τοπικό Δίκτυο",   "utility",   "11888",            None,            None,                     "Νερό"),
        ("Cosmote Επαγγελματικό",    "utility",   "13888",            None,            None,                     "Τηλεφωνία & internet"),
    ]
    contacts = []
    for c_name, c_type, c_phone, c_email, c_addr, c_notes in contact_defs:
        c = db.query(Contact).filter(Contact.name == c_name).first()
        if not c:
            c = Contact(
                name=c_name,
                type=c_type,
                phone=c_phone,
                email=c_email,
                address=c_addr,
                notes=c_notes,
                is_active=True,
            )
            db.add(c)
            db.flush()
        contacts.append(c)
    print(f"  created {len(contacts)} contacts")

    # ── Customers ─────────────────────────────────────────────────────────────
    customer_defs = [
        ("Γιώργος Σταθόπουλος", "Γιώργης",  "+30 6944 111222", None,                      "Τακτικός πελάτης, κάθε Παρασκευή βράδυ"),
        ("Ελευθερία Νικολάου",  "Ελευθερία","+30 6977 334455", "eleftheria@gmail.com",    "Αλλεργία στη γλουτένη"),
        ("Δημήτρης Παππάς",     "Μήτσος",   "+30 6955 667788", None,                      "Προτιμά τραπέζι στη βεράντα"),
        ("Σοφία Αλεξίου",       "Σοφία",    "+30 6933 998877", "sofia.alex@hotmail.com",  "VIP πελάτης"),
        ("Ομάδα Εργατών",       None,        "+30 6900 123456", None,                      "Ομάδα οικοδόμων — συχνά μεσημέρι"),
    ]
    customers = []
    for c_name, c_nick, c_phone, c_email, c_notes in customer_defs:
        c = db.query(Customer).filter(Customer.name == c_name).first()
        if not c:
            c = Customer(
                name=c_name,
                nickname=c_nick,
                phone=c_phone,
                email=c_email,
                notes=c_notes,
                is_active=True,
                created_by_id=manager.id,
            )
            db.add(c)
            db.flush()
        customers.append(c)
    print(f"  created {len(customers)} customers")

    db.commit()

    # ── Historical days ───────────────────────────────────────────────────────
    today = datetime.now(timezone.utc).date()
    total_orders = 0
    total_revenue = 0.0

    print(f"\nGenerating {DEMO_DAYS} days of history...")

    # Keep track of created business days (date → BusinessDay) for expense linking
    bday_by_date: dict[date, BusinessDay] = {}

    for days_ago in range(DEMO_DAYS, 0, -1):
        day_date = today - timedelta(days=days_ago)
        dow = day_date.weekday()
        multiplier = DOW_MULTIPLIER[dow]

        open_time  = _utc(datetime(day_date.year, day_date.month, day_date.day,
                                   10, random.randint(0, 30)))
        close_time = _utc(datetime(day_date.year, day_date.month, day_date.day,
                                   23, random.randint(15, 45)))

        bday = BusinessDay(
            status="closed",
            opened_at=open_time,
            opened_by_id=manager.id,
            closed_at=close_time,
            closed_by_id=manager.id,
        )
        db.add(bday)
        db.flush()
        bday_by_date[day_date] = bday

        # 2-4 waiters work each day
        working_waiters = random.sample(waiters, k=random.randint(2, len(waiters)))
        STARTING_CASH_OPTIONS = [50.0, 80.0, 100.0, 110.0, 120.0, 150.0, 200.0]
        shifts = {}
        shift_totals = {}
        for w in working_waiters:
            shift_start = open_time + timedelta(minutes=random.randint(0, 30))
            shift_end   = close_time - timedelta(minutes=random.randint(0, 20))
            shift = WaiterShift(
                waiter_id=w.id,
                business_day_id=bday.id,
                started_at=shift_start,
                ended_at=shift_end,
                starting_cash=random.choice(STARTING_CASH_OPTIONS),
            )
            db.add(shift)
            db.flush()
            shifts[w.id] = shift
            shift_totals[shift.id] = 0.0

        # Orders distributed realistically across the day
        base_n = random.randint(*ORDERS_PER_DAY)
        n_orders = max(5, int(base_n * multiplier))

        # ~8% of days have 1-2 online orders
        n_online = random.choices([0, 1, 2], weights=[0.60, 0.28, 0.12])[0]
        n_pos = n_orders - n_online

        order_times = []
        for _ in range(n_pos):
            order_times.append(_random_time_in_day(day_date, HOUR_WEIGHTS))
        order_times.sort()

        # Online orders land at lunch or dinner time
        online_order_times = []
        for _ in range(n_online):
            h = random.choices([12, 13, 19, 20, 21], weights=[2, 2, 3, 3, 2])[0]
            online_order_times.append(datetime(day_date.year, day_date.month, day_date.day,
                                               h, random.randint(0, 59)))

        table_busy_until: dict[int, datetime] = {}

        # ── POS orders ────────────────────────────────────────────────────────
        for opened_at_naive in order_times:
            opened_at = _utc(opened_at_naive)

            free_tables = [
                t for t in all_tables
                if table_busy_until.get(t.id, datetime.min.replace(tzinfo=timezone.utc)) <= opened_at
            ]
            if not free_tables:
                free_tables = all_tables
            table = random.choice(free_tables)

            waiter = random.choice(working_waiters)
            shift  = shifts[waiter.id]

            duration_min = random.randint(20, 70)
            closed_at = opened_at + timedelta(minutes=duration_min)
            if closed_at > close_time:
                closed_at = close_time - timedelta(minutes=2)

            table_busy_until[table.id] = closed_at

            # ~15% of orders linked to a known customer
            order_customer = random.choice(customers) if random.random() < 0.15 else None

            order = Order(
                table_id=table.id,
                opened_by=waiter.id,
                business_day_id=bday.id,
                status="closed",
                closed_by=manager.id,
                notes=None,
                source="pos",
                customer_id=order_customer.id if order_customer else None,
            )
            order.opened_at = opened_at
            order.closed_at = closed_at
            db.add(order)
            db.flush()

            db.add(OrderWaiter(order_id=order.id, waiter_id=waiter.id, assigned_at=opened_at))
            _audit(db, order.id, "ORDER_OPENED", waiter_id=waiter.id, created_at=opened_at)

            n_items = random.randint(2, 6)
            chosen_category_lists = random.choices(category_product_map, weights=CATEGORY_WEIGHTS, k=n_items)
            items_added = []
            order_total = 0.0
            items_added_at = opened_at + timedelta(minutes=random.randint(2, 8))

            for prod_list in chosen_category_lists:
                product = random.choice(prod_list)
                qty = random.choices([1, 2, 3], weights=[0.7, 0.2, 0.1])[0]

                # ~10% chance the item gets cancelled
                is_cancelled = random.random() < 0.10
                paid_at = closed_at - timedelta(minutes=random.randint(1, 5))
                line_total = product.base_price * qty

                item = OrderItem(
                    order_id=order.id,
                    product_id=product.id,
                    added_by=waiter.id,
                    quantity=qty,
                    unit_price=product.base_price,
                    unit_cost=product.cost_simple,
                    status="cancelled" if is_cancelled else "paid",
                    printed=True,
                )

                if is_cancelled:
                    cancel_at = items_added_at + timedelta(minutes=random.randint(1, 10))
                    item.cancelled_by = waiter.id
                    item.cancelled_at = _utc(cancel_at)
                    item.cancel_reason = random.choice(CANCEL_REASONS)
                else:
                    payment_method = random.choice(PAYMENT_METHODS)
                    item.paid_by = waiter.id
                    item.paid_at = _utc(paid_at)
                    item.payment_method = payment_method
                    item.paid_in_shift_id = shift.id
                    order_total += line_total
                    shift_totals[shift.id] = round(shift_totals[shift.id] + line_total, 2)

                item.added_at = _utc(items_added_at)
                db.add(item)
                db.flush()
                items_added.append(item.id)

            _audit(db, order.id, "ITEMS_ADDED",
                   waiter_id=waiter.id, item_ids=items_added, created_at=items_added_at)

            payment_method = random.choice(PAYMENT_METHODS)
            pay_at = closed_at - timedelta(minutes=1)
            _audit(db, order.id, "PAYMENT",
                   waiter_id=waiter.id, item_ids=items_added,
                   amount=round(order_total, 2), payment_method=payment_method,
                   created_at=pay_at)
            _audit(db, order.id, "ORDER_CLOSED", waiter_id=waiter.id, created_at=closed_at)

            total_orders  += 1
            total_revenue += order_total

        # ── Online orders ─────────────────────────────────────────────────────
        online_ref_counter = days_ago * 10  # rough unique ref per day
        for ot in online_order_times:
            opened_at = _utc(ot)
            duration_min = random.randint(25, 50)
            closed_at = opened_at + timedelta(minutes=duration_min)
            if closed_at > close_time:
                closed_at = close_time - timedelta(minutes=2)

            online_type = random.choice(["delivery", "dine_in"])
            online_names = ["Αλέξης Κ.", "Μαρία Π.", "Νίκος Σ.", "Αθηνά Λ.", "Θανάσης Β.", "Γιώτα Δ."]
            online_phones = ["+30 694 111 0000", "+30 697 222 1111", "+30 693 333 2222",
                             "+30 699 444 3333", "+30 698 555 4444"]
            o_name = random.choice(online_names)
            o_phone = random.choice(online_phones)
            o_status = random.choices(
                ["delivered", "delivered", "delivered", "rejected"],
                weights=[0.85, 0.05, 0.05, 0.05]
            )[0]

            ref = f"ORD-{online_ref_counter:04d}"
            online_ref_counter += 1

            order = Order(
                table_id=None,
                opened_by=manager.id,
                business_day_id=bday.id,
                status="closed" if o_status == "delivered" else "cancelled",
                closed_by=manager.id,
                source="online",
                online_order_ref=ref,
                online_status=o_status,
                online_customer_name=o_name,
                online_customer_phone=o_phone,
                online_order_type=online_type,
                online_customer_notes=random.choice([None, None, "Χωρίς κρεμμύδι", "Γρήγορα παρακαλώ", None]),
                order_type="delivery" if online_type == "delivery" else "here",
            )
            order.opened_at = opened_at
            order.closed_at = closed_at
            db.add(order)
            db.flush()

            _audit(db, order.id, "ORDER_OPENED", waiter_id=manager.id, created_at=opened_at)

            n_items = random.randint(1, 4)
            chosen_category_lists = random.choices(category_product_map, weights=CATEGORY_WEIGHTS, k=n_items)
            items_added = []
            order_total = 0.0
            items_added_at = opened_at + timedelta(minutes=2)

            for prod_list in chosen_category_lists:
                product = random.choice(prod_list)
                qty = random.choices([1, 2], weights=[0.8, 0.2])[0]
                line_total = product.base_price * qty
                order_total += line_total

                item = OrderItem(
                    order_id=order.id,
                    product_id=product.id,
                    added_by=manager.id,
                    quantity=qty,
                    unit_price=product.base_price,
                    unit_cost=product.cost_simple,
                    status="paid" if o_status == "delivered" else "cancelled",
                    printed=True,
                    paid_by=manager.id if o_status == "delivered" else None,
                    paid_at=_utc(closed_at) if o_status == "delivered" else None,
                    payment_method="card" if o_status == "delivered" else None,
                )
                item.added_at = _utc(items_added_at)
                db.add(item)
                db.flush()
                items_added.append(item.id)

            if o_status == "delivered":
                _audit(db, order.id, "PAYMENT",
                       waiter_id=manager.id, item_ids=items_added,
                       amount=round(order_total, 2), payment_method="card",
                       created_at=closed_at - timedelta(minutes=1))
                total_revenue += order_total
            _audit(db, order.id, "ORDER_CLOSED", waiter_id=manager.id, created_at=closed_at)
            total_orders += 1

        # Write total_collected onto shifts
        for w in working_waiters:
            s = shifts[w.id]
            s.total_collected = shift_totals[s.id]

        db.commit()
        print(f"  {day_date}  {n_orders:2d} orders  {len(working_waiters)} waiters  (dow={dow})")

    # ── Expenses ──────────────────────────────────────────────────────────────
    print("\nAdding expenses...")

    supplier_meat  = contacts[0]
    supplier_fish  = contacts[1]
    supplier_olive = contacts[2]
    supplier_bev   = contacts[3]
    util_power     = contacts[4]
    util_water     = contacts[5]
    util_phone     = contacts[6]

    def _add_expense(description, category, contact, amount, paid_amount,
                     days_back, notes=None):
        exp_date = today - timedelta(days=days_back)
        bday_obj = bday_by_date.get(exp_date)
        due = _utc(datetime(exp_date.year, exp_date.month, exp_date.day, 9, 0))
        exp = Expense(
            description=description,
            category=category,
            contact_id=contact.id if contact else None,
            total_amount=amount,
            paid_amount=paid_amount,
            due_date=due,
            business_day_id=bday_obj.id if bday_obj else None,
            created_by_id=manager.id,
            notes=notes,
        )
        exp.created_at = due
        db.add(exp)
        db.flush()
        if paid_amount > 0:
            pmt = ExpensePayment(
                expense_id=exp.id,
                amount=paid_amount,
                paid_by_id=manager.id,
                notes=None,
            )
            pmt.paid_at = due + timedelta(hours=1)
            db.add(pmt)
        db.flush()

    # Recurring weekly: meat supplier (~every 5 days)
    for i in range(0, DEMO_DAYS, 5):
        _add_expense("Κρέας εβδομάδας", "supplier", supplier_meat,
                     round(random.uniform(180, 280), 2), 0,
                     DEMO_DAYS - i, "Παράδοση Δευτέρα πρωί")

    # Recurring bi-weekly: fish
    for i in range(0, DEMO_DAYS, 10):
        _add_expense("Ψάρια & θαλασσινά", "supplier", supplier_fish,
                     round(random.uniform(90, 160), 2), 0,
                     DEMO_DAYS - i)

    # Olive oil once a month
    for i in range(0, DEMO_DAYS, 30):
        _add_expense("Ελαιόλαδο & τυρί", "supplier", supplier_olive,
                     round(random.uniform(120, 200), 2), 0,
                     DEMO_DAYS - i)

    # Beverages every 2 weeks
    for i in range(0, DEMO_DAYS, 14):
        amt = round(random.uniform(200, 350), 2)
        _add_expense("Αναψυκτικά & μπύρες", "supplier", supplier_bev,
                     amt, amt,   # fully paid
                     DEMO_DAYS - i)

    # Monthly utility bills
    for month_offset in range(4):
        days_back = 30 * (month_offset + 1)
        _add_expense("ΔΕΗ — μηνιαίος λογαριασμός", "utilities", util_power,
                     round(random.uniform(280, 420), 2), 0, days_back)
        _add_expense("Νερό — μηνιαίος λογαριασμός", "utilities", util_water,
                     round(random.uniform(45, 90), 2), 0, days_back + 2)
        _add_expense("Τηλεφωνία & Internet", "utilities", util_phone,
                     49.90, 49.90, days_back + 1)

    # Rent once a month
    for month_offset in range(4):
        days_back = 30 * (month_offset + 1) - 5
        _add_expense("Ενοίκιο καταστήματος", "rent", None,
                     1200.0, 1200.0, days_back,
                     "Πάντα στις αρχές του μήνα")

    # One maintenance expense
    _add_expense("Επισκευή ψυγείου", "maintenance", None,
                 320.0, 320.0, 45, "Συμπιεστής — Τεχνίτης Σωτήρης")

    db.commit()
    print("  expenses done")

    # ── Tabs ──────────────────────────────────────────────────────────────────
    print("Adding tabs...")

    def _add_tab(customer, entries_desc, payments, is_closed=False):
        """entries_desc: list of (amount, description). payments: list of amounts."""
        tab_opened = _utc(datetime.now(timezone.utc) - timedelta(days=random.randint(5, 30)))
        tab = Tab(
            customer_id=customer.id,
            status="closed" if is_closed else "open",
            opened_at=tab_opened,
            closed_by_id=manager.id if is_closed else None,
            closed_at=tab_opened + timedelta(days=random.randint(3, 15)) if is_closed else None,
            notes=None,
        )
        db.add(tab)
        db.flush()

        entry_time = tab_opened
        for amount, desc in entries_desc:
            entry_time += timedelta(days=random.randint(1, 3))
            e = TabEntry(
                tab_id=tab.id,
                order_id=None,
                order_item_id=None,
                amount=amount,
                description=desc,
                created_by_id=manager.id,
            )
            e.created_at = entry_time
            db.add(e)

        pay_time = entry_time + timedelta(days=1)
        for pay_amount in payments:
            pay_time += timedelta(hours=random.randint(1, 24))
            p = TabPayment(
                tab_id=tab.id,
                amount=pay_amount,
                payment_method=random.choice(["cash", "card"]),
                received_by_id=manager.id,
                notes=None,
            )
            p.created_at = pay_time
            db.add(p)

        db.flush()

    # Open tab: Γιώργης hasn't settled his tab yet
    _add_tab(customers[0], [
        (35.50, "Δείπνο 3 ατόμων — 15/05"),
        (28.00, "Μεσημεριανό — 22/05"),
        (42.00, "Δείπνο 4 ατόμων — 28/05"),
    ], [20.00])  # partial payment

    # Closed tab: Σοφία settled hers
    _add_tab(customers[3], [
        (55.00, "Εταιρικό δείπνο — 10/04"),
        (30.00, "Μεσημεριανό — 18/04"),
    ], [85.00], is_closed=True)

    # Open tab: Ομάδα εργατών — running tab
    _add_tab(customers[4], [
        (22.00, "Μεσημεριανό ομάδα — 01/06"),
        (25.50, "Μεσημεριανό ομάδα — 05/06"),
        (19.00, "Μεσημεριανό ομάδα — 09/06"),
    ], [30.00])

    db.commit()
    print("  tabs done")

    # ── Site Notes & Todos ────────────────────────────────────────────────────
    print("Adding notes & todos...")

    notes_data = [
        ("Ο φούρνος χρειάζεται σέρβις — να κλείσουμε ραντεβού με τεχνίτη.", True),
        ("Νέα τιμή μπύρας από 1 Ιουλίου: Heineken 4.50€ (από 4.00€).", False),
        ("Παραγγελία ελαιολάδου: 10 κιλά εξαιρετικό παρθένο από Αλεξόπουλο.", False),
        ("ΔΕΗ: η επόμενη πληρωμή λήγει στις 20 Ιουλίου. Να μην ξεχαστεί!", True),
        ("Το κλιματιστικό βεράντας λειτουργεί πάλι μετά την επισκευή (Παρ. 07/06).", False),
    ]
    for body, pinned in notes_data:
        note = SiteNote(
            body=body,
            created_by_id=manager.id,
            is_pinned=pinned,
        )
        db.add(note)

    todos_data = [
        ("Ανανέωση τιμοκαταλόγου για καλοκαίρι",         "high",   False),
        ("Αγορά νέων ποτηριών κρασιού (χάλασαν 4)",       "normal", False),
        ("Έλεγχος πυροσβεστήρων — Ιούλιος",               "high",   False),
        ("Εκπαίδευση νέου σερβιτόρου στο σύστημα",        "normal", False),
        ("Ανανέωση άδειας λειτουργίας",                   "high",   False),
        ("Παραγγελία νέων χαρτοπετσετών και υλικών bar",  "normal", True),
        ("Φωτογράφιση νέων πιάτων για online menu",       "normal", False),
    ]
    for body, priority, is_done in todos_data:
        todo = SiteTodo(
            body=body,
            priority=priority,
            is_done=is_done,
            created_by_id=manager.id,
            done_by_id=manager.id if is_done else None,
            done_at=_utc(datetime.now(timezone.utc) - timedelta(days=2)) if is_done else None,
        )
        db.add(todo)

    db.commit()
    print("  notes & todos done")

    # ── Schedule (next 3 weeks) ───────────────────────────────────────────────
    print("Adding schedule for next 3 weeks...")

    # Each waiter works 5 days out of 7, with realistic shift patterns
    # Mon-Fri: full shifts, Sat: both sessions, Sun: afternoon/evening only
    shift_patterns = {
        0: [("10:00", "16:00"), ("16:00", "23:30")],   # Mon — two shifts
        1: [("10:00", "16:00"), ("16:00", "23:30")],   # Tue
        2: [("10:00", "16:00"), ("16:00", "23:30")],   # Wed
        3: [("10:00", "16:00"), ("16:00", "23:30")],   # Thu
        4: [("10:00", "17:00"), ("17:00", "23:30")],   # Fri
        5: [("10:00", "16:00"), ("16:00", "23:30")],   # Sat — busy day
        6: [("11:00", "16:00"), ("16:00", "23:00")],   # Sun
    }

    # Assign waiters to shifts: each day, 2 waiters per slot
    for weeks_ahead in range(3):
        for day_offset in range(7):
            sched_date = today + timedelta(weeks=weeks_ahead, days=day_offset)
            dow = sched_date.weekday()
            day_patterns = shift_patterns[dow]

            # Shuffle waiters and assign 2 per slot
            waiter_pool = waiters[:]
            random.shuffle(waiter_pool)
            for slot_idx, (start_t, end_t) in enumerate(day_patterns):
                # Pick 2 waiters per slot (rotate through pool)
                slot_waiters = [waiter_pool[slot_idx % len(waiter_pool)],
                                waiter_pool[(slot_idx + 1) % len(waiter_pool)]]
                for w in slot_waiters:
                    # Don't double-schedule same waiter on same day
                    already = db.query(ScheduledShift).filter(
                        ScheduledShift.user_id == w.id,
                        ScheduledShift.scheduled_date == sched_date,
                    ).first()
                    if not already:
                        sched = ScheduledShift(
                            user_id=w.id,
                            scheduled_date=sched_date,
                            start_time=start_t,
                            end_time=end_t,
                            notes=None,
                            created_by_id=manager.id,
                        )
                        db.add(sched)

    db.commit()
    print("  schedule done")

    print(f"\nDone.")
    print(f"  Total orders   : {total_orders}")
    print(f"  Total revenue  : €{total_revenue:,.2f}")
    print(f"\nLogin credentials:")
    print(f"  Manager  — username: manager  |  password: password  |  PIN: 1234")
    for w in waiters:
        print(f"  Waiter   — username: {w.username}  |  nickname: {w.nickname}")

finally:
    db.close()
