-- Strict serial-number inventory tracking for MariaDB 10.4+
-- Safe to run more than once. Existing product_serials data is preserved.
-- Select your POS database before running this script:
-- USE `pos`;

START TRANSACTION;

ALTER TABLE `business`
    ADD COLUMN IF NOT EXISTS `enable_serial_tracking` TINYINT(1) NOT NULL DEFAULT 0
    AFTER `enable_lot_number`;

CREATE TABLE IF NOT EXISTS `product_serials` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `business_id` INT(10) UNSIGNED NOT NULL,
    `product_id` INT(10) UNSIGNED NOT NULL,
    `variation_id` INT(10) UNSIGNED NOT NULL,
    `purchase_id` INT(10) UNSIGNED DEFAULT NULL,
    `purchase_line_id` INT(10) UNSIGNED DEFAULT NULL,
    `location_id` INT(10) UNSIGNED NOT NULL,
    `serial_no` VARCHAR(191) NOT NULL,
    `received_at` DATETIME DEFAULT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'available',
    `sell_id` INT(10) UNSIGNED DEFAULT NULL,
    `sell_line_id` INT(10) UNSIGNED DEFAULT NULL,
    `sold_at` DATETIME DEFAULT NULL,
    `return_id` INT(10) UNSIGNED DEFAULT NULL,
    `rma_id` INT(10) UNSIGNED DEFAULT NULL,
    `stock_transfer_id` INT(10) UNSIGNED DEFAULT NULL,
    `stock_adjustment_id` INT(10) UNSIGNED DEFAULT NULL,
    `last_movement` VARCHAR(191) DEFAULT NULL,
    `created_by` INT(10) UNSIGNED DEFAULT NULL,
    `updated_by` INT(10) UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_serials_business_serial_unique` (`business_id`, `serial_no`),
    KEY `product_serials_lookup_index` (`business_id`, `location_id`, `status`),
    KEY `product_serials_product_id_index` (`product_id`),
    KEY `product_serials_variation_id_index` (`variation_id`),
    KEY `product_serials_received_at_index` (`received_at`),
    KEY `product_serials_purchase_line_id_index` (`purchase_line_id`),
    KEY `product_serials_sell_line_id_index` (`sell_line_id`),
    KEY `product_serials_return_id_index` (`return_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Upgrade older/partial product_serials tables without deleting data.
ALTER TABLE `product_serials`
    ADD COLUMN IF NOT EXISTS `purchase_line_id` INT(10) UNSIGNED DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `sell_line_id` INT(10) UNSIGNED DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `return_id` INT(10) UNSIGNED DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `received_at` DATETIME DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `status` VARCHAR(191) NOT NULL DEFAULT 'available',
    ADD COLUMN IF NOT EXISTS `last_movement` VARCHAR(191) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `created_at` TIMESTAMP NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `updated_at` TIMESTAMP NULL DEFAULT NULL;

UPDATE `product_serials`
SET `received_at` = `created_at`
WHERE `received_at` IS NULL;

CREATE TABLE IF NOT EXISTS `product_serial_movements` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_serial_id` INT(10) UNSIGNED NOT NULL,
    `business_id` INT(10) UNSIGNED NOT NULL,
    `transaction_id` INT(10) UNSIGNED DEFAULT NULL,
    `transaction_line_id` INT(10) UNSIGNED DEFAULT NULL,
    `from_location_id` INT(10) UNSIGNED DEFAULT NULL,
    `to_location_id` INT(10) UNSIGNED DEFAULT NULL,
    `movement_type` VARCHAR(50) NOT NULL,
    `status_before` VARCHAR(30) DEFAULT NULL,
    `status_after` VARCHAR(30) NOT NULL,
    `created_by` INT(10) UNSIGNED DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `product_serial_movements_product_serial_id_index` (`product_serial_id`),
    KEY `product_serial_movements_business_id_index` (`business_id`),
    KEY `product_serial_movements_transaction_id_index` (`transaction_id`),
    KEY `product_serial_movements_transaction_line_id_index` (`transaction_line_id`),
    KEY `product_serial_movements_from_location_id_index` (`from_location_id`),
    KEY `product_serial_movements_to_location_id_index` (`to_location_id`),
    KEY `product_serial_movements_movement_type_index` (`movement_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prevent Laravel from attempting to recreate these tables later.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT
    '2026_06_15_000001_create_product_serial_tracking_tables',
    COALESCE((SELECT MAX(`batch`) + 1 FROM `migrations` AS `m2`), 1)
WHERE NOT EXISTS (
    SELECT 1
    FROM `migrations`
    WHERE `migration` = '2026_06_15_000001_create_product_serial_tracking_tables'
);

INSERT INTO `migrations` (`migration`, `batch`)
SELECT
    '2026_06_20_000002_add_received_at_to_product_serials',
    COALESCE((SELECT MAX(`batch`) + 1 FROM `migrations` AS `m2`), 1)
WHERE NOT EXISTS (
    SELECT 1
    FROM `migrations`
    WHERE `migration` = '2026_06_20_000002_add_received_at_to_product_serials'
);

COMMIT;

-- Activate serial tracking for every business:
-- UPDATE `business` SET `enable_serial_tracking` = 1;

-- Or activate it for one business only:
-- UPDATE `business` SET `enable_serial_tracking` = 1 WHERE `id` = 1;