-- Inventory module Phase 2
-- Stock Adjustments, Serial Numbers, Batch Tracking, Stock Counts, Asset Inventory
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Stock adjustments (audit log of quantity changes)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_adjustments` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `adjustment_date`       DATE                NOT NULL,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `warehouse_id`          BIGINT UNSIGNED     NOT NULL,
    `quantity_before`       DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `quantity_after`        DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `delta`                 DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `mode`                  ENUM('set','add')   NOT NULL DEFAULT 'set',
    `reason`                VARCHAR(150)        NULL,
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `product_id` (`product_id`),
    KEY `warehouse_id` (`warehouse_id`),
    KEY `adjustment_date` (`adjustment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Serial numbers
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_serials` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `warehouse_id`          BIGINT UNSIGNED     NULL,
    `serial_number`         VARCHAR(100)        NOT NULL,
    `status`                ENUM('in_stock','allocated','sold','scrapped') NOT NULL DEFAULT 'in_stock',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `serial_number` (`serial_number`),
    KEY `product_id` (`product_id`),
    KEY `warehouse_id` (`warehouse_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Batch / lot tracking
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_batches` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `warehouse_id`          BIGINT UNSIGNED     NULL,
    `batch_number`          VARCHAR(80)         NOT NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `manufactured_date`     DATE                NULL,
    `expiry_date`           DATE                NULL,
    `status`                ENUM('active','expired','depleted') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_batch` (`product_id`, `batch_number`),
    KEY `warehouse_id` (`warehouse_id`),
    KEY `status` (`status`),
    KEY `expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Stock counts (physical inventory)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_counts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `count_date`            DATE                NOT NULL,
    `warehouse_id`          BIGINT UNSIGNED     NOT NULL,
    `status`                ENUM('draft','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT                NULL,
    `completed_at`          DATETIME            NULL,
    `completed_by`          BIGINT UNSIGNED     NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `warehouse_id` (`warehouse_id`),
    KEY `status` (`status`),
    KEY `count_date` (`count_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_count_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `stock_count_id`        BIGINT UNSIGNED     NOT NULL,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `system_qty`            DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `counted_qty`           DECIMAL(12,3)       NULL,
    `variance`              DECIMAL(12,3)       NULL,
    `notes`                 TEXT                NULL,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `stock_count_id` (`stock_count_id`),
    KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Asset inventory (fixed / tagged assets in inventory)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_assets` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `asset_tag`             VARCHAR(50)         NOT NULL,
    `name`                  VARCHAR(200)        NOT NULL,
    `product_id`            BIGINT UNSIGNED     NULL,
    `category`              VARCHAR(100)        NULL,
    `location`              VARCHAR(150)        NULL,
    `serial_number`         VARCHAR(100)        NULL,
    `purchase_date`         DATE                NULL,
    `purchase_cost`         DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                ENUM('active','in_repair','disposed','lost') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `asset_tag` (`asset_tag`),
    KEY `product_id` (`product_id`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Permissions
-- ---------------------------------------------------------------------------
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_adjustments', 'stock_adjustments.view', 'View Stock Adjustments', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_adjustments.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_adjustments', 'stock_adjustments.create', 'Create Stock Adjustments', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_adjustments.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'serial_numbers', 'serial_numbers.view', 'View Serial Numbers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'serial_numbers.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'serial_numbers', 'serial_numbers.create', 'Create Serial Numbers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'serial_numbers.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'serial_numbers', 'serial_numbers.edit', 'Edit Serial Numbers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'serial_numbers.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'serial_numbers', 'serial_numbers.delete', 'Delete Serial Numbers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'serial_numbers.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'batches', 'batches.view', 'View Batch Tracking', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'batches.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'batches', 'batches.create', 'Create Batches', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'batches.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'batches', 'batches.edit', 'Edit Batches', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'batches.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'batches', 'batches.delete', 'Delete Batches', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'batches.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'barcodes', 'barcodes.view', 'View Barcode & Labels', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'barcodes.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_counts', 'stock_counts.view', 'View Stock Counts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_counts.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_counts', 'stock_counts.create', 'Create Stock Counts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_counts.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_counts', 'stock_counts.edit', 'Edit Stock Counts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_counts.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_counts', 'stock_counts.complete', 'Complete Stock Counts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_counts.complete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock_counts', 'stock_counts.delete', 'Delete Stock Counts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock_counts.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'reorder_levels', 'reorder_levels.view', 'View Reorder Levels', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'reorder_levels.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'reorder_levels', 'reorder_levels.edit', 'Edit Reorder Levels', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'reorder_levels.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_inventory', 'asset_inventory.view', 'View Asset Inventory', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_inventory.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_inventory', 'asset_inventory.create', 'Create Asset Inventory', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_inventory.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_inventory', 'asset_inventory.edit', 'Edit Asset Inventory', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_inventory.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_inventory', 'asset_inventory.delete', 'Delete Asset Inventory', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_inventory.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'inventory', 'inventory.reports', 'View Inventory Reports', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'inventory.reports');

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND (
      p.slug LIKE 'stock_adjustments.%'
      OR p.slug LIKE 'serial_numbers.%'
      OR p.slug LIKE 'batches.%'
      OR p.slug LIKE 'barcodes.%'
      OR p.slug LIKE 'stock_counts.%'
      OR p.slug LIKE 'reorder_levels.%'
      OR p.slug LIKE 'asset_inventory.%'
      OR p.slug = 'inventory.reports'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
