MAIN_UI.py

"""
UI/MAIN_UI.py
Professional Restaurant Billing System
- Multi-page: Dashboard, Order Entry, Order History, Reports, Menu Page, Menu Management,
              Staff Management, Customer Management, Settings
- Theme switcher + modern gradient backgrounds
- CSV import for menu, searchable menu, invoice preview, JSON bill export
"""

import streamlit as st
from pathlib import Path
import sqlite3
import pandas as pd
import datetime as dt
import json
import os

try:
    menu_df = pd.read_csv("DATA/menu.csv")
    # Fix for NaN values (choose one)
    menu_df = pd.read_csv("DATA/menu.csv").fillna("")
    # menu_df = menu_df.dropna()   # OR drop missing rows
except Exception as e:
    st.error(f"Error loading menu.csv: {e}")

# --------------------------------------------------------------------------------------
# Paths & bootstrapping
# --------------------------------------------------------------------------------------
ROOT = Path(__file__).parents[1]
DB_DIR = ROOT / "DB"
DATA_DIR = ROOT / "DATA"
DB_PATH = DB_DIR / "RESTAURANT.DB"

# Ensure folders exist
DB_DIR.mkdir(parents=True, exist_ok=True)
DATA_DIR.mkdir(parents=True, exist_ok=True)

# --------------------------------------------------------------------------------------
# DB helpers
# --------------------------------------------------------------------------------------
def get_conn():
    return sqlite3.connect(DB_PATH, check_same_thread=False)

def init_db():
    conn = get_conn()
    c = conn.cursor()

    # Menu (with optional image url/path)
    c.execute("""
        CREATE TABLE IF NOT EXISTS menu(
            item_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            category TEXT,
            price REAL,
            gst_percent REAL DEFAULT 5,
            image TEXT
        )
    """)

    # Orders master
    c.execute("""
        CREATE TABLE IF NOT EXISTS orders(
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_name TEXT,
            customer_contact TEXT,
            customer_email TEXT,
            notes TEXT,
            mode TEXT,
            table_no TEXT,
            delivery_address TEXT,
            discount REAL,
            payment_method TEXT,
            total REAL,
            gst REAL,
            created_at TEXT,
            staff_id INTEGER,
            customer_id INTEGER
        )
    """)

    # Order line items (store price snapshot at time of order)
    c.execute("""
        CREATE TABLE IF NOT EXISTS order_items(
            order_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER,
            item_id INTEGER,
            qty INTEGER,
            price REAL
        )
    """)

    # Staff
    c.execute("""
        CREATE TABLE IF NOT EXISTS staff(
            staff_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            role TEXT,
            contact TEXT
        )
    """)

    # Customers
    c.execute("""
        CREATE TABLE IF NOT EXISTS customers(
            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            contact TEXT,
            email TEXT
        )
    """)

    conn.commit()
    conn.close()

init_db()

# --------------------------------------------------------------------------------------
# Theme system
# --------------------------------------------------------------------------------------
THEMES = {
    "Luxury Red-Orange": "linear-gradient(135deg,#ff512f,#dd2476)",
    "Elegant Blue-Purple": "linear-gradient(135deg,#1e3c72,#2a5298)",
    "Modern Green-Teal": "linear-gradient(135deg,#11998e,#38ef7d)",
    "Classy Black-Gold": "linear-gradient(135deg,#000000,#434343)",
    "Spotify Dark": "linear-gradient(135deg,#0f0f0f,#1db954)"
}

def inject_theme(gradient_css):
    css = f"""
    <style>
    :root {{
      --accent-1:#FFD700;
      --accent-2:#2ecc71;
      --bg:{gradient_css};
      --card:rgba(255,255,255,0.08);
    }}
    [data-testid="stAppViewContainer"] {{
      background: var(--bg);
      color: #fff;
    }}
    [data-testid="stSidebar"] {{
      background: rgba(0,0,0,0.75);
      color: #fff;
      backdrop-filter: blur(6px);
    }}
    .app-title {{
      text-align:center;
      font-size:30px;
      font-weight:900;
      color: var(--accent-1);
      margin: 6px 0 16px 0;
      letter-spacing: .5px;
    }}
    .card {{
      background: var(--card);
      padding: 14px;
      border-radius: 16px;
      box-shadow: 0 10px 24px rgba(0,0,0,.25);
      margin-bottom: 14px;
    }}
    .menu-card {{
      background: var(--card);
      padding: 12px;
      border-radius: 16px;
      box-shadow: 0 8px 20px rgba(0,0,0,.25);
      margin-bottom: 12px;
    }}
    .menu-card h4 {{ margin:6px 0; color: var(--accent-1); }}
    .menu-price {{ color: var(--accent-2); font-weight: 800; font-size: 18px; }}
    .small-muted {{ opacity:.9; font-size: 12px; }}
    </style>
    """
    st.markdown(css, unsafe_allow_html=True)

# Persist theme in session
if "theme" not in st.session_state:
    st.session_state.theme = "Elegant Blue-Purple"
inject_theme(THEMES[st.session_state.theme])

# --------------------------------------------------------------------------------------
# Page config
# --------------------------------------------------------------------------------------
st.set_page_config(page_title="Restaurant Billing", layout="wide")
st.markdown('<div class="app-title">🍽️ Restaurant Billing System</div>', unsafe_allow_html=True)

# --------------------------------------------------------------------------------------
# Sidebar Navigation
# --------------------------------------------------------------------------------------
page = st.sidebar.radio(
    "Navigate",
    [
        "🏠 Dashboard",
        "🧾 Order Entry",
        "📜 Order History",
        "📊 Reports",
        "📖 Menu Page",
        "🍽️ Menu Management",
        "👨‍🍳 Staff Management",
        "👤 Customer Management",
        "⚙️ Settings"
    ]
)

# --------------------------------------------------------------------------------------
# Helper utilities
# --------------------------------------------------------------------------------------
def calc_totals(items, discount=0.0):
    """
    items: list of tuples (price, gst_percent, qty)
    returns subtotal, gst, net
    """
    subtotal = sum(p * q for p, g, q in items)
    gst_total = sum((p * (g/100.0)) * q for p, g, q in items)
    net = max(0.0, subtotal + gst_total - (discount or 0.0))
    return round(subtotal, 2), round(gst_total, 2), round(net, 2)

def export_bill_json(order_id, payload):
    out_path = DATA_DIR / f"bill_{order_id}.json"
    with open(out_path, "w", encoding="utf-8") as f:
        json.dump(payload, f, indent=2, ensure_ascii=False)

def to_date(s):
    try:
        return pd.to_datetime(s).date()
    except Exception:
        return None

# --------------------------------------------------------------------------------------
# Pages
# --------------------------------------------------------------------------------------

# DASHBOARD
if page == "🏠 Dashboard":
    st.header("📊 Dashboard Overview")
    conn = get_conn()
    orders = pd.read_sql("SELECT * FROM orders", conn)
    conn.close()

    if orders.empty:
        st.info("No orders yet. Switch to **🧾 Order Entry** to create your first order.")
    else:
        orders["created_at"] = pd.to_datetime(orders["created_at"])
        today = dt.date.today()
        today_sales = orders[orders["created_at"].dt.date == today]["total"].sum()
        total_orders = len(orders)

        # Top item
        conn = get_conn()
        top_df = pd.read_sql("""
            SELECT m.name, SUM(oi.qty) AS qty
            FROM order_items oi
            JOIN menu m ON m.item_id = oi.item_id
            GROUP BY oi.item_id
            ORDER BY qty DESC
            LIMIT 1
        """, conn)
        conn.close()
        top_item = top_df.iloc[0]["name"] if not top_df.empty else "—"

        c1, c2, c3 = st.columns(3)
        c1.metric("💰 Today's Sales", f"₹{today_sales:.2f}")
        c2.metric("🛒 Orders (All-Time)", int(total_orders))
        c3.metric("🍴 Top Item", top_item)

        st.subheader("📈 Sales Trend (by Day)")
        series = orders.groupby(orders["created_at"].dt.date)["total"].sum()
        st.line_chart(series)

# ORDER ENTRY
elif page == "🧾 Order Entry":
    st.header("🧾 New Order")

    # Customer & mode
    c1, c2, c3 = st.columns(3)
    with c1:
        cust_name = st.text_input("Customer Name")
    with c2:
        cust_contact = st.text_input("Contact")
    with c3:
        cust_email = st.text_input("Email")

    notes = st.text_area("Special Notes (e.g., No Onion / Extra Spicy)")
    mode = st.selectbox("Order Type", ["DINE-IN", "TAKEAWAY", "DELIVERY"])
    table_no, delivery_address = None, None
    if mode == "DINE-IN":
        table_no = st.number_input("Table No", 1, 200, 1)
    elif mode == "DELIVERY":
        delivery_address = st.text_area("Delivery Address")

    # Assign staff / customer (optional)
    conn = get_conn()
    staff_df = pd.read_sql("SELECT * FROM staff", conn)
    cust_df = pd.read_sql("SELECT * FROM customers", conn)
    menu_df = pd.read_sql("SELECT * FROM menu", conn)
    conn.close()

    sc1, sc2 = st.columns(2)
    with sc1:
        staff_id = None
        if not staff_df.empty:
            staff_map = {f"{r['name']} ({r['role']})": int(r["staff_id"]) for _, r in staff_df.iterrows()}
            staff_pick = st.selectbox("Handled by (Staff)", ["— None —"] + list(staff_map.keys()))
            staff_id = staff_map.get(staff_pick)
        else:
            st.caption("No staff yet. Add in **👨‍🍳 Staff Management**.")

    with sc2:
      customer_id = None
      if not cust_df.empty:
          cust_map = {f"{r['name']} ({r['contact']})": int(r["customer_id"]) for _, r in cust_df.iterrows()}
          cust_pick = st.selectbox("Existing Customer", ["— None —"] + list(cust_map.keys()))
          customer_id = cust_map.get(cust_pick)
      else:
          st.caption("No saved customers yet. Add in **👤 Customer Management**.")

    # Menu selection
    order_items = []   # (item_id, qty, price, gst%)
    if not menu_df.empty:
        st.subheader("🍴 Menu")
        search = st.text_input("🔎 Search dish")
        if search:
            show_df = menu_df[menu_df["name"].str.contains(search, case=False)]
        else:
            show_df = menu_df

        cols = st.columns(3)
        for idx, row in show_df.iterrows():
            with cols[idx % 3]:
                st.markdown("<div class='menu-card'>", unsafe_allow_html=True)
                st.markdown(f"<h4>{row['name']}</h4>", unsafe_allow_html=True)
                st.markdown(f"<div class='menu-price'>₹{row['price']:.2f}</div>", unsafe_allow_html=True)
                st.caption(f"GST {row['gst_percent']}% · {row['category']}")
                qty = st.number_input(f"Qty #{int(row['item_id'])}", 0, 50, 0, key=f"qty_{int(row['item_id'])}")
                if qty > 0:
                    order_items.append((int(row["item_id"]), int(qty), float(row["price"]), float(row["gst_percent"])))
                st.markdown("</div>", unsafe_allow_html=True)
    else:
        st.warning("No dishes in menu. Add some in **🍽️ Menu Management**.")

    discount = st.number_input("Discount (₹)", 0.0, 100000.0, 0.0, step=10.0)
    pay_method = st.radio("Payment Method", ["Cash", "Card", "UPI"], horizontal=True)

    # Preview
    if st.button("Preview Invoice"):
        if order_items:
            items_for_calc = [(p, g, q) for _, q, p, g in order_items]
            subtotal, gst_total, net = calc_totals(items_for_calc, discount)
            st.subheader("🧾 Invoice Preview")
            st.write(f"Customer: {cust_name or '—'} | Mode: {mode} | Payment: {pay_method}")
            st.write(f"Subtotal: ₹{subtotal:.2f} | GST: ₹{gst_total:.2f} | Discount: ₹{discount:.2f}")
            st.success(f"**Net Total: ₹{net:.2f}**")
        else:
            st.warning("No items selected.")

    # Place order
    if st.button("Place Order"):
        if order_items:
            items_for_calc = [(p, g, q) for _, q, p, g in order_items]
            subtotal, gst_total, net = calc_totals(items_for_calc, discount)
            now = dt.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

            conn = get_conn()
            cur = conn.cursor()
            cur.execute("""
                INSERT INTO orders(customer_name,customer_contact,customer_email,notes,
                                   mode,table_no,delivery_address,discount,payment_method,
                                   total,gst,created_at,staff_id,customer_id)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            """, (cust_name, cust_contact, cust_email, notes, mode, table_no, delivery_address,
                  discount, pay_method, net, gst_total, now, staff_id, customer_id))
            oid = cur.lastrowid

            for item_id, qty, price, gstp in order_items:
                cur.execute("INSERT INTO order_items(order_id,item_id,qty,price) VALUES(?,?,?,?)",
                            (oid, item_id, qty, price))
            conn.commit()
            conn.close()

            bill_payload = {
                "order_id": oid,
                "customer": {
                    "name": cust_name, "contact": cust_contact, "email": cust_email
                },
                "mode": mode,
                "table_no": table_no,
                "delivery_address": delivery_address,
                "notes": notes,
                "payment_method": pay_method,
                "items": [
                    {"item_id": i, "qty": q, "price": p} for i, q, p, _ in order_items
                ],
                "totals": {"subtotal": subtotal, "gst": gst_total, "discount": discount, "net": net},
                "created_at": now,
                "staff_id": staff_id,
                "customer_id": customer_id
            }
            export_bill_json(oid, bill_payload)

            st.success(f"✅ Order #{oid} placed. Net: ₹{net:.2f}")
        else:
            st.warning("No items selected.")

# ORDER HISTORY
elif page == "📜 Order History":
    st.header("📜 Order History")
    conn = get_conn()
    df = pd.read_sql("SELECT * FROM orders ORDER BY created_at DESC", conn)
    conn.close()
    if df.empty:
        st.info("No orders yet.")
    else:
        # Quick filters
        c1, c2, c3 = st.columns(3)
        with c1:
            search_id = st.text_input("Search by Order ID")
        with c2:
            search_name = st.text_input("Search by Customer Name")
        with c3:
            pay_filter = st.selectbox("Payment Filter", ["All", "Cash", "Card", "UPI"])

        show = df.copy()
        if search_id.strip():
            try:
                oid = int(search_id.strip())
                show = show[show["order_id"] == oid]
            except:
                st.warning("Order ID must be a number.")
        if search_name.strip():
            show = show[show["customer_name"].fillna("").str.contains(search_name, case=False)]
        if pay_filter != "All":
            show = show[show["payment_method"] == pay_filter]

        st.dataframe(show, use_container_width=True)

        cancel_id = st.number_input("Cancel Order ID", 0, step=1)
        if st.button("Cancel Order") and cancel_id > 0:
            conn = get_conn()
            cur = conn.cursor()
            cur.execute("DELETE FROM order_items WHERE order_id=?", (cancel_id,))
            cur.execute("DELETE FROM orders WHERE order_id=?", (cancel_id,))
            conn.commit()
            conn.close()
            st.success(f"❌ Order {int(cancel_id)} cancelled")

# REPORTS
elif page == "📊 Reports":
    st.header("📊 Sales Reports")
    # Date range
    c1, c2 = st.columns(2)
    with c1:
        start = st.date_input("From", dt.date.today() - dt.timedelta(days=7))
    with c2:
        end = st.date_input("To", dt.date.today())

    conn = get_conn()
    orders = pd.read_sql("SELECT * FROM orders", conn)
    top_items = pd.read_sql("""
        SELECT m.name, SUM(oi.qty) AS qty
        FROM order_items oi
        JOIN menu m ON m.item_id = oi.item_id
        GROUP BY oi.item_id
        ORDER BY qty DESC
        LIMIT 10
    """, conn)
    conn.close()

    if orders.empty:
        st.info("No orders yet.")
    else:
        orders["created_at"] = pd.to_datetime(orders["created_at"])
        mask = (orders["created_at"].dt.date >= start) & (orders["created_at"].dt.date <= end)
        filt = orders[mask]
        if filt.empty:
            st.warning("No sales in selected range.")
        else:
            st.subheader("💰 Sales Summary")
            total_sales = filt["total"].sum()
            total_orders = len(filt)
            avg_ticket = (total_sales / total_orders) if total_orders else 0
            c1, c2, c3 = st.columns(3)
            c1.metric("Total Sales", f"₹{total_sales:.2f}")
            c2.metric("Orders", int(total_orders))
            c3.metric("Avg Ticket", f"₹{avg_ticket:.2f}")

            st.subheader("📈 Sales by Day")
            by_day = filt.groupby(filt["created_at"].dt.date)["total"].sum()
            st.line_chart(by_day)

            st.subheader("🏆 Top Items (All-Time)")
            if not top_items.empty:
                st.bar_chart(top_items.set_index("name"))

            # Export CSV
            if st.button("Export orders CSV"):
                out = DATA_DIR / f"sales_{start}_{end}.csv"
                filt.to_csv(out, index=False)
                st.success(f"Exported: {out}")

# MENU PAGE (Public view)
elif page == "📖 Menu Page":
    st.header("📖 Our Menu")
    conn = get_conn()
    menu_df = pd.read_sql("SELECT * FROM menu", conn)
    conn.close()

    if menu_df.empty:
        st.warning("No dishes yet. Add dishes in **🍽️ Menu Management**.")
    else:
        cats = list(menu_df["category"].dropna().unique())
        pick = st.multiselect("Filter by category", cats, default=cats)
        show = menu_df[menu_df["category"].isin(pick)] if pick else menu_df

        for cat in (show["category"].unique()):
            st.subheader(f"🍴 {cat}")
            cat_items = show[show["category"] == cat]
            cols = st.columns(3)
            for idx, row in cat_items.iterrows():
                with cols[idx % 3]:
                    st.markdown("<div class='menu-card'>", unsafe_allow_html=True)
                    if row.get("image"):
                        try:
                            st.image(row["image"], use_container_width=True)
                        except Exception:
                            st.caption("Image not found")
                    st.markdown(f"<h4>{row['name']}</h4>", unsafe_allow_html=True)
                    st.markdown(f"<div class='menu-price'>₹{row['price']:.2f}</div>", unsafe_allow_html=True)
                    st.caption(f"GST {row['gst_percent']}%")
                    st.markdown("</div>", unsafe_allow_html=True)

# MENU MANAGEMENT (Admin)
elif page == "🍽️ Menu Management":
    st.header("🍽️ Menu Management")

    # Bulk import via CSV
    with st.expander("📥 Bulk Import via CSV (name,category,price,gst_percent,image)"):
        up = st.file_uploader("Upload CSV", type=["csv"])
        if up is not None:
            try:
                df = pd.read_csv(up)
                need_cols = {"name","category","price","gst_percent"}
                if not need_cols.issubset(set(df.columns)):
                    st.error("CSV must have columns: name, category, price, gst_percent (image optional)")
                else:
                    df = df.fillna({"image": None})
                    conn = get_conn()
                    cur = conn.cursor()
                    cur.executemany(
                        "INSERT INTO menu(name,category,price,gst_percent,image) VALUES(?,?,?,?,?)",
                        list(df[["name","category","price","gst_percent","image"]].itertuples(index=False, name=None))
                    )
                    conn.commit(); conn.close()
                    st.success(f"Imported {len(df)} dishes ✅")
            except Exception as e:
                st.error(f"Failed to import: {e}")

    # Add single dish
    with st.form("add_form", clear_on_submit=True):
        c1, c2 = st.columns([2,1])
        with c1:
            n = st.text_input("Dish Name")
            cat = st.text_input("Category", "Main")
            img = st.text_input("Image URL/Path (optional)")
        with c2:
            price = st.number_input("Price ₹", 0.0, 100000.0, 120.0)
            gst = st.number_input("GST %", 0.0, 50.0, 5.0)
        ok = st.form_submit_button("Add Dish")
        if ok and n:
            conn = get_conn(); cur = conn.cursor()
            cur.execute("INSERT INTO menu(name,category,price,gst_percent,image) VALUES(?,?,?,?,?)",
                        (n, cat, price, gst, img or None))
            conn.commit(); conn.close()
            st.success("Dish added ✅")

    # Search & list
    search = st.text_input("🔍 Search dish")
    conn = get_conn()
    mdf = pd.read_sql("SELECT * FROM menu", conn)
    conn.close()
    if not mdf.empty:
        if search:
            mdf = mdf[mdf["name"].str.contains(search, case=False)]
        st.dataframe(mdf, use_container_width=True)
    else:
        st.caption("No dishes yet.")

# STAFF
elif page == "👨‍🍳 Staff Management":
    st.header("👨‍🍳 Staff Management")
    with st.form("staff_form", clear_on_submit=True):
        n = st.text_input("Staff Name")
        role = st.selectbox("Role", ["Cashier", "Waiter", "Manager", "Chef"])
        contact = st.text_input("Contact")
        ok = st.form_submit_button("Add Staff")
        if ok and n:
            conn = get_conn(); cur = conn.cursor()
            cur.execute("INSERT INTO staff(name,role,contact) VALUES(?,?,?)", (n, role, contact))
            conn.commit(); conn.close()
            st.success("Staff added ✅")

    conn = get_conn()
    sdf = pd.read_sql("SELECT * FROM staff", conn)
    conn.close()
    st.subheader("Staff List")
    if sdf.empty:
        st.caption("No staff yet.")
    else:
        st.dataframe(sdf, use_container_width=True)

# CUSTOMERS
elif page == "👤 Customer Management":
    st.header("👤 Customer Management")
    with st.form("cust_form", clear_on_submit=True):
        n = st.text_input("Customer Name")
        contact = st.text_input("Contact")
        email = st.text_input("Email")
        ok = st.form_submit_button("Save Customer")
        if ok and n:
            conn = get_conn(); cur = conn.cursor()
            cur.execute("INSERT INTO customers(name,contact,email) VALUES(?,?,?)", (n, contact, email))
            conn.commit(); conn.close()
            st.success("Customer saved ✅")

    conn = get_conn()
    cdf = pd.read_sql("SELECT * FROM customers", conn)
    conn.close()
    st.subheader("Customer List")
    if cdf.empty:
        st.caption("No customers yet.")
    else:
        st.dataframe(cdf, use_container_width=True)

# SETTINGS
elif page == "⚙️ Settings":
    st.header("⚙️ Appearance & Settings")
    theme_choice = st.selectbox("🎨 Theme", list(THEMES.keys()), index=list(THEMES.keys()).index(st.session_state.theme))
    apply = st.button("Apply Theme")
    if apply:
        st.session_state.theme = theme_choice
        inject_theme(THEMES[theme_choice])
        st.success(f"Applied: {theme_choice}")

    st.markdown("—")
    st.subheader("🙌 Shortcuts")
    st.caption("• Use **Menu Management** to upload CSV and bulk-create dishes.")
    st.caption("• Bills are exported as JSON in the **DATA** folder automatically after placing an order.")