-- Cash Flow Categories Table
CREATE TABLE IF NOT EXISTS `{business_code}_cashflow_categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_name` VARCHAR(100) NOT NULL,
    `category_type` ENUM('income', 'expense') NOT NULL,
    `description` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_system_category` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_category_type` (`category_type`),
    INDEX `idx_active` (`is_active`),
    UNIQUE KEY `unique_category_name_type` (`category_name`, `category_type`)
);

-- Cash Flow Transactions Table
CREATE TABLE IF NOT EXISTS `{business_code}_cashflow_transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `transaction_type` ENUM('income', 'expense') NOT NULL,
    `category_id` INT NOT NULL,
    `amount` DECIMAL(15,2) NOT NULL,
    `transaction_date` DATETIME NOT NULL,
    `description` TEXT,
    `reference_number` VARCHAR(100),
    `payment_method_id` INT NOT NULL,
    `receipt_image_path` VARCHAR(500),
    `notes` TEXT,
    `linked_sales_transaction_id` INT NULL, -- Link to sales_transactions for automatic income
    `created_by` VARCHAR(100),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_transaction_type` (`transaction_type`),
    INDEX `idx_category_id` (`category_id`),
    INDEX `idx_transaction_date` (`transaction_date`),
    INDEX `idx_linked_sales` (`linked_sales_transaction_id`),
    INDEX `idx_payment_method_id` (`payment_method_id`),
    FOREIGN KEY (`category_id`) REFERENCES `{business_code}_cashflow_categories`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`payment_method_id`) REFERENCES `{business_code}_payment_method`(`id`) ON DELETE RESTRICT
);

-- Insert default system categories for new businesses
INSERT INTO `{business_code}_cashflow_categories` (`category_name`, `category_type`, `description`, `is_system_category`) VALUES
-- Income Categories
('Sales Revenue', 'income', 'Revenue from product sales', TRUE),
('Service Revenue', 'income', 'Revenue from services provided', TRUE),
('Credit Settlements', 'income', 'Payments received from credit customers', TRUE),
('Other Income', 'income', 'Miscellaneous income sources', TRUE),

-- Expense Categories  
('Cost of Goods Sold', 'expense', 'Direct costs of products sold', TRUE),
('Returns & Refunds', 'expense', 'Refunds and returns processing', TRUE),
('Rent & Utilities', 'expense', 'Store rent, electricity, water, internet', TRUE),
('Staff Wages', 'expense', 'Employee salaries and wages', TRUE),
('Marketing & Advertising', 'expense', 'Promotional activities and advertising', TRUE),
('Office Supplies', 'expense', 'Stationery, printing, and office materials', TRUE),
('Equipment & Maintenance', 'expense', 'Equipment purchases and repairs', TRUE),
('Transportation', 'expense', 'Delivery, fuel, and transport costs', TRUE),
('Professional Services', 'expense', 'Accounting, legal, and consulting fees', TRUE),
('Insurance', 'expense', 'Business insurance premiums', TRUE),
('Taxes & Licenses', 'expense', 'Government taxes and license fees', TRUE),
('Bank Charges', 'expense', 'Banking fees and transaction charges', TRUE),
('Other Expenses', 'expense', 'Miscellaneous business expenses', TRUE); 