-- AlterTable
ALTER TABLE `purchases` ADD COLUMN `agent_name` VARCHAR(255) NULL,
    ADD COLUMN `association_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    ADD COLUMN `date_of_despatch` DATE NULL,
    ADD COLUMN `extra_freight` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    ADD COLUMN `freight` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    ADD COLUMN `miscellaneous_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    ADD COLUMN `place_of_arrival` VARCHAR(255) NULL,
    ADD COLUMN `place_of_despatch` VARCHAR(255) NULL,
    ADD COLUMN `purchase_type` ENUM('DIRECT', 'COMMISSION_ARRIVAL') NOT NULL DEFAULT 'DIRECT',
    ADD COLUMN `total_freight` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    ADD COLUMN `truck_no` VARCHAR(50) NULL,
    ADD COLUMN `unloading_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    MODIFY `rate_per_unit` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    MODIFY `goods_total` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    MODIFY `surcharge_total` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    MODIFY `cost_per_unit` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    MODIFY `grand_total` DECIMAL(12, 2) NOT NULL DEFAULT 0.00;

-- CreateTable
CREATE TABLE `arrival_settlements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_id` BIGINT UNSIGNED NOT NULL,
    `settlement_no` VARCHAR(50) NOT NULL,
    `settlement_date` DATE NOT NULL,
    `total_weight_sold` DECIMAL(12, 3) NOT NULL,
    `gross_sales_total` DECIMAL(12, 2) NOT NULL,
    `commission_percent` DECIMAL(5, 2) NOT NULL DEFAULT 4.00,
    `commission_amount` DECIMAL(12, 2) NOT NULL,
    `freight_deducted` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `extra_freight_deducted` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `association_deducted` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `unloading_deducted` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `misc_deducted` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `total_deductions` DECIMAL(12, 2) NOT NULL,
    `advance_adjusted` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `net_payable` DECIMAL(12, 2) NOT NULL,
    `paid_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `balance_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `status` ENUM('UNSETTLED', 'PARTIALLY_SETTLED', 'SETTLED') NOT NULL DEFAULT 'UNSETTLED',
    `payment_mode` ENUM('CASH', 'BANK', 'UPI') NULL,
    `remarks` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT UNSIGNED NULL,

    UNIQUE INDEX `arrival_settlements_purchase_id_key`(`purchase_id`),
    UNIQUE INDEX `arrival_settlements_settlement_no_key`(`settlement_no`),
    INDEX `arrival_settlements_settlement_date_idx`(`settlement_date`),
    INDEX `arrival_settlements_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `arrival_settlements` ADD CONSTRAINT `arrival_settlements_purchase_id_fkey` FOREIGN KEY (`purchase_id`) REFERENCES `purchases`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `arrival_settlements` ADD CONSTRAINT `arrival_settlements_created_by_fkey` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;