-- Operations phase 4: Field Service, Quality Control, Resources backends
-- Run after operations_phase3.sql
-- Safe to re-run: IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Field Service
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fs_service_visits` (
    `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)     NOT NULL,
    `title`                     VARCHAR(200)    NOT NULL,
    `description`               TEXT            NULL,
    `company_id`                BIGINT UNSIGNED NULL,
    `work_order_id`             BIGINT UNSIGNED NULL,
    `technician_employee_id`    BIGINT UNSIGNED NULL,
    `priority`                  ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status`                    ENUM('draft','scheduled','en_route','on_site','completed','cancelled') NOT NULL DEFAULT 'draft',
    `location`                  VARCHAR(200)    NULL,
    `scheduled_date`            DATE            NULL,
    `scheduled_start`           DATETIME        NULL,
    `scheduled_end`             DATETIME        NULL,
    `arrived_at`                DATETIME        NULL,
    `completed_at`              DATETIME        NULL,
    `notes`                     TEXT            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 `company_id` (`company_id`),
    KEY `work_order_id` (`work_order_id`),
    KEY `technician_employee_id` (`technician_employee_id`),
    KEY `scheduled_date` (`scheduled_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fs_technician_jobs` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `service_visit_id`      BIGINT UNSIGNED NULL,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `title`                 VARCHAR(200)    NOT NULL,
    `role`                  VARCHAR(100)    NULL,
    `status`                ENUM('assigned','accepted','in_progress','done','cancelled') NOT NULL DEFAULT 'assigned',
    `priority`              ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `due_date`              DATE            NULL,
    `assigned_at`           DATETIME        NULL,
    `started_at`            DATETIME        NULL,
    `completed_at`          DATETIME        NULL,
    `notes`                 TEXT            NULL,
    `assigned_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 `service_visit_id` (`service_visit_id`),
    KEY `employee_id` (`employee_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fs_inspection_reports` (
    `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)     NOT NULL,
    `service_visit_id`          BIGINT UNSIGNED NULL,
    `work_order_id`             BIGINT UNSIGNED NULL,
    `inspector_employee_id`     BIGINT UNSIGNED NULL,
    `title`                     VARCHAR(200)    NOT NULL,
    `findings`                  TEXT            NULL,
    `result`                    ENUM('pass','fail','conditional','pending') NOT NULL DEFAULT 'pending',
    `status`                    ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
    `inspected_at`              DATETIME        NULL,
    `notes`                     TEXT            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 `service_visit_id` (`service_visit_id`),
    KEY `work_order_id` (`work_order_id`),
    KEY `inspector_employee_id` (`inspector_employee_id`),
    KEY `status` (`status`),
    KEY `result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fs_customer_signatures` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `service_visit_id`      BIGINT UNSIGNED NULL,
    `inspection_report_id`  BIGINT UNSIGNED NULL,
    `company_id`            BIGINT UNSIGNED NULL,
    `signer_name`           VARCHAR(150)    NOT NULL,
    `signer_title`          VARCHAR(100)    NULL,
    `signature_notes`       TEXT            NULL,
    `signed_at`             DATETIME        NULL,
    `status`                ENUM('captured','voided') NOT NULL DEFAULT 'captured',
    `captured_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 `service_visit_id` (`service_visit_id`),
    KEY `inspection_report_id` (`inspection_report_id`),
    KEY `company_id` (`company_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Quality Control
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `qc_inspections` (
    `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)     NOT NULL,
    `title`                     VARCHAR(200)    NOT NULL,
    `description`               TEXT            NULL,
    `company_id`                BIGINT UNSIGNED NULL,
    `work_order_id`             BIGINT UNSIGNED NULL,
    `service_visit_id`          BIGINT UNSIGNED NULL,
    `inspector_employee_id`     BIGINT UNSIGNED NULL,
    `priority`                  ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    `status`                    ENUM('planned','in_progress','completed','cancelled') NOT NULL DEFAULT 'planned',
    `overall_result`            ENUM('pass','fail','na','pending') NOT NULL DEFAULT 'pending',
    `location`                  VARCHAR(200)    NULL,
    `scheduled_date`            DATE            NULL,
    `completed_at`              DATETIME        NULL,
    `score`                     DECIMAL(8,2)    NULL,
    `notes`                     TEXT            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 `overall_result` (`overall_result`),
    KEY `inspector_employee_id` (`inspector_employee_id`),
    KEY `scheduled_date` (`scheduled_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `qc_audit_checklists` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `name`                  VARCHAR(200)    NOT NULL,
    `code`                  VARCHAR(50)     NULL,
    `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 `number` (`number`),
    KEY `status` (`status`),
    KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS `qc_performance_scores` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `subject_type`          ENUM('employee','work_order','service_visit','inspection') NOT NULL DEFAULT 'employee',
    `subject_id`            BIGINT UNSIGNED NOT NULL,
    `subject_label`         VARCHAR(200)    NULL,
    `metric_code`           VARCHAR(80)     NOT NULL,
    `metric_name`           VARCHAR(150)    NOT NULL,
    `score`                 DECIMAL(8,2)    NOT NULL DEFAULT 0,
    `max_score`             DECIMAL(8,2)    NOT NULL DEFAULT 100,
    `period_start`          DATE            NULL,
    `period_end`            DATE            NULL,
    `status`                ENUM('draft','published') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT            NULL,
    `scored_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 `subject` (`subject_type`, `subject_id`),
    KEY `metric_code` (`metric_code`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Resources
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ops_staff_allocations` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `employee_id`           BIGINT UNSIGNED NOT NULL,
    `work_order_id`         BIGINT UNSIGNED NULL,
    `service_visit_id`      BIGINT UNSIGNED NULL,
    `role`                  VARCHAR(100)    NULL,
    `status`                ENUM('planned','active','released') NOT NULL DEFAULT 'planned',
    `from_date`             DATE            NULL,
    `to_date`               DATE            NULL,
    `notes`                 VARCHAR(500)    NULL,
    `assigned_at`           DATETIME        NULL,
    `assigned_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 `employee_id` (`employee_id`),
    KEY `work_order_id` (`work_order_id`),
    KEY `service_visit_id` (`service_visit_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ops_equipment_allocations` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `inventory_asset_id`    BIGINT UNSIGNED NOT NULL,
    `work_order_id`         BIGINT UNSIGNED NULL,
    `service_visit_id`      BIGINT UNSIGNED NULL,
    `status`                ENUM('assigned','returned','lost') NOT NULL DEFAULT 'assigned',
    `notes`                 VARCHAR(500)    NULL,
    `assigned_at`           DATETIME        NULL,
    `returned_at`           DATETIME        NULL,
    `assigned_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 `inventory_asset_id` (`inventory_asset_id`),
    KEY `work_order_id` (`work_order_id`),
    KEY `service_visit_id` (`service_visit_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ops_material_usage` (
    `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)     NOT NULL,
    `product_id`                BIGINT UNSIGNED NOT NULL,
    `work_order_id`             BIGINT UNSIGNED NULL,
    `service_visit_id`          BIGINT UNSIGNED NULL,
    `quantity`                  DECIMAL(12,3)   NOT NULL DEFAULT 0,
    `unit_label`                VARCHAR(30)     NULL,
    `used_at`                   DATETIME        NULL,
    `used_by_employee_id`       BIGINT UNSIGNED NULL,
    `notes`                     VARCHAR(500)    NULL,
    `status`                    ENUM('recorded','voided') NOT NULL DEFAULT 'recorded',
    `recorded_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 `product_id` (`product_id`),
    KEY `work_order_id` (`work_order_id`),
    KEY `service_visit_id` (`service_visit_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Permissions (expand beyond view-only from phase 3)
-- ---------------------------------------------------------------------------
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'field_service', 'field_service.create', 'Create Field Service Records', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'field_service.create');

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

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

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

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

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

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

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

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

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