-- Users table
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(36) PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  role ENUM('admin', 'editor', 'clinical_editor', 'user') DEFAULT 'user',
  status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login DATETIME,
  INDEX idx_email (email),
  INDEX idx_username (username),
  INDEX idx_role (role)
);

-- Login attempt tracking (identifier + IP) for DB-backed backoff/lockout
CREATE TABLE IF NOT EXISTS auth_login_attempts (
  login_identifier VARCHAR(255) NOT NULL,
  client_ip VARCHAR(64) NOT NULL,
  failed_attempts INT NOT NULL DEFAULT 0,
  lock_until DATETIME NULL,
  next_allowed_at DATETIME NULL,
  last_failed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (login_identifier, client_ip),
  INDEX idx_lock_until (lock_until),
  INDEX idx_last_failed_at (last_failed_at)
);

-- Refresh token sessions (hashed token storage + rotation metadata)
CREATE TABLE IF NOT EXISTS auth_refresh_tokens (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  family_id VARCHAR(36) NOT NULL,
  created_ip VARCHAR(64),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  revoked_reason VARCHAR(255) NULL,
  replaced_by_token_id VARCHAR(36) NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (replaced_by_token_id) REFERENCES auth_refresh_tokens(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_family_id (family_id),
  INDEX idx_expires_at (expires_at),
  INDEX idx_revoked_at (revoked_at)
);

-- Posts table (if not exists)
CREATE TABLE IF NOT EXISTS posts (
  id VARCHAR(36) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  username VARCHAR(100),
  image VARCHAR(255),
  date DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(36),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_date (date)
);

-- Activity log table for admin tracking
CREATE TABLE IF NOT EXISTS activity_logs (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36),
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50),
  entity_id VARCHAR(36),
  description TEXT,
  ip_address VARCHAR(45),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
);

-- Diseases and Conditions table
CREATE TABLE IF NOT EXISTS diseases (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  alias VARCHAR(255),
  first_letter CHAR(1) NOT NULL,
  description TEXT,
  overview TEXT,
  symptoms TEXT,
  causes TEXT,
  risk_factors TEXT,
  complications TEXT,
  prevention TEXT,
  diagnosis_treatment TEXT,
  content TEXT,
  icd11_uri VARCHAR(768) NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_name (name),
  INDEX idx_first_letter (first_letter),
  INDEX idx_status (status),
  UNIQUE KEY uq_diseases_icd11_uri (icd11_uri)
);

-- Services table
CREATE TABLE IF NOT EXISTS services (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  alias VARCHAR(255),
  category ENUM('general_care', 'diagnostic', 'specialized') NOT NULL,
  first_letter CHAR(1) NOT NULL,
  description TEXT,
  image VARCHAR(255),
  operating_days VARCHAR(255),
  operating_hours VARCHAR(255),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_name (name),
  INDEX idx_category (category),
  INDEX idx_first_letter (first_letter),
  INDEX idx_status (status)
);

-- Senior Management table
CREATE TABLE IF NOT EXISTS senior_management (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  designation VARCHAR(255) NOT NULL,
  image VARCHAR(255),
  bio TEXT,
  email VARCHAR(255),
  phone VARCHAR(50),
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_display_order (display_order)
);

-- Donors and Partners table
CREATE TABLE IF NOT EXISTS donors_partners (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  image VARCHAR(255) NOT NULL,
  website VARCHAR(255),
  description TEXT,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_display_order (display_order)
);

-- Departments table
CREATE TABLE IF NOT EXISTS departments (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  alias VARCHAR(255),
  first_letter CHAR(1) NOT NULL,
  description TEXT,
  vision TEXT,
  mission TEXT,
  image VARCHAR(255),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_name (name),
  INDEX idx_first_letter (first_letter),
  INDEX idx_status (status)
);

-- Department Units table
CREATE TABLE IF NOT EXISTS department_units (
  id VARCHAR(36) PRIMARY KEY,
  department_id VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
  INDEX idx_department_id (department_id),
  INDEX idx_status (status),
  INDEX idx_display_order (display_order)
);

-- Optional public descriptions for symptoms aggregated from disease.symptoms (key = browseNormKey)
CREATE TABLE IF NOT EXISTS symptom_browse_descriptions (
  norm_key VARCHAR(191) NOT NULL PRIMARY KEY,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Phrases hidden from public symptom browse (still in disease text; norm_key = browseNormKey)
CREATE TABLE IF NOT EXISTS symptom_browse_exclusions (
  norm_key VARCHAR(191) NOT NULL PRIMARY KEY,
  display_label VARCHAR(512) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Post Likes table
CREATE TABLE IF NOT EXISTS post_likes (
  id VARCHAR(36) PRIMARY KEY,
  post_id VARCHAR(36) NOT NULL,
  user_ip VARCHAR(45) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  UNIQUE KEY unique_post_ip (post_id, user_ip),
  INDEX idx_post_id (post_id),
  INDEX idx_user_ip (user_ip)
);

-- Post Comments table
CREATE TABLE IF NOT EXISTS post_comments (
  id VARCHAR(36) PRIMARY KEY,
  post_id VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  comment TEXT NOT NULL,
  status ENUM('approved', 'pending', 'spam') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  INDEX idx_post_id (post_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

-- Post Images table for multiple images per post
CREATE TABLE IF NOT EXISTS post_images (
  id VARCHAR(36) PRIMARY KEY,
  post_id VARCHAR(36) NOT NULL,
  image VARCHAR(255) NOT NULL,
  display_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  INDEX idx_post_id (post_id),
  INDEX idx_display_order (display_order)
);

-- Tests & Procedures table
CREATE TABLE IF NOT EXISTS tests_procedures (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  alias VARCHAR(255),
  first_letter CHAR(1) NOT NULL,
  description TEXT,
  overview TEXT,
  why_its_done TEXT,
  how_you_prepare TEXT,
  what_you_can_expect TEXT,
  results TEXT,
  risks TEXT,
  content TEXT,
  image VARCHAR(255),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_name (name),
  INDEX idx_first_letter (first_letter),
  INDEX idx_status (status)
);

-- Blogs table
CREATE TABLE IF NOT EXISTS blogs (
  id VARCHAR(36) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  excerpt TEXT,
  image VARCHAR(255),
  category VARCHAR(100),
  author VARCHAR(255) NOT NULL,
  status ENUM('draft', 'published') DEFAULT 'draft',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_title (title),
  INDEX idx_category (category),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

-- Events table
CREATE TABLE IF NOT EXISTS events (
  id VARCHAR(36) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  image VARCHAR(255),
  event_date DATETIME NOT NULL,
  location VARCHAR(255),
  status ENUM('upcoming', 'past') DEFAULT 'upcoming',
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_title (title),
  INDEX idx_event_date (event_date),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

-- Gallery table
CREATE TABLE IF NOT EXISTS gallery (
  id VARCHAR(36) PRIMARY KEY,
  image VARCHAR(255) NOT NULL,
  category ENUM('Facilities', 'Events', 'Outreach', 'Staff') NOT NULL,
  caption TEXT,
  created_by VARCHAR(36),
  uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_category (category),
  INDEX idx_uploaded_at (uploaded_at)
);

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
  id VARCHAR(36) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  short_description TEXT,
  featured_image VARCHAR(255),
  status ENUM('ongoing', 'planned', 'completed') DEFAULT 'planned',
  progress INT DEFAULT 0,
  start_date DATE,
  end_date DATE,
  beneficiaries TEXT,
  outcomes TEXT,
  created_by VARCHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  INDEX idx_created_by (created_by)
);

-- Project images (for sliders/galleries)
CREATE TABLE IF NOT EXISTS project_images (
  id VARCHAR(36) PRIMARY KEY,
  project_id VARCHAR(36) NOT NULL,
  image VARCHAR(255) NOT NULL,
  caption TEXT,
  display_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_project_id (project_id),
  INDEX idx_display_order (display_order)
);

-- Project partners
CREATE TABLE IF NOT EXISTS project_partners (
  id VARCHAR(36) PRIMARY KEY,
  project_id VARCHAR(36) NOT NULL,
  partner_name VARCHAR(255) NOT NULL,
  partner_logo VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_project_id (project_id)
);

-- Project documents
CREATE TABLE IF NOT EXISTS project_documents (
  id VARCHAR(36) PRIMARY KEY,
  project_id VARCHAR(36) NOT NULL,
  document_name VARCHAR(255) NOT NULL,
  document_path VARCHAR(255) NOT NULL,
  document_type VARCHAR(50) DEFAULT 'pdf',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_project_id (project_id),
  INDEX idx_document_type (document_type)
);