-- Nakhsha Customer Central
-- Migration 003: issue subcategories + 24-hour automatic escalation
-- Run ONCE in phpMyAdmin against nakhshahomes2_app.

CREATE TABLE issue_subcategories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_issue_subcategory(category_id, name),
  INDEX idx_issue_subcategory_category(category_id, is_active),
  CONSTRAINT fk_issue_subcategory_category
    FOREIGN KEY(category_id)
    REFERENCES issue_categories(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE tickets
  ADD COLUMN subcategory_id INT UNSIGNED NULL AFTER category_id,
  ADD COLUMN escalation_stage TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER assigned_by,
  ADD COLUMN assigned_at DATETIME NULL AFTER escalation_stage,
  ADD COLUMN last_action_at DATETIME NULL AFTER assigned_at,
  ADD COLUMN escalation_due_at DATETIME NULL AFTER last_action_at,
  ADD COLUMN last_escalated_at DATETIME NULL AFTER escalation_due_at,
  ADD INDEX idx_ticket_escalation_due(escalation_due_at, status),
  ADD CONSTRAINT fk_ticket_subcategory
    FOREIGN KEY(subcategory_id)
    REFERENCES issue_subcategories(id)
    ON DELETE SET NULL;

-- Dummy subcategories. Replace these later with the final client-approved list.

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Workmanship concern' FROM issue_categories WHERE name='Site execution concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Drawing / site mismatch' FROM issue_categories WHERE name='Site execution concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Material concern' FROM issue_categories WHERE name='Site execution concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Site cleanliness / debris' FROM issue_categories WHERE name='Site execution concern';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Power point not working' FROM issue_categories WHERE name='Electrical';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Switch / socket issue' FROM issue_categories WHERE name='Electrical';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'MCB / distribution board issue' FROM issue_categories WHERE name='Electrical';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Wiring concern' FROM issue_categories WHERE name='Electrical';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Lighting point issue' FROM issue_categories WHERE name='Electrical';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Water leakage' FROM issue_categories WHERE name='Plumbing';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Low water pressure' FROM issue_categories WHERE name='Plumbing';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Drainage blockage' FROM issue_categories WHERE name='Plumbing';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Sanitary fitting issue' FROM issue_categories WHERE name='Plumbing';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Water supply issue' FROM issue_categories WHERE name='Plumbing';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Paint finish' FROM issue_categories WHERE name='Finishing / quality';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Tile alignment / finish' FROM issue_categories WHERE name='Finishing / quality';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Door / window finish' FROM issue_categories WHERE name='Finishing / quality';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Crack / surface defect' FROM issue_categories WHERE name='Finishing / quality';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Polish / laminate finish' FROM issue_categories WHERE name='Finishing / quality';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Exposed electrical wire' FROM issue_categories WHERE name='Safety concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Unsafe opening / edge' FROM issue_categories WHERE name='Safety concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Scaffolding / work-at-height concern' FROM issue_categories WHERE name='Safety concern';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Debris / access hazard' FROM issue_categories WHERE name='Safety concern';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Work not started' FROM issue_categories WHERE name='Schedule / delay';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Milestone delayed' FROM issue_categories WHERE name='Schedule / delay';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Material delay' FROM issue_categories WHERE name='Schedule / delay';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Manpower delay' FROM issue_categories WHERE name='Schedule / delay';

INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'General query' FROM issue_categories WHERE name='Other';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Documentation' FROM issue_categories WHERE name='Other';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Access / coordination' FROM issue_categories WHERE name='Other';
INSERT IGNORE INTO issue_subcategories(category_id,name)
SELECT id,'Other' FROM issue_categories WHERE name='Other';