-- Cincinnati Fire Department Portal - Database Schema
-- Run: mysql -u root -p < deploy/schema.sql

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

USE cincinnatifire_db;

-- Create dedicated user
CREATE USER IF NOT EXISTS 'cincfire_user'@'localhost' IDENTIFIED BY 'CfdPortal2026!';
GRANT ALL PRIVILEGES ON cincinnatifire_db.* TO 'cincfire_user'@'localhost';
CREATE USER IF NOT EXISTS 'cincfire_user'@'127.0.0.1' IDENTIFIED BY 'CfdPortal2026!';
GRANT ALL PRIVILEGES ON cincinnatifire_db.* TO 'cincfire_user'@'127.0.0.1';
FLUSH PRIVILEGES;

-- USERS
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','chief','asst_chief','district_chief','captain','lieutenant','firefighter','hr_finance','guest') DEFAULT 'firefighter',
  station VARCHAR(20),
  unit VARCHAR(10),
  badge_number VARCHAR(20),
  status ENUM('pending','active','suspended') DEFAULT 'pending',
  avatar_url VARCHAR(255),
  radio_enabled TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP NULL,
  approved_by INT NULL,
  approved_at TIMESTAMP NULL,
  INDEX idx_users_role (role),
  INDEX idx_users_status (status),
  INDEX idx_users_station (station)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- INCIDENTS CACHE (from Active911)
CREATE TABLE IF NOT EXISTS incidents_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id VARCHAR(100) UNIQUE,
  incident_type VARCHAR(100),
  address TEXT,
  city VARCHAR(100),
  units_assigned TEXT,
  priority VARCHAR(20),
  status VARCHAR(50),
  latitude DECIMAL(10,8),
  longitude DECIMAL(11,8),
  raw_json TEXT,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  displayed TINYINT(1) DEFAULT 0,
  INDEX idx_ic_received (received_at),
  INDEX idx_ic_displayed (displayed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OT RECORDS
CREATE TABLE IF NOT EXISTS ot_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  shift_date DATE,
  unit VARCHAR(10),
  hours DECIMAL(5,2),
  ot_type VARCHAR(50),
  notes TEXT,
  approved_by INT NULL,
  status ENUM('pending','approved','denied') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_ot_user (user_id),
  INDEX idx_ot_date (shift_date),
  INDEX idx_ot_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FILE UPLOADS (OT Taskforce documents)
CREATE TABLE IF NOT EXISTS uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  filename VARCHAR(255),
  original_name VARCHAR(255),
  file_type VARCHAR(50),
  file_size INT,
  module VARCHAR(50),
  description TEXT,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_uploads_user (user_id),
  INDEX idx_uploads_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NEWS CACHE
CREATE TABLE IF NOT EXISTS news_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title TEXT,
  link TEXT,
  source VARCHAR(100),
  published_at TIMESTAMP NULL,
  cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_news_source (source),
  INDEX idx_news_cached (cached_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- AUDIT LOG
CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(100) NOT NULL,
  detail TEXT,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_action (action),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- RATE LIMITING
CREATE TABLE IF NOT EXISTS rate_limits (
  ip VARCHAR(45) PRIMARY KEY,
  attempts INT DEFAULT 0,
  first_attempt DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_attempt DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SETTINGS (admin-configurable key-value store)
CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default settings
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
  ('google_slides_url', ''),
  ('ot_embed_url', ''),
  ('target_solutions_url', 'https://app.targetsolutions.com'),
  ('eso_url', 'https://app.esosuite.net'),
  ('admin_email', 'admin@cincinnatifire.ai');

-- Insert default admin account
-- Password: We1come! (bcrypt cost 12)
INSERT INTO users (first_name, last_name, email, username, password_hash, role, status)
VALUES ('Admin', 'CFD', 'admin@cincinnatifire.ai', 'admin',
  '$2y$12$lK73Itkm4KmbiZu7zKpLTuBsAMekW4yes0a8Cva9RjKG14x8T9brC',
  'admin', 'active')
ON DUPLICATE KEY UPDATE username = username;
