-- CreateTable
CREATE TABLE `purchases` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_date` DATE NOT NULL,
    `bill_no` VARCHAR(50) NULL,
    `supplier_id` BIGINT UNSIGNED NOT NULL,
    `vegetable_id` BIGINT UNSIGNED NOT NULL,
    `lot_no` VARCHAR(100) NOT NULL,
    `unit` ENUM('KG', 'BAG', 'BOX') NOT NULL DEFAULT 'KG',
    `quantity` DECIMAL(12, 3) NOT NULL,
    `rate_per_unit` DECIMAL(10, 2) NOT NULL,
    `transport_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `loading_unloading_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `other_charges` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `thaiyal_charges` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `extra_charges` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `goods_total` DECIMAL(12, 2) NOT NULL,
    `surcharge_total` DECIMAL(12, 2) NOT NULL,
    `cost_per_unit` DECIMAL(10, 2) NOT NULL,
    `grand_total` DECIMAL(12, 2) NOT NULL,
    `arrival_date` DATE NULL,
    `payment_status` ENUM('UNPAID', 'PARTIALLY_PAID', 'PAID') NOT NULL DEFAULT 'UNPAID',
    `advance_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `balance_amount` 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,
    `deleted_at` DATETIME(3) NULL,

    UNIQUE INDEX `purchases_lot_no_key`(`lot_no`),
    INDEX `purchases_supplier_id_idx`(`supplier_id`),
    INDEX `purchases_vegetable_id_idx`(`vegetable_id`),
    INDEX `purchases_purchase_date_idx`(`purchase_date`),
    INDEX `purchases_payment_status_idx`(`payment_status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `purchases` ADD CONSTRAINT `purchases_supplier_id_fkey` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `purchases` ADD CONSTRAINT `purchases_vegetable_id_fkey` FOREIGN KEY (`vegetable_id`) REFERENCES `vegetables`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;