-- =============================================
-- Ghalla Mandi ERP: Sale Module (Pakki Parchi)
-- Database: naveed_trader_db
-- =============================================

CREATE TABLE IF NOT EXISTS `sale_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,
    `due_date` DATE DEFAULT NULL COMMENT 'When the payment is due',
    `khareedar_id` INT NOT NULL COMMENT 'FK to accounts (party/khareedar)',
    `havala_id` INT DEFAULT NULL COMMENT 'FK to accounts (reference/guarantor)',
    `lot_id` INT NOT NULL COMMENT 'FK to purchase_lots',
    `item_id` INT NOT NULL,
    `sale_weight` DECIMAL(12,2) NOT NULL,
    `rate_basis` INT DEFAULT 40,
    `rate` DECIMAL(12,2) NOT NULL,
    `gross_amount` DECIMAL(15,2) NOT NULL,
    `total_additions` DECIMAL(15,2) DEFAULT 0.00,
    `net_receivable` DECIMAL(15,2) NOT NULL,
    `payment_method_id` INT DEFAULT NULL COMMENT 'FK to accounts (bank_cash). NULL = khata/udhar',
    `amount_received` DECIMAL(15,2) DEFAULT 0.00,
    `remaining_balance` DECIMAL(15,2) DEFAULT 0.00,
    `journal_id` INT DEFAULT NULL,
    `settlement_journal_id` INT DEFAULT NULL,
    `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 (`khareedar_id`) REFERENCES `accounts`(`id`),
    FOREIGN KEY (`havala_id`) REFERENCES `accounts`(`id`),
    FOREIGN KEY (`lot_id`) REFERENCES `purchase_lots`(`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_khareedar` (`khareedar_id`),
    INDEX `idx_lot` (`lot_id`),
    INDEX `idx_reversed` (`is_reversed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sale_additions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `sale_id` INT NOT NULL,
    `account_id` INT NOT NULL COMMENT 'FK to accounts (system_income/system_expense)',
    `description` VARCHAR(255) NOT NULL,
    `percentage` DECIMAL(5,2) DEFAULT NULL COMMENT 'For commission percentage',
    `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`sale_id`) REFERENCES `sale_master`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`),
    INDEX `idx_sale` (`sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
