-- Operations phase 2: Tasks, checklist items, work order approval
-- Run after operations.sql
-- Safe to re-run: uses IF NOT EXISTS / information_schema checks / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Work order approval: status enum + rejection_reason
-- ---------------------------------------------------------------------------
ALTER TABLE `work_orders`
    MODIFY COLUMN `status` ENUM(
        'draft',
        'open',
        'in_progress',
        'on_hold',
        'pending_approval',
        'rejected',
        'completed',
        'cancelled'
    ) NOT NULL DEFAULT 'open';

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'work_orders'
      AND COLUMN_NAME = 'rejection_reason'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `work_orders` ADD COLUMN `rejection_reason` VARCHAR(500) NULL AFTER `notes`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- Operations tasks
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `operations_tasks` (
    `id`                      BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                  VARCHAR(40)         NOT NULL,
    `title`                   VARCHAR(200)        NOT NULL,
    `description`             TEXT                NULL,
    `priority`                ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status`                  ENUM('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
    `due_date`                DATE                NULL,
    `completed_at`            DATETIME            NULL,
    `assignee_employee_id`    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 `status` (`status`),
    KEY `priority` (`priority`),
    KEY `due_date` (`due_date`),
    KEY `assignee_employee_id` (`assignee_employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `operations_task_checklist_items` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `task_id`               BIGINT UNSIGNED     NOT NULL,
    `title`                 VARCHAR(255)        NOT NULL,
    `is_done`               TINYINT(1)          NOT NULL DEFAULT 0,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `task_id` (`task_id`),
    KEY `is_done` (`is_done`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'work_orders', 'work_orders.approve', 'Approve Work Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'work_orders.approve');

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

-- ---------------------------------------------------------------------------
-- Directed approval requests (send to a specific approver)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `operations_approval_requests` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `request_type`          ENUM('expense','work','purchase','leave','general') NOT NULL DEFAULT 'general',
    `title`                 VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `amount`                DECIMAL(15,2)       NULL,
    `requester_user_id`     BIGINT UNSIGNED     NOT NULL,
    `approver_user_id`      BIGINT UNSIGNED     NOT NULL,
    `status`                ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    `rejection_reason`      VARCHAR(500)        NULL,
    `decided_at`            DATETIME            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 `status` (`status`),
    KEY `request_type` (`request_type`),
    KEY `requester_user_id` (`requester_user_id`),
    KEY `approver_user_id` (`approver_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'operations_approvals', 'operations_approvals.decide', 'Approve or Reject Operations Approval Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'operations_approvals.decide');

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