-- =============================================
-- Ghalla Mandi ERP: Purchase Module (Katchi Parchi)
-- Database: naveed_trader_db
-- =============================================

CREATE TABLE IF NOT EXISTS `purchase_master` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(36) NOT NULL UNIQUE COMMENT 'Idempotency key',
    `voucher_no` VARCHAR(20) NOT NULL UNIQUE,
    `voucher_date` DATE NOT NULL,
    `zamidar_id` INT NOT NULL COMMENT 'FK to accounts (party/zamidar)',
    `item_id` INT NOT NULL,
    `vehicle_no` VARCHAR(100) DEFAULT NULL,
    `packing` VARCHAR(20) DEFAULT 'بوری',
    `bori_qty` INT DEFAULT 0,
    `tara` DECIMAL(10,2) DEFAULT 0.00,
    `gross_weight` DECIMAL(12,2) NOT NULL,
    `kat` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Deduction for waste/kachra',
    `net_weight` DECIMAL(12,2) NOT NULL,
    `rate_basis` INT DEFAULT 40 COMMENT '1, 40, or 100 KG',
    `rate` DECIMAL(12,2) NOT NULL,
    `gross_amount` DECIMAL(15,2) NOT NULL,
    `total_deductions` DECIMAL(15,2) DEFAULT 0.00,
    `net_payable` DECIMAL(15,2) NOT NULL,
    `payment_method_id` INT DEFAULT NULL COMMENT 'FK to accounts (bank_cash). NULL = khata/udhar',
    `amount_paid` DECIMAL(15,2) DEFAULT 0.00,
    `remaining_balance` DECIMAL(15,2) DEFAULT 0.00,
    `journal_id` INT DEFAULT NULL COMMENT 'Primary journal for this purchase',
    `settlement_journal_id` INT DEFAULT NULL COMMENT 'Payment settlement journal',
    `is_reversed` TINYINT(1) DEFAULT 0,
    `reversed_by_journal_id` INT DEFAULT NULL,
    `created_by` INT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`zamidar_id`) REFERENCES `accounts`(`id`),
    FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    FOREIGN KEY (`payment_method_id`) REFERENCES `accounts`(`id`),
    FOREIGN KEY (`journal_id`) REFERENCES `journals`(`id`),
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_voucher_date` (`voucher_date`),
    INDEX `idx_zamidar` (`zamidar_id`),
    INDEX `idx_reversed` (`is_reversed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_deductions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `purchase_id` INT NOT NULL,
    `account_id` INT NOT NULL COMMENT 'FK to accounts (system_expense: mazdoori, commission, kiraya)',
    `description` VARCHAR(255) NOT NULL,
    `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`purchase_id`) REFERENCES `purchase_master`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`),
    INDEX `idx_purchase` (`purchase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_lots` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `purchase_id` INT NOT NULL,
    `lot_number` VARCHAR(20) NOT NULL UNIQUE,
    `item_id` INT NOT NULL,
    `item_name` VARCHAR(255) DEFAULT NULL COMMENT 'Denormalized for quick display',
    `zamidar_name` VARCHAR(255) DEFAULT NULL COMMENT 'Denormalized for quick display',
    `zamidar_id` INT NOT NULL,
    `packing` VARCHAR(20) DEFAULT 'بوری',
    `net_weight` DECIMAL(12,2) NOT NULL COMMENT 'Original weight at purchase',
    `remaining_qty` DECIMAL(12,2) NOT NULL COMMENT 'Current available weight',
    `status` ENUM('available', 'partial', 'sold') DEFAULT 'available',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`purchase_id`) REFERENCES `purchase_master`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    FOREIGN KEY (`zamidar_id`) REFERENCES `accounts`(`id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_lot_number` (`lot_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
