-- Employee Documents 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 `document_types` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(15)         NOT NULL,
    `requires_expiry`       TINYINT(1)          NOT NULL DEFAULT 0,
    `requires_number`       TINYINT(1)          NOT NULL DEFAULT 0,
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_documents` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `document_type_id`      BIGINT UNSIGNED     NOT NULL,
    `document_number`       VARCHAR(100)        NULL,
    `issue_date`            DATE                NULL,
    `expiry_date`           DATE                NULL,
    `notes`                 TEXT                NULL,
    `status`                ENUM('active','archived') NOT NULL DEFAULT 'active',
    `version_count`         INT UNSIGNED        NOT NULL DEFAULT 0,
    `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 `document_type_id` (`document_type_id`),
    KEY `expiry_date` (`expiry_date`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_document_versions` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_document_id`  BIGINT UNSIGNED     NOT NULL,
    `version_number`        INT UNSIGNED        NOT NULL,
    `file_path`             VARCHAR(255)        NOT NULL,
    `original_filename`     VARCHAR(255)        NOT NULL,
    `file_size`             INT UNSIGNED        NOT NULL DEFAULT 0,
    `mime_type`             VARCHAR(100)        NULL,
    `change_notes`          TEXT                NULL,
    `uploaded_by`           BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `employee_document_id` (`employee_document_id`),
    KEY `version_number` (`version_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default document types
INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Passport', 'PASSPORT', 1, 1, 'Employee passport', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'PASSPORT');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'CPR / National ID', 'CPR', 0, 1, 'CPR or national identification card', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'CPR');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Visa', 'VISA', 1, 1, 'Work or residence visa', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'VISA');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Work Permit', 'WORK_PERMIT', 1, 1, 'Work permit or labour card', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'WORK_PERMIT');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Driving License', 'DRIVING', 1, 1, 'Driving license', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'DRIVING');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Employment Contract', 'CONTRACT', 1, 0, 'Signed employment contract', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'CONTRACT');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Certificates', 'CERT', 0, 0, 'Training, education, and professional certificates', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'CERT');

INSERT INTO `document_types` (`name`, `code`, `requires_expiry`, `requires_number`, `description`, `status`, `created_at`)
SELECT 'Medical Documents', 'MEDICAL', 1, 0, 'Medical fitness and health records', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `document_types` WHERE `code` = 'MEDICAL');

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

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

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

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

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'employee_documents', 'employee_documents.download', 'Download Employee Documents', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'employee_documents.download');

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