<?php
require __DIR__ . '/bootstrap.php';

use App\Core\Auth;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;
use App\Services\NotificationService;

$user = Auth::requireUser();
if (!Auth::hasPermission('notifications.manage', $user) && !Auth::hasPermission('walks.manage', $user) && !Auth::hasPermission('walks.edit', $user)) {
    Response::error('You do not have permission to manage walk email content.', 403);
}

$pdo = Database::connection();
$input = request_input();
$action = (string)($input['action'] ?? 'overview');
$svc = new NotificationService();

function pyrk_walk_email_table_exists(PDO $pdo, string $table): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
        $stmt->execute([$table]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function pyrk_walk_email_column_exists(PDO $pdo, string $table, string $column): bool
{
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
        $stmt->execute([$table, $column]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Throwable) {
        return false;
    }
}

function pyrk_walk_email_clean_html(string $html): string
{
    $html = trim($html);
    if ($html === '') return '';
    $allowed = '<p><br><strong><b><em><i><u><ul><ol><li><a><h2><h3><h4><blockquote><div><span><hr>';
    $html = strip_tags($html, $allowed);
    $html = preg_replace('/\son\w+\s*=\s*(".*?"|\'.*?\'|[^\s>]+)/i', '', $html);
    $html = preg_replace('/href\s*=\s*([\'\"])\s*javascript:.*?\1/i', 'href="#"', $html);
    $html = preg_replace_callback('/<a\b([^>]*)>/i', static function ($m) {
        $attrs = $m[1] ?? '';
        if (!preg_match('/\bhref\s*=/i', $attrs)) return '<a href="#">';
        $attrs = preg_replace('/\s(on\w+|style)\s*=\s*(".*?"|\'.*?\'|[^\s>]+)/i', '', $attrs);
        if (!preg_match('/\btarget\s*=/i', $attrs)) $attrs .= ' target="_blank"';
        if (!preg_match('/\brel\s*=/i', $attrs)) $attrs .= ' rel="noopener noreferrer"';
        return '<a' . $attrs . '>';
    }, $html);
    return $html;
}

function pyrk_walk_email_walks(PDO $pdo): array
{
    if (!pyrk_walk_email_table_exists($pdo, 'walks')) {
        return [];
    }

    $hasRoutes = pyrk_walk_email_table_exists($pdo, 'routes');
    $routeNameColumn = null;
    if ($hasRoutes) {
        if (pyrk_walk_email_column_exists($pdo, 'routes', 'route_name')) {
            $routeNameColumn = 'route_name';
        } elseif (pyrk_walk_email_column_exists($pdo, 'routes', 'title')) {
            $routeNameColumn = 'title';
        } elseif (pyrk_walk_email_column_exists($pdo, 'routes', 'name')) {
            $routeNameColumn = 'name';
        }
    }

    $statusFilter = 'LOWER(COALESCE(w.status, "")) NOT IN ("deleted", "removed", "trash", "trashed", "archived")';

    try {
        if ($hasRoutes && $routeNameColumn) {
            $sql = 'SELECT w.id, w.title, w.description, w.walk_date, w.start_time, w.expected_finish_time, w.status, w.access_type, w.payment_model, w.route_id,
                    COALESCE(r.' . $routeNameColumn . ', CONCAT("Route #", w.route_id)) AS route_name
                FROM walks w
                LEFT JOIN routes r ON r.id = w.route_id
                WHERE ' . $statusFilter . '
                ORDER BY w.walk_date DESC, w.start_time DESC, w.id DESC
                LIMIT 300';
            return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        }

        $stmt = $pdo->query('SELECT w.id, w.title, w.description, w.walk_date, w.start_time, w.expected_finish_time, w.status, w.access_type, w.payment_model, w.route_id,
                CONCAT("Route #", w.route_id) AS route_name
            FROM walks w
            WHERE ' . $statusFilter . '
            ORDER BY w.walk_date DESC, w.start_time DESC, w.id DESC
            LIMIT 300');
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        try {
            $stmt = $pdo->query('SELECT id, title, walk_date, start_time, status, route_id FROM walks ORDER BY id DESC LIMIT 300');
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($rows as &$row) {
                $row['route_name'] = 'Route #' . (string)($row['route_id'] ?? '');
                $row['description'] = $row['description'] ?? '';
            }
            unset($row);
            return $rows;
        } catch (Throwable) {
            return [];
        }
    }
}

function pyrk_walk_email_template(PDO $pdo, NotificationService $svc, int $walkId, string $templateKey): array
{
    $defaults = $svc->defaultWalkEmailTemplate($templateKey);
    $row = null;
    if (pyrk_walk_email_table_exists($pdo, 'walk_email_templates')) {
        $stmt = $pdo->prepare('SELECT subject, body_html, is_enabled, updated_at FROM walk_email_templates WHERE walk_id = ? AND template_key = ? LIMIT 1');
        $stmt->execute([$walkId, $templateKey]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }
    return [
        'template_key' => $templateKey,
        'label' => (string)($defaults['label'] ?? $templateKey),
        'description' => (string)($defaults['description'] ?? ''),
        'subject' => $row ? (string)$row['subject'] : (string)$defaults['subject'],
        'body_html' => $row ? (string)$row['body_html'] : (string)$defaults['body_html'],
        'is_enabled' => $row ? (int)$row['is_enabled'] : 1,
        'is_custom' => $row ? 1 : 0,
        'updated_at' => $row['updated_at'] ?? null,
        'default_subject' => (string)$defaults['subject'],
        'default_body_html' => (string)$defaults['body_html'],
    ];
}

if ($action === 'overview') {
    Response::ok([
        'walks' => pyrk_walk_email_walks($pdo),
        'catalog' => $svc->walkEmailTemplateCatalog(),
        'variables' => $svc->walkEmailTemplateVariables(),
        'installed' => pyrk_walk_email_table_exists($pdo, 'walk_email_templates') ? 1 : 0,
    ]);
}

if ($action === 'get') {
    $walkId = (int)($input['walk_id'] ?? 0);
    $templateKey = (string)($input['template_key'] ?? 'member_invitation');
    if ($walkId <= 0) Response::error('Choose a walk first.');
    Response::ok(['template' => pyrk_walk_email_template($pdo, $svc, $walkId, $templateKey)]);
}

if ($action === 'save') {
    if (!pyrk_walk_email_table_exists($pdo, 'walk_email_templates')) {
        Response::error('Walk email template table is not installed. Run run_this_sql_walk_email_templates.sql first.');
    }
    $walkId = (int)($input['walk_id'] ?? 0);
    $templateKey = (string)($input['template_key'] ?? 'member_invitation');
    $catalog = $svc->walkEmailTemplateCatalog();
    if ($walkId <= 0) Response::error('Choose a walk first.');
    if (!isset($catalog[$templateKey])) Response::error('Unknown template type.');

    $subject = trim((string)($input['subject'] ?? ''));
    $body = pyrk_walk_email_clean_html((string)($input['body_html'] ?? ''));
    if ($subject === '') Response::error('Subject is required.');
    if (strlen($subject) > 255) Response::error('Subject is too long. Keep it below 255 characters.');
    if (trim(strip_tags($body)) === '' && strpos($body, '{walk_button}') === false) Response::error('Email body is required.');

    $stmt = $pdo->prepare('INSERT INTO walk_email_templates (walk_id, template_key, subject, body_html, is_enabled, created_by, updated_by, created_at, updated_at)
        VALUES (?, ?, ?, ?, 1, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE subject = VALUES(subject), body_html = VALUES(body_html), is_enabled = 1, updated_by = VALUES(updated_by), updated_at = NOW()');
    $stmt->execute([$walkId, $templateKey, $subject, $body, (int)$user['id'], (int)$user['id']]);

    try {
        Security::audit((int)$user['id'], 'walk_email_template_saved', 'walks', $walkId, ['template_key' => $templateKey]);
    } catch (Throwable) {}

    Response::ok(['template' => pyrk_walk_email_template($pdo, $svc, $walkId, $templateKey)], 'Email content saved.');
}

if ($action === 'reset') {
    if (!pyrk_walk_email_table_exists($pdo, 'walk_email_templates')) {
        Response::error('Walk email template table is not installed. Run run_this_sql_walk_email_templates.sql first.');
    }
    $walkId = (int)($input['walk_id'] ?? 0);
    $templateKey = (string)($input['template_key'] ?? 'member_invitation');
    if ($walkId <= 0) Response::error('Choose a walk first.');
    $stmt = $pdo->prepare('DELETE FROM walk_email_templates WHERE walk_id = ? AND template_key = ?');
    $stmt->execute([$walkId, $templateKey]);
    Response::ok(['template' => pyrk_walk_email_template($pdo, $svc, $walkId, $templateKey)], 'Default content restored.');
}

if ($action === 'preview') {
    $walkId = (int)($input['walk_id'] ?? 0);
    $templateKey = (string)($input['template_key'] ?? 'member_invitation');
    if ($walkId <= 0) Response::error('Choose a walk first.');
    $member = [
        'id' => (int)$user['id'],
        'full_name' => (string)($user['full_name'] ?? 'Sample Member'),
        'email' => (string)($user['email'] ?? 'member@example.com'),
    ];
    $subject = array_key_exists('subject', $input) ? trim((string)$input['subject']) : null;
    $body = array_key_exists('body_html', $input) ? pyrk_walk_email_clean_html((string)$input['body_html']) : null;
    $rendered = ($subject !== null || $body !== null)
        ? $svc->renderWalkEmailContentFromRaw($walkId, $templateKey, (string)($subject ?? ''), (string)($body ?? ''), $member)
        : $svc->renderWalkEmailContent($walkId, $templateKey, $member);
    Response::ok(['preview' => $rendered]);
}

if ($action === 'send_test') {
    $walkId = (int)($input['walk_id'] ?? 0);
    $templateKey = (string)($input['template_key'] ?? 'member_invitation');
    $toEmail = trim((string)($input['to_email'] ?? ($user['email'] ?? '')));
    if ($walkId <= 0) Response::error('Choose a walk first.');
    if (!filter_var($toEmail, FILTER_VALIDATE_EMAIL)) Response::error('Enter a valid test email address.');
    $member = [
        'id' => (int)$user['id'],
        'full_name' => (string)($user['full_name'] ?? 'Pyrk Admin'),
        'email' => $toEmail,
    ];
    $subject = array_key_exists('subject', $input) ? trim((string)$input['subject']) : null;
    $body = array_key_exists('body_html', $input) ? pyrk_walk_email_clean_html((string)$input['body_html']) : null;
    $rendered = ($subject !== null || $body !== null)
        ? $svc->renderWalkEmailContentFromRaw($walkId, $templateKey, (string)($subject ?? ''), (string)($body ?? ''), $member)
        : $svc->renderWalkEmailContent($walkId, $templateKey, $member);
    $result = $svc->sendEmail((int)$user['id'], $walkId, $toEmail, (string)($user['full_name'] ?? 'Pyrk Admin'), '[TEST] ' . $rendered['subject'], $rendered['html'], $rendered['text']);
    Response::ok(['result' => $result], 'Test email processed.');
}

Response::error('Unsupported walk email template action.', 404);
