-- Comprehensive Expenses System Schema
-- Tables use business-specific naming: {businesscode}_expenses_*

-- Expense Categories (extends beyond cash flow categories)
CREATE TABLE {business_code}_expenses_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    category_code VARCHAR(50) UNIQUE,
    description TEXT,
    parent_category_id INT DEFAULT NULL,
    is_system_category BOOLEAN DEFAULT FALSE,
    requires_approval BOOLEAN DEFAULT FALSE,
    approval_limit DECIMAL(15,2) DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_category_id) REFERENCES {business_code}_expenses_categories(id)
);

-- Vendors/Suppliers Management
CREATE TABLE {business_code}_expenses_vendors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vendor_code VARCHAR(50) UNIQUE NOT NULL,
    vendor_name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    tax_id VARCHAR(50),
    payment_terms VARCHAR(100),
    currency_code VARCHAR(3) DEFAULT 'PHP',
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Main Expenses Table
CREATE TABLE {business_code}_expenses_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_number VARCHAR(100) UNIQUE NOT NULL,
    category_id INT NOT NULL,
    vendor_id INT DEFAULT NULL,
    shift_id INT DEFAULT NULL,
    expense_date DATETIME NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    total_amount DECIMAL(15,2) NOT NULL,
    currency_code VARCHAR(3) DEFAULT 'PHP',
    exchange_rate DECIMAL(10,4) DEFAULT 1.0000,
    description TEXT NOT NULL,
    reference_number VARCHAR(100),
    payment_method_id INT NOT NULL,
    payment_status ENUM('PENDING', 'PAID', 'PARTIALLY_PAID', 'OVERDUE', 'CANCELLED') DEFAULT 'PENDING',
    approval_status ENUM('DRAFT', 'PENDING_APPROVAL', 'APPROVED', 'REJECTED') DEFAULT 'DRAFT',
    recurring_type ENUM('NONE', 'DAILY', 'WEEKLY', 'MONTHLY', 'QUARTERLY', 'YEARLY') DEFAULT 'NONE',
    recurring_interval INT DEFAULT 1,
    recurring_until DATE DEFAULT NULL,
    parent_expense_id INT DEFAULT NULL,
    receipt_attachment VARCHAR(255),
    notes TEXT,
    created_by VARCHAR(100),
    approved_by VARCHAR(100),
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES {business_code}_expenses_categories(id),
    FOREIGN KEY (vendor_id) REFERENCES {business_code}_expenses_vendors(id),
    FOREIGN KEY (parent_expense_id) REFERENCES {business_code}_expenses_transactions(id),
    INDEX idx_shift_id (shift_id),
    INDEX idx_payment_method_id (payment_method_id),
    FOREIGN KEY (payment_method_id) REFERENCES {business_code}_payment_method(id)
);

-- Expense Line Items (for detailed tracking)
CREATE TABLE {business_code}_expenses_line_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_id INT NOT NULL,
    item_description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,3) DEFAULT 1.000,
    unit_price DECIMAL(15,2) NOT NULL,
    line_total DECIMAL(15,2) NOT NULL,
    tax_rate DECIMAL(5,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_id) REFERENCES {business_code}_expenses_transactions(id) ON DELETE CASCADE
);

-- Expense Payments Tracking
CREATE TABLE {business_code}_expenses_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_id INT NOT NULL,
    payment_amount DECIMAL(15,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method_id INT NOT NULL,
    reference_number VARCHAR(100),
    notes TEXT,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_id) REFERENCES {business_code}_expenses_transactions(id) ON DELETE CASCADE,
    INDEX idx_payment_method_id (payment_method_id),
    FOREIGN KEY (payment_method_id) REFERENCES {business_code}_payment_method(id)
);

-- Expense Approvals Workflow
CREATE TABLE {business_code}_expenses_approvals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_id INT NOT NULL,
    approver_name VARCHAR(100) NOT NULL,
    approval_level INT DEFAULT 1,
    status ENUM('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'PENDING',
    comments TEXT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_id) REFERENCES {business_code}_expenses_transactions(id) ON DELETE CASCADE
);

-- Recurring Expenses Schedule
CREATE TABLE {business_code}_expenses_recurring (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_expense_id INT NOT NULL,
    next_due_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_generated_date DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (template_expense_id) REFERENCES {business_code}_expenses_transactions(id)
);

-- Insert Default Expense Categories
INSERT INTO {business_code}_expenses_categories (category_name, category_code, description, is_system_category) VALUES
('Supplies and Office Materials', 'SUPPLIES', 'Stationery, cleaning supplies, packaging materials, and minor operational items.', TRUE),
('Rent and Lease', 'RENT', 'Expenses for renting or leasing storefronts, offices, or storage spaces.', TRUE),
('Marketing and Advertising', 'MARKETING', 'Promotional activities, social media marketing, printing, and signage.', TRUE),
('Professional Services', 'PROFESSIONAL', 'Fees for legal, accounting, consulting, or other professional services.', TRUE),
('Technology and Software', 'TECHNOLOGY', 'Expenses for POS software, business management tools, and IT support.', TRUE),
('Transportation and Delivery', 'TRANSPORTATION', 'Costs for vehicle expenses, fuel, courier, or delivery services.', TRUE),
('Insurance', 'INSURANCE', 'Business, property, liability, and worker’s compensation insurance premiums.', TRUE),
('Training and Development', 'TRAINING', 'Employee training programs, workshops, and skill development.', TRUE),
('Equipment and Maintenance', 'MAINTENANCE', 'Purchase or repair of equipment, machinery, kitchen appliances, or POS hardware.', TRUE),
('Banking and Financial Charges', 'BANK_FEES', 'Bank fees, interest on loans, credit card processing fees.', TRUE),
('Licenses and Permits', 'PERMITS', 'Fees paid to government authorities for operational licenses and permits.', TRUE),
('Payroll Taxes and Benefits', 'BENEFITS', 'Employer contributions to social security, health insurance, retirement plans, and other benefits.', TRUE),
('Inventory Purchases', 'INVENTORY', 'Cost of goods bought for resale or use in operations, including wholesale stock.', TRUE),
('Utilities', 'UTILITIES', 'Costs for electricity, water, gas, internet, and phone services.', TRUE),
('Salaries and Wages', 'SALARIES', 'Payments to employees, including wages, salaries, and overtime.', TRUE),
('Food and Beverage Costs', 'FB_COST', 'Expenses for purchasing ingredients, beverages, and related consumables.', TRUE),
('Depreciation and Amortization', 'DEPRECIATION', 'Gradual expense recognition for tangible and intangible assets.', TRUE),
('Miscellaneous', 'MISCELLANEOUS', 'Unclassified or irregular expenses that don’t fit into other categories.', TRUE);


-- Create indexes for better performance
CREATE INDEX idx_expenses_transactions_date ON {business_code}_expenses_transactions(expense_date);
CREATE INDEX idx_expenses_transactions_status ON {business_code}_expenses_transactions(payment_status, approval_status);
CREATE INDEX idx_expenses_transactions_vendor ON {business_code}_expenses_transactions(vendor_id);
CREATE INDEX idx_expenses_transactions_category ON {business_code}_expenses_transactions(category_id);
CREATE INDEX idx_expenses_payments_expense ON {business_code}_expenses_payments(expense_id);
CREATE INDEX idx_expenses_line_items_expense ON {business_code}_expenses_line_items(expense_id); 