-- CreateTable
CREATE TABLE `arrival_custom_pattis` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_id` BIGINT UNSIGNED NOT NULL,
    `patti_no` VARCHAR(50) NULL,
    `settlement_date` DATE NULL,
    `items` JSON NOT NULL,
    `total_weight_sold` DECIMAL(12, 3) NOT NULL DEFAULT 0.000,
    `gross_sales_total` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `commission_percent` DECIMAL(5, 2) NOT NULL DEFAULT 4.00,
    `commission_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `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 DEFAULT 0.00,
    `advance_adjusted` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `net_payable` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `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_custom_pattis_purchase_id_key`(`purchase_id`),
    INDEX `arrival_custom_pattis_settlement_date_idx`(`settlement_date`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

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