-- MySQL schema for Safety Incident Reporting System
-- Create DB/schema separately if needed: CREATE DATABASE safety_schema CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Then: USE safety_schema;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  user_type ENUM('admin','end_user') NOT NULL DEFAULT 'end_user',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS config_incident_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS config_severity_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS config_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS config_departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incidents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_no VARCHAR(30) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  incident_type_id INT NOT NULL,
  severity_id INT NOT NULL,
  location_id INT NOT NULL,
  department_id INT NOT NULL,
  reported_by_user_id INT NULL,
  occurred_at DATETIME NULL,
  status ENUM('open','closed','force_closed') NOT NULL DEFAULT 'open',
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  closed_at DATETIME NULL,
  force_close_reason TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX (severity_id),
  INDEX (status),
  CONSTRAINT fk_inc_type FOREIGN KEY (incident_type_id) REFERENCES config_incident_types(id),
  CONSTRAINT fk_sev FOREIGN KEY (severity_id) REFERENCES config_severity_levels(id),
  CONSTRAINT fk_loc FOREIGN KEY (location_id) REFERENCES config_locations(id),
  CONSTRAINT fk_dep FOREIGN KEY (department_id) REFERENCES config_departments(id),
  CONSTRAINT fk_rep FOREIGN KEY (reported_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX (incident_id),
  CONSTRAINT fk_attach_inc FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed defaults if empty
INSERT IGNORE INTO users (id, full_name, email, user_type, created_at) VALUES
  (1, 'Admin User', 'admin@example.com', 'admin', NOW()),
  (2, 'End User', 'user@example.com', 'end_user', NOW());

INSERT IGNORE INTO config_incident_types (id, name, created_at) VALUES
  (1, 'Near Miss', NOW()),
  (2, 'Injury', NOW()),
  (3, 'Property Damage', NOW());

INSERT IGNORE INTO config_severity_levels (id, name, sort_order, created_at) VALUES
  (1, 'Low', 10, NOW()),
  (2, 'Medium', 20, NOW()),
  (3, 'High', 30, NOW()),
  (4, 'Critical', 40, NOW());

INSERT IGNORE INTO config_locations (id, name, created_at) VALUES
  (1, 'Plant Floor', NOW()),
  (2, 'Warehouse', NOW()),
  (3, 'Loading Bay', NOW());

INSERT IGNORE INTO config_departments (id, name, created_at) VALUES
  (1, 'Production', NOW()),
  (2, 'Maintenance', NOW()),
  (3, 'Quality', NOW());
