-- =====================================================
-- CREATE EXPENSE CATEGORIES AND EXPENSES TABLES
-- =====================================================

-- Expense Categories Table
CREATE TABLE IF NOT EXISTS expense_categories (
    expense_category_id INT AUTO_INCREMENT PRIMARY KEY,
    expense_category_name VARCHAR(255) NOT NULL,
    expense_category_description TEXT,
    expense_category_is_active TINYINT(1) DEFAULT 1,
    expense_category_created_by INT,
    expense_category_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_category_created_by) REFERENCES admins(admin_id)
);

-- Expenses Table
CREATE TABLE IF NOT EXISTS expenses (
    expense_id INT AUTO_INCREMENT PRIMARY KEY,
    expense_category_id INT NOT NULL,
    expense_amount DECIMAL(15, 2) NOT NULL,
    expense_description TEXT,
    expense_date DATE NOT NULL,
    expense_notes TEXT,
    expense_created_by INT,
    expense_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_category_id) REFERENCES expense_categories(expense_category_id),
    FOREIGN KEY (expense_created_by) REFERENCES admins(admin_id)
);

-- Create indexes for better performance
CREATE INDEX idx_expenses_category ON expenses(expense_category_id);
CREATE INDEX idx_expenses_date ON expenses(expense_date);
CREATE INDEX idx_expenses_created_by ON expenses(expense_created_by);