<?php

if (!function_exists('app_config')) {
    function app_config(): array
    {
        static $config = null;

        if ($config !== null) {
            return $config;
        }

        $configFile = dirname(__DIR__) . '/config.php';

        if (!file_exists($configFile)) {
            throw new RuntimeException('config.php bulunamadı.');
        }

        $config = require $configFile;

        if (!is_array($config)) {
            throw new RuntimeException('config.php geçerli bir dizi döndürmüyor.');
        }

        return $config;
    }
}

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!function_exists('db')) {
    function db(): PDO
    {
        static $pdo = null;

        if ($pdo !== null) {
            return $pdo;
        }

        $cfg = app_config();
        $dbPath = $cfg['database']['path'] ?? (dirname(__DIR__) . '/storage/database.sqlite');
        $dbDir = dirname($dbPath);

        if (!is_dir($dbDir)) {
            if (!mkdir($dbDir, 0775, true) && !is_dir($dbDir)) {
                throw new RuntimeException('storage klasörü oluşturulamadı: ' . $dbDir);
            }
        }

        if (!is_writable($dbDir)) {
            throw new RuntimeException('storage klasörü yazılabilir değil: ' . $dbDir);
        }

        $pdo = new PDO('sqlite:' . $dbPath);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

        initDatabase($pdo);

        return $pdo;
    }
}

if (!function_exists('initDatabase')) {
    function initDatabase(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS campaigns (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                subject TEXT NOT NULL,
                html_body TEXT NOT NULL,
                plain_body TEXT,
                status TEXT NOT NULL DEFAULT 'queued',
                total_count INTEGER NOT NULL DEFAULT 0,
                success_count INTEGER NOT NULL DEFAULT 0,
                failed_count INTEGER NOT NULL DEFAULT 0,
                opened_count INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL,
                started_at TEXT DEFAULT NULL,
                completed_at TEXT DEFAULT NULL
            )
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS recipients (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT NOT NULL UNIQUE,
                unsubscribed INTEGER NOT NULL DEFAULT 0,
                unsubscribe_token TEXT NOT NULL,
                created_at TEXT NOT NULL
            )
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS sends (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                campaign_id INTEGER NOT NULL,
                recipient_id INTEGER NOT NULL,
                email TEXT NOT NULL,
                tracking_token TEXT NOT NULL UNIQUE,
                status TEXT NOT NULL DEFAULT 'pending',
                error_message TEXT DEFAULT NULL,
                opened_at TEXT DEFAULT NULL,
                sent_at TEXT DEFAULT NULL,
                created_at TEXT NOT NULL,
                FOREIGN KEY (campaign_id) REFERENCES campaigns(id),
                FOREIGN KEY (recipient_id) REFERENCES recipients(id)
            )
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS attachments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                campaign_id INTEGER NOT NULL,
                original_name TEXT NOT NULL,
                stored_name TEXT NOT NULL,
                file_path TEXT NOT NULL,
                mime_type TEXT DEFAULT NULL,
                file_size INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL,
                FOREIGN KEY (campaign_id) REFERENCES campaigns(id)
            )
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS customer_groups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                created_at TEXT NOT NULL
            )
        ");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS customer_group_emails (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                group_id INTEGER NOT NULL,
                email TEXT NOT NULL,
                created_at TEXT NOT NULL,
                FOREIGN KEY (group_id) REFERENCES customer_groups(id)
            )
        ");

        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_sends_campaign_id ON sends(campaign_id)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_sends_recipient_id ON sends(recipient_id)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_sends_tracking_token ON sends(tracking_token)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_attachments_campaign_id ON attachments(campaign_id)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_customer_group_emails_group_id ON customer_group_emails(group_id)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_customer_group_emails_email ON customer_group_emails(email)");

        try {
            $columns = [];
            $result = $pdo->query("PRAGMA table_info(campaigns)");
            foreach ($result->fetchAll() as $col) {
                $columns[] = $col['name'];
            }

            if (!in_array('status', $columns, true)) {
                $pdo->exec("ALTER TABLE campaigns ADD COLUMN status TEXT NOT NULL DEFAULT 'queued'");
            }

            if (!in_array('started_at', $columns, true)) {
                $pdo->exec("ALTER TABLE campaigns ADD COLUMN started_at TEXT DEFAULT NULL");
            }

            if (!in_array('completed_at', $columns, true)) {
                $pdo->exec("ALTER TABLE campaigns ADD COLUMN completed_at TEXT DEFAULT NULL");
            }
        } catch (\Throwable $e) {
        }
    }
}

if (!function_exists('flash')) {
    function flash(string $message, string $type = 'success'): void
    {
        $_SESSION['flash'] = [
            'message' => $message,
            'type' => $type
        ];
    }
}

if (!function_exists('getFlash')) {
    function getFlash(): ?array
    {
        if (!isset($_SESSION['flash'])) {
            return null;
        }

        $flash = $_SESSION['flash'];
        unset($_SESSION['flash']);

        return $flash;
    }
}

if (!function_exists('redirect')) {
    function redirect(string $path): void
    {
        header('Location: ' . $path);
        exit;
    }
}

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

if (!function_exists('base_url')) {
    function base_url(): string
    {
        $cfg = app_config();
        return rtrim($cfg['app']['base_url'] ?? '', '/');
    }
}

if (!function_exists('randomToken')) {
    function randomToken(int $bytes = 16): string
    {
        return bin2hex(random_bytes($bytes));
    }
}

if (!function_exists('parseRecipients')) {
    function parseRecipients(string $input): array
    {
        $emails = preg_split('/[\s,;\n\r\t]+/', $input) ?: [];
        $valid = [];

        foreach ($emails as $email) {
            $email = trim(mb_strtolower($email));
            if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $valid[] = $email;
            }
        }

        return array_values(array_unique($valid));
    }
}

if (!function_exists('max_recipient_count')) {
    function max_recipient_count(): int
    {
        return 100;
    }
}

if (!function_exists('max_total_attachment_size_bytes')) {
    function max_total_attachment_size_bytes(): int
    {
        return 10 * 1024 * 1024;
    }
}

if (!function_exists('validateRecipientCount')) {
    function validateRecipientCount(array $recipients): void
    {
        $count = count(array_values(array_unique($recipients)));

        if ($count > max_recipient_count()) {
            throw new RuntimeException(
                'En fazla ' . max_recipient_count() . ' alıcı yükleyebilirsin. Şu anki sayı: ' . $count
            );
        }
    }
}

if (!function_exists('validateAttachmentsTotalSize')) {
    function validateAttachmentsTotalSize(array $files): void
    {
        if (!isset($files['size']) || !is_array($files['size']) || count($files['size']) === 0) {
            return;
        }

        $total = 0;

        foreach ($files['size'] as $index => $size) {
            $error = $files['error'][$index] ?? UPLOAD_ERR_NO_FILE;

            if ($error === UPLOAD_ERR_NO_FILE) {
                continue;
            }

            if ($error !== UPLOAD_ERR_OK) {
                throw new RuntimeException('Dosya yükleme hatası oluştu.');
            }

            $total += (int)$size;
        }

        if ($total > max_total_attachment_size_bytes()) {
            $mb = round($total / 1024 / 1024, 2);
            throw new RuntimeException(
                'Ek dosyaların toplam boyutu en fazla 10 MB olabilir. Şu anki toplam: ' . $mb . ' MB'
            );
        }
    }
}

if (!function_exists('createCampaign')) {
    function createCampaign(PDO $pdo, string $subject, string $html, string $plain, int $totalCount): int
    {
        $stmt = $pdo->prepare("
            INSERT INTO campaigns (subject, html_body, plain_body, total_count, status, created_at)
            VALUES (:subject, :html_body, :plain_body, :total_count, 'queued', :created_at)
        ");

        $stmt->execute([
            ':subject' => $subject,
            ':html_body' => $html,
            ':plain_body' => $plain,
            ':total_count' => $totalCount,
            ':created_at' => date('Y-m-d H:i:s')
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('setCampaignStatus')) {
    function setCampaignStatus(PDO $pdo, int $campaignId, string $status): void
    {
        $fields = ['status' => $status, 'id' => $campaignId];
        $sql = "UPDATE campaigns SET status = :status";

        if ($status === 'sending') {
            $sql .= ", started_at = COALESCE(started_at, :started_at)";
            $fields['started_at'] = date('Y-m-d H:i:s');
        }

        if ($status === 'completed') {
            $sql .= ", completed_at = :completed_at";
            $fields['completed_at'] = date('Y-m-d H:i:s');
        }

        $sql .= " WHERE id = :id";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($fields);
    }
}

if (!function_exists('findOrCreateRecipient')) {
    function findOrCreateRecipient(PDO $pdo, string $email): array
    {
        $stmt = $pdo->prepare("SELECT * FROM recipients WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $email]);
        $recipient = $stmt->fetch();

        if ($recipient) {
            return $recipient;
        }

        $token = randomToken(16);

        $stmt = $pdo->prepare("
            INSERT INTO recipients (email, unsubscribed, unsubscribe_token, created_at)
            VALUES (:email, 0, :token, :created_at)
        ");

        $stmt->execute([
            ':email' => $email,
            ':token' => $token,
            ':created_at' => date('Y-m-d H:i:s')
        ]);

        $id = (int)$pdo->lastInsertId();

        $stmt = $pdo->prepare("SELECT * FROM recipients WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $id]);

        return $stmt->fetch();
    }
}

if (!function_exists('getRecipientById')) {
    function getRecipientById(PDO $pdo, int $recipientId): ?array
    {
        $stmt = $pdo->prepare("SELECT * FROM recipients WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $recipientId]);
        $row = $stmt->fetch();

        return $row ?: null;
    }
}

if (!function_exists('createSendRecord')) {
    function createSendRecord(PDO $pdo, int $campaignId, int $recipientId, string $email): array
    {
        $token = randomToken(16);

        $stmt = $pdo->prepare("
            INSERT INTO sends (campaign_id, recipient_id, email, tracking_token, status, created_at)
            VALUES (:campaign_id, :recipient_id, :email, :tracking_token, 'pending', :created_at)
        ");

        $stmt->execute([
            ':campaign_id' => $campaignId,
            ':recipient_id' => $recipientId,
            ':email' => $email,
            ':tracking_token' => $token,
            ':created_at' => date('Y-m-d H:i:s')
        ]);

        return [
            'id' => (int)$pdo->lastInsertId(),
            'token' => $token
        ];
    }
}

if (!function_exists('getQueuedCampaignIds')) {
    function getQueuedCampaignIds(PDO $pdo, int $limit = 3): array
    {
        $stmt = $pdo->prepare("
            SELECT id
            FROM campaigns
            WHERE status IN ('queued', 'sending')
            ORDER BY id ASC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return array_map('intval', array_column($stmt->fetchAll(), 'id'));
    }
}

if (!function_exists('getPendingSendsByCampaign')) {
    function getPendingSendsByCampaign(PDO $pdo, int $campaignId, int $limit = 5): array
    {
        $stmt = $pdo->prepare("
            SELECT s.*
            FROM sends s
            INNER JOIN recipients r ON r.id = s.recipient_id
            WHERE s.campaign_id = :campaign_id
              AND s.status = 'pending'
              AND r.unsubscribed = 0
            ORDER BY s.id ASC
            LIMIT :limit
        ");
        $stmt->bindValue(':campaign_id', $campaignId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }
}

if (!function_exists('countPendingSendsByCampaign')) {
    function countPendingSendsByCampaign(PDO $pdo, int $campaignId): int
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM sends s
            INNER JOIN recipients r ON r.id = s.recipient_id
            WHERE s.campaign_id = :campaign_id
              AND s.status = 'pending'
              AND r.unsubscribed = 0
        ");
        $stmt->execute([':campaign_id' => $campaignId]);

        return (int)$stmt->fetchColumn();
    }
}

if (!function_exists('getUnsubscribeTokenByEmail')) {
    function getUnsubscribeTokenByEmail(string $email): ?string
    {
        $stmt = db()->prepare("SELECT unsubscribe_token FROM recipients WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => mb_strtolower(trim($email))]);
        $row = $stmt->fetch();

        return $row['unsubscribe_token'] ?? null;
    }
}

if (!function_exists('buildTrackedHtml')) {
    function buildTrackedHtml(string $html, string $trackingToken, string $email = ''): string
    {
        $baseUrl = base_url();
        $trackingUrl = $baseUrl . '/open.php?token=' . urlencode($trackingToken);

        $unsubscribeBlock = '';

        if ($email !== '') {
            $unsubscribeToken = getUnsubscribeTokenByEmail($email);

            if ($unsubscribeToken) {
                $unsubscribeUrl = $baseUrl . '/unsubscribe.php?token=' . urlencode($unsubscribeToken);

                $unsubscribeBlock = '
                    <div style="margin-top:24px;font-size:12px;color:#777;line-height:1.5;">
                        Bu e-postaları almak istemiyorsanız
                        <a href="' . e($unsubscribeUrl) . '" target="_blank" style="color:#777;text-decoration:underline;">abonelikten çıkın</a>.
                    </div>
                ';
            }
        }

        $pixel = '<img src="' . e($trackingUrl) . '" width="1" height="1" alt="" style="display:block;border:0;opacity:0;visibility:hidden;" />';

        if (stripos($html, '</body>') !== false) {
            return str_ireplace('</body>', $unsubscribeBlock . $pixel . '</body>', $html);
        }

        return $html . $unsubscribeBlock . $pixel;
    }
}

if (!function_exists('updateSendStatus')) {
    function updateSendStatus(PDO $pdo, int $sendId, bool $success, ?string $errorMessage = null): void
    {
        $stmt = $pdo->prepare("
            UPDATE sends
            SET status = :status,
                error_message = :error_message,
                sent_at = :sent_at
            WHERE id = :id
        ");

        $stmt->execute([
            ':status' => $success ? 'success' : 'failed',
            ':error_message' => $success ? null : mb_substr((string)$errorMessage, 0, 2000),
            ':sent_at' => date('Y-m-d H:i:s'),
            ':id' => $sendId
        ]);
    }
}

if (!function_exists('markOpenedByToken')) {
    function markOpenedByToken(PDO $pdo, string $token): void
    {
        $stmt = $pdo->prepare("
            UPDATE sends
            SET opened_at = COALESCE(opened_at, :opened_at)
            WHERE tracking_token = :token
        ");

        $stmt->execute([
            ':opened_at' => date('Y-m-d H:i:s'),
            ':token' => $token
        ]);

        $stmt = $pdo->prepare("
            SELECT campaign_id
            FROM sends
            WHERE tracking_token = :token
            LIMIT 1
        ");
        $stmt->execute([':token' => $token]);
        $row = $stmt->fetch();

        if ($row && isset($row['campaign_id'])) {
            finalizeCampaignStats($pdo, (int)$row['campaign_id']);
        }
    }
}

if (!function_exists('unsubscribeByToken')) {
    function unsubscribeByToken(PDO $pdo, string $token): bool
    {
        $stmt = $pdo->prepare("
            UPDATE recipients
            SET unsubscribed = 1
            WHERE unsubscribe_token = :token
        ");

        $stmt->execute([':token' => $token]);

        return $stmt->rowCount() > 0;
    }
}

if (!function_exists('finalizeCampaignStats')) {
    function finalizeCampaignStats(PDO $pdo, int $campaignId): void
    {
        $stmt = $pdo->prepare("
            SELECT
                COUNT(*) AS total_count,
                SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_count,
                COUNT(DISTINCT CASE WHEN opened_at IS NOT NULL THEN recipient_id END) AS opened_count
            FROM sends
            WHERE campaign_id = :campaign_id
        ");

        $stmt->execute([':campaign_id' => $campaignId]);
        $stats = $stmt->fetch();

        $stmt = $pdo->prepare("
            UPDATE campaigns
            SET total_count = :total_count,
                success_count = :success_count,
                failed_count = :failed_count,
                opened_count = :opened_count
            WHERE id = :id
        ");

        $stmt->execute([
            ':total_count' => (int)($stats['total_count'] ?? 0),
            ':success_count' => (int)($stats['success_count'] ?? 0),
            ':failed_count' => (int)($stats['failed_count'] ?? 0),
            ':opened_count' => (int)($stats['opened_count'] ?? 0),
            ':id' => $campaignId
        ]);
    }
}

if (!function_exists('attachments_dir')) {
    function attachments_dir(): string
    {
        return dirname(__DIR__) . '/storage/attachments';
    }
}

if (!function_exists('ensure_attachments_dir')) {
    function ensure_attachments_dir(): string
    {
        $dir = attachments_dir();

        if (!is_dir($dir)) {
            if (!mkdir($dir, 0775, true) && !is_dir($dir)) {
                throw new RuntimeException('Attachment klasörü oluşturulamadı: ' . $dir);
            }
        }

        if (!is_writable($dir)) {
            throw new RuntimeException('Attachment klasörü yazılabilir değil: ' . $dir);
        }

        return $dir;
    }
}

if (!function_exists('allowed_attachment_extensions')) {
    function allowed_attachment_extensions(): array
    {
        return [
            'jpg', 'jpeg', 'png', 'gif', 'webp',
            'pdf',
            'doc', 'docx',
            'xls', 'xlsx',
            'csv'
        ];
    }
}

if (!function_exists('saveUploadedAttachments')) {
    function saveUploadedAttachments(PDO $pdo, int $campaignId, array $files): array
    {
        $saved = [];

        if (!isset($files['name']) || !is_array($files['name']) || count($files['name']) === 0) {
            return $saved;
        }

        $dir = ensure_attachments_dir();
        $allowedExtensions = allowed_attachment_extensions();

        foreach ($files['name'] as $index => $originalName) {
            $error = $files['error'][$index] ?? UPLOAD_ERR_NO_FILE;

            if ($error === UPLOAD_ERR_NO_FILE) {
                continue;
            }

            if ($error !== UPLOAD_ERR_OK) {
                throw new RuntimeException('Dosya yükleme hatası: ' . $originalName);
            }

            $tmpName = $files['tmp_name'][$index] ?? '';
            $size = (int)($files['size'][$index] ?? 0);
            $mime = $files['type'][$index] ?? 'application/octet-stream';

            if (!$tmpName || !is_uploaded_file($tmpName)) {
                throw new RuntimeException('Geçersiz yükleme: ' . $originalName);
            }

            $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));

            if (!in_array($extension, $allowedExtensions, true)) {
                throw new RuntimeException('İzin verilmeyen dosya türü: ' . $originalName);
            }

            $safeStoredName = date('YmdHis') . '_' . bin2hex(random_bytes(8)) . '.' . $extension;
            $destination = $dir . '/' . $safeStoredName;

            if (!move_uploaded_file($tmpName, $destination)) {
                throw new RuntimeException('Dosya taşınamadı: ' . $originalName);
            }

            $stmt = $pdo->prepare("
                INSERT INTO attachments (
                    campaign_id,
                    original_name,
                    stored_name,
                    file_path,
                    mime_type,
                    file_size,
                    created_at
                ) VALUES (
                    :campaign_id,
                    :original_name,
                    :stored_name,
                    :file_path,
                    :mime_type,
                    :file_size,
                    :created_at
                )
            ");

            $stmt->execute([
                ':campaign_id' => $campaignId,
                ':original_name' => $originalName,
                ':stored_name' => $safeStoredName,
                ':file_path' => $destination,
                ':mime_type' => $mime,
                ':file_size' => $size,
                ':created_at' => date('Y-m-d H:i:s'),
            ]);

            $saved[] = [
                'original_name' => $originalName,
                'stored_name' => $safeStoredName,
                'file_path' => $destination,
                'mime_type' => $mime,
                'file_size' => $size,
            ];
        }

        return $saved;
    }
}

if (!function_exists('getCampaignAttachments')) {
    function getCampaignAttachments(PDO $pdo, int $campaignId): array
    {
        $stmt = $pdo->prepare("
            SELECT *
            FROM attachments
            WHERE campaign_id = :campaign_id
            ORDER BY id ASC
        ");
        $stmt->execute([':campaign_id' => $campaignId]);

        return $stmt->fetchAll();
    }
}

if (!function_exists('allowed_recipient_file_extensions')) {
    function allowed_recipient_file_extensions(): array
    {
        return ['xlsx', 'csv'];
    }
}

if (!function_exists('detectEmailColumnIndex')) {
    function detectEmailColumnIndex(array $headerRow): ?int
    {
        $possibleNames = [
            'email',
            'e-mail',
            'mail',
            'eposta',
            'e posta',
            'email address',
            'mail address'
        ];

        foreach ($headerRow as $index => $value) {
            $normalized = mb_strtolower(trim((string)$value));
            $normalized = preg_replace('/\s+/', ' ', $normalized);

            if (in_array($normalized, $possibleNames, true)) {
                return (int)$index;
            }
        }

        return null;
    }
}

if (!function_exists('excelColumnToIndex')) {
    function excelColumnToIndex(string $letters): int
    {
        $letters = strtoupper(trim($letters));
        $index = 0;

        for ($i = 0; $i < strlen($letters); $i++) {
            $index = ($index * 26) + (ord($letters[$i]) - ord('A') + 1);
        }

        return $index - 1;
    }
}

if (!function_exists('extractEmailsFromCsvFile')) {
    function extractEmailsFromCsvFile(string $filePath): array
    {
        $emails = [];

        $handle = fopen($filePath, 'r');
        if (!$handle) {
            throw new RuntimeException('CSV dosyası açılamadı.');
        }

        $header = fgetcsv($handle, 0, ',');
        if ($header === false) {
            fclose($handle);
            return [];
        }

        $emailIndex = detectEmailColumnIndex($header);

        if ($emailIndex === null) {
            fclose($handle);
            throw new RuntimeException('CSV içinde e-posta sütunu bulunamadı.');
        }

        while (($row = fgetcsv($handle, 0, ',')) !== false) {
            $value = trim((string)($row[$emailIndex] ?? ''));

            if ($value !== '' && filter_var($value, FILTER_VALIDATE_EMAIL)) {
                $emails[] = mb_strtolower($value);
            }
        }

        fclose($handle);

        return array_values(array_unique($emails));
    }
}

if (!function_exists('extractEmailsFromXlsxFile')) {
    function extractEmailsFromXlsxFile(string $filePath): array
    {
        if (!class_exists('ZipArchive')) {
            throw new RuntimeException('Sunucuda ZipArchive aktif değil. XLSX okumak için gereklidir.');
        }

        $zip = new ZipArchive();
        if ($zip->open($filePath) !== true) {
            throw new RuntimeException('XLSX dosyası açılamadı.');
        }

        $sharedStrings = [];
        $emails = [];

        $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
        if ($sharedXml !== false) {
            $xml = @simplexml_load_string($sharedXml);
            if ($xml) {
                foreach ($xml->si as $si) {
                    $text = '';

                    if (isset($si->t)) {
                        $text = (string)$si->t;
                    } else {
                        foreach ($si->r as $run) {
                            $text .= (string)$run->t;
                        }
                    }

                    $sharedStrings[] = trim($text);
                }
            }
        }

        $workbookXml = $zip->getFromName('xl/workbook.xml');
        if ($workbookXml === false) {
            $zip->close();
            throw new RuntimeException('XLSX workbook.xml bulunamadı.');
        }

        $workbook = @simplexml_load_string($workbookXml);
        if (!$workbook) {
            $zip->close();
            throw new RuntimeException('XLSX workbook.xml okunamadı.');
        }

        $workbookRelsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
        $relationships = [];

        if ($workbookRelsXml !== false) {
            $rels = @simplexml_load_string($workbookRelsXml);
            if ($rels) {
                foreach ($rels->Relationship as $rel) {
                    $id = (string)$rel['Id'];
                    $target = (string)$rel['Target'];
                    $relationships[$id] = 'xl/' . ltrim($target, '/');
                }
            }
        }

        $sheetPaths = [];

        foreach ($workbook->sheets->sheet as $sheet) {
            $attributes = $sheet->attributes('r', true);
            $rid = (string)($attributes['id'] ?? '');

            if ($rid !== '' && isset($relationships[$rid])) {
                $sheetPaths[] = $relationships[$rid];
            }
        }

        foreach ($sheetPaths as $sheetPath) {
            $sheetXml = $zip->getFromName($sheetPath);
            if ($sheetXml === false) {
                continue;
            }

            $sheet = @simplexml_load_string($sheetXml);
            if (!$sheet || !isset($sheet->sheetData)) {
                continue;
            }

            $rows = [];

            foreach ($sheet->sheetData->row as $row) {
                $rowData = [];

                foreach ($row->c as $cell) {
                    $cellRef = (string)($cell['r'] ?? '');
                    $columnLetters = preg_replace('/\d+/', '', $cellRef);
                    $colIndex = excelColumnToIndex($columnLetters);

                    $type = (string)($cell['t'] ?? '');
                    $value = '';

                    if ($type === 's') {
                        $index = (int)($cell->v ?? -1);
                        if ($index >= 0 && isset($sharedStrings[$index])) {
                            $value = $sharedStrings[$index];
                        }
                    } else {
                        $value = trim((string)($cell->v ?? ''));
                    }

                    $rowData[$colIndex] = trim((string)$value);
                }

                if (!empty($rowData)) {
                    ksort($rowData);
                    $rows[] = $rowData;
                }
            }

            if (empty($rows)) {
                continue;
            }

            $headerRow = $rows[0];
            $emailIndex = detectEmailColumnIndex($headerRow);

            if ($emailIndex === null) {
                continue;
            }

            for ($i = 1; $i < count($rows); $i++) {
                $value = trim((string)($rows[$i][$emailIndex] ?? ''));

                if ($value !== '' && filter_var($value, FILTER_VALIDATE_EMAIL)) {
                    $emails[] = mb_strtolower($value);
                }
            }

            break;
        }

        $zip->close();

        return array_values(array_unique($emails));
    }
}

if (!function_exists('extractEmailsFromUploadedRecipientFile')) {
    function extractEmailsFromUploadedRecipientFile(array $file): array
    {
        $error = $file['error'] ?? UPLOAD_ERR_NO_FILE;

        if ($error === UPLOAD_ERR_NO_FILE) {
            return [];
        }

        if ($error !== UPLOAD_ERR_OK) {
            throw new RuntimeException('Alıcı dosyası yükleme hatası oluştu.');
        }

        $tmpName = $file['tmp_name'] ?? '';
        $originalName = $file['name'] ?? '';

        if ($tmpName === '' || !is_uploaded_file($tmpName)) {
            throw new RuntimeException('Geçersiz alıcı dosyası.');
        }

        $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));

        if (!in_array($extension, allowed_recipient_file_extensions(), true)) {
            throw new RuntimeException('Sadece .xlsx ve .csv alıcı dosyaları desteklenir.');
        }

        if ($extension === 'csv') {
            return extractEmailsFromCsvFile($tmpName);
        }

        if ($extension === 'xlsx') {
            return extractEmailsFromXlsxFile($tmpName);
        }

        return [];
    }
}

if (!function_exists('createCustomerGroup')) {
    function createCustomerGroup(PDO $pdo, string $name): int
    {
        $stmt = $pdo->prepare("
            INSERT INTO customer_groups (name, created_at)
            VALUES (:name, :created_at)
        ");

        $stmt->execute([
            ':name' => trim($name),
            ':created_at' => date('Y-m-d H:i:s')
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('getCustomerGroups')) {
    function getCustomerGroups(PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT
                g.*,
                COUNT(e.id) AS email_count
            FROM customer_groups g
            LEFT JOIN customer_group_emails e ON e.group_id = g.id
            GROUP BY g.id
            ORDER BY g.id DESC
        ");

        return $stmt->fetchAll();
    }
}

if (!function_exists('getCustomerGroupById')) {
    function getCustomerGroupById(PDO $pdo, int $id): ?array
    {
        $stmt = $pdo->prepare("SELECT * FROM customer_groups WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();

        return $row ?: null;
    }
}

if (!function_exists('getCustomerGroupEmails')) {
    function getCustomerGroupEmails(PDO $pdo, int $groupId): array
    {
        $stmt = $pdo->prepare("
            SELECT email
            FROM customer_group_emails
            WHERE group_id = :group_id
            ORDER BY id ASC
        ");
        $stmt->execute([':group_id' => $groupId]);

        return array_values(array_unique(array_column($stmt->fetchAll(), 'email')));
    }
}

if (!function_exists('replaceCustomerGroupEmails')) {
    function replaceCustomerGroupEmails(PDO $pdo, int $groupId, array $emails): void
    {
        $emails = array_values(array_unique(array_filter(array_map('trim', $emails))));

        $stmt = $pdo->prepare("DELETE FROM customer_group_emails WHERE group_id = :group_id");
        $stmt->execute([':group_id' => $groupId]);

        $stmt = $pdo->prepare("
            INSERT INTO customer_group_emails (group_id, email, created_at)
            VALUES (:group_id, :email, :created_at)
        ");

        foreach ($emails as $email) {
            $stmt->execute([
                ':group_id' => $groupId,
                ':email' => strtolower($email),
                ':created_at' => date('Y-m-d H:i:s')
            ]);
        }
    }
}

if (!function_exists('createCustomerGroupFromEmails')) {
    function createCustomerGroupFromEmails(PDO $pdo, string $name, array $emails): int
    {
        $groupId = createCustomerGroup($pdo, $name);
        replaceCustomerGroupEmails($pdo, $groupId, $emails);
        return $groupId;
    }
}

if (!function_exists('deleteCustomerGroupById')) {
    function deleteCustomerGroupById(PDO $pdo, int $groupId): bool
    {
        $stmt = $pdo->prepare("DELETE FROM customer_group_emails WHERE group_id = :group_id");
        $stmt->execute([':group_id' => $groupId]);

        $stmt = $pdo->prepare("DELETE FROM customer_groups WHERE id = :id");
        $stmt->execute([':id' => $groupId]);

        return $stmt->rowCount() > 0;
    }
}

if (!function_exists('sendEmailNow')) {
    function sendEmailNow(string $toEmail, string $subject, string $html, string $plain, array $attachments = []): array
    {
        $cfg = app_config();

        $phpmailerBase = dirname(__DIR__) . '/lib/PHPMailer/src/';
        $phpmailerFile = $phpmailerBase . 'PHPMailer.php';
        $smtpFile = $phpmailerBase . 'SMTP.php';
        $exceptionFile = $phpmailerBase . 'Exception.php';

        if (!file_exists($phpmailerFile) || !file_exists($smtpFile) || !file_exists($exceptionFile)) {
            return [
                'success' => false,
                'error' => 'PHPMailer manuel dosyaları eksik. lib/PHPMailer/src altında PHPMailer.php, SMTP.php ve Exception.php bulunmalı.'
            ];
        }

        require_once $exceptionFile;
        require_once $phpmailerFile;
        require_once $smtpFile;

        if (!class_exists(\PHPMailer\PHPMailer\PHPMailer::class)) {
            return [
                'success' => false,
                'error' => 'PHPMailer sınıfı yüklenemedi.'
            ];
        }

        $mail = new \PHPMailer\PHPMailer\PHPMailer(true);

        try {
            $mail->isSMTP();
            $mail->Host = $cfg['mail']['smtp_host'] ?? '';
            $mail->Port = (int)($cfg['mail']['smtp_port'] ?? 587);
            $mail->SMTPAuth = true;
            $mail->Username = $cfg['mail']['smtp_username'] ?? '';
            $mail->Password = $cfg['mail']['smtp_password'] ?? '';
            $mail->Timeout = (int)($cfg['mail']['smtp_timeout'] ?? 20);

            $secure = strtolower((string)($cfg['mail']['smtp_secure'] ?? 'tls'));
            if ($secure === 'ssl') {
                $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS;
            } else {
                $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
            }

            if (!empty($cfg['mail']['smtp_debug'])) {
                $mail->SMTPDebug = (int)$cfg['mail']['smtp_debug'];
                $mail->Debugoutput = function ($str, $level) {
                    error_log("SMTP DEBUG [$level]: $str");
                };
            }

            $mail->CharSet = 'UTF-8';
            $mail->Encoding = 'base64';

            $fromEmail = $cfg['mail']['from_email'] ?? '';
            $fromName = $cfg['mail']['from_name'] ?? 'Mailer';

            if ($fromEmail === '') {
                return [
                    'success' => false,
                    'error' => 'config.php içinde from_email tanımlı değil.'
                ];
            }

            $mail->setFrom($fromEmail, $fromName);
            $mail->addAddress($toEmail);

            if (!empty($cfg['mail']['reply_to'])) {
                $mail->addReplyTo($cfg['mail']['reply_to'], $cfg['mail']['reply_to_name'] ?? '');
            }

            $mail->isHTML(true);
            $mail->Subject = $subject;
            $mail->Body = $html;
            $mail->AltBody = !empty($plain) ? $plain : trim(strip_tags($html));

            foreach ($attachments as $attachment) {
                $path = $attachment['file_path'] ?? '';
                $name = $attachment['original_name'] ?? basename((string)$path);

                if ($path && file_exists($path)) {
                    $mail->addAttachment($path, $name);
                }
            }

            $mail->send();

            return [
                'success' => true,
                'error' => null
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'error' => $mail->ErrorInfo ?: $e->getMessage()
            ];
        }
    }
}

if (!function_exists('getDashboardStats')) {
    function getDashboardStats(PDO $pdo): array
    {
        $campaigns = (int)$pdo->query("SELECT COUNT(*) FROM campaigns")->fetchColumn();
        $totalSent = (int)$pdo->query("SELECT COUNT(*) FROM sends")->fetchColumn();
        $success = (int)$pdo->query("SELECT COUNT(*) FROM sends WHERE status = 'success'")->fetchColumn();
        $failed = (int)$pdo->query("SELECT COUNT(*) FROM sends WHERE status = 'failed'")->fetchColumn();
        $opened = (int)$pdo->query("SELECT COUNT(DISTINCT recipient_id) FROM sends WHERE opened_at IS NOT NULL")->fetchColumn();

        return [
            'campaigns' => $campaigns,
            'total_sent' => $totalSent,
            'success' => $success,
            'failed' => $failed,
            'opened' => $opened
        ];
    }
}

if (!function_exists('getCampaigns')) {
    function getCampaigns(PDO $pdo, int $limit = 20): array
    {
        $stmt = $pdo->prepare("
            SELECT *
            FROM campaigns
            ORDER BY id DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }
}

if (!function_exists('getCampaignById')) {
    function getCampaignById(PDO $pdo, int $campaignId): ?array
    {
        $stmt = $pdo->prepare("SELECT * FROM campaigns WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $campaignId]);
        $row = $stmt->fetch();

        return $row ?: null;
    }
}

if (!function_exists('getCampaignSends')) {
    function getCampaignSends(PDO $pdo, int $campaignId): array
    {
        $stmt = $pdo->prepare("
            SELECT s.*, r.unsubscribed
            FROM sends s
            LEFT JOIN recipients r ON r.id = s.recipient_id
            WHERE s.campaign_id = :campaign_id
            ORDER BY s.id DESC
        ");
        $stmt->execute([':campaign_id' => $campaignId]);

        return $stmt->fetchAll();
    }
}

if (!function_exists('openedRate')) {
    function openedRate(int $opened, int $success): float
    {
        if ($success <= 0) {
            return 0;
        }

        return round(($opened / $success) * 100, 2);
    }
}

if (!function_exists('successRate')) {
    function successRate(int $success, int $total): float
    {
        if ($total <= 0) {
            return 0;
        }

        return round(($success / $total) * 100, 2);
    }
}

if (!function_exists('getAnalyticsSummary')) {
    function getAnalyticsSummary(PDO $pdo): array
    {
        $campaignCount = (int)$pdo->query("SELECT COUNT(*) FROM campaigns")->fetchColumn();
        $totalSent = (int)$pdo->query("SELECT COUNT(*) FROM sends")->fetchColumn();
        $success = (int)$pdo->query("SELECT COUNT(*) FROM sends WHERE status = 'success'")->fetchColumn();
        $failed = (int)$pdo->query("SELECT COUNT(*) FROM sends WHERE status = 'failed'")->fetchColumn();
        $opened = (int)$pdo->query("SELECT COUNT(DISTINCT recipient_id) FROM sends WHERE opened_at IS NOT NULL")->fetchColumn();

        $queued = (int)$pdo->query("SELECT COUNT(*) FROM campaigns WHERE status = 'queued'")->fetchColumn();
        $sending = (int)$pdo->query("SELECT COUNT(*) FROM campaigns WHERE status = 'sending'")->fetchColumn();
        $completed = (int)$pdo->query("SELECT COUNT(*) FROM campaigns WHERE status = 'completed'")->fetchColumn();

        return [
            'campaign_count' => $campaignCount,
            'total_sent' => $totalSent,
            'success' => $success,
            'failed' => $failed,
            'opened' => $opened,
            'queued' => $queued,
            'sending' => $sending,
            'completed' => $completed,
            'success_rate' => $totalSent > 0 ? round(($success / $totalSent) * 100, 2) : 0,
            'open_rate' => $success > 0 ? round(($opened / $success) * 100, 2) : 0,
        ];
    }
}

if (!function_exists('getCampaignPerformanceData')) {
    function getCampaignPerformanceData(PDO $pdo, int $limit = 12): array
    {
        $stmt = $pdo->prepare("
            SELECT
                id,
                subject,
                total_count,
                success_count,
                failed_count,
                opened_count,
                created_at
            FROM campaigns
            ORDER BY id DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return array_reverse($stmt->fetchAll());
    }
}

if (!function_exists('getDailySendStats')) {
    function getDailySendStats(PDO $pdo, int $days = 14): array
    {
        $stmt = $pdo->prepare("
            SELECT
                substr(created_at, 1, 10) as day,
                COUNT(*) as total,
                SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed
            FROM sends
            WHERE created_at >= datetime('now', :days)
            GROUP BY substr(created_at, 1, 10)
            ORDER BY day ASC
        ");
        $stmt->execute([
            ':days' => '-' . (int)$days . ' days'
        ]);

        return $stmt->fetchAll();
    }
}

if (!function_exists('getTopOpenCampaigns')) {
    function getTopOpenCampaigns(PDO $pdo, int $limit = 5): array
    {
        $stmt = $pdo->prepare("
            SELECT
                id,
                subject,
                success_count,
                opened_count,
                CASE
                    WHEN success_count > 0 THEN ROUND((opened_count * 100.0) / success_count, 2)
                    ELSE 0
                END as open_rate
            FROM campaigns
            WHERE success_count > 0
            ORDER BY open_rate DESC, id DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }
}

if (!function_exists('deleteCampaignById')) {
    function deleteCampaignById(PDO $pdo, int $campaignId): bool
    {
        $attachments = getCampaignAttachments($pdo, $campaignId);

        foreach ($attachments as $attachment) {
            $path = $attachment['file_path'] ?? '';
            if ($path && file_exists($path)) {
                @unlink($path);
            }
        }

        $stmt = $pdo->prepare("DELETE FROM attachments WHERE campaign_id = :campaign_id");
        $stmt->execute([':campaign_id' => $campaignId]);

        $stmt = $pdo->prepare("DELETE FROM sends WHERE campaign_id = :campaign_id");
        $stmt->execute([':campaign_id' => $campaignId]);

        $stmt = $pdo->prepare("DELETE FROM campaigns WHERE id = :id");
        $stmt->execute([':id' => $campaignId]);

        return $stmt->rowCount() > 0;
    }
}

if (!function_exists('deleteAllCampaigns')) {
    function deleteAllCampaigns(PDO $pdo): void
    {
        $stmt = $pdo->query("SELECT file_path FROM attachments");
        $files = $stmt->fetchAll();

        foreach ($files as $file) {
            $path = $file['file_path'] ?? '';
            if ($path && file_exists($path)) {
                @unlink($path);
            }
        }

        $pdo->exec("DELETE FROM attachments");
        $pdo->exec("DELETE FROM sends");
        $pdo->exec("DELETE FROM campaigns");
    }
}