-- Operations module: Work Orders
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS
-- Equipment assignment uses inventory_assets (run inventory_phase2.sql first if needed).

CREATE TABLE IF NOT EXISTS `work_orders` (
    `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('draft','open','in_progress','on_hold','completed','cancelled') NOT NULL DEFAULT 'open',
    `scheduled_date`        DATE                NULL,
    `due_date`              DATE                NULL,
    `location`              VARCHAR(200)        NULL,
    `notes`                 TEXT                NULL,
    `completion_notes`      TEXT                NULL,
    `started_at`            DATETIME            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 `status` (`status`),
    KEY `priority` (`priority`),
    KEY `scheduled_date` (`scheduled_date`),
    KEY `due_date` (`due_date`),
    KEY `completed_at` (`completed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `work_order_staff` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `work_order_id`         BIGINT UNSIGNED     NOT NULL,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `role`                  VARCHAR(100)        NULL,
    `notes`                 VARCHAR(500)        NULL,
    `assigned_at`           DATETIME            NULL,
    `assigned_by`           BIGINT UNSIGNED     NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `work_order_employee` (`work_order_id`, `employee_id`),
    KEY `employee_id` (`employee_id`),
    KEY `work_order_id` (`work_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `work_order_equipment` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `work_order_id`         BIGINT UNSIGNED     NOT NULL,
    `inventory_asset_id`    BIGINT UNSIGNED     NOT NULL,
    `notes`                 VARCHAR(500)        NULL,
    `assigned_at`           DATETIME            NULL,
    `assigned_by`           BIGINT UNSIGNED     NULL,
    `returned_at`           DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `work_order_asset` (`work_order_id`, `inventory_asset_id`),
    KEY `inventory_asset_id` (`inventory_asset_id`),
    KEY `work_order_id` (`work_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'work_orders', 'work_orders.complete', 'Complete Work Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'work_orders.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 = 'operations.view'
      OR p.slug LIKE 'work_orders.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
