-- MySQL schema for Incident Management System

CREATE DATABASE IF NOT EXISTS incident_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE incident_db;

CREATE TABLE IF NOT EXISTS incidents (
  id VARCHAR(40) NOT NULL,
  incident_datetime DATETIME NOT NULL,
  type VARCHAR(40) NOT NULL,
  location VARCHAR(255) NOT NULL,
  severity ENUM('Low','Medium','High','Critical') NOT NULL DEFAULT 'Medium',
  status ENUM('Open','Investigating','Resolved','Closed') NOT NULL DEFAULT 'Open',
  title VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  reporter_name VARCHAR(120) NOT NULL,
  reporter_email VARCHAR(180) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_created_at (created_at),
  INDEX idx_status (status),
  INDEX idx_severity (severity),
  INDEX idx_type (type)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS incident_comments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  incident_id VARCHAR(40) NOT NULL,
  comment TEXT NOT NULL,
  actor_name VARCHAR(120) NOT NULL,
  actor_email VARCHAR(180) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_incident_id (incident_id),
  CONSTRAINT fk_comments_incident
    FOREIGN KEY (incident_id) REFERENCES incidents(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;
