-- Employee Assets tables for HRM module
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS

CREATE TABLE IF NOT EXISTS `asset_types` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(10)         NOT NULL,
    `category`              VARCHAR(50)         NULL,
    `requires_identifier`   TINYINT(1)          NOT NULL DEFAULT 0,
    `is_consumable`         TINYINT(1)          NOT NULL DEFAULT 0,
    `requires_return`       TINYINT(1)          NOT NULL DEFAULT 1,
    `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 `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_asset_assignments` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `asset_type_id`         BIGINT UNSIGNED     NOT NULL,
    `asset_name`            VARCHAR(200)        NOT NULL,
    `identifier`            VARCHAR(100)        NULL,
    `quantity`              DECIMAL(8,2)        NOT NULL DEFAULT 1,
    `assigned_date`         DATE                NOT NULL,
    `returned_date`         DATE                NULL,
    `status`                ENUM('assigned','returned','lost','damaged') NOT NULL DEFAULT 'assigned',
    `condition_on_assign`   VARCHAR(100)        NULL,
    `condition_on_return`   VARCHAR(100)        NULL,
    `notes`                 TEXT                NULL,
    `assigned_by`           BIGINT UNSIGNED     NULL,
    `returned_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`),
    KEY `employee_id` (`employee_id`),
    KEY `asset_type_id` (`asset_type_id`),
    KEY `status` (`status`),
    KEY `assigned_date` (`assigned_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default asset types
INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Mobile Phone', 'MOB', 'electronics', 1, 0, 1, 'Company mobile phones and smartphones', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'MOB');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'SIM Card', 'SIM', 'telecom', 1, 0, 1, 'Mobile SIM cards and data lines', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'SIM');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Laptop', 'LAP', 'electronics', 1, 0, 1, 'Laptops and notebooks', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'LAP');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Cleaning Supplies', 'CLN', 'supplies', 0, 1, 0, 'Cleaning materials and consumables', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'CLN');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Uniform', 'UNI', 'apparel', 0, 0, 0, 'Work uniforms and PPE', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'UNI');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Vehicle', 'VEH', 'transport', 1, 0, 1, 'Company vehicles and keys', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'VEH');

INSERT INTO `asset_types` (`name`, `code`, `category`, `requires_identifier`, `is_consumable`, `requires_return`, `description`, `status`, `created_at`)
SELECT 'Other', 'OTH', 'general', 0, 0, 1, 'Miscellaneous company assets', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `asset_types` WHERE `code` = 'OTH');

-- Asset type permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_types', 'asset_types.view', 'View Asset Types', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_types.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_types', 'asset_types.create', 'Create Asset Types', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_types.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_types', 'asset_types.edit', 'Edit Asset Types', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_types.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'asset_types', 'asset_types.delete', 'Delete Asset Types', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'asset_types.delete');

-- Employee asset permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_assets', 'employee_assets.view', 'View Employee Assets', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_assets.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_assets', 'employee_assets.create', 'Assign Employee Assets', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_assets.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_assets', 'employee_assets.edit', 'Edit Employee Assets', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_assets.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_assets', 'employee_assets.delete', 'Delete Employee Assets', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_assets.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_assets', 'employee_assets.return', 'Return Employee Assets', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_assets.return');

-- Grant all new permissions to Administrator role
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 'asset_types.%' OR p.slug LIKE 'employee_assets.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
