<?php
// includes/announcement_system.php — clean standalone announcement system
// Uses new tables only: ushs_announcements + ushs_announcement_targets + ushs_announcement_reads.

require_once __DIR__ . '/app_helpers.php';

if (!function_exists('ann_h')) {
    function ann_h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}

function ensureUnifiedAnnouncementSystem(PDO $db): void {
    ensureCsrTeamTables($db);
    addColumnIfMissing($db, 'csr_users', 'role', "ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr' AFTER max_active_chats");
    try { $db->exec("ALTER TABLE csr_users MODIFY role ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr'"); } catch (Throwable $e) {}

    $db->exec("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");

    $db->exec("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");

    $db->exec("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");

    try {
        if (columnExists($db, 'csr_notifications', 'reference_type')) {
            $db->exec("UPDATE csr_notifications SET is_read = 1 WHERE COALESCE(reference_type, '') = 'announcement'");
        }
    } catch (Throwable $e) {}
}

function annGetCsr(PDO $db, int $csrId): ?array {
    $stmt = $db->prepare("SELECT id, first_name, last_name, email, COALESCE(role,'csr') AS role, is_active FROM csr_users WHERE id=:id LIMIT 1");
    $stmt->execute([':id'=>$csrId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function annFullName(array $u): string {
    $name = trim(($u['first_name'] ?? '') . ' ' . ($u['last_name'] ?? ''));
    return $name !== '' ? $name : (($u['email'] ?? '') ?: ('User #' . ($u['id'] ?? '')));
}

function annRoleLabel(string $role): string {
    return $role === 'csr_manager' ? 'CSR Manager' : ($role === 'team_leader' ? 'Team Leader' : 'CSR Agent');
}

function annSenderLabel(string $senderType): string {
    return $senderType === 'admin' ? 'Admin' : ($senderType === 'csr_manager' ? 'CSR Manager' : 'Team Leader');
}

function annTargetLabel(PDO $db, array $ann): string {
    $type = $ann['target_type'] ?? '';
    $id = (int)($ann['id'] ?? 0);
    if ($type === 'all') return 'All CSR users';
    $stmt = $db->prepare("SELECT * FROM ushs_announcement_targets WHERE announcement_id=:id ORDER BY id");
    $stmt->execute([':id'=>$id]);
    $targets = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($type === 'role') {
        $roles = array_map(fn($t)=>annRoleLabel($t['target_role'] ?? 'csr'), $targets);
        return $roles ? implode(', ', array_unique($roles)) : 'Role target';
    }
    if ($type === 'user') {
        $ids = array_values(array_filter(array_map(fn($t)=>(int)($t['target_user_id'] ?? 0), $targets)));
        if (!$ids) return 'Specific person';
        $holders = implode(',', array_fill(0, count($ids), '?'));
        $s = $db->prepare("SELECT id, first_name, last_name, email FROM csr_users WHERE id IN ($holders) ORDER BY first_name,last_name");
        $s->execute($ids);
        $names = array_map('annFullName', $s->fetchAll(PDO::FETCH_ASSOC));
        return $names ? implode(', ', $names) : 'Specific person';
    }
    if ($type === 'team' || $type === 'team_agents') {
        $ids = array_values(array_filter(array_map(fn($t)=>(int)($t['target_team_id'] ?? 0), $targets)));
        if (!$ids) return $type === 'team_agents' ? 'Team agents' : 'Team';
        $holders = implode(',', array_fill(0, count($ids), '?'));
        $s = $db->prepare("SELECT team_name FROM csr_teams WHERE id IN ($holders) ORDER BY team_name");
        $s->execute($ids);
        $names = $s->fetchAll(PDO::FETCH_COLUMN);
        $suffix = $type === 'team_agents' ? ' agents only' : ' leader + agents';
        return ($names ? implode(', ', $names) : 'Team') . $suffix;
    }
    return 'Target';
}

function annGetAllCsrUsers(PDO $db): array {
    $stmt = $db->query("SELECT id, first_name, last_name, email, COALESCE(role,'csr') AS role FROM csr_users WHERE COALESCE(is_active,1)=1 ORDER BY FIELD(COALESCE(role,'csr'),'csr_manager','team_leader','csr'), first_name, last_name, email");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function annGetTeams(PDO $db, ?int $leaderId = null): array {
    if ($leaderId) {
        $stmt = $db->prepare("SELECT id, team_name, manager_id FROM csr_teams WHERE is_active=1 AND manager_id=:id ORDER BY team_name");
        $stmt->execute([':id'=>$leaderId]);
    } else {
        $stmt = $db->query("SELECT id, team_name, manager_id FROM csr_teams WHERE is_active=1 ORDER BY team_name");
    }
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function annCreate(PDO $db, string $senderType, int $senderId, string $title, string $message, string $priority, string $targetType, array $targetValues): int {
    ensureUnifiedAnnouncementSystem($db);
    $title = trim($title);
    $message = trim($message);
    if ($title === '' || $message === '') throw new Exception('Title and message are required.');
    if (!in_array($priority, ['info','success','warning','danger'], true)) $priority = 'info';
    if (!in_array($senderType, ['admin','csr_manager','team_leader'], true)) throw new Exception('Invalid sender.');
    if (!in_array($targetType, ['all','role','team','team_agents','user'], true)) throw new Exception('Invalid announcement target.');

    $db->beginTransaction();
    try {
        $stmt = $db->prepare("INSERT INTO ushs_announcements (title,message,priority,sender_type,sender_id,target_type,is_active) VALUES (:title,:message,:priority,:sender_type,:sender_id,:target_type,1)");
        $stmt->execute([':title'=>$title, ':message'=>$message, ':priority'=>$priority, ':sender_type'=>$senderType, ':sender_id'=>$senderId, ':target_type'=>$targetType]);
        $annId = (int)$db->lastInsertId();

        if ($targetType === 'role') {
            $roles = array_values(array_unique(array_filter($targetValues, fn($r)=>in_array($r, ['csr','team_leader','csr_manager'], true))));
            if (!$roles) throw new Exception('Please select at least one role.');
            $ins = $db->prepare("INSERT INTO ushs_announcement_targets (announcement_id,target_role) VALUES (:a,:r)");
            foreach ($roles as $r) $ins->execute([':a'=>$annId, ':r'=>$r]);
        } elseif ($targetType === 'team' || $targetType === 'team_agents') {
            $teamIds = array_values(array_unique(array_filter(array_map('intval', $targetValues))));
            if (!$teamIds) throw new Exception('Please select at least one team.');
            $ins = $db->prepare("INSERT INTO ushs_announcement_targets (announcement_id,target_team_id) VALUES (:a,:t)");
            foreach ($teamIds as $t) $ins->execute([':a'=>$annId, ':t'=>$t]);
        } elseif ($targetType === 'user') {
            $userIds = array_values(array_unique(array_filter(array_map('intval', $targetValues))));
            if (!$userIds) throw new Exception('Please select a person.');
            $ins = $db->prepare("INSERT INTO ushs_announcement_targets (announcement_id,target_user_id) VALUES (:a,:u)");
            foreach ($userIds as $u) $ins->execute([':a'=>$annId, ':u'=>$u]);
        }

        $db->commit();
        return $annId;
    } catch (Throwable $e) {
        if ($db->inTransaction()) $db->rollBack();
        throw $e;
    }
}

function annCanReceive(PDO $db, int $annId, int $csrId): bool {
    $sql = "
        SELECT a.id
        FROM ushs_announcements a
        INNER JOIN csr_users cu ON cu.id = :csr_id AND COALESCE(cu.is_active,1)=1
        WHERE a.id = :ann_id AND a.is_active = 1
          AND (
            a.target_type = 'all'
            OR (a.target_type = 'role' AND EXISTS (
                SELECT 1 FROM ushs_announcement_targets t WHERE t.announcement_id=a.id AND t.target_role=COALESCE(cu.role,'csr')
            ))
            OR (a.target_type = 'user' AND EXISTS (
                SELECT 1 FROM ushs_announcement_targets t WHERE t.announcement_id=a.id AND t.target_user_id=cu.id
            ))
            OR (a.target_type = 'team' AND EXISTS (
                SELECT 1 FROM ushs_announcement_targets t
                LEFT JOIN csr_teams ct ON ct.id=t.target_team_id
                LEFT JOIN csr_team_members tm ON tm.team_id=t.target_team_id AND tm.csr_id=cu.id
                WHERE t.announcement_id=a.id AND (ct.manager_id=cu.id OR tm.csr_id IS NOT NULL)
            ))
            OR (a.target_type = 'team_agents' AND EXISTS (
                SELECT 1 FROM ushs_announcement_targets t
                INNER JOIN csr_team_members tm ON tm.team_id=t.target_team_id AND tm.csr_id=cu.id
                WHERE t.announcement_id=a.id AND COALESCE(cu.role,'csr')='csr'
            ))
          )
        LIMIT 1";
    $stmt = $db->prepare($sql);
    $stmt->execute([':ann_id'=>$annId, ':csr_id'=>$csrId]);
    return (bool)$stmt->fetchColumn();
}

function annVisibleWhereSql(): string {
    return "
      AND (
        a.target_type = 'all'
        OR (a.target_type = 'role' AND EXISTS (SELECT 1 FROM ushs_announcement_targets t WHERE t.announcement_id=a.id AND t.target_role=COALESCE(cu.role,'csr')))
        OR (a.target_type = 'user' AND EXISTS (SELECT 1 FROM ushs_announcement_targets t WHERE t.announcement_id=a.id AND t.target_user_id=cu.id))
        OR (a.target_type = 'team' AND EXISTS (
            SELECT 1 FROM ushs_announcement_targets t
            LEFT JOIN csr_teams ct ON ct.id=t.target_team_id
            LEFT JOIN csr_team_members tm ON tm.team_id=t.target_team_id AND tm.csr_id=cu.id
            WHERE t.announcement_id=a.id AND (ct.manager_id=cu.id OR tm.csr_id IS NOT NULL)
        ))
        OR (a.target_type = 'team_agents' AND EXISTS (
            SELECT 1 FROM ushs_announcement_targets t
            INNER JOIN csr_team_members tm ON tm.team_id=t.target_team_id AND tm.csr_id=cu.id
            WHERE t.announcement_id=a.id AND COALESCE(cu.role,'csr')='csr'
        ))
      )";
}

function annGetUnreadForUser(PDO $db, int $csrId, int $limit = 8): array {
    ensureUnifiedAnnouncementSystem($db);
    $limit = max(1, min(30, $limit));
    $where = annVisibleWhereSql();
    $countSql = "SELECT COUNT(DISTINCT a.id)
        FROM ushs_announcements a
        INNER JOIN csr_users cu ON cu.id=:csr_id AND COALESCE(cu.is_active,1)=1
        LEFT JOIN ushs_announcement_reads r ON r.announcement_id=a.id AND r.csr_id=cu.id
        WHERE a.is_active=1 AND r.id IS NULL $where";
    $stmt = $db->prepare($countSql);
    $stmt->execute([':csr_id'=>$csrId]);
    $count = (int)$stmt->fetchColumn();

    $sql = "SELECT DISTINCT a.id, a.title, a.message, a.priority, a.created_at, a.sender_type,
            CASE WHEN a.sender_type='admin' THEN 'Admin'
                 ELSE TRIM(CONCAT(COALESCE(s.first_name,''),' ',COALESCE(s.last_name,''))) END AS created_by_name
        FROM ushs_announcements a
        INNER JOIN csr_users cu ON cu.id=:csr_id AND COALESCE(cu.is_active,1)=1
        LEFT JOIN csr_users s ON s.id=a.sender_id AND a.sender_type<>'admin'
        LEFT JOIN ushs_announcement_reads r ON r.announcement_id=a.id AND r.csr_id=cu.id
        WHERE a.is_active=1 AND r.id IS NULL $where
        ORDER BY a.created_at DESC, a.id DESC
        LIMIT $limit";
    $stmt = $db->prepare($sql);
    $stmt->execute([':csr_id'=>$csrId]);
    return ['unread_count'=>$count, 'announcements'=>$stmt->fetchAll(PDO::FETCH_ASSOC)];
}

function annGetInboxForUser(PDO $db, int $csrId, int $limit = 100): array {
    ensureUnifiedAnnouncementSystem($db);
    $limit = max(1, min(300, $limit));
    $where = annVisibleWhereSql();
    $sql = "SELECT DISTINCT a.id, a.title, a.message, a.priority, a.created_at, a.sender_type,
            CASE WHEN a.sender_type='admin' THEN 'Admin'
                 ELSE TRIM(CONCAT(COALESCE(s.first_name,''),' ',COALESCE(s.last_name,''))) END AS created_by_name,
            r.read_at, IF(r.id IS NULL,0,1) AS is_read
        FROM ushs_announcements a
        INNER JOIN csr_users cu ON cu.id=:csr_id AND COALESCE(cu.is_active,1)=1
        LEFT JOIN csr_users s ON s.id=a.sender_id AND a.sender_type<>'admin'
        LEFT JOIN ushs_announcement_reads r ON r.announcement_id=a.id AND r.csr_id=cu.id
        WHERE a.is_active=1 $where
        ORDER BY a.created_at DESC, a.id DESC
        LIMIT $limit";
    $stmt = $db->prepare($sql);
    $stmt->execute([':csr_id'=>$csrId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function annMarkRead(PDO $db, int $annId, int $csrId): void {
    ensureUnifiedAnnouncementSystem($db);
    if (!annCanReceive($db, $annId, $csrId)) throw new Exception('Announcement is not available for this user.');
    $stmt = $db->prepare("INSERT INTO ushs_announcement_reads (announcement_id,csr_id,read_at) VALUES (:a,:c,NOW()) ON DUPLICATE KEY UPDATE read_at=NOW()");
    $stmt->execute([':a'=>$annId, ':c'=>$csrId]);
}

function annToggle(PDO $db, int $annId, string $senderType, int $senderId): void {
    $stmt = $db->prepare("UPDATE ushs_announcements SET is_active=IF(is_active=1,0,1) WHERE id=:id AND sender_type=:st AND sender_id=:sid");
    $stmt->execute([':id'=>$annId, ':st'=>$senderType, ':sid'=>$senderId]);
}

function annDelete(PDO $db, int $annId, string $senderType, int $senderId): void {
    $db->beginTransaction();
    try {
        $stmt = $db->prepare("SELECT id FROM ushs_announcements WHERE id=:id AND sender_type=:st AND sender_id=:sid LIMIT 1");
        $stmt->execute([':id'=>$annId, ':st'=>$senderType, ':sid'=>$senderId]);
        if (!$stmt->fetchColumn()) throw new Exception('Announcement not found.');
        $db->prepare("DELETE FROM ushs_announcement_reads WHERE announcement_id=:id")->execute([':id'=>$annId]);
        $db->prepare("DELETE FROM ushs_announcement_targets WHERE announcement_id=:id")->execute([':id'=>$annId]);
        $db->prepare("DELETE FROM ushs_announcements WHERE id=:id")->execute([':id'=>$annId]);
        $db->commit();
    } catch (Throwable $e) {
        if ($db->inTransaction()) $db->rollBack();
        throw $e;
    }
}

function annListSent(PDO $db, string $senderType, int $senderId, int $limit = 150): array {
    ensureUnifiedAnnouncementSystem($db);
    $stmt = $db->prepare("SELECT * FROM ushs_announcements WHERE sender_type=:st AND sender_id=:sid ORDER BY created_at DESC, id DESC LIMIT " . max(1, min(300, $limit)));
    $stmt->execute([':st'=>$senderType, ':sid'=>$senderId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
