# filepath: d:\shopai\app.py
import gradio as gr
import sqlite3
import whisper
import re
from datetime import datetime, timedelta
import csv  # Moved outside the function for efficiency

# Load Whisper model once at module load time (only done once):
model = whisper.load_model("tiny")

# Initialize SQLite database
def init_db():
    conn = sqlite3.connect("sales.db")
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS sales (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            item TEXT,
            quantity INTEGER,
            price REAL,
            revenue REAL,
            profit REAL
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            description TEXT,
            amount REAL
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS credits (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            customer TEXT,
            amount REAL,
            status TEXT
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE,
            cost_price REAL,
            selling_price REAL
        )
    ''')
    conn.commit()
    conn.close()

def transcribe(audio):
    if audio is None:
        return ""
    try:
        result = model.transcribe(audio)
        return result["text"]
    except Exception as e:
        return f"Transcription error: {e}"

# Placeholder for processing sales input
def process_input(text):
    if not text or not text.strip():
        return (
            "⚠️ Please enter a command or sales record.\n\n"
            "Examples:\n"
            "- Sold 5 items of soap\n"
            "- Show today's sales\n"
            "- Show this week's sales\n"
            "- Show sales for 2025-06-08\n"
            "- Export sales"
        )
    text = text.strip().lower()

    # (1) Show today's sales
    if "show today's sales" in text or "today sales" in text:
        date = datetime.now().strftime("%Y-%m-%d")
        return get_sales_summary(date=date)

    # (2) Show this week's sales
    elif "show this week's sales" in text or "this week sales" in text:
        today = datetime.now()
        start_of_week = (today - timedelta(days=today.weekday())).strftime("%Y-%m-%d")
        end_of_week = today.strftime("%Y-%m-%d")
        return get_sales_summary(start_date=start_of_week, end_date=end_of_week)

    # (3) Show this month's sales
    elif "show this month's sales" in text:
        today = datetime.now()
        start_of_month = today.replace(day=1).strftime("%Y-%m-%d")
        end_of_month = today.strftime("%Y-%m-%d")
        return get_sales_summary(start_date=start_of_month, end_date=end_of_month)

    # (4) Show this year's sales
    elif "show this year's sales" in text:
        today = datetime.now()
        start_of_year = today.replace(month=1, day=1).strftime("%Y-%m-%d")
        end_of_year = today.strftime("%Y-%m-%d")
        return get_sales_summary(start_date=start_of_year, end_date=end_of_year)

    # (5) Show sales for a specific date
    elif "show sales for" in text:
        match = re.search(r"show sales for (\d{4}-\d{2}-\d{2})", text)
        if match:
            date = match.group(1)
            return get_sales_summary(date=date)
        else:
            return (
                "⚠️ Please specify the date in YYYY-MM-DD format.\n"
                "Example: `Show sales for 2025-06-08`"
            )

    # (6) Export sales
    elif "export sales" in text:
        try:
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute("SELECT * FROM sales")
                rows = c.fetchall()
            with open("sales_export.csv", "w", newline="", encoding="utf-8") as f:
                writer = csv.writer(f)
                writer.writerow(["id", "date", "item", "quantity", "price", "revenue", "profit"])
                writer.writerows(rows)
            return "✅ Sales data exported to **sales_export.csv**"
        except Exception as e:
            return f"❌ Error exporting data: {e}"

    # (7) Business analytics
    elif (
        "show analytics" in text
        or "business analytics" in text
        or "show business report" in text
        or "business report" in text
    ):
        return get_business_analytics()

    # (8) Show recent expenses
    elif "show expenses" in text:
        with sqlite3.connect("sales.db") as conn:
            c = conn.cursor()
            c.execute("SELECT date, description, amount FROM expenses ORDER BY date DESC LIMIT 10")
            rows = c.fetchall()
        if not rows:
            return "No expenses recorded."
        summary = "### Recent Expenses\n\n"
        for row in rows:
            summary += f"- {row[0]}: {row[1]} ₹{row[2]}\n"
        return summary

    # (9) Show pending credits
    elif "show credits" in text:
        with sqlite3.connect("sales.db") as conn:
            c = conn.cursor()
            c.execute("SELECT date, customer, amount, status FROM credits WHERE status='pending' ORDER BY date DESC")
            rows = c.fetchall()
        if not rows:
            return "No pending credits."
        summary = "### Pending Credits\n\n"
        for row in rows:
            summary += f"- {row[0]}: {row[1]} ₹{row[2]} ({row[3]})\n"
        return summary

    # (10) Add expense
    elif text.startswith("expense"):
        match = re.search(r"expense\s+([\w\s]+)\s+(\d+)", text)
        if match:
            desc = match.group(1).strip()
            amount = float(match.group(2))
            date = datetime.now().strftime("%Y-%m-%d")
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute("INSERT INTO expenses (date, description, amount) VALUES (?, ?, ?)", (date, desc, amount))
                conn.commit()
            return f"✅ Expense recorded: {desc} ₹{amount}"
        else:
            return "⚠️ Please use format: Expense [description] [amount] (e.g., Expense rent 5000)"

    # (11) Add credit
    elif text.startswith("credit"):
        match = re.search(r"credit\s+([\w\s]+)\s+(\d+)", text)
        if match:
            customer = match.group(1).strip()
            amount = float(match.group(2))
            date = datetime.now().strftime("%Y-%m-%d")
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute("INSERT INTO credits (date, customer, amount, status) VALUES (?, ?, ?, ?)", (date, customer, amount, "pending"))
                conn.commit()
            return f"✅ Credit recorded: {customer} ₹{amount} (pending)"
        else:
            return "⚠️ Please use format: Credit [customer] [amount] (e.g., Credit Ramesh 200)"

    # (12) Add or update item prices
    match = re.search(r"(add|update) item (\w+)\s+cost\s*([\d.]+)\s+sell(?:ing)?\s*([\d.]+)", text)
    if match:
        action = match.group(1)
        item = match.group(2).lower()
        cost = float(match.group(3))
        sell = float(match.group(4))

        try:
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute(
                    "INSERT OR REPLACE INTO items (name, cost_price, selling_price) VALUES (?, ?, ?)",
                    (item, cost, sell)
                )
                conn.commit()
        except sqlite3.Error as db_err:
            return f"❌ Database error while updating item: {db_err}"

        return f"✅ Item '{item}' {action}d: Cost ₹{cost}, Selling ₹{sell}"

    # (13) SALES: "Sold X items of Y" (Requires cost/sell in DB)
    match = re.search(r"sold\s*(\d+)\s*items?\s+of\s+(\w+)", text)
    if match:
        try:
            quantity = int(match.group(1))
            item_name = match.group(2).lower()

            # Lookup cost & selling price
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute("SELECT cost_price, selling_price FROM items WHERE name = ?", (item_name,))
                row = c.fetchone()

            if not row:
                return (
                    f"⚠️ Item '{item_name}' not found.\n\n"
                    "Please add it first using:\n"
                    "`Add item soap cost 7 sell 10`"
                )

            cost_price, selling_price = row
            revenue = quantity * selling_price
            profit = (selling_price - cost_price) * quantity
            gst_rate = 0.18
            gst_amount = revenue * gst_rate
            total_with_gst = revenue + gst_amount
            date = datetime.now().strftime("%Y-%m-%d")

            # Insert sale
            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute(
                    "INSERT INTO sales (date, item, quantity, price, revenue, profit) VALUES (?, ?, ?, ?, ?, ?)",
                    (date, item_name, quantity, selling_price, revenue, profit)
                )
                conn.commit()

            return (
                f"✅ Recorded **{quantity} {item_name}(s)** at ₹{selling_price} each.\n"
                f"Revenue: ₹{revenue}, Profit: ₹{profit}\n"
                f"GST (18%): ₹{gst_amount:.2f}, Total with GST: ₹{total_with_gst:.2f}"
            )
        except Exception as e:
            return f"❌ Error recording sale: {e}"

    # (14) Simple sales: "sold 5 soaps"
    match = re.search(r"sold\s*(\d+)\s+(\w+)", text)
    if match:
        quantity = int(match.group(1))
        item = match.group(2).lower()
        with sqlite3.connect("sales.db") as conn:
            c = conn.cursor()
            c.execute("SELECT cost_price, selling_price FROM items WHERE name = ?", (item,))
            row = c.fetchone()

        if row:
            cost_price, selling_price = row
            revenue = quantity * selling_price
            profit = (selling_price - cost_price) * quantity
            gst_rate = 0.18
            gst_amount = revenue * gst_rate
            total_with_gst = revenue + gst_amount
            date = datetime.now().strftime("%Y-%m-%d")

            with sqlite3.connect("sales.db") as conn:
                c = conn.cursor()
                c.execute(
                    "INSERT INTO sales (date, item, quantity, price, revenue, profit) VALUES (?, ?, ?, ?, ?, ?)",
                    (date, item, quantity, selling_price, revenue, profit)
                )
                conn.commit()

            return (
                f"✅ Recorded: **{quantity} {item}(s)** at ₹{selling_price} each.\n"
                f"Revenue: ₹{revenue}, Profit: ₹{profit}\n"
                f"GST (18%): ₹{gst_amount:.2f}, Total with GST: ₹{total_with_gst:.2f}"
            )
        else:
            return f"⚠️ Item '{item}' not found. Please add it first: `Add item {item} cost [cost] sell [sell_price]`"

    # (15) Fallback
    return (
        "⚠️ Could not understand input.\n\n"
        "Please try commands like:\n"
        "- `Add item soap cost 7 sell 10`\n"
        "- `Sold 5 items of soap`\n"
        "- `Show today's sales`\n"
        "- `Export sales`\n"
    )

# Helper function for summaries
def get_sales_summary(date=None, start_date=None, end_date=None):
    try:
        with sqlite3.connect("sales.db") as conn:
            c = conn.cursor()
            if date:
                c.execute("SELECT item, quantity, revenue, profit FROM sales WHERE date = ?", (date,))
            elif start_date and end_date:
                c.execute("SELECT item, quantity, revenue, profit FROM sales WHERE date BETWEEN ? AND ?", (start_date, end_date))
            else:
                return "Invalid date range."
            rows = c.fetchall()

        if not rows:
            return "No sales recorded for the selected period."
        summary = "### Sales Summary\n\n"
        total_revenue = 0
        total_profit = 0
        for row in rows:
            summary += f"- **{row[0]}**: {row[1]} sold, Revenue ₹{row[2]}, Profit ₹{row[3]}\n"
            total_revenue += row[2]
            total_profit += row[3]
        summary += f"\n**Total Revenue:** ₹{total_revenue}\n\n**Total Profit:** ₹{total_profit}"
        return summary
    except Exception as e:
        return f"Error fetching summary: {e}"

def get_recent_sales(limit=10):
    try:
        conn = sqlite3.connect("sales.db")
        c = conn.cursor()
        c.execute("SELECT date, item, quantity, price, revenue, profit FROM sales ORDER BY id DESC LIMIT ?", (limit,))
        rows = c.fetchall()
        conn.close()
        headers = ["Date", "Item", "Quantity", "Price", "Revenue", "Profit"]
        return [headers] + [list(row) for row in rows]
    except Exception as e:
        return [["Error fetching sales history:", str(e)]]

def get_business_analytics():
    try:
        conn = sqlite3.connect("sales.db")
        c = conn.cursor()
        # Total sales, revenue, profit
        c.execute("SELECT COUNT(*), SUM(quantity), SUM(revenue), SUM(profit) FROM sales")
        total_count, total_qty, total_revenue, total_profit = c.fetchone()
        # Top-selling item
        c.execute("SELECT item, SUM(quantity) as qty FROM sales GROUP BY item ORDER BY qty DESC LIMIT 1")
        top_item = c.fetchone()
        # Best day (by revenue)
        c.execute("SELECT date, SUM(revenue) as rev FROM sales GROUP BY date ORDER BY rev DESC LIMIT 1")
        best_day = c.fetchone()
        # Average sale value
        c.execute("SELECT AVG(revenue) FROM sales")
        avg_sale = c.fetchone()[0]
        # Unique items
        c.execute("SELECT COUNT(DISTINCT item) FROM sales")
        unique_items = c.fetchone()[0]
        # Total expenses
        c.execute("SELECT SUM(amount) FROM expenses")
        total_expenses = c.fetchone()[0] or 0
        # Total credits
        c.execute("SELECT SUM(amount) FROM credits WHERE status='pending'")
        total_credits = c.fetchone()[0] or 0
        # Net profit
        net_profit = (total_profit or 0) - total_expenses
        # Item-wise breakdown
        c.execute("SELECT item, SUM(quantity), SUM(revenue), SUM(profit) FROM sales GROUP BY item")
        item_rows = c.fetchall()
        conn.close()

        summary = "### 📊 Business Analytics\n\n"
        summary += f"- **Total sales records:** {total_count or 0}\n"
        summary += f"- **Total quantity sold:** {total_qty or 0}\n"
        summary += f"- **Unique items sold:** {unique_items or 0}\n"
        summary += f"- **Total revenue:** ₹{total_revenue or 0}\n"
        summary += f"- **Total profit:** ₹{total_profit or 0}\n"
        summary += f"- **Total expenses:** ₹{total_expenses}\n"
        summary += f"- **Net profit (after expenses):** ₹{net_profit}\n"
        summary += f"- **Pending credits:** ₹{total_credits}\n"
        if top_item:
            summary += f"- **Top-selling item:** {top_item[0]} ({top_item[1]} sold)\n"
        if best_day:
            summary += f"- **Best day:** {best_day[0]} (Revenue ₹{best_day[1]})\n"
        summary += f"- **Average sale value:** ₹{avg_sale or 0:.2f}\n"
        summary += "\n#### Item-wise Breakdown\n"
        for row in item_rows:
            summary += f"- **{row[0]}**: {row[1]} sold, Revenue ₹{row[2]}, Profit ₹{row[3]}\n"
        summary += "\n_Tip: For trends, export your data and use Excel or Google Sheets charts!_"
        return summary
    except Exception as e:
        return f"Error generating analytics: {e}"

# Gradio interface
def handle_input(text, audio):
    if audio is not None:
        text = transcribe(audio)
    result = process_input(text)
    sales_table = get_recent_sales()
    return result, sales_table

init_db()

iface = gr.Interface(
    fn=handle_input,
    inputs=[
        gr.Textbox(
            label="Enter sales or summary",
            placeholder="E.g. Sold 5 soaps at 10 each\nShow today's sales\nShow this week's sales\nShow sales for 2025-06-08\nExport sales"
        ),
        gr.Audio(type="filepath", label="Or speak")
    ],
    outputs=[
        gr.Markdown(label="Result"),
        # "label" might be deprecated in gr.Dataframe
        # rename to "header" or remove if it generates a warning:
        gr.Dataframe(headers=["Date", "Item", "Quantity", "Price", "Revenue", "Profit"])
    ],
    title="ShopAI Assistant",
    description=(
        "Welcome to ShopAI! 🎤📝\n\n"
        "• **Record sales** by typing or speaking (e.g., `Sold 5 soaps at 10 each`).\n"
        "• **Get summaries**: `Show today's sales`, `Show this week's sales`, or `Show sales for 2025-06-08`.\n"
        "• **Export all sales as CSV**: `Export sales`.\n"
        "• **Add or update items**: `Add item soap cost 7 sell 10`\n"
        "• **Quick sales**: `Sold 5 soap`\n"
        "• All calculations are automatic!\n\n"
        "Below, you can also see your recent sales history.\n"
        "Click **Clear** to reset the form."
    ),
    flagging_mode="never",
    examples=[
        ["Sold 3 biscuits at 5 each", None],
        ["Show today's sales", None],
        ["Show this week's sales", None],
        ["Show sales for 2025-06-08", None],
        ["Export sales", None]
    ],
    theme="default"
)

if __name__ == "__main__":
    iface.launch()