CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(50) DEFAULT NULL,
  role ENUM('admin','hr','manager','staff') NOT NULL DEFAULT 'staff',
  employee_id VARCHAR(100) DEFAULT NULL,
  avatar_url TEXT DEFAULT NULL,
  gender VARCHAR(20) DEFAULT NULL,
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  profile_completed TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_profiles (
  user_id INT PRIMARY KEY,
  department VARCHAR(120) DEFAULT NULL,
  position VARCHAR(120) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  cv_url TEXT DEFAULT NULL,
  date_of_birth DATE DEFAULT NULL,
  contract_start_date DATE DEFAULT NULL,
  contract_end_date DATE DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS staff (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(50) DEFAULT NULL,
  department VARCHAR(120) NOT NULL,
  position VARCHAR(120) NOT NULL,
  join_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  profile_image TEXT DEFAULT NULL,
  status ENUM('pending','active','inactive') NOT NULL DEFAULT 'pending',
  approval_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  approval_notes TEXT DEFAULT NULL,
  approved_by INT DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_staff_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_staff_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  head_user_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_departments_head FOREIGN KEY (head_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS email_verification_tokens (
  user_id INT PRIMARY KEY,
  code VARCHAR(12) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_password_reset_token_hash (token_hash)
);

CREATE TABLE IF NOT EXISTS refresh_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  ip_address VARCHAR(64) DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_refresh_token_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uk_refresh_token_hash (token_hash)
);

CREATE TABLE IF NOT EXISTS calls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  call_uuid VARCHAR(64) NOT NULL,
  caller_id INT NOT NULL,
  callee_id INT NOT NULL,
  status ENUM('ringing','accepted','declined','ended','cancelled','missed') NOT NULL DEFAULT 'ringing',
  call_type ENUM('audio','video') NOT NULL DEFAULT 'audio',
  offer_sdp MEDIUMTEXT NULL,
  answer_sdp MEDIUMTEXT NULL,
  ended_reason VARCHAR(100) NULL,
  accepted_at DATETIME NULL,
  ended_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_calls_caller (caller_id),
  INDEX idx_calls_callee (callee_id),
  INDEX idx_calls_status (status)
);

CREATE TABLE IF NOT EXISTS device_tokens (
  user_id VARCHAR(128) PRIMARY KEY,
  token TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS announcements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  type ENUM('general','urgent','holiday','meeting','policy','event','maintenance','other') DEFAULT 'general',
  priority ENUM('low','medium','high','critical') DEFAULT 'medium',
  target_audience ENUM('all','staff','management','hr','finance','academic','admin','it','custom') DEFAULT 'all',
  audience_filter JSON DEFAULT NULL,
  attachments JSON DEFAULT NULL,
  author_id INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  view_count INT NOT NULL DEFAULT 0,
  expires_at DATETIME NULL,
  scheduled_at DATETIME NULL,
  published_at DATETIME NULL,
  scheduled_by INT NULL,
  status ENUM('sent','scheduled','draft') NOT NULL DEFAULT 'sent',
  send_push_enabled TINYINT(1) NOT NULL DEFAULT 1,
  send_email_enabled TINYINT(1) NOT NULL DEFAULT 0,
  archived_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_modified_by INT NULL,
  CONSTRAINT fk_announcements_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_announcements_scheduled_by FOREIGN KEY (scheduled_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_announcements_last_modified_by FOREIGN KEY (last_modified_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS communication_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  channel ENUM('email','push','sms') NOT NULL DEFAULT 'email',
  category ENUM('announcement','custom','system') NOT NULL DEFAULT 'custom',
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NULL,
  recipients LONGTEXT NULL,
  recipient_count INT NOT NULL DEFAULT 0,
  status ENUM('pending','sent','failed') NOT NULL DEFAULT 'sent',
  error TEXT NULL,
  metadata JSON DEFAULT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  CONSTRAINT fk_communication_logs_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS notification_preferences (
  user_id INT PRIMARY KEY,
  push_enabled TINYINT(1) NOT NULL DEFAULT 1,
  messages TINYINT(1) NOT NULL DEFAULT 1,
  leave_requests TINYINT(1) NOT NULL DEFAULT 1,
  attendance_reminders TINYINT(1) NOT NULL DEFAULT 1,
  announcements TINYINT(1) NOT NULL DEFAULT 1,
  reports TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_notification_preferences_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  body TEXT,
  attachment_url TEXT DEFAULT NULL,
  seen_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME DEFAULT NULL,
  CONSTRAINT fk_messages_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_messages_receiver FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_messages_sender_receiver (sender_id, receiver_id),
  INDEX idx_messages_receiver_sender (receiver_id, sender_id)
);
