-- Employees table 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 `employees` (
    `id`                        BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_number`           VARCHAR(30)         NOT NULL,
    `full_name`                 VARCHAR(200)        NOT NULL,
    `department_id`             BIGINT UNSIGNED     NULL,
    `branch_id`                 BIGINT UNSIGNED     NULL,
    `job_title`                 VARCHAR(150)        NULL,
    `email`                     VARCHAR(191)        NULL,
    `phone`                     VARCHAR(30)         NULL,
    `mobile`                    VARCHAR(30)         NULL,
    `cpr_number`                VARCHAR(20)         NULL,
    `nationality`               VARCHAR(100)        NULL,
    `date_of_birth`             DATE                NULL,
    `gender`                    ENUM('male','female','other') NULL,
    `marital_status`            ENUM('single','married','divorced','widowed') NULL,
    `address`                   TEXT                NULL,
    `city`                      VARCHAR(100)        NULL,
    `country`                   VARCHAR(100)        NULL,
    `emergency_contact_name`    VARCHAR(150)        NULL,
    `emergency_contact_phone`   VARCHAR(30)         NULL,
    `hire_date`                 DATE                NULL,
    `employment_type`           ENUM('full_time','part_time','contract','intern') NOT NULL DEFAULT 'full_time',
    `photo_path`                VARCHAR(255)        NULL,
    `cpr_document_path`         VARCHAR(255)        NULL,
    `bank_name`                 VARCHAR(100)        NULL,
    `bank_account`              VARCHAR(50)         NULL,
    `notes`                     TEXT                NULL,
    `user_id`                   BIGINT UNSIGNED     NULL,
    `status`                    ENUM('active','inactive','terminated','on_leave') NOT NULL DEFAULT 'active',
    `created_by`                BIGINT UNSIGNED     NULL,
    `updated_by`                BIGINT UNSIGNED     NULL,
    `created_at`                DATETIME            NULL,
    `updated_at`                DATETIME            NULL,
    `deleted_at`                DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employee_number` (`employee_number`),
    KEY `department_id` (`department_id`),
    KEY `branch_id` (`branch_id`),
    KEY `status` (`status`),
    KEY `cpr_number` (`cpr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- HRM permissions (run after table exists)
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'departments', 'departments.view', 'View Departments', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'departments.view');

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'departments', 'departments.export', 'Export Departments', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'departments.export');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'departments', 'departments.print', 'Print Departments', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'departments.print');

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employees', 'employees.export', 'Export Employees', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employees.export');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employees', 'employees.print', 'Print Employees', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employees.print');

-- 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 'departments.%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

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 'employees.%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
