-- =============================================
-- Ghalla Mandi ERP: Chart of Accounts & Items
-- Database: naveed_trader_db
-- =============================================

CREATE TABLE IF NOT EXISTS `accounts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `type` ENUM('party', 'bank_cash', 'system_income', 'system_expense', 'capital') NOT NULL,
    `sub_type` VARCHAR(50) DEFAULT NULL COMMENT 'party: zamidar/khareedar/driver/broker | bank_cash: cash/bank/wallet | system: commission/mazdoori/tora/kiraya/market_fee/purchase/sale/expense',
    `phone` VARCHAR(20) DEFAULT NULL,
    `city` VARCHAR(100) DEFAULT NULL,
    `opening_balance` DECIMAL(15,2) DEFAULT 0.00,
    `balance_type` ENUM('Dr', 'Cr') DEFAULT 'Dr',
    `is_system` TINYINT(1) DEFAULT 0 COMMENT '1 = system account, cannot be deleted',
    `is_active` TINYINT(1) DEFAULT 1,
    `cycle_start_date` DATE DEFAULT NULL COMMENT 'When the payment cycle begins',
    `alert_date` DATE DEFAULT NULL COMMENT 'Calculated next deadline/alert date',
    `alert_interval_days` INT DEFAULT 0 COMMENT 'Days to add for recurring alerts',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_type` (`type`),
    INDEX `idx_sub_type` (`sub_type`),
    INDEX `idx_type_subtype` (`type`, `sub_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `unit` VARCHAR(50) DEFAULT 'KG',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
