-- Maintenance Governance Framework
-- Adds role/stage governance metadata, transition controls, RFQ vendor normalization,
-- quote tracking, and full stage history logging.

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_id BIGINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_role_id (role_id),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  assigned_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_by BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_role (user_id, role_id),
  KEY idx_user_roles_role_id (role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(120) NOT NULL,
  label VARCHAR(200) NULL,
  module VARCHAR(120) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_stages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  stage_name VARCHAR(120) NOT NULL,
  stage_order INT NOT NULL DEFAULT 0,
  is_terminal TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_maintenance_stages_name (stage_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stage_transitions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_stage_id BIGINT UNSIGNED NOT NULL,
  to_stage_id BIGINT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_stage_transition (from_stage_id, to_stage_id),
  KEY idx_stage_transitions_to (to_stage_id),
  CONSTRAINT fk_stage_transitions_from FOREIGN KEY (from_stage_id) REFERENCES maintenance_stages(id) ON DELETE CASCADE,
  CONSTRAINT fk_stage_transitions_to FOREIGN KEY (to_stage_id) REFERENCES maintenance_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_stage_permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id BIGINT UNSIGNED NOT NULL,
  stage_id BIGINT UNSIGNED NOT NULL,
  can_act TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_role_stage_permission (role_id, stage_id),
  KEY idx_role_stage_permissions_stage (stage_id),
  CONSTRAINT fk_rsp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rsp_stage FOREIGN KEY (stage_id) REFERENCES maintenance_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_rfqs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  maintenance_job_id BIGINT UNSIGNED NOT NULL,
  workshop_id BIGINT UNSIGNED NULL,
  rfq_number VARCHAR(64) NULL,
  rfq_sent_date DATE NULL,
  status ENUM('draft','sent','quoted','selected','rejected') NOT NULL DEFAULT 'sent',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_maintenance_rfqs_job (maintenance_job_id),
  KEY idx_maintenance_rfqs_workshop (workshop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_rfq_vendors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rfq_id BIGINT UNSIGNED NOT NULL,
  vendor_workshop_id BIGINT UNSIGNED NOT NULL,
  invited_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  invitation_status ENUM('pending','sent','declined','responded') NOT NULL DEFAULT 'sent',
  PRIMARY KEY (id),
  UNIQUE KEY uq_rfq_vendor (rfq_id, vendor_workshop_id),
  KEY idx_rfq_vendors_workshop (vendor_workshop_id),
  CONSTRAINT fk_rfq_vendors_rfq FOREIGN KEY (rfq_id) REFERENCES maintenance_rfqs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_quotes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rfq_vendor_id BIGINT UNSIGNED NOT NULL,
  quote_file VARCHAR(255) NULL,
  quote_amount DECIMAL(12,2) NULL,
  quote_received_date DATE NULL,
  notes TEXT NULL,
  status ENUM('pending','submitted','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_maintenance_quotes_vendor (rfq_vendor_id),
  CONSTRAINT fk_maintenance_quotes_vendor FOREIGN KEY (rfq_vendor_id) REFERENCES maintenance_rfq_vendors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_stage_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  maintenance_job_id BIGINT UNSIGNED NOT NULL,
  stage_from VARCHAR(120) NULL,
  stage_to VARCHAR(120) NULL,
  action_code VARCHAR(120) NULL,
  comment TEXT NULL,
  changed_by BIGINT UNSIGNED NULL,
  changed_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_job_stage_history_job (maintenance_job_id),
  KEY idx_job_stage_history_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed canonical maintenance stages if missing.
INSERT IGNORE INTO maintenance_stages (stage_name, stage_order, is_terminal) VALUES
('Complaint Logged', 10, 0),
('Maintenance Review', 20, 0),
('Work Order Raised', 30, 0),
('RFQ Sent', 40, 0),
('Quote Submitted', 50, 0),
('Quote Vetted', 60, 0),
('Job Order Approved', 70, 0),
('Workshop In Progress', 80, 0),
('Inspection', 90, 0),
('Internal Control', 100, 0),
('Closed', 110, 1),
('Suspended', 120, 0),
('Cancelled', 130, 1);

-- Seed canonical stage transitions.
INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Complaint Logged' AND t.stage_name IN ('Maintenance Review','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Maintenance Review' AND t.stage_name IN ('Work Order Raised','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Work Order Raised' AND t.stage_name IN ('RFQ Sent','Quote Submitted','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'RFQ Sent' AND t.stage_name IN ('Quote Submitted','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Quote Submitted' AND t.stage_name IN ('Quote Vetted','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Quote Vetted' AND t.stage_name IN ('Job Order Approved','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Job Order Approved' AND t.stage_name IN ('Workshop In Progress','Suspended','Cancelled');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Workshop In Progress' AND t.stage_name IN ('Inspection','Suspended');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Inspection' AND t.stage_name IN ('Internal Control','Workshop In Progress');

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Internal Control' AND t.stage_name = 'Closed';

INSERT IGNORE INTO stage_transitions (from_stage_id, to_stage_id, is_active)
SELECT f.id, t.id, 1
FROM maintenance_stages f
INNER JOIN maintenance_stages t
WHERE f.stage_name = 'Suspended' AND t.stage_name IN ('Workshop In Progress','Cancelled');