-- ISB Case Management System - MySQL Database Initialization Script
-- Run this script in phpMyAdmin or MySQL CLI to create the database tables

-- Sessions table (for express-session)
CREATE TABLE IF NOT EXISTS sessions (
  session_id VARCHAR(128) PRIMARY KEY,
  expires INT(11) UNSIGNED NOT NULL,
  data MEDIUMTEXT
);

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  username VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  profile_image_url VARCHAR(500),
  must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
  last_login_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User Profiles table
CREATE TABLE IF NOT EXISTS user_profiles (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id VARCHAR(36) NOT NULL UNIQUE,
  role ENUM('investigator', 'supervisor', 'admin', 'auditor') NOT NULL DEFAULT 'investigator',
  badge_number VARCHAR(50) UNIQUE,
  department VARCHAR(255),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Cases table
CREATE TABLE IF NOT EXISTS cases (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  case_number VARCHAR(50) NOT NULL UNIQUE,
  title VARCHAR(500) NOT NULL,
  description TEXT,
  type ENUM('criminal', 'internal_affairs', 'fraud', 'cybercrime') NOT NULL,
  status ENUM('open', 'active', 'under_review', 'closed', 'urgent') NOT NULL DEFAULT 'open',
  priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
  lead_investigator_id VARCHAR(36),
  assigned_officers JSON DEFAULT '[]',
  is_restricted BOOLEAN NOT NULL DEFAULT FALSE,
  created_by VARCHAR(36) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  closed_at TIMESTAMP NULL
);

-- Case Notes table
CREATE TABLE IF NOT EXISTS case_notes (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  case_id VARCHAR(36) NOT NULL,
  author_id VARCHAR(36) NOT NULL,
  content TEXT NOT NULL,
  is_confidential BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Audit Logs table
CREATE TABLE IF NOT EXISTS audit_logs (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id VARCHAR(36) NOT NULL,
  user_name VARCHAR(255),
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id VARCHAR(36),
  details TEXT,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Report Templates table
CREATE TABLE IF NOT EXISTS report_templates (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  template_type ENUM('file_upload', 'question_form') NOT NULL DEFAULT 'file_upload',
  case_types JSON NOT NULL DEFAULT '[]',
  schema_json TEXT NOT NULL DEFAULT '{}',
  max_file_size_mb INT,
  allowed_file_types JSON,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by VARCHAR(36) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Case Tasks table
CREATE TABLE IF NOT EXISTS case_tasks (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  case_id VARCHAR(36) NOT NULL,
  investigator_id VARCHAR(36) NOT NULL,
  supervisor_id VARCHAR(36),
  title VARCHAR(500) NOT NULL,
  description TEXT,
  status ENUM('assigned', 'in_progress', 'submitted', 'under_review', 'completed', 'archived') NOT NULL DEFAULT 'assigned',
  started_at TIMESTAMP NULL,
  submitted_at TIMESTAMP NULL,
  reviewed_at TIMESTAMP NULL,
  review_notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Case Reports table
CREATE TABLE IF NOT EXISTS case_reports (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  case_id VARCHAR(36) NOT NULL,
  template_id VARCHAR(36),
  report_type VARCHAR(50) DEFAULT 'summary',
  author_id VARCHAR(36) NOT NULL,
  title VARCHAR(500) NOT NULL,
  status ENUM('draft', 'submitted', 'approved', 'rejected') NOT NULL DEFAULT 'draft',
  form_data TEXT,
  pdf_path VARCHAR(500),
  submitted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Report Attachments table
CREATE TABLE IF NOT EXISTS report_attachments (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  report_id VARCHAR(36) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_type VARCHAR(100) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_size INT NOT NULL,
  uploaded_by VARCHAR(36) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX idx_sessions_expire ON sessions(expires);
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);
CREATE INDEX idx_cases_case_number ON cases(case_number);
CREATE INDEX idx_cases_lead_investigator ON cases(lead_investigator_id);
CREATE INDEX idx_cases_status ON cases(status);
CREATE INDEX idx_case_notes_case_id ON case_notes(case_id);
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
CREATE INDEX idx_case_tasks_case_id ON case_tasks(case_id);
CREATE INDEX idx_case_tasks_investigator ON case_tasks(investigator_id);
CREATE INDEX idx_case_reports_case_id ON case_reports(case_id);

-- Insert default admin user (password: admin123)
-- Password hash for 'admin123' using bcrypt
INSERT INTO users (id, username, password_hash, first_name, last_name, email, must_change_password)
VALUES (
  UUID(),
  'admin',
  '$2a$10$rQZ7gqKjZLJv8YsQkJ8lVuZKx5X5Xq5K5vZK5X5Xq5K5vZK5X5Xq.',
  'System',
  'Administrator',
  'admin@isb.fenwick.gov',
  FALSE
);

-- Get the admin user ID and create the profile
SET @admin_id = (SELECT id FROM users WHERE username = 'admin');

INSERT INTO user_profiles (id, user_id, role, badge_number, department, is_active)
VALUES (
  UUID(),
  @admin_id,
  'admin',
  'ISB-001',
  'Administration',
  TRUE
);

-- Display success message
SELECT 'Database initialized successfully!' AS Status;