-- =============================================
-- Ghalla Mandi ERP: Core Accounting (Journals)
-- Database: naveed_trader_db
-- =============================================

CREATE TABLE IF NOT EXISTS `journals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(36) NOT NULL UNIQUE COMMENT 'Idempotency key',
    `journal_date` DATE NOT NULL,
    `source_type` ENUM('purchase', 'sale', 'cash_bank', 'expense', 'opening', 'settlement') NOT NULL,
    `source_id` INT DEFAULT NULL COMMENT 'FK to source table (purchase_master.id, sale_master.id, etc.)',
    `narration` TEXT DEFAULT NULL,
    `total_amount` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Total Dr or Cr amount for quick reference',
    `is_reversed` TINYINT(1) DEFAULT 0,
    `reversed_by_id` INT DEFAULT NULL COMMENT 'ID of the reversing journal',
    `created_by` INT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_source` (`source_type`, `source_id`),
    INDEX `idx_date` (`journal_date`),
    INDEX `idx_reversed` (`is_reversed`),
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_entries` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `journal_id` INT NOT NULL,
    `account_id` INT NOT NULL,
    `debit` DECIMAL(15,2) DEFAULT 0.00,
    `credit` DECIMAL(15,2) DEFAULT 0.00,
    `narration` VARCHAR(500) DEFAULT NULL,
    FOREIGN KEY (`journal_id`) REFERENCES `journals`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`),
    INDEX `idx_journal` (`journal_id`),
    INDEX `idx_account` (`account_id`),
    INDEX `idx_account_journal` (`account_id`, `journal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
