-- Full announcement remake for USHS
-- Run once in phpMyAdmin if you want to create the tables manually.
-- The PHP files also auto-create these tables when any announcement page is opened.

ALTER TABLE csr_users
  MODIFY role ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr';

CREATE TABLE IF NOT EXISTS ushs_announcements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  message TEXT NOT NULL,
  priority ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
  sender_type ENUM('admin','csr_manager','team_leader') NOT NULL,
  sender_id INT NOT NULL,
  target_type ENUM('all','role','team','team_agents','user') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ushs_ann_active (is_active, created_at),
  INDEX idx_ushs_ann_sender (sender_type, sender_id),
  INDEX idx_ushs_ann_target_type (target_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ushs_announcement_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  announcement_id INT NOT NULL,
  target_role ENUM('csr','team_leader','csr_manager') NULL,
  target_team_id INT NULL,
  target_user_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ushs_ann_targets_ann (announcement_id),
  INDEX idx_ushs_ann_targets_role (target_role),
  INDEX idx_ushs_ann_targets_team (target_team_id),
  INDEX idx_ushs_ann_targets_user (target_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ushs_announcement_reads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  announcement_id INT NOT NULL,
  csr_id INT NOT NULL,
  read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ushs_ann_read (announcement_id, csr_id),
  INDEX idx_ushs_ann_reads_csr (csr_id),
  INDEX idx_ushs_ann_reads_ann (announcement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Keep announcements out of the normal notification bell if the old system inserted them there.
UPDATE csr_notifications
SET is_read = 1
WHERE COALESCE(reference_type, '') = 'announcement';
