-- CreateTable
CREATE TABLE `retail_stocks` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_id` BIGINT UNSIGNED NOT NULL,
    `lot_no` VARCHAR(100) NOT NULL,
    `allocated_unit` ENUM('KG', 'BAG', 'BOX') NOT NULL DEFAULT 'KG',
    `allocated_qty` DECIMAL(12, 3) NOT NULL,
    `quantity` DECIMAL(12, 3) NOT NULL,
    `remaining_quantity` DECIMAL(12, 3) NOT NULL DEFAULT 0.000,
    `cost_per_unit` DECIMAL(10, 2) NOT NULL,
    `allocation_date` DATE NOT NULL,
    `remarks` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `deleted_at` DATETIME(3) NULL,
    `created_by` BIGINT UNSIGNED NULL,

    UNIQUE INDEX `retail_stocks_lot_no_key`(`lot_no`),
    INDEX `retail_stocks_purchase_id_idx`(`purchase_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `retail_sales` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_no` VARCHAR(50) NOT NULL,
    `customer_id` BIGINT UNSIGNED NULL,
    `walk_in_name` VARCHAR(255) NULL,
    `walk_in_mobile` VARCHAR(20) NULL,
    `invoice_date` DATE NOT NULL,
    `sub_total` DECIMAL(12, 2) NOT NULL,
    `discount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `grand_total` DECIMAL(12, 2) NOT NULL,
    `received_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `balance_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `payment_status` ENUM('UNPAID', 'PARTIALLY_PAID', 'PAID') NOT NULL DEFAULT 'UNPAID',
    `remarks` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `deleted_at` DATETIME(3) NULL,
    `created_by` BIGINT UNSIGNED NULL,

    UNIQUE INDEX `retail_sales_invoice_no_key`(`invoice_no`),
    INDEX `retail_sales_customer_id_idx`(`customer_id`),
    INDEX `retail_sales_invoice_date_idx`(`invoice_date`),
    INDEX `retail_sales_payment_status_idx`(`payment_status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `retail_sale_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `retail_sale_id` BIGINT UNSIGNED NOT NULL,
    `retail_stock_id` BIGINT UNSIGNED NOT NULL,
    `quantity` DECIMAL(12, 3) NOT NULL,
    `cost_per_unit` DECIMAL(10, 2) NOT NULL,
    `selling_rate` DECIMAL(10, 2) NOT NULL,
    `total` DECIMAL(12, 2) NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT UNSIGNED NULL,

    INDEX `retail_sale_items_retail_sale_id_idx`(`retail_sale_id`),
    INDEX `retail_sale_items_retail_stock_id_idx`(`retail_stock_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `retail_stock_spoilages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `retail_stock_id` BIGINT UNSIGNED NOT NULL,
    `spoilage_date` DATE NOT NULL,
    `quantity` DECIMAL(12, 3) NOT NULL,
    `reason` VARCHAR(255) 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,

    INDEX `retail_stock_spoilages_retail_stock_id_idx`(`retail_stock_id`),
    INDEX `retail_stock_spoilages_spoilage_date_idx`(`spoilage_date`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

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

-- AddForeignKey
ALTER TABLE `retail_sales` ADD CONSTRAINT `retail_sales_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `retail_sale_items` ADD CONSTRAINT `retail_sale_items_retail_sale_id_fkey` FOREIGN KEY (`retail_sale_id`) REFERENCES `retail_sales`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `retail_sale_items` ADD CONSTRAINT `retail_sale_items_retail_stock_id_fkey` FOREIGN KEY (`retail_stock_id`) REFERENCES `retail_stocks`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `retail_stock_spoilages` ADD CONSTRAINT `retail_stock_spoilages_retail_stock_id_fkey` FOREIGN KEY (`retail_stock_id`) REFERENCES `retail_stocks`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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