<?php
/**
 * Notification run + delivery logging (DB)
 */
 
function ensureNotificationLogsTables(): void {
    global $pdo;
    if (!isset($pdo)) return;
 
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS notification_runs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            kind VARCHAR(50) NOT NULL,
            started_at DATETIME NOT NULL,
            finished_at DATETIME NULL,
            status ENUM('running','ok','error') NOT NULL DEFAULT 'running',
            summary TEXT NULL,
            error_message TEXT NULL,
            INDEX idx_notification_runs_kind_started (kind, started_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
 
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS notification_deliveries (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            run_id INT NULL,
            kind VARCHAR(50) NOT NULL,
            channel ENUM('sms','email','push') NOT NULL,
            context_key VARCHAR(100) NULL,
            recipient_member_id INT NULL,
            recipient_phone VARCHAR(40) NULL,
            recipient_email VARCHAR(255) NULL,
            recipient_name VARCHAR(255) NULL,
            provider VARCHAR(50) NULL,
            status ENUM('sent','failed') NOT NULL,
            error_message TEXT NULL,
            created_at DATETIME NOT NULL,
            INDEX idx_notification_deliveries_run_id (run_id),
            INDEX idx_notification_deliveries_kind_created (kind, created_at),
            INDEX idx_notification_deliveries_status_created (status, created_at),
            INDEX idx_notification_deliveries_member_created (recipient_member_id, created_at),
            CONSTRAINT fk_notification_deliveries_run
                FOREIGN KEY (run_id) REFERENCES notification_runs(id)
                ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
}
 
function logNotificationRunStart(string $kind): ?int {
    global $pdo;
    if (!isset($pdo)) return null;
    ensureNotificationLogsTables();
    $stmt = $pdo->prepare("INSERT INTO notification_runs (kind, started_at, status) VALUES (?, NOW(), 'running')");
    $stmt->execute([$kind]);
    return (int) $pdo->lastInsertId();
}
 
function logNotificationRunFinish(int $runId, string $status, ?string $summary = null, ?string $errorMessage = null): void {
    global $pdo;
    if (!isset($pdo) || $runId <= 0) return;
    ensureNotificationLogsTables();
    $status = in_array($status, ['ok', 'error'], true) ? $status : 'ok';
    $stmt = $pdo->prepare("UPDATE notification_runs SET finished_at = NOW(), status = ?, summary = ?, error_message = ? WHERE id = ?");
    $stmt->execute([$status, $summary, $errorMessage, $runId]);
}
 
function logNotificationDelivery(array $row): void {
    global $pdo;
    if (!isset($pdo)) return;
    ensureNotificationLogsTables();
 
    $kind = (string)($row['kind'] ?? '');
    $channel = (string)($row['channel'] ?? '');
    if ($kind === '' || !in_array($channel, ['sms', 'email', 'push'], true)) return;
 
    $stmt = $pdo->prepare("
        INSERT INTO notification_deliveries (
            run_id, kind, channel, context_key,
            recipient_member_id, recipient_phone, recipient_email, recipient_name,
            provider, status, error_message, created_at
        ) VALUES (
            ?, ?, ?, ?,
            ?, ?, ?, ?,
            ?, ?, ?, NOW()
        )
    ");
 
    $status = in_array(($row['status'] ?? ''), ['sent', 'failed'], true) ? $row['status'] : 'failed';
 
    $stmt->execute([
        isset($row['run_id']) ? (int)$row['run_id'] : null,
        $kind,
        $channel,
        $row['context_key'] ?? null,
        isset($row['recipient_member_id']) && $row['recipient_member_id'] !== null ? (int)$row['recipient_member_id'] : null,
        $row['recipient_phone'] ?? null,
        $row['recipient_email'] ?? null,
        $row['recipient_name'] ?? null,
        $row['provider'] ?? null,
        $status,
        $row['error_message'] ?? null,
    ]);
}

