




-- Add is_active column to vendors table
ALTER TABLE `vendors` 
ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active, 0=Inactive' AFTER `address`;

-- Update existing suppliers to be active by default
UPDATE `vendors` SET `is_active` = 1 WHERE `is_active` IS NULL;

-- Add index for better performance when filtering by status
ALTER TABLE `vendors` 
ADD INDEX `idx_vendors_active` (`is_active`);



-- 1. Chart of Accounts System
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', 'revenue', 'expense') NOT NULL,
    account_category ENUM('current_asset', 'fixed_asset', 'current_liability', 'long_term_liability', 'equity', 'revenue', 'cost_of_sales', 'operating_expense', 'other_expense') NOT NULL,
    parent_account_id INT NULL,
    account_level INT DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    current_balance DECIMAL(15,2) DEFAULT 0.00,
    store_id INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_account_code (account_code),
    INDEX idx_account_type (account_type),
    INDEX idx_store_id (store_id),
    INDEX idx_is_active (is_active)
);

-- 2. Account Types
CREATE TABLE account_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type_code VARCHAR(10) UNIQUE NOT NULL,
    type_name VARCHAR(50) NOT NULL,
    default_side ENUM('DR', 'CR') NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_code (type_code),
    INDEX idx_sort_order (sort_order)
);

-- 3. Journal System
CREATE TABLE journal_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entry_number VARCHAR(20) UNIQUE NOT NULL,
    entry_date DATE NOT NULL,
    transaction_date DATE NOT NULL,
    source_type ENUM('sale', 'purchase', 'purchase_return', 'expense', 'payment', 'receipt', 'adjustment', 'manual', 'opening_balance') NOT NULL,
    reference_id INT NULL,
    description TEXT,
    total_debit DECIMAL(15,2) DEFAULT 0.00,
    total_credit DECIMAL(15,2) DEFAULT 0.00,
    status ENUM('draft', 'posted', 'void') DEFAULT 'draft',
    store_id INT DEFAULT 1,
    user_id INT NOT NULL,
    posted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_entry_number (entry_number),
    INDEX idx_entry_date (entry_date),
    INDEX idx_source_type (source_type),
    INDEX idx_reference_id (reference_id),
    INDEX idx_status (status),
    INDEX idx_store_id (store_id),
    INDEX idx_user_id (user_id)
);

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.00,
    credit_amount DECIMAL(15,2) DEFAULT 0.00,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_journal_entry_id (journal_entry_id),
    INDEX idx_account_id (account_id),
    FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE
);

-- 4. Account Mapping System
CREATE TABLE account_mappings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    mapping_code VARCHAR(50) UNIQUE NOT NULL,
    mapping_name VARCHAR(100) NOT NULL,
    account_id INT NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mapping_code (mapping_code),
    INDEX idx_account_id (account_id),
    INDEX idx_is_active (is_active)
);

-- 5. Financial Periods
CREATE TABLE fiscal_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,
    closed_by INT NULL,
    closed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    INDEX idx_is_closed (is_closed)
);













-- =====================================================
-- ITRPOS FINANCIAL MODULE - DEFAULT CHART OF ACCOUNTS (FIXED)
-- Use INSERT IGNORE to prevent duplicate entry errors
-- =====================================================

-- Insert default account types (IGNORE duplicates)
INSERT IGNORE INTO account_types (type_code, type_name, default_side, description, sort_order) VALUES
('AST', 'Assets', 'DR', 'All asset accounts', 1),
('LBT', 'Liabilities', 'CR', 'All liability accounts', 2),
('EQT', 'Equity', 'CR', 'All equity accounts', 3),
('REV', 'Revenue', 'CR', 'All revenue accounts', 4),
('EXP', 'Expenses', 'DR', 'All expense accounts', 5);

-- Insert default chart of accounts (IGNORE duplicates)
INSERT IGNORE INTO chart_of_accounts (account_code, account_name, account_type, account_category, account_level, description) VALUES
-- Revenue Accounts
('4000', 'Sales Revenue', 'revenue', 'revenue', 1, 'Primary sales income'),
('4001', 'Service Income', 'revenue', 'revenue', 1, 'Service-related income'),
('4002', 'Sales Discount', 'revenue', 'revenue', 1, 'Sales discounts given'),
('4003', 'Sales Return', 'revenue', 'revenue', 1, 'Sales returns and refunds'),
('4004', 'Other Income', 'revenue', 'revenue', 1, 'Miscellaneous income'),

-- Cost of Sales
('5000', 'Cost of Goods Sold', 'expense', 'cost_of_sales', 1, 'Direct cost of inventory sold'),

-- Operating Expenses
('6000', 'Operating Expenses', 'expense', 'operating_expense', 1, 'All operating expenses'),
('6001', 'Payroll Expenses', 'expense', 'operating_expense', 2, 'Wages and salaries'),
('6002', 'Rent Expense', 'expense', 'operating_expense', 2, 'Premises rent'),
('6003', 'Utilities', 'expense', 'operating_expense', 2, 'Electricity, water, gas'),
('6004', 'Marketing', 'expense', 'operating_expense', 2, 'Advertising and promotion'),
('6005', 'Supplies', 'expense', 'operating_expense', 2, 'Operating supplies'),
('6006', 'Maintenance', 'expense', 'operating_expense', 2, 'Equipment maintenance'),
('6007', 'Insurance', 'expense', 'operating_expense', 2, 'Business insurance'),
('6008', 'Professional Fees', 'expense', 'operating_expense', 2, 'Legal, accounting fees'),
('6009', 'Bank Charges', 'expense', 'operating_expense', 2, 'Bank fees and charges'),
('6010', 'Depreciation', 'expense', 'operating_expense', 2, 'Asset depreciation'),

-- Assets
('1000', 'Current Assets', 'asset', 'current_asset', 1, 'All current assets'),
('1100', 'Cash and Cash Equivalents', 'asset', 'current_asset', 2, 'Cash and bank accounts'),
('1101', 'Cash in Hand', 'asset', 'current_asset', 3, 'Physical cash on hand'),
('1102', 'Bank Accounts', 'asset', 'current_asset', 3, 'All bank accounts'),
('1103', 'Petty Cash', 'asset', 'current_asset', 3, 'Petty cash fund'),
('1200', 'Accounts Receivable', 'asset', 'current_asset', 2, 'Money owed by customers'),
('1300', 'Inventory', 'asset', 'current_asset', 2, 'Stock and supplies'),
('1400', 'Prepaid Expenses', 'asset', 'current_asset', 2, 'Prepaid rent, insurance, etc.'),
('1500', 'Tax Recoverable', 'asset', 'current_asset', 2, 'Input tax recoverable'),

-- Fixed Assets
('2000', 'Fixed Assets', 'asset', 'fixed_asset', 1, 'All fixed assets'),
('2100', 'Equipment', 'asset', 'fixed_asset', 2, 'Kitchen equipment, POS systems'),
('2200', 'Furniture and Fixtures', 'asset', 'fixed_asset', 2, 'Tables, chairs, decor'),
('2300', 'Vehicles', 'asset', 'fixed_asset', 2, 'Delivery vehicles'),
('2400', 'Buildings', 'asset', 'fixed_asset', 2, 'Restaurant premises'),
('2500', 'Accumulated Depreciation', 'asset', 'fixed_asset', 2, 'Accumulated depreciation'),

-- Liabilities
('3000', 'Current Liabilities', 'liability', 'current_liability', 1, 'All current liabilities'),
('3100', 'Accounts Payable', 'liability', 'current_liability', 2, 'Money owed to suppliers'),
('3200', 'Accrued Expenses', 'liability', 'current_liability', 2, 'Accrued wages, utilities'),
('3300', 'Taxes Payable', 'liability', 'current_liability', 2, 'Sales tax, income tax'),
('3400', 'Unearned Revenue', 'liability', 'current_liability', 2, 'Advance payments received'),

-- Equity
('4000', 'Owner\'s Equity', 'equity', 'equity', 1, 'Owner\'s investment and retained earnings'),
('4100', 'Owner\'s Investment', 'equity', 'equity', 2, 'Initial and additional investments'),
('4200', 'Retained Earnings', 'equity', 'equity', 2, 'Accumulated profits/losses'),
('4300', 'Current Year Earnings', 'equity', 'equity', 2, 'Current year profit/loss');












-- =====================================================
-- ITRPOS FINANCIAL MODULE - ACCOUNT MAPPINGS
-- =====================================================

-- Insert default account mappings
INSERT INTO account_mappings (mapping_code, mapping_name, account_id, description) VALUES
-- Revenue Mappings
('rev_Sale', 'Sales Revenue', (SELECT id FROM chart_of_accounts WHERE account_code = '4000'), 'Primary sales income'),
('rev_Service', 'Service Income', (SELECT id FROM chart_of_accounts WHERE account_code = '4001'), 'Service-related income'),
('rev_Discount', 'Sales Discount', (SELECT id FROM chart_of_accounts WHERE account_code = '4002'), 'Sales discounts given'),
('rev_Return', 'Sales Return', (SELECT id FROM chart_of_accounts WHERE account_code = '4003'), 'Sales returns and refunds'),
('rev_Other', 'Other Income', (SELECT id FROM chart_of_accounts WHERE account_code = '4004'), 'Miscellaneous income'),

-- Cost of Sales
('exp_CostOfSales', 'Cost of Goods Sold', (SELECT id FROM chart_of_accounts WHERE account_code = '5000'), 'Direct cost of inventory sold'),

-- Asset Mappings
('ast_Cash', 'Cash in Hand', (SELECT id FROM chart_of_accounts WHERE account_code = '1101'), 'Physical cash on hand'),
('ast_Bank', 'Bank Accounts', (SELECT id FROM chart_of_accounts WHERE account_code = '1102'), 'All bank accounts'),
('ast_PettyCash', 'Petty Cash', (SELECT id FROM chart_of_accounts WHERE account_code = '1103'), 'Petty cash fund'),
('ast_Customer', 'Accounts Receivable', (SELECT id FROM chart_of_accounts WHERE account_code = '1200'), 'Money owed by customers'),
('ast_Stock', 'Inventory', (SELECT id FROM chart_of_accounts WHERE account_code = '1300'), 'Stock and supplies'),
('ast_Prepaid', 'Prepaid Expenses', (SELECT id FROM chart_of_accounts WHERE account_code = '1400'), 'Prepaid rent, insurance, etc.'),
('ast_TaxRecoverable', 'Tax Recoverable', (SELECT id FROM chart_of_accounts WHERE account_code = '1500'), 'Input tax recoverable'),

-- Fixed Asset Mappings
('ast_Equipment', 'Equipment', (SELECT id FROM chart_of_accounts WHERE account_code = '2100'), 'Kitchen equipment, POS systems'),
('ast_Furniture', 'Furniture and Fixtures', (SELECT id FROM chart_of_accounts WHERE account_code = '2200'), 'Tables, chairs, decor'),
('ast_Vehicles', 'Vehicles', (SELECT id FROM chart_of_accounts WHERE account_code = '2300'), 'Delivery vehicles'),
('ast_Buildings', 'Buildings', (SELECT id FROM chart_of_accounts WHERE account_code = '2400'), 'Restaurant premises'),
('ast_AccumulatedDepreciation', 'Accumulated Depreciation', (SELECT id FROM chart_of_accounts WHERE account_code = '2500'), 'Accumulated depreciation'),

-- Liability Mappings
('lib_Supplier', 'Accounts Payable', (SELECT id FROM chart_of_accounts WHERE account_code = '3100'), 'Money owed to suppliers'),
('lib_Accrued', 'Accrued Expenses', (SELECT id FROM chart_of_accounts WHERE account_code = '3200'), 'Accrued wages, utilities'),
('lib_Tax', 'Taxes Payable', (SELECT id FROM chart_of_accounts WHERE account_code = '3300'), 'Sales tax, income tax'),
('lib_Unearned', 'Unearned Revenue', (SELECT id FROM chart_of_accounts WHERE account_code = '3400'), 'Advance payments received'),

-- Equity Mappings
('eqt_Investment', 'Owner\'s Investment', (SELECT id FROM chart_of_accounts WHERE account_code = '4100'), 'Initial and additional investments'),
('eqt_RetainedEarnings', 'Retained Earnings', (SELECT id FROM chart_of_accounts WHERE account_code = '4200'), 'Accumulated profits/losses'),
('eqt_CurrentYear', 'Current Year Earnings', (SELECT id FROM chart_of_accounts WHERE account_code = '4300'), 'Current year profit/loss'),

-- Expense Mappings
('exp_Expenses', 'Operating Expenses', (SELECT id FROM chart_of_accounts WHERE account_code = '6000'), 'All operating expenses'),
('exp_Payroll', 'Payroll Expenses', (SELECT id FROM chart_of_accounts WHERE account_code = '6001'), 'Wages and salaries'),
('exp_Rent', 'Rent Expense', (SELECT id FROM chart_of_accounts WHERE account_code = '6002'), 'Premises rent'),
('exp_Utilities', 'Utilities', (SELECT id FROM chart_of_accounts WHERE account_code = '6003'), 'Electricity, water, gas'),
('exp_Marketing', 'Marketing', (SELECT id FROM chart_of_accounts WHERE account_code = '6004'), 'Advertising and promotion'),
('exp_Supplies', 'Supplies', (SELECT id FROM chart_of_accounts WHERE account_code = '6005'), 'Operating supplies'),
('exp_Maintenance', 'Maintenance', (SELECT id FROM chart_of_accounts WHERE account_code = '6006'), 'Equipment maintenance'),
('exp_Insurance', 'Insurance', (SELECT id FROM chart_of_accounts WHERE account_code = '6007'), 'Business insurance'),
('exp_ProfessionalFees', 'Professional Fees', (SELECT id FROM chart_of_accounts WHERE account_code = '6008'), 'Legal, accounting fees'),
('exp_BankCharges', 'Bank Charges', (SELECT id FROM chart_of_accounts WHERE account_code = '6009'), 'Bank fees and charges'),
('exp_Depreciation', 'Depreciation', (SELECT id FROM chart_of_accounts WHERE account_code = '6010'), 'Asset depreciation');






ALTER TABLE `expense_accounts` 
ADD COLUMN `chart_of_account_id` int NULL AFTER `name`,
ADD COLUMN `mapped_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP AFTER `chart_of_account_id`,
ADD COLUMN `mapped_by` int NULL AFTER `mapped_at`;

-- Add foreign key constraint
ALTER TABLE `expense_accounts` 
ADD CONSTRAINT `fk_expense_accounts_chart_of_accounts` 
FOREIGN KEY (`chart_of_account_id`) REFERENCES `chart_of_accounts`(`id`) 
ON DELETE SET NULL ON UPDATE CASCADE;

-- Add index for better performance
ALTER TABLE `expense_accounts` 
ADD INDEX `idx_chart_of_account_id` (`chart_of_account_id`);

-- Add index for mapping tracking
ALTER TABLE `expense_accounts` 
ADD INDEX `idx_mapped_at` (`mapped_at`);







-- Add journal_entry_id to expenses table for audit trail
ALTER TABLE expenses ADD COLUMN journal_entry_id INT NULL AFTER bank_account_id;
ALTER TABLE expenses ADD FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id);














-- 1. Create table_transfer_logs table for tracking transfers and merges
CREATE TABLE IF NOT EXISTS `table_transfer_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `source_table_id` int DEFAULT NULL,
  `target_table_id` int DEFAULT NULL,
  `hold_order_id` int DEFAULT NULL,
  `transferred_by` int DEFAULT NULL,
  `transfer_type` enum('transfer','merge') NOT NULL DEFAULT 'transfer',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_source_table` (`source_table_id`),
  KEY `idx_target_table` (`target_table_id`),
  KEY `idx_hold_order` (`hold_order_id`),
  KEY `idx_transferred_by` (`transferred_by`),
  KEY `idx_transfer_type` (`transfer_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2. Add updated_at column to hold_orders table if it doesn't exist
ALTER TABLE `hold_orders` ADD COLUMN `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- 3. Add indexes for better performance on table operations
ALTER TABLE `hold_orders` ADD INDEX `idx_table_id_created` (`table_id`, `created_at`),
ADD INDEX `idx_order_mode_table` (`order_mode`, `table_id`);

ALTER TABLE `hold_items` ADD INDEX `idx_hold_id_item` (`hold_id`, `item_id`);












-- Stock Adjustment System Database Schema

-- Stock Adjustment Header Table
CREATE TABLE IF NOT EXISTS `stock_adjustments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `adjustment_number` varchar(50) NOT NULL UNIQUE,
  `store_id` int NOT NULL,
  `adjustment_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `total_items` int DEFAULT 0,
  `total_adjustment_value` decimal(12,2) DEFAULT 0.00,
  `total_current_stock_value` decimal(12,2) DEFAULT 0.00,
  `total_new_stock_value` decimal(12,2) DEFAULT 0.00,
  `remarks` text,
  `created_by` int NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`),
  KEY `adjustment_date` (`adjustment_date`),
  KEY `created_by` (`created_by`)
);

-- Stock Adjustment Items Table
CREATE TABLE IF NOT EXISTS `stock_adjustment_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `adjustment_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `current_quantity` decimal(12,6) NOT NULL,
  `adjustment_quantity` decimal(12,6) NOT NULL,
  `new_quantity` decimal(12,6) NOT NULL,
  `cost_price` decimal(10,2) NOT NULL,
  `current_stock_value` decimal(12,2) NOT NULL,
  `adjustment_value` decimal(12,2) NOT NULL,
  `new_stock_value` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `adjustment_id` (`adjustment_id`),
  KEY `item_id` (`item_id`)
);







ALTER TABLE `stock_adjustments` 
ADD COLUMN `journal_entry_id` int NULL AFTER `created_by`,
ADD KEY `journal_entry_id` (`journal_entry_id`);

-- Add comment for documentation
ALTER TABLE `stock_adjustments` 
MODIFY COLUMN `journal_entry_id` int NULL COMMENT 'Reference to journal_entries table for financial tracking';






INSERT INTO `chart_of_accounts` (
    `account_code`, 
    `account_name`, 
    `account_type`, 
    `account_category`, 
    `parent_account_id`, 
    `account_level`, 
    `is_active`, 
    `description`, 
    `opening_balance`, 
    `current_balance`, 
    `store_id`, 
    `created_at`, 
    `updated_at`
) VALUES (
    '6011', 
    'Stock Loss', 
    'expense', 
    'operating_expense', 
    NULL, 
    2, 
    1, 
    'Losses due to stock adjustments, damage, theft, or obsolescence', 
    0.00, 
    0.00, 
    1, 
    CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
);



INSERT INTO `account_mappings` (
    `mapping_code`, 
    `mapping_name`, 
    `account_id`, 
    `description`, 
    `is_active`, 
    `created_at`, 
    `updated_at`
) VALUES (
    'exp_StockLoss', 
    'Stock Loss', 
    (SELECT id FROM chart_of_accounts WHERE account_code = '6011' LIMIT 1), 
    'Account for tracking inventory losses and stock adjustments', 
    1, 
    CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
);








ALTER TABLE sales ADD COLUMN customer_id INT NULL AFTER customer_name;
ALTER TABLE sales ADD INDEX idx_customer_id (customer_id);



INSERT INTO settings (store_id, category_id, name, code, type, value, default_value, description, is_active) VALUES
(NULL, 1, 'Print Customer Number', 'print_cus_number', 'boolean', '1', '1', 'Print Customer Phone Number on Print', 1),
(NULL, 1, 'Print Customer Address', 'print_cus_address', 'boolean', '1', '1', 'Print Customer Address on Print', 1);







-- Quotations table
CREATE TABLE `quotations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `quotation_number` varchar(50) NOT NULL,
  `customer_id` int DEFAULT NULL,
  `customer_name` varchar(100) DEFAULT NULL,
  `quotation_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_until` date DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(10,2) DEFAULT '0.00',
  `discount_amount` decimal(10,2) DEFAULT '0.00',
  `net_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','sent','accepted','rejected','expired','converted') DEFAULT 'draft',
  `remarks` text,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotation_number` (`quotation_number`),
  KEY `idx_quotations_store_id` (`store_id`),
  KEY `idx_quotations_customer_id` (`customer_id`),
  KEY `idx_quotations_status` (`status`)
);

-- Quotation items table
CREATE TABLE `quotation_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quotation_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `remarks` text,
  PRIMARY KEY (`id`),
  KEY `quotation_id` (`quotation_id`),
  KEY `item_id` (`item_id`)
);





ALTER TABLE quotations DROP COLUMN valid_until;






INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
VALUES (NULL, 1, 'Dealer Branding', 'dealer_branding', 'text', 'IT Resources | 📞 0311-391-7777', 'IT Resources | 📞 0311-391-7777', 'Dealer company name and contact info for receipt footer', 1);
