-- Inventory module
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Catalog master data
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(30)         NOT NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_brands` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(30)         NOT NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `units_of_measure` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(20)         NOT NULL,
    `symbol`                VARCHAR(20)         NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `warehouses` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NOT NULL,
    `address`               VARCHAR(255)        NULL,
    `city`                  VARCHAR(100)        NULL,
    `country`               VARCHAR(100)        NULL,
    `is_default`            TINYINT(1)          NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') 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 `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Products
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(200)        NOT NULL,
    `sku`                   VARCHAR(50)         NOT NULL,
    `barcode`               VARCHAR(50)         NULL,
    `category_id`           BIGINT UNSIGNED     NULL,
    `brand_id`              BIGINT UNSIGNED     NULL,
    `unit_id`               BIGINT UNSIGNED     NULL,
    `description`           TEXT                NULL,
    `cost_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sale_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `reorder_level`         DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `status`                ENUM('active','inactive') 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 `sku` (`sku`),
    KEY `barcode` (`barcode`),
    KEY `category_id` (`category_id`),
    KEY `brand_id` (`brand_id`),
    KEY `unit_id` (`unit_id`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Stock balances & transfers
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_balances` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `warehouse_id`          BIGINT UNSIGNED     NOT NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `reserved_qty`          DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_warehouse` (`product_id`, `warehouse_id`),
    KEY `warehouse_id` (`warehouse_id`),
    KEY `quantity` (`quantity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_transfers` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `transfer_date`         DATE                NOT NULL,
    `from_warehouse_id`     BIGINT UNSIGNED     NOT NULL,
    `to_warehouse_id`       BIGINT UNSIGNED     NOT NULL,
    `status`                ENUM('draft','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 `from_warehouse_id` (`from_warehouse_id`),
    KEY `to_warehouse_id` (`to_warehouse_id`),
    KEY `status` (`status`),
    KEY `transfer_date` (`transfer_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_transfer_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `stock_transfer_id`     BIGINT UNSIGNED     NOT NULL,
    `product_id`            BIGINT UNSIGNED     NOT NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `notes`                 TEXT                NULL,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `stock_transfer_id` (`stock_transfer_id`),
    KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Seed defaults
-- ---------------------------------------------------------------------------
INSERT INTO `units_of_measure` (`name`, `code`, `symbol`, `description`, `status`, `created_at`)
SELECT 'Pieces', 'PCS', 'pcs', 'Individual units', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `units_of_measure` WHERE `code` = 'PCS');

INSERT INTO `units_of_measure` (`name`, `code`, `symbol`, `description`, `status`, `created_at`)
SELECT 'Kilogram', 'KG', 'kg', 'Weight in kilograms', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `units_of_measure` WHERE `code` = 'KG');

INSERT INTO `units_of_measure` (`name`, `code`, `symbol`, `description`, `status`, `created_at`)
SELECT 'Litre', 'LTR', 'L', 'Volume in litres', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `units_of_measure` WHERE `code` = 'LTR');

INSERT INTO `warehouses` (`name`, `code`, `is_default`, `status`, `created_at`)
SELECT 'Main Warehouse', 'MAIN', 1, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `warehouses` WHERE `code` = 'MAIN');

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

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

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock', 'stock.view', 'View Stock', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'stock', 'stock.adjust', 'Adjust Stock', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'stock.adjust');

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

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 = 'inventory.view'
      OR p.slug LIKE 'products.%'
      OR p.slug LIKE 'categories.%'
      OR p.slug LIKE 'brands.%'
      OR p.slug LIKE 'units_of_measure.%'
      OR p.slug LIKE 'warehouses.%'
      OR p.slug LIKE 'stock.%'
      OR p.slug LIKE 'stock_transfers.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
