-- ==============================================================================================
-- DATABASE SCHEMA UPDATES FOR TABLE TIMERS AND EXPENSE MANAGEMENT
-- Execute these queries manually in your MySQL database
-- ==============================================================================================

-- ----------------------------------------------------------------------------------------------
-- 1. Table Order Timers
-- ----------------------------------------------------------------------------------------------

-- Add the tracking column for when an order starts on a table
ALTER TABLE restaurant_tables
ADD COLUMN current_order_started_at DATETIME NULL DEFAULT NULL;

-- ----------------------------------------------------------------------------------------------
-- 2. Expense Management (Accounting)
-- ----------------------------------------------------------------------------------------------

-- Create expense categories table
CREATE TABLE IF NOT EXISTS expense_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- Pre-populate with typical default categories
INSERT IGNORE INTO expense_categories (name) VALUES 
('Groceries/Ingredients'),
('Utilities'),
('Maintenance & Repairs'),
('Salaries & Wages'),
('Refunds'),
('Marketing'),
('Miscellaneous');

-- Create expenses table
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    txn_id VARCHAR(50) NOT NULL UNIQUE,
    category_id INT NOT NULL,
    payment_method VARCHAR(50) NOT NULL, -- e.g., 'Cash', 'UPI', 'Card', 'NetBanking'
    amount DECIMAL(10, 2) NOT NULL,
    description TEXT,
    date DATE NOT NULL,
    time TIME NOT NULL,
    user_id VARCHAR(100) NULL, -- Optional tracking for who logged it
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE RESTRICT
);

-- (Optional) Create cash drawer logs table to support End-of-Day closing discrepancy tracking
CREATE TABLE IF NOT EXISTS cash_drawer_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    starting_cash DECIMAL(10, 2) DEFAULT 0.00,
    expected_cash DECIMAL(10, 2) DEFAULT 0.00,
    actual_cash DECIMAL(10, 2) DEFAULT 0.00,
    discrepancy DECIMAL(10, 2) DEFAULT 0.00,
    closed_by VARCHAR(100) NULL,
    closed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT
);