<?php

declare(strict_types=1);

namespace Admin\Controllers;

use App\Core\Mail;

/**
 * AdminNewsletterController — Admin management of newsletter subscribers & emails
 *
 * Robust version with direct SQL fallbacks, error handling, and debug info.
 * Handles tables that may be missing the student_id column (older migrations).
 */
class AdminNewsletterController extends AdminController
{
    /**
     * Ensure the newsletter tables exist with all required columns.
     * Uses CREATE TABLE IF NOT EXISTS, then ALTER TABLE to add missing columns.
     */
    private function ensureTablesExist(): void
    {
        // Create subscribers table without student_id first (base schema)
        try {
            $this->db->exec("
                CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
                    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    `email` VARCHAR(255) NOT NULL,
                    `name` VARCHAR(150) DEFAULT NULL,
                    `status` ENUM('active','unsubscribed','bounced') NOT NULL DEFAULT 'active',
                    `verification_token` VARCHAR(64) DEFAULT NULL,
                    `is_verified` TINYINT(1) NOT NULL DEFAULT 1,
                    `source` VARCHAR(50) DEFAULT 'homepage',
                    `subscribed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `unsubscribed_at` TIMESTAMP NULL DEFAULT NULL,
                    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `uk_newsletter_email` (`email`),
                    INDEX `idx_newsletter_status` (`status`, `subscribed_at`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
        } catch (\Throwable $e) {
            error_log('AdminNewsletter: create subscribers table failed: ' . $e->getMessage());
        }

        // Add student_id column if missing
        $this->addColumnIfMissing('newsletter_subscribers', 'student_id', 'INT UNSIGNED DEFAULT NULL');

        // Add index on student_id if missing
        try {
            $idxCheck = $this->db->query("SHOW INDEX FROM `newsletter_subscribers` WHERE Key_name = 'idx_student_id'");
            if ($idxCheck && $idxCheck->fetch() === false) {
                $colCheck = $this->db->query("SHOW COLUMNS FROM `newsletter_subscribers` LIKE 'student_id'");
                if ($colCheck && $colCheck->fetch() !== false) {
                    $this->db->exec("ALTER TABLE `newsletter_subscribers` ADD INDEX `idx_student_id` (`student_id`)");
                }
            }
        } catch (\Throwable $e) {
            // Index may already exist — ignore
        }

        // Create emails log table
        try {
            $this->db->exec("
                CREATE TABLE IF NOT EXISTS `newsletter_emails` (
                    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    `subject` VARCHAR(300) NOT NULL,
                    `body_html` MEDIUMTEXT DEFAULT NULL,
                    `body_text` MEDIUMTEXT DEFAULT NULL,
                    `recipient_count` INT UNSIGNED NOT NULL DEFAULT 0,
                    `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
                    `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
                    `status` ENUM('draft','sending','sent','failed') NOT NULL DEFAULT 'draft',
                    `sent_at` TIMESTAMP NULL DEFAULT NULL,
                    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    INDEX `idx_newsletter_email_status` (`status`, `created_at`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
        } catch (\Throwable $e) {
            error_log('AdminNewsletter: create emails table failed: ' . $e->getMessage());
        }
    }

    /**
     * Add a column to a table if it doesn't already exist.
     */
    private function addColumnIfMissing(string $table, string $column, string $definition): void
    {
        try {
            $check = $this->db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
            if ($check && $check->fetch() === false) {
                $this->db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
            }
        } catch (\Throwable $e) {
            // Table might not exist yet — ignore
        }
    }

    /**
     * Check if a column exists in a table.
     *
     * NOTE: This overrides AdminController::columnExists() but keeps the
     * same `protected` visibility so PHP doesn't fatal with
     * "Access level ... must be protected (as in class AdminController) or weaker".
     * The body uses SHOW COLUMNS (more portable than INFORMATION_SCHEMA on
     * older MySQL versions) and is functionally equivalent to the parent.
     */
    protected function columnExists(string $table, string $column): bool
    {
        try {
            $check = $this->db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
            return $check && $check->fetch() !== false;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * List all subscribers.
     * Uses direct SQL as primary method, model as fallback.
     */
    public function index(array $params = []): void
    {
        requireAdmin();

        $this->ensureTablesExist();

        $search       = trim($_GET['search'] ?? '');
        $statusFilter = trim($_GET['status'] ?? '');

        // Check which columns exist
        $hasStudentIdCol = $this->columnExists('newsletter_subscribers', 'student_id');

        // ── Primary: Direct SQL query (most reliable) ──
        $subscribers = [];
        $counts      = ['active' => 0, 'unsubscribed' => 0, 'bounced' => 0, 'total' => 0];
        $dbError     = '';

        try {
            // Build WHERE clause
            $where  = '1=1';
            $sqlParams = [];

            if (!empty($search)) {
                $where .= ' AND (`email` LIKE :search OR `name` LIKE :search2)';
                $sqlParams[':search']  = '%' . $search . '%';
                $sqlParams[':search2'] = '%' . $search . '%';
            }

            if (!empty($statusFilter) && in_array($statusFilter, ['active', 'unsubscribed', 'bounced'], true)) {
                $where .= ' AND `status` = :status';
                $sqlParams[':status'] = $statusFilter;
            }

            // Get subscribers — SELECT * works regardless of which columns exist
            $sql = "SELECT * FROM `newsletter_subscribers` WHERE {$where} ORDER BY `subscribed_at` DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($sqlParams);
            $subscribers = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            // Get counts
            $countSql = "SELECT `status`, COUNT(*) as cnt FROM `newsletter_subscribers` GROUP BY `status`";
            $countStmt = $this->db->prepare($countSql);
            $countStmt->execute();
            foreach ($countStmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $counts[$row['status']] = (int) $row['cnt'];
                $counts['total'] += (int) $row['cnt'];
            }
        } catch (\Throwable $e) {
            $dbError = 'Database query error: ' . $e->getMessage();
            error_log('AdminNewsletter index error: ' . $e->getMessage());

            // ── Fallback: Try using the model ──
            try {
                $model = $this->model('NewsletterSubscriber');
                $subscribers = $model->getAllSubscribers($search, $statusFilter);
                $counts = $model->getCounts();
                $dbError = ''; // Model worked, clear error
            } catch (\Throwable $e2) {
                $dbError .= ' | Model fallback also failed: ' . $e2->getMessage();
                error_log('AdminNewsletter model fallback error: ' . $e2->getMessage());
            }
        }

        // Check if table actually exists and has data
        $tableExists = false;
        $rawRowCount = 0;
        try {
            $checkStmt = $this->db->query("SELECT COUNT(*) FROM `newsletter_subscribers`");
            $rawRowCount = (int) $checkStmt->fetchColumn();
            $tableExists = true;
        } catch (\Throwable $e) {
            $tableExists = false;
        }

        $this->adminView('newsletter_subscribers', [
            'title'           => 'Newsletter Subscribers',
            'currentPage'     => 'newsletter',
            'subscribers'     => $subscribers,
            'counts'          => $counts,
            'search'          => $search,
            'statusFilter'    => $statusFilter,
            'dbError'         => $dbError,
            'tableExists'     => $tableExists,
            'rawRowCount'     => $rawRowCount,
            'hasStudentIdCol' => $hasStudentIdCol,
        ]);
    }

    /**
     * Toggle subscriber status (active/unsubscribed).
     */
    public function toggleStatus(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);
        if ($id <= 0) {
            flash('error', 'Invalid subscriber ID.');
            redirect(BASE_PATH . '/admin/newsletter');
        }

        try {
            // Direct SQL approach
            $stmt = $this->db->prepare("SELECT `status` FROM `newsletter_subscribers` WHERE `id` = :id");
            $stmt->execute([':id' => $id]);
            $current = $stmt->fetchColumn();

            if ($current === 'active') {
                $sql = "UPDATE `newsletter_subscribers` SET `status` = 'unsubscribed', `unsubscribed_at` = NOW() WHERE `id` = :id";
            } else {
                $sql = "UPDATE `newsletter_subscribers` SET `status` = 'active', `unsubscribed_at` = NULL WHERE `id` = :id";
            }

            $updateStmt = $this->db->prepare($sql);
            $updateStmt->execute([':id' => $id]);

            flash('success', 'Subscriber status updated.');
        } catch (\Throwable $e) {
            flash('error', 'Failed to update subscriber status: ' . $e->getMessage());
        }

        redirect(BASE_PATH . '/admin/newsletter');
    }

    /**
     * Delete a subscriber.
     */
    public function delete(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);
        if ($id <= 0) {
            flash('error', 'Invalid subscriber ID.');
            redirect(BASE_PATH . '/admin/newsletter');
        }

        try {
            $stmt = $this->db->prepare("DELETE FROM `newsletter_subscribers` WHERE `id` = :id");
            $stmt->execute([':id' => $id]);
            flash('success', 'Subscriber deleted.');
        } catch (\Throwable $e) {
            flash('error', 'Failed to delete subscriber: ' . $e->getMessage());
        }

        redirect(BASE_PATH . '/admin/newsletter');
    }

    /**
     * Show the compose/send email form.
     */
    public function compose(array $params = []): void
    {
        requireAdmin();

        $this->ensureTablesExist();

        // Get active subscriber count via direct SQL
        $activeCount = 0;
        try {
            $stmt = $this->db->query("SELECT COUNT(*) FROM `newsletter_subscribers` WHERE `status` = 'active'");
            $activeCount = (int) $stmt->fetchColumn();
        } catch (\Throwable $e) {}

        // Check PHPMailer availability
        $mailerAvailable = false;
        $smtpConfigured  = false;
        try {
            $smtpSettings = $this->readSmtpSettingsFromDb();
            $mailHelper = new Mail();
            $mailHelper->setConfig(array_merge([
                'smtp_host' => '', 'smtp_port' => '587', 'smtp_username' => '',
                'smtp_password' => '', 'smtp_encryption' => 'tls',
                'smtp_from_email' => '', 'smtp_from_name' => 'Gmind Institute',
            ], $smtpSettings));
            $mailerAvailable = $mailHelper->isAvailable();
            $smtpConfigured  = $mailHelper->isConfigured();
        } catch (\Throwable $e) {
            error_log('AdminNewsletter compose mail check error: ' . $e->getMessage());
        }

        $this->adminView('newsletter_compose', [
            'title'           => 'Send Newsletter',
            'currentPage'     => 'newsletter',
            'activeCount'     => $activeCount,
            'mailerAvailable' => $mailerAvailable,
            'smtpConfigured'  => $smtpConfigured,
        ]);
    }

    /**
     * Send newsletter email to all active subscribers.
     */
    public function send(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $subject  = trim($_POST['subject'] ?? '');
        $bodyHtml = trim($_POST['body_html'] ?? '');
        $bodyText = trim($_POST['body_text'] ?? '') ?: null;
        $testMode = isset($_POST['test_mode']) && $_POST['test_mode'] === '1';
        $testEmail = trim($_POST['test_email'] ?? '');

        if (empty($subject) || empty($bodyHtml)) {
            flash('error', 'Subject and email body are required.');
            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        // Get unsubscribe link base
        $unsubscribeBase = (isset($_SERVER['HTTPS']) ? 'https' : 'http') . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . BASE_PATH . '/newsletter/unsubscribe';

        // Append unsubscribe link to body
        $unsubscribeHtml = '<br><br><hr style="border:none;border-top:1px solid #ddd;margin:20px 0;"><p style="font-size:12px;color:#888;">You received this email because you subscribed to Gmind Institute newsletter. <a href="' . $unsubscribeBase . '?token={{email}}" style="color:#888;">Unsubscribe</a></p>';
        $bodyHtml .= $unsubscribeHtml;

        // Inject SMTP settings from DB
        $smtpSettings = $this->readSmtpSettingsFromDb();
        $mailHelper = new Mail();
        $mailHelper->setConfig(array_merge([
            'smtp_host' => '', 'smtp_port' => '587', 'smtp_username' => '',
            'smtp_password' => '', 'smtp_encryption' => 'tls',
            'smtp_from_email' => '', 'smtp_from_name' => 'Gmind Institute',
        ], $smtpSettings));

        if (!$mailHelper->isAvailable()) {
            flash('error', 'PHPMailer library not found. Please check your PHPMailer installation.');
            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        if (!$mailHelper->isConfigured()) {
            flash('error', 'SMTP is not configured. Please configure SMTP settings first.');
            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        // Test mode — send only to test email
        if ($testMode && !empty($testEmail)) {
            $result = $mailHelper->send($testEmail, 'Test Recipient', $subject, $bodyHtml, $bodyText);

            if ($result) {
                $this->logEmail('[TEST] ' . $subject, $bodyHtml, $bodyText, 1, 1, 0, 'sent');
                flash('success', "Test email sent successfully to {$testEmail}!");
            } else {
                $errMsg = $mailHelper->getLastError();
                flash('error', 'Failed to send test email. Error: ' . htmlspecialchars($errMsg, ENT_QUOTES, 'UTF-8'));
            }

            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        // Get all active subscribers via direct SQL
        $recipients = [];
        try {
            $stmt = $this->db->query("SELECT `email`, `name` FROM `newsletter_subscribers` WHERE `status` = 'active' AND `is_verified` = 1 ORDER BY `subscribed_at` ASC");
            $recipients = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            flash('error', 'Failed to get subscribers: ' . $e->getMessage());
            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        if (empty($recipients)) {
            flash('warning', 'No active subscribers to send emails to.');
            redirect(BASE_PATH . '/admin/newsletter/compose');
        }

        // Send in batches
        $result = $mailHelper->sendBulk($recipients, $subject, $bodyHtml, $bodyText);

        // Log the email
        $this->logEmail(
            $subject,
            $bodyHtml,
            $bodyText,
            count($recipients),
            $result['sent'],
            $result['failed'],
            $result['failed'] === 0 ? 'sent' : 'partial'
        );

        if ($result['failed'] === 0) {
            flash('success', "Newsletter sent successfully to {$result['sent']} subscribers!");
        } else {
            flash('warning', "Newsletter sent to {$result['sent']} subscribers. {$result['failed']} failed.");
        }

        redirect(BASE_PATH . '/admin/newsletter/compose');
    }

    /**
     * View sent email logs.
     */
    public function logs(array $params = []): void
    {
        requireAdmin();

        $this->ensureTablesExist();

        $emailLogs = [];
        try {
            $stmt = $this->db->query("SELECT * FROM `newsletter_emails` ORDER BY `created_at` DESC LIMIT 50");
            $emailLogs = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            error_log('AdminNewsletter logs error: ' . $e->getMessage());
        }

        $this->adminView('newsletter_logs', [
            'title'      => 'Newsletter Email Logs',
            'currentPage' => 'newsletter',
            'emailLogs'  => $emailLogs,
        ]);
    }

    // =========================================================================
    // PRIVATE HELPERS
    // =========================================================================

    /**
     * Log a sent newsletter email directly via SQL.
     */
    private function logEmail(string $subject, ?string $bodyHtml, ?string $bodyText, int $recipientCount, int $sentCount, int $failedCount, string $status = 'sent'): void
    {
        try {
            $stmt = $this->db->prepare(
                "INSERT INTO `newsletter_emails` (`subject`, `body_html`, `body_text`, `recipient_count`, `sent_count`, `failed_count`, `status`, `sent_at`)
                 VALUES (:subject, :body_html, :body_text, :recipient_count, :sent_count, :failed_count, :status, NOW())"
            );
            $stmt->execute([
                ':subject'         => $subject,
                ':body_html'       => $bodyHtml,
                ':body_text'       => $bodyText,
                ':recipient_count' => $recipientCount,
                ':sent_count'      => $sentCount,
                ':failed_count'    => $failedCount,
                ':status'          => $status,
            ]);
        } catch (\Throwable $e) {
            error_log('AdminNewsletter logEmail failed: ' . $e->getMessage());
        }
    }

    /**
     * Read SMTP settings directly from database.
     *
     * Reads from BOTH column pairs (setting_key/setting_value AND key_name/value)
     * and merges them. key_name/value fills in any keys where setting_value is empty.
     * This is critical because the settings table has dual-column schema.
     */
    private function readSmtpSettingsFromDb(): array
    {
        $result = [];
        $keys = ['smtp_host', 'smtp_port', 'smtp_username', 'smtp_password', 'smtp_encryption', 'smtp_from_email', 'smtp_from_name'];

        if (!$this->db) {
            return $result;
        }

        // Method 1: Read from setting_key / setting_value
        try {
            $placeholders = implode(',', array_fill(0, count($keys), '?'));
            $stmt = $this->db->prepare(
                "SELECT `setting_key`, `setting_value` FROM `settings` WHERE `setting_key` IN ({$placeholders})"
            );
            $stmt->execute($keys);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($rows as $row) {
                $k = $row['setting_key'] ?? '';
                if (in_array($k, $keys, true)) {
                    $result[$k] = $row['setting_value'] ?? '';
                }
            }
        } catch (\Throwable $e) {}

        // Method 2: Read from key_name / value — fill any missing or empty values
        // DO NOT return early — always check BOTH column pairs
        try {
            $placeholders = implode(',', array_fill(0, count($keys), '?'));
            $stmt = $this->db->prepare(
                "SELECT `key_name`, `value` FROM `settings` WHERE `key_name` IN ({$placeholders})"
            );
            $stmt->execute($keys);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($rows as $row) {
                $k = $row['key_name'] ?? '';
                $v = $row['value'] ?? '';
                if (in_array($k, $keys, true)) {
                    // Use key_name/value only if setting_key value is missing or empty
                    if (!isset($result[$k]) || $result[$k] === '') {
                        $result[$k] = $v;
                    }
                }
            }
        } catch (\Throwable $e) {}

        return $result;
    }
}
