<?php

// Prevent direct script access
if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME'])) {
    die('No direct script access allowed');
}

// --- Broadcast Related Queries ---

/**
 * Creates a new broadcast message record.
 */
function createBroadcast(string $message_text): int {
    try {
        $stmt = pdo()->prepare("INSERT INTO broadcasts (message_text, status, created_at) VALUES (?, 'pending', NOW())");
        $stmt->execute([$message_text]);
        return (int)pdo()->lastInsertId();
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error creating broadcast: " . $e->getMessage() . "\n", FILE_APPEND);
        return 0;
    }
}

/**
 * Adds jobs to the broadcast queue for all eligible users.
 * Returns the count of unique users targeted.
 */
function addBroadcastJobs(int $broadcast_id): int {
    try {
        $pdo = pdo();
        $pdo->beginTransaction();

        // Find active users with a valid chat ID on either platform
        $stmt_users = $pdo->query("SELECT id as user_id, telegram_chat_id, bale_chat_id 
                                      FROM users 
                                      WHERE is_active = TRUE 
                                      AND status != 'awaiting_phone' 
                                      AND (telegram_chat_id IS NOT NULL OR bale_chat_id IS NOT NULL)");
        $users = $stmt_users->fetchAll();

        if (empty($users)) {
             $pdo->rollBack();
             // Update broadcast status to sent (as there are no users)
             updateBroadcastStatus($broadcast_id, 'sent', 0);
             return 0;
        }

        $stmt_insert = $pdo->prepare("INSERT INTO broadcast_queue (broadcast_id, user_id, chat_id, platform, status, created_at) VALUES (?, ?, ?, ?, 'pending', NOW())");
        $job_count = 0;
        $user_ids_targeted = [];

        foreach ($users as $user) {
            $user_ids_targeted[$user['user_id']] = 1; // Track unique users
            if (!empty($user['telegram_chat_id'])) {
                $stmt_insert->execute([$broadcast_id, $user['user_id'], $user['telegram_chat_id'], 'telegram']);
                $job_count++;
            }
            if (!empty($user['bale_chat_id'])) {
                $stmt_insert->execute([$broadcast_id, $user['user_id'], $user['bale_chat_id'], 'bale']);
                $job_count++;
            }
        }

        $unique_user_count = count($user_ids_targeted);
        updateBroadcastStatus($broadcast_id, 'sending', $unique_user_count); // Update status and total users

        $pdo->commit();
        return $unique_user_count;
    } catch (PDOException $e) {
         if ($pdo->inTransaction()) {
            $pdo->rollBack();
         }
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error adding broadcast jobs for {$broadcast_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        // Attempt to mark broadcast as failed
        updateBroadcastStatus($broadcast_id, 'failed');
        return 0;
    }
}

/**
 * Updates the status and total user count of a broadcast.
 */
function updateBroadcastStatus(int $broadcast_id, string $status, ?int $total_users = null): bool {
     try {
        $sql = "UPDATE broadcasts SET status = ?";
        $params = [$status];
        if ($total_users !== null) {
            $sql .= ", total_users = ?";
            $params[] = $total_users;
        }
        $sql .= " WHERE id = ?";
        $params[] = $broadcast_id;

        $stmt = pdo()->prepare($sql);
        return $stmt->execute($params);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error updating broadcast status for {$broadcast_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

/**
 * Marks a broadcast as fully sent.
 */
function finalizeBroadcast(int $broadcast_id): bool {
    return updateBroadcastStatus($broadcast_id, 'sent');
}

/**
 * Fetches the next broadcast that has pending jobs.
 */
function getNextPendingBroadcast(): ?array {
     try {
        // Find a broadcast ID that is 'sending' and still has 'pending' jobs
        $stmt = pdo()->query("SELECT b.id, b.message_text
                                FROM broadcasts b
                                JOIN broadcast_queue q ON b.id = q.broadcast_id
                                WHERE b.status = 'sending' AND q.status = 'pending'
                                GROUP BY b.id
                                ORDER BY b.created_at ASC
                                LIMIT 1");
        return $stmt->fetch() ?: null;
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting next pending broadcast: " . $e->getMessage() . "\n", FILE_APPEND);
        return null;
    }
}

/**
 * Fetches a batch of pending jobs for a specific broadcast, attempting to lock them.
 */
function getPendingBroadcastJobs(int $broadcast_id, int $limit): array {
    try {
        // Use FOR UPDATE SKIP LOCKED to handle concurrent cron runs
        $stmt = pdo()->prepare("SELECT id, chat_id, platform
                                FROM broadcast_queue
                                WHERE broadcast_id = :broadcast_id AND status = 'pending'
                                ORDER BY id ASC 
                                LIMIT :limit
                                FOR UPDATE SKIP LOCKED");
        $stmt->bindValue(':broadcast_id', $broadcast_id, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting pending broadcast jobs for {$broadcast_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return [];
    }
}

/**
 * Counts remaining pending jobs for a broadcast.
 */
function countPendingJobsForBroadcast(int $broadcast_id): int {
     try {
        $stmt = pdo()->prepare("SELECT COUNT(*) FROM broadcast_queue WHERE broadcast_id = :broadcast_id AND status = 'pending'");
        $stmt->bindValue(':broadcast_id', $broadcast_id, PDO::PARAM_INT);
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error counting pending jobs for {$broadcast_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return 0; // Assume 0 on error
    }
}

/**
 * Updates the status of a specific broadcast job.
 */
function updateBroadcastJobStatus(int $job_id, string $status): bool {
     try {
        $stmt = pdo()->prepare("UPDATE broadcast_queue SET status = ?, processed_at = NOW() WHERE id = ?");
        return $stmt->execute([$status, $job_id]);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error updating broadcast job status for {$job_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

/**
 * Updates the sent/failed counts for a broadcast.
 */
function updateBroadcastStats(int $broadcast_id, int $sent_increment, int $failed_increment): bool {
     try {
        $stmt = pdo()->prepare("UPDATE broadcasts SET sent_count = sent_count + ?, failed_count = failed_count + ? WHERE id = ?");
        return $stmt->execute([$sent_increment, $failed_increment, $broadcast_id]);
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error updating broadcast stats for {$broadcast_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

?>
