-- Financial Module Database Schema

-- 1. Chart of Accounts
CREATE TABLE chart_of_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_code VARCHAR(20) UNIQUE NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    account_type ENUM('asset', 'liability', 'equity', 'income', 'expense') NOT NULL,
    parent_id INT NULL,
    level INT DEFAULT 1,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES chart_of_accounts(id),
    INDEX idx_account_type (account_type),
    INDEX idx_active (is_active)
);

-- 2. Journal Entries
CREATE TABLE journal_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reference_number VARCHAR(50) UNIQUE NOT NULL,
    journal_type ENUM('general', 'sales', 'purchase', 'cash_receipt', 'cash_payment', 'adjustment') DEFAULT 'general',
    description TEXT NOT NULL,
    transaction_date DATE NOT NULL,
    posting_date TIMESTAMP NULL,
    created_by INT,
    approved_by INT,
    status ENUM('draft', 'posted', 'reversed') DEFAULT 'draft',
    reversal_of INT NULL, -- For tracking reversed entries
    total_debit DECIMAL(15,2) DEFAULT 0,
    total_credit DECIMAL(15,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (reversal_of) REFERENCES journal_entries(id),
    INDEX idx_status (status),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_journal_type (journal_type)
);

-- 3. Journal Entry Lines
CREATE TABLE journal_entry_lines (
    id INT PRIMARY KEY AUTO_INCREMENT,
    journal_entry_id INT NOT NULL,
    account_id INT NOT NULL,
    debit_amount DECIMAL(15,2) DEFAULT 0,
    credit_amount DECIMAL(15,2) DEFAULT 0,
    description TEXT,
    reference_type VARCHAR(50), -- sales_order, purchase_order, etc.
    reference_id INT, -- ID of the related transaction
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id),
    INDEX idx_journal_entry (journal_entry_id),
    INDEX idx_account (account_id),
    INDEX idx_reference (reference_type, reference_id)
);

-- 4. Add financial columns to customers table (if not exists)
ALTER TABLE customers 
ADD COLUMN IF NOT EXISTS outstanding_balance DECIMAL(15,2) DEFAULT 0,
ADD COLUMN IF NOT EXISTS current_balance DECIMAL(15,2) DEFAULT 0,
ADD COLUMN IF NOT EXISTS credit_used DECIMAL(15,2) DEFAULT 0;

-- 5. Add financial columns to suppliers table (if not exists)
ALTER TABLE suppliers 
ADD COLUMN IF NOT EXISTS outstanding_balance DECIMAL(15,2) DEFAULT 0,
ADD COLUMN IF NOT EXISTS current_balance DECIMAL(15,2) DEFAULT 0;

-- 6. Cash Transactions
CREATE TABLE cash_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_number VARCHAR(50) UNIQUE NOT NULL,
    transaction_type ENUM('income', 'expense', 'transfer', 'adjustment') NOT NULL,
    transaction_category VARCHAR(100),
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    description TEXT,
    created_by INT,
    approved_by INT,
    status ENUM('draft', 'approved', 'reversed') DEFAULT 'draft',
    reversal_of INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (reversal_of) REFERENCES cash_transactions(id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_status (status)
);

-- 7. Financial Periods
CREATE TABLE financial_periods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    period_name VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_closed BOOLEAN DEFAULT FALSE,
    closing_date TIMESTAMP NULL,
    closed_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (closed_by) REFERENCES users(id),
    UNIQUE KEY unique_period (start_date, end_date),
    CHECK (end_date >= start_date),
    INDEX idx_is_closed (is_closed)
);

-- 8. Account Balances (for performance)
CREATE TABLE account_balances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    period_id INT NOT NULL,
    opening_balance DECIMAL(15,2) DEFAULT 0,
    debit_amount DECIMAL(15,2) DEFAULT 0,
    credit_amount DECIMAL(15,2) DEFAULT 0,
    closing_balance DECIMAL(15,2) AS (opening_balance + debit_amount - credit_amount) STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id),
    FOREIGN KEY (period_id) REFERENCES financial_periods(id),
    UNIQUE KEY unique_account_period (account_id, period_id)
);

-- 9. Customer Transactions
CREATE TABLE customer_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    transaction_type ENUM('invoice', 'payment', 'credit_note', 'debit_note', 'adjustment') NOT NULL,
    reference_type VARCHAR(50), -- sales_order, cash_receipt, etc.
    reference_id INT,
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    due_date DATE,
    status ENUM('open', 'partial', 'closed', 'overdue') DEFAULT 'open',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_customer (customer_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_status (status)
);

-- 10. Supplier Transactions
CREATE TABLE supplier_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    transaction_type ENUM('invoice', 'payment', 'credit_note', 'debit_note', 'adjustment') NOT NULL,
    reference_type VARCHAR(50), -- purchase_order, cash_payment, etc.
    reference_id INT,
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    due_date DATE,
    status ENUM('open', 'partial', 'closed', 'overdue') DEFAULT 'open',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_status (status)
);

-- Insert default chart of accounts
INSERT INTO chart_of_accounts (account_code, account_name, account_type, level) VALUES
('1000', 'Cash on Hand', 'asset', 1),
('1001', 'Bank Account', 'asset', 1),
('1200', 'Accounts Receivable', 'asset', 1),
('1201', 'Inventory', 'asset', 1),
('1500', 'Fixed Assets', 'asset', 1),
('2000', 'Accounts Payable', 'liability', 1),
('2001', 'Accrued Expenses', 'liability', 1),
('2500', 'Loans Payable', 'liability', 1),
('3000', 'Owner Equity', 'equity', 1),
('4000', 'Sales Revenue', 'income', 1),
('4100', 'Service Revenue', 'income', 1),
('5000', 'Cost of Goods Sold', 'expense', 1),
('5100', 'Operating Expenses', 'expense', 1),
('5200', 'Salary Expense', 'expense', 1),
('5300', 'Rent Expense', 'expense', 1);

-- Insert current financial period
INSERT INTO financial_periods (period_name, start_date, end_date) VALUES
('2023 - October', '2023-10-01', '2023-10-31');