<?php

class CommunicationsApiController extends Controller
{
    // ═══════════════════════════════════════════════════
    //  HELPER: Build DataTable response
    // ═══════════════════════════════════════════════════

    private function datatable(Request $request, string $baseQuery, array $baseParams, array $columns, array $searchCols = []): void
    {
        $db    = $this->db();
        $draw  = (int)$request->get('draw', 1);
        $start = (int)$request->get('start', 0);
        $len   = (int)$request->get('length', 25);
        $searchVal = trim($request->get('search')['value'] ?? '');

        $orderCol = (int)($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtolower($request->get('order')[0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

        // Total
        $totalSql = "SELECT COUNT(*) FROM ({$baseQuery}) AS __t";
        $total = (int)$db->fetchColumn($totalSql, $baseParams);

        // Search
        $filterSql = $baseQuery;
        $filterParams = $baseParams;
        if ($searchVal !== '' && !empty($searchCols)) {
            $searchParts = [];
            foreach ($searchCols as $sc) {
                $searchParts[] = "{$sc} LIKE ?";
                $filterParams[] = "%{$searchVal}%";
            }
            $filterSql .= " AND (" . implode(' OR ', $searchParts) . ")";
        }

        $filteredTotal = (int)$db->fetchColumn("SELECT COUNT(*) FROM ({$filterSql}) AS __t", $filterParams);

        // Order
        $colName = $columns[$orderCol] ?? $columns[0];
        $filterSql .= " ORDER BY {$colName} {$orderDir}";

        // Limit
        $filterSql .= " LIMIT ? OFFSET ?";
        $filterParams[] = $len;
        $filterParams[] = $start;

        $rows = $db->fetchAll($filterSql, $filterParams);

        $this->json([
            'draw'            => $draw,
            'recordsTotal'    => $total,
            'recordsFiltered' => $filteredTotal,
            'data'            => $rows,
        ]);
    }

    // ═══════════════════════════════════════════════════
    //  NOTIFICATION TEMPLATES
    // ═══════════════════════════════════════════════════

    public function templatesDatatable(Request $request): void
    {
        $sql = "SELECT nt.id, nt.template_code, nt.name_en, nt.name_ar, nt.event_trigger,
                       nc.name_en AS channel_name, nt.is_active, nt.created_at
                FROM notification_templates nt
                LEFT JOIN notification_channels nc ON nt.channel_id = nc.id
                WHERE 1=1";
        $cols = ['nt.id', 'nt.template_code', 'nt.name_en', 'nc.name_en', 'nt.event_trigger', 'nt.is_active', 'nt.created_at'];
        $search = ['nt.template_code', 'nt.name_en', 'nt.name_ar', 'nt.event_trigger'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function templateStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'template_code' => 'required|string|max:50',
            'name_en'       => 'required|string|max:200',
            'name_ar'       => 'required|string|max:200',
            'channel_id'    => 'required|integer|exists:notification_channels,id',
            'event_trigger' => 'required|string|max:100',
            'subject_en'    => 'nullable|string|max:500',
            'subject_ar'    => 'nullable|string|max:500',
            'body_en'       => 'required|string',
            'body_ar'       => 'nullable|string',
            'is_active'     => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;

        // Parse variables from body
        preg_match_all('/\{(\w+)\}/', ($data['body_en'] ?? '') . ($data['body_ar'] ?? ''), $matches);
        $vars = array_unique($matches[1] ?? []);
        $data['variables'] = !empty($vars) ? json_encode(array_values($vars)) : null;

        $db = $this->db();
        $id = $db->insert('notification_templates', $data);

        $this->audit('create', 'notification_templates', $id, 'Created notification template: ' . $data['template_code']);
        $this->json(['success' => true, 'message' => lang('comm.template_created'), 'redirect' => '/communications/templates']);
    }

    public function templateUpdate(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $old = $db->fetchOne("SELECT * FROM notification_templates WHERE id = ?", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Template not found'], 404);
            return;
        }

        $v = new Validator($request->all(), [
            'template_code' => 'required|string|max:50',
            'name_en'       => 'required|string|max:200',
            'name_ar'       => 'required|string|max:200',
            'channel_id'    => 'required|integer|exists:notification_channels,id',
            'event_trigger' => 'required|string|max:100',
            'subject_en'    => 'nullable|string|max:500',
            'subject_ar'    => 'nullable|string|max:500',
            'body_en'       => 'required|string',
            'body_ar'       => 'nullable|string',
            'is_active'     => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;

        preg_match_all('/\{(\w+)\}/', ($data['body_en'] ?? '') . ($data['body_ar'] ?? ''), $matches);
        $vars = array_unique($matches[1] ?? []);
        $data['variables'] = !empty($vars) ? json_encode(array_values($vars)) : null;

        $db->update('notification_templates', $data, 'id = ?', [$id]);

        $this->audit('update', 'notification_templates', $id, 'Updated notification template', $old, $data);
        $this->json(['success' => true, 'message' => lang('comm.template_updated'), 'redirect' => '/communications/templates']);
    }

    public function templateDelete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();

        $used = (int)$db->fetchColumn("SELECT COUNT(*) FROM notification_queue WHERE template_id = ?", [$id]);
        if ($used > 0) {
            $db->update('notification_templates', ['is_active' => 0], 'id = ?', [$id]);
            $this->audit('update', 'notification_templates', $id, 'Deactivated template (in use)');
            $this->json(['success' => true, 'message' => lang('comm.template_deactivated')]);
            return;
        }

        $db->delete('notification_templates', 'id = ?', [$id]);
        $this->audit('delete', 'notification_templates', $id, 'Deleted notification template');
        $this->json(['success' => true, 'message' => lang('comm.template_deleted')]);
    }

    // ═══════════════════════════════════════════════════
    //  CHANNELS
    // ═══════════════════════════════════════════════════

    public function channelsDatatable(Request $request): void
    {
        $sql = "SELECT id, channel_code, name_en, name_ar, channel_type, provider, is_active, created_at
                FROM notification_channels WHERE 1=1";
        $cols = ['id', 'channel_code', 'name_en', 'channel_type', 'provider', 'is_active', 'created_at'];
        $search = ['channel_code', 'name_en', 'name_ar', 'channel_type', 'provider'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function channelStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'channel_code' => 'required|string|max:20',
            'name_en'      => 'required|string|max:100',
            'name_ar'      => 'required|string|max:100',
            'channel_type' => 'required|in:email,sms,push,whatsapp,in_app,webhook',
            'provider'     => 'nullable|string|max:50',
            'is_active'    => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;

        $configStr = $request->post('config', '');
        if ($configStr) {
            $decoded = json_decode($configStr, true);
            $data['config'] = $decoded !== null ? $configStr : null;
        }

        $id = $this->db()->insert('notification_channels', $data);
        $this->audit('create', 'notification_channels', $id, 'Created channel: ' . $data['channel_code']);
        $this->json(['success' => true, 'message' => lang('comm.channel_created'), 'redirect' => '/communications/channels']);
    }

    public function channelUpdate(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $old = $db->fetchOne("SELECT * FROM notification_channels WHERE id = ?", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Channel not found'], 404);
            return;
        }

        $v = new Validator($request->all(), [
            'channel_code' => 'required|string|max:20',
            'name_en'      => 'required|string|max:100',
            'name_ar'      => 'required|string|max:100',
            'channel_type' => 'required|in:email,sms,push,whatsapp,in_app,webhook',
            'provider'     => 'nullable|string|max:50',
            'is_active'    => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;

        $configStr = $request->post('config', '');
        if ($configStr) {
            $decoded = json_decode($configStr, true);
            $data['config'] = $decoded !== null ? $configStr : null;
        }

        $db->update('notification_channels', $data, 'id = ?', [$id]);
        $this->audit('update', 'notification_channels', $id, 'Updated channel', $old, $data);
        $this->json(['success' => true, 'message' => lang('comm.channel_updated'), 'redirect' => '/communications/channels']);
    }

    public function channelDelete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $this->db()->update('notification_channels', ['is_active' => 0], 'id = ?', [$id]);
        $this->audit('update', 'notification_channels', $id, 'Deactivated channel');
        $this->json(['success' => true, 'message' => lang('comm.channel_deactivated')]);
    }

    // ═══════════════════════════════════════════════════
    //  TRIGGERS
    // ═══════════════════════════════════════════════════

    public function triggersDatatable(Request $request): void
    {
        $sql = "SELECT nt.id, nt.trigger_code, nt.trigger_name_en, nt.trigger_name_ar, nt.module, nt.event_type,
                       ntpl.name_en AS template_name, nt.is_active, nt.created_at
                FROM notification_triggers nt
                LEFT JOIN notification_templates ntpl ON nt.template_id = ntpl.id
                WHERE 1=1";
        $cols = ['nt.id', 'nt.trigger_code', 'nt.trigger_name_en', 'nt.module', 'nt.event_type', 'ntpl.name_en', 'nt.is_active', 'nt.created_at'];
        $search = ['nt.trigger_code', 'nt.trigger_name_en', 'nt.trigger_name_ar', 'nt.module', 'nt.event_type'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function triggerStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'trigger_code'    => 'required|string|max:50',
            'trigger_name_en' => 'required|string|max:200',
            'trigger_name_ar' => 'required|string|max:200',
            'module'          => 'required|string|max:50',
            'event_type'      => 'required|string|max:50',
            'template_id'     => 'nullable|integer',
            'is_active'       => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;
        $data['template_id'] = $data['template_id'] ?: null;

        $condStr = $request->post('conditions', '');
        $data['conditions'] = ($condStr && json_decode($condStr) !== null) ? $condStr : null;

        $id = $this->db()->insert('notification_triggers', $data);
        $this->audit('create', 'notification_triggers', $id, 'Created trigger: ' . $data['trigger_code']);
        $this->json(['success' => true, 'message' => lang('comm.trigger_created'), 'redirect' => '/communications/triggers']);
    }

    public function triggerUpdate(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $old = $db->fetchOne("SELECT * FROM notification_triggers WHERE id = ?", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Trigger not found'], 404);
            return;
        }

        $v = new Validator($request->all(), [
            'trigger_code'    => 'required|string|max:50',
            'trigger_name_en' => 'required|string|max:200',
            'trigger_name_ar' => 'required|string|max:200',
            'module'          => 'required|string|max:50',
            'event_type'      => 'required|string|max:50',
            'template_id'     => 'nullable|integer',
            'is_active'       => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;
        $data['template_id'] = $data['template_id'] ?: null;

        $condStr = $request->post('conditions', '');
        $data['conditions'] = ($condStr && json_decode($condStr) !== null) ? $condStr : null;

        $db->update('notification_triggers', $data, 'id = ?', [$id]);
        $this->audit('update', 'notification_triggers', $id, 'Updated trigger', $old, $data);
        $this->json(['success' => true, 'message' => lang('comm.trigger_updated'), 'redirect' => '/communications/triggers']);
    }

    public function triggerDelete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $this->db()->update('notification_triggers', ['is_active' => 0], 'id = ?', [$id]);
        $this->audit('update', 'notification_triggers', $id, 'Deactivated trigger');
        $this->json(['success' => true, 'message' => lang('comm.trigger_deactivated')]);
    }

    // ═══════════════════════════════════════════════════
    //  QUEUE
    // ═══════════════════════════════════════════════════

    public function queueDatatable(Request $request): void
    {
        $status = $request->get('filter_status', '');
        $sql    = "SELECT nq.id, nq.recipient_type, nq.recipient_address, nq.subject,
                          nq.priority, nq.status, nq.scheduled_at, nq.sent_at, nq.failure_reason,
                          nq.retry_count, nq.max_retries, nq.created_at,
                          nc.name_en AS channel_name, nt.name_en AS template_name
                   FROM notification_queue nq
                   LEFT JOIN notification_channels nc ON nq.channel_id = nc.id
                   LEFT JOIN notification_templates nt ON nq.template_id = nt.id
                   WHERE 1=1";
        $params = [];

        if ($status) {
            $sql .= " AND nq.status = ?";
            $params[] = $status;
        }

        $cols = ['nq.id', 'nc.name_en', 'nq.recipient_address', 'nq.subject', 'nq.status', 'nq.priority', 'nq.created_at'];
        $search = ['nq.recipient_address', 'nq.subject', 'nc.name_en', 'nt.name_en'];

        $this->datatable($request, $sql, $params, $cols, $search);
    }

    public function queueRetry(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $item = $db->fetchOne("SELECT * FROM notification_queue WHERE id = ? AND status = 'failed'", [$id]);

        if (!$item) {
            $this->json(['success' => false, 'message' => 'Queue item not found or not in failed state'], 404);
            return;
        }

        $db->update('notification_queue', [
            'status'      => 'queued',
            'retry_count' => $item['retry_count'] + 1,
            'failure_reason' => null,
            'failed_at'   => null,
        ], 'id = ?', [$id]);

        $this->audit('update', 'notification_queue', $id, 'Retried failed notification');
        $this->json(['success' => true, 'message' => lang('comm.queue_retried')]);
    }

    public function queueCancel(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $db->update('notification_queue', ['status' => 'cancelled'], "id = ? AND status IN ('queued','failed')", [$id]);
        $this->audit('update', 'notification_queue', $id, 'Cancelled queued notification');
        $this->json(['success' => true, 'message' => lang('comm.queue_cancelled')]);
    }

    // ═══════════════════════════════════════════════════
    //  SENT
    // ═══════════════════════════════════════════════════

    public function sentDatatable(Request $request): void
    {
        $sql = "SELECT ns.id, nc.name_en AS channel_name, ns.recipient_type, ns.recipient_address,
                       ns.subject, ns.delivery_status, ns.sent_at, ns.delivered_at, ns.read_at,
                       nt.name_en AS template_name
                FROM notifications_sent ns
                LEFT JOIN notification_channels nc ON ns.channel_id = nc.id
                LEFT JOIN notification_templates nt ON ns.template_id = nt.id
                WHERE 1=1";
        $cols = ['ns.id', 'nc.name_en', 'ns.recipient_address', 'ns.subject', 'ns.delivery_status', 'ns.sent_at', 'ns.delivered_at'];
        $search = ['ns.recipient_address', 'ns.subject', 'nc.name_en', 'nt.name_en'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    // ═══════════════════════════════════════════════════
    //  BULK COMMUNICATIONS
    // ═══════════════════════════════════════════════════

    public function bulkDatatable(Request $request): void
    {
        $sql = "SELECT bc.id, bc.campaign_name, nc.name_en AS channel_name,
                       bc.total_recipients, bc.sent_count, bc.failed_count,
                       bc.status, bc.scheduled_at, bc.started_at, bc.completed_at, bc.created_at
                FROM bulk_communications bc
                LEFT JOIN notification_channels nc ON bc.channel_id = nc.id
                WHERE 1=1";
        $cols = ['bc.id', 'bc.campaign_name', 'nc.name_en', 'bc.total_recipients', 'bc.status', 'bc.created_at'];
        $search = ['bc.campaign_name', 'nc.name_en'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function bulkStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'campaign_name' => 'required|string|max:300',
            'channel_id'    => 'required|integer|exists:notification_channels,id',
            'subject'       => 'nullable|string|max:500',
            'body'          => 'required|string',
            'status'        => 'nullable|in:draft,scheduled',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['status'] = $data['status'] ?? 'draft';
        $data['created_by'] = Auth::id();

        $templateId = $request->post('template_id');
        $data['template_id'] = $templateId ?: null;

        $scheduledAt = $request->post('scheduled_at');
        $data['scheduled_at'] = $scheduledAt ?: null;

        // Target criteria
        $criteria = [];
        $memTypes = $request->post('target_membership_types', []);
        $memStatus = $request->post('target_status', '');
        if (!empty($memTypes)) $criteria['membership_types'] = $memTypes;
        if ($memStatus) $criteria['member_status'] = $memStatus;
        $data['target_criteria'] = !empty($criteria) ? json_encode($criteria) : null;

        $db = $this->db();
        $id = $db->insert('bulk_communications', $data);

        // Build recipients from criteria
        $recipientQuery = "SELECT m.id, m.primary_phone, m.primary_email, m.full_name_en
                           FROM members m WHERE m.is_deleted = 0 AND m.status = 'active'";
        $rParams = [];

        if (!empty($memTypes)) {
            $placeholders = implode(',', array_fill(0, count($memTypes), '?'));
            $recipientQuery .= " AND m.membership_type_id IN ({$placeholders})";
            $rParams = array_merge($rParams, $memTypes);
        }

        $channel = $db->fetchOne("SELECT channel_type FROM notification_channels WHERE id = ?", [$data['channel_id']]);
        $members = $db->fetchAll($recipientQuery, $rParams);

        $recipientCount = 0;
        foreach ($members as $member) {
            $address = '';
            if ($channel && $channel['channel_type'] === 'email') {
                $address = $member['primary_email'] ?? '';
            } elseif ($channel && in_array($channel['channel_type'], ['sms', 'whatsapp'])) {
                $address = $member['primary_phone'] ?? '';
            } else {
                $address = $member['primary_email'] ?? $member['primary_phone'] ?? '';
            }

            if (!$address) continue;

            $db->insert('bulk_communication_recipients', [
                'communication_id' => $id,
                'member_id'        => $member['id'],
                'recipient_address' => $address,
                'status'           => 'pending',
            ]);
            $recipientCount++;
        }

        $db->update('bulk_communications', ['total_recipients' => $recipientCount], 'id = ?', [$id]);

        $this->audit('create', 'bulk_communications', $id, "Created bulk campaign: {$data['campaign_name']} ({$recipientCount} recipients)");
        $this->json(['success' => true, 'message' => lang('comm.bulk_created') . " ({$recipientCount} recipients)", 'redirect' => '/communications/bulk/' . $id]);
    }

    public function bulkSend(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $campaign = $db->fetchOne("SELECT * FROM bulk_communications WHERE id = ?", [$id]);

        if (!$campaign || !in_array($campaign['status'], ['draft', 'scheduled', 'paused'])) {
            $this->json(['success' => false, 'message' => 'Campaign cannot be sent'], 400);
            return;
        }

        $db->update('bulk_communications', [
            'status'     => 'sending',
            'started_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$id]);

        // Queue each pending recipient
        $recipients = $db->fetchAll(
            "SELECT bcr.id, bcr.member_id, bcr.recipient_address
             FROM bulk_communication_recipients bcr
             WHERE bcr.communication_id = ? AND bcr.status = 'pending'", [$id]
        );

        $sentCount = 0;
        foreach ($recipients as $recip) {
            $db->insert('notification_queue', [
                'channel_id'        => $campaign['channel_id'],
                'template_id'       => $campaign['template_id'],
                'recipient_type'    => 'member',
                'recipient_id'      => $recip['member_id'],
                'recipient_address' => $recip['recipient_address'],
                'subject'           => $campaign['subject'],
                'body'              => $campaign['body'],
                'priority'          => 5,
                'status'            => 'queued',
                'reference_type'    => 'bulk_communication',
                'reference_id'      => $id,
            ]);

            $db->update('bulk_communication_recipients', ['status' => 'sent', 'sent_at' => date('Y-m-d H:i:s')], 'id = ?', [$recip['id']]);
            $sentCount++;
        }

        $db->update('bulk_communications', [
            'status'       => 'completed',
            'sent_count'   => $sentCount,
            'completed_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$id]);

        $this->audit('execute', 'bulk_communications', $id, "Sent bulk campaign ({$sentCount} queued)");
        $this->json(['success' => true, 'message' => lang('comm.bulk_sent') . " ({$sentCount} queued)"]);
    }

    public function bulkCancel(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $this->db()->update('bulk_communications', ['status' => 'cancelled'], "id = ? AND status IN ('draft','scheduled','paused')", [$id]);
        $this->audit('update', 'bulk_communications', $id, 'Cancelled bulk campaign');
        $this->json(['success' => true, 'message' => lang('comm.bulk_cancelled')]);
    }

    public function bulkRecipients(Request $request): void
    {
        $id = (int)$request->routeParam('id');
        $sql = "SELECT bcr.id, bcr.recipient_address, bcr.status, bcr.sent_at, bcr.error_message,
                       m.membership_number, m.full_name_en
                FROM bulk_communication_recipients bcr
                LEFT JOIN members m ON bcr.member_id = m.id
                WHERE bcr.communication_id = ?";
        $cols = ['bcr.id', 'bcr.recipient_address', 'm.full_name_en', 'bcr.status', 'bcr.sent_at'];
        $search = ['bcr.recipient_address', 'm.full_name_en', 'm.membership_number'];

        $this->datatable($request, $sql, [$id], $cols, $search);
    }

    // ═══════════════════════════════════════════════════
    //  EMAIL & SMS LOGS
    // ═══════════════════════════════════════════════════

    public function emailLogDatatable(Request $request): void
    {
        $sql = "SELECT id, to_address, from_address, subject, status, sent_at, delivered_at,
                       bounced_at, failure_reason, provider, member_id, created_at
                FROM email_log WHERE 1=1";
        $cols = ['id', 'to_address', 'subject', 'status', 'sent_at', 'created_at'];
        $search = ['to_address', 'from_address', 'subject', 'provider'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function smsLogDatatable(Request $request): void
    {
        $sql = "SELECT id, phone_number, message_text, message_type, provider, status,
                       sent_at, delivered_at, failure_reason, cost, member_id, created_at
                FROM sms_log WHERE 1=1";
        $cols = ['id', 'phone_number', 'message_type', 'status', 'sent_at', 'cost', 'created_at'];
        $search = ['phone_number', 'message_text', 'provider', 'message_type'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    // ═══════════════════════════════════════════════════
    //  ANNOUNCEMENTS
    // ═══════════════════════════════════════════════════

    public function announcementsDatatable(Request $request): void
    {
        $sql = "SELECT id, title_en, title_ar, category, priority, target_audience,
                       publish_date, expiry_date, is_published, is_pinned, views_count, created_at
                FROM announcements WHERE 1=1";
        $cols = ['id', 'title_en', 'category', 'priority', 'target_audience', 'is_published', 'is_pinned', 'publish_date', 'created_at'];
        $search = ['title_en', 'title_ar', 'category'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function announcementStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'title_en'        => 'required|string|max:500',
            'title_ar'        => 'nullable|string|max:500',
            'body_en'         => 'required|string',
            'body_ar'         => 'nullable|string',
            'category'        => 'required|in:general,maintenance,event,policy,financial,emergency,other',
            'priority'        => 'required|in:low,normal,high,urgent',
            'target_audience' => 'required|in:all_members,specific_types,specific_members,staff,board,public',
            'publish_date'    => 'required|datetime',
            'expiry_date'     => 'nullable|datetime',
            'is_published'    => 'nullable|boolean',
            'is_pinned'       => 'nullable|boolean',
            'send_notification' => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_published']      = !empty($data['is_published']) ? 1 : 0;
        $data['is_pinned']         = !empty($data['is_pinned']) ? 1 : 0;
        $data['send_notification'] = !empty($data['send_notification']) ? 1 : 0;
        $data['created_by']        = Auth::id();

        $targetTypes = $request->post('target_membership_types', []);
        $data['target_membership_types'] = !empty($targetTypes) ? json_encode($targetTypes) : null;

        // Handle image upload
        $imageFile = $request->file('image');
        if ($imageFile && $imageFile['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($imageFile);
            $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'gif', 'webp'])->setMaxSize(5 * 1024 * 1024)->setDestination('uploads/announcements');
            if ($upload->validate()) {
                $data['image_path'] = $upload->save();
            }
        }

        // Handle attachment upload
        $attachFile = $request->file('attachment');
        if ($attachFile && $attachFile['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($attachFile);
            $upload->setAllowedTypes(['pdf', 'doc', 'docx', 'jpg', 'png'])->setMaxSize(10 * 1024 * 1024)->setDestination('uploads/announcements');
            if ($upload->validate()) {
                $data['attachment_path'] = $upload->save();
            }
        }

        $id = $this->db()->insert('announcements', $data);
        $this->audit('create', 'announcements', $id, 'Created announcement: ' . $data['title_en']);
        $this->json(['success' => true, 'message' => lang('comm.announcement_created'), 'redirect' => '/communications/announcements']);
    }

    public function announcementUpdate(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $old = $db->fetchOne("SELECT * FROM announcements WHERE id = ?", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Announcement not found'], 404);
            return;
        }

        $v = new Validator($request->all(), [
            'title_en'        => 'required|string|max:500',
            'title_ar'        => 'nullable|string|max:500',
            'body_en'         => 'required|string',
            'body_ar'         => 'nullable|string',
            'category'        => 'required|in:general,maintenance,event,policy,financial,emergency,other',
            'priority'        => 'required|in:low,normal,high,urgent',
            'target_audience' => 'required|in:all_members,specific_types,specific_members,staff,board,public',
            'publish_date'    => 'required|datetime',
            'expiry_date'     => 'nullable|datetime',
            'is_published'    => 'nullable|boolean',
            'is_pinned'       => 'nullable|boolean',
            'send_notification' => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_published']      = !empty($data['is_published']) ? 1 : 0;
        $data['is_pinned']         = !empty($data['is_pinned']) ? 1 : 0;
        $data['send_notification'] = !empty($data['send_notification']) ? 1 : 0;

        $targetTypes = $request->post('target_membership_types', []);
        $data['target_membership_types'] = !empty($targetTypes) ? json_encode($targetTypes) : null;

        $imageFile = $request->file('image');
        if ($imageFile && $imageFile['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($imageFile);
            $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'gif', 'webp'])->setMaxSize(5 * 1024 * 1024)->setDestination('uploads/announcements');
            if ($upload->validate()) {
                $data['image_path'] = $upload->save();
            }
        }

        $attachFile = $request->file('attachment');
        if ($attachFile && $attachFile['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($attachFile);
            $upload->setAllowedTypes(['pdf', 'doc', 'docx', 'jpg', 'png'])->setMaxSize(10 * 1024 * 1024)->setDestination('uploads/announcements');
            if ($upload->validate()) {
                $data['attachment_path'] = $upload->save();
            }
        }

        $db->update('announcements', $data, 'id = ?', [$id]);
        $this->audit('update', 'announcements', $id, 'Updated announcement', $old, $data);
        $this->json(['success' => true, 'message' => lang('comm.announcement_updated'), 'redirect' => '/communications/announcements']);
    }

    public function announcementDelete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $this->db()->delete('announcements', 'id = ?', [$id]);
        $this->audit('delete', 'announcements', $id, 'Deleted announcement');
        $this->json(['success' => true, 'message' => lang('comm.announcement_deleted')]);
    }

    public function announcementTogglePublish(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $ann = $db->fetchOne("SELECT is_published FROM announcements WHERE id = ?", [$id]);
        if (!$ann) {
            $this->json(['success' => false, 'message' => 'Not found'], 404);
            return;
        }

        $newVal = $ann['is_published'] ? 0 : 1;
        $db->update('announcements', ['is_published' => $newVal], 'id = ?', [$id]);
        $this->audit('update', 'announcements', $id, $newVal ? 'Published announcement' : 'Unpublished announcement');
        $this->json(['success' => true, 'message' => $newVal ? lang('comm.published') : lang('comm.unpublished')]);
    }

    public function announcementTogglePin(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $ann = $db->fetchOne("SELECT is_pinned FROM announcements WHERE id = ?", [$id]);
        if (!$ann) {
            $this->json(['success' => false, 'message' => 'Not found'], 404);
            return;
        }

        $newVal = $ann['is_pinned'] ? 0 : 1;
        $db->update('announcements', ['is_pinned' => $newVal], 'id = ?', [$id]);
        $this->json(['success' => true, 'message' => $newVal ? lang('comm.pinned') : lang('comm.unpinned')]);
    }

    // ═══════════════════════════════════════════════════
    //  DOCUMENT TEMPLATES
    // ═══════════════════════════════════════════════════

    public function docTemplatesDatatable(Request $request): void
    {
        $sql = "SELECT id, template_code, name_en, name_ar, category, template_type,
                       paper_size, orientation, is_active, version, created_at
                FROM document_templates WHERE 1=1";
        $cols = ['id', 'template_code', 'name_en', 'category', 'template_type', 'is_active', 'version', 'created_at'];
        $search = ['template_code', 'name_en', 'name_ar', 'category', 'template_type'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    public function docTemplateStore(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $v = new Validator($request->all(), [
            'template_code'   => 'required|string|max:30',
            'name_en'         => 'required|string|max:200',
            'name_ar'         => 'required|string|max:200',
            'category'        => 'required|string|max:50',
            'template_type'   => 'required|in:letter,certificate,receipt,invoice,contract,form,report,badge,other',
            'template_body_en'=> 'nullable|string',
            'template_body_ar'=> 'nullable|string',
            'paper_size'      => 'required|in:a4,a3,letter,legal,custom',
            'orientation'     => 'required|in:portrait,landscape',
            'header_template' => 'nullable|string',
            'footer_template' => 'nullable|string',
            'is_active'       => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;
        $data['created_by'] = Auth::id();
        $data['version'] = 1;

        // Extract variables from body
        preg_match_all('/\{\{(\w+)\}\}/', ($data['template_body_en'] ?? '') . ($data['template_body_ar'] ?? ''), $matches);
        $vars = array_unique($matches[1] ?? []);
        $data['variables'] = !empty($vars) ? json_encode(array_values($vars)) : null;

        $id = $this->db()->insert('document_templates', $data);
        $this->audit('create', 'document_templates', $id, 'Created document template: ' . $data['template_code']);
        $this->json(['success' => true, 'message' => lang('comm.doc_template_created'), 'redirect' => '/communications/document-templates']);
    }

    public function docTemplateUpdate(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $old = $db->fetchOne("SELECT * FROM document_templates WHERE id = ?", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Template not found'], 404);
            return;
        }

        $v = new Validator($request->all(), [
            'template_code'   => 'required|string|max:30',
            'name_en'         => 'required|string|max:200',
            'name_ar'         => 'required|string|max:200',
            'category'        => 'required|string|max:50',
            'template_type'   => 'required|in:letter,certificate,receipt,invoice,contract,form,report,badge,other',
            'template_body_en'=> 'nullable|string',
            'template_body_ar'=> 'nullable|string',
            'paper_size'      => 'required|in:a4,a3,letter,legal,custom',
            'orientation'     => 'required|in:portrait,landscape',
            'header_template' => 'nullable|string',
            'footer_template' => 'nullable|string',
            'is_active'       => 'nullable|boolean',
        ]);

        if ($v->fails()) {
            $this->json(['success' => false, 'errors' => $v->errors()], 422);
            return;
        }

        $data = $v->validated();
        $data['is_active'] = !empty($data['is_active']) ? 1 : 0;
        $data['version'] = ($old['version'] ?? 1) + 1;

        preg_match_all('/\{\{(\w+)\}\}/', ($data['template_body_en'] ?? '') . ($data['template_body_ar'] ?? ''), $matches);
        $vars = array_unique($matches[1] ?? []);
        $data['variables'] = !empty($vars) ? json_encode(array_values($vars)) : null;

        $db->update('document_templates', $data, 'id = ?', [$id]);
        $this->audit('update', 'document_templates', $id, 'Updated document template', $old, $data);
        $this->json(['success' => true, 'message' => lang('comm.doc_template_updated'), 'redirect' => '/communications/document-templates']);
    }

    public function docTemplateDelete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $this->db()->update('document_templates', ['is_active' => 0], 'id = ?', [$id]);
        $this->audit('update', 'document_templates', $id, 'Deactivated document template');
        $this->json(['success' => true, 'message' => lang('comm.doc_template_deactivated')]);
    }

    // ═══════════════════════════════════════════════════
    //  DOCUMENT GENERATION
    // ═══════════════════════════════════════════════════

    public function previewDocument(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $templateId = (int)$request->post('template_id');
        $memberId   = (int)$request->post('member_id', 0);
        $db = $this->db();

        $template = $db->fetchOne("SELECT * FROM document_templates WHERE id = ?", [$templateId]);
        if (!$template) {
            $this->json(['success' => false, 'message' => 'Template not found'], 404);
            return;
        }

        $member = null;
        if ($memberId) {
            $member = $db->fetchOne(
                "SELECT m.*, mt.name_en AS membership_type_en, mt.name_ar AS membership_type_ar
                 FROM members m LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
                 WHERE m.id = ?", [$memberId]
            );
        }

        $html = $this->renderDocumentTemplate($template, $member);
        $this->json(['success' => true, 'html' => $html]);
    }

    public function generateDocumentPost(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $templateId = (int)$request->post('template_id');
        $memberId   = (int)$request->post('member_id', 0);
        $db = $this->db();

        $template = $db->fetchOne("SELECT * FROM document_templates WHERE id = ?", [$templateId]);
        if (!$template) {
            $this->json(['success' => false, 'message' => 'Template not found'], 404);
            return;
        }

        $member = null;
        if ($memberId) {
            $member = $db->fetchOne(
                "SELECT m.*, mt.name_en AS membership_type_en, mt.name_ar AS membership_type_ar
                 FROM members m LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
                 WHERE m.id = ?", [$memberId]
            );
        }

        $html = $this->renderDocumentTemplate($template, $member);

        // Save to file
        $storagePath = ROOT_PATH . '/storage/exports';
        if (!is_dir($storagePath)) @mkdir($storagePath, 0755, true);

        $filename = 'doc_' . $template['template_code'] . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.html';
        $filePath = $storagePath . '/' . $filename;
        file_put_contents($filePath, $html);

        $varsUsed = [];
        if ($member) $varsUsed['member_id'] = $memberId;

        $docId = $db->insert('generated_documents', [
            'template_id'    => $templateId,
            'member_id'      => $memberId ?: null,
            'variables_used' => json_encode($varsUsed),
            'output_path'    => 'storage/exports/' . $filename,
            'output_format'  => 'html',
            'generated_by'   => Auth::id(),
        ]);

        $this->audit('create', 'generated_documents', $docId, "Generated document from template: {$template['template_code']}");
        $this->json([
            'success'  => true,
            'message'  => lang('comm.document_generated'),
            'html'     => $html,
            'doc_id'   => $docId,
        ]);
    }

    public function generatedDatatable(Request $request): void
    {
        $sql = "SELECT gd.id, dt.template_code, dt.name_en AS template_name,
                       m.membership_number, m.full_name_en AS member_name,
                       gd.output_format, gd.print_count, gd.generated_at,
                       u.full_name_en AS generated_by_name
                FROM generated_documents gd
                LEFT JOIN document_templates dt ON gd.template_id = dt.id
                LEFT JOIN members m ON gd.member_id = m.id
                LEFT JOIN auth_users u ON gd.generated_by = u.id
                WHERE 1=1";
        $cols = ['gd.id', 'dt.template_code', 'dt.name_en', 'm.full_name_en', 'gd.output_format', 'gd.generated_at'];
        $search = ['dt.template_code', 'dt.name_en', 'm.full_name_en', 'm.membership_number'];

        $this->datatable($request, $sql, [], $cols, $search);
    }

    // ═══════════════════════════════════════════════════
    //  PRIVATE: Template renderer
    // ═══════════════════════════════════════════════════

    private function renderDocumentTemplate(array $template, ?array $member): string
    {
        $isAr = Lang::current() === 'ar';
        $body = $isAr ? ($template['template_body_ar'] ?: $template['template_body_en'] ?? '') : ($template['template_body_en'] ?? '');
        $header = $template['header_template'] ?? '';
        $footer = $template['footer_template'] ?? '';

        // Replace placeholders
        $replacements = [
            'club_name'         => Config::get($isAr ? 'club_name_ar' : 'club_name_en', 'The Club'),
            'club_phone'        => Config::get('club_phone', ''),
            'club_email'        => Config::get('club_email', ''),
            'club_address'      => Config::get($isAr ? 'club_address_ar' : 'club_address_en', ''),
            'current_date'      => date('Y-m-d'),
            'current_datetime'  => date('Y-m-d H:i'),
        ];

        if ($member) {
            $replacements = array_merge($replacements, [
                'member_number'     => $member['membership_number'] ?? '',
                'member_name_en'    => $member['full_name_en'] ?? '',
                'member_name_ar'    => $member['full_name_ar'] ?? '',
                'member_name'       => $isAr ? ($member['full_name_ar'] ?? $member['full_name_en'] ?? '') : ($member['full_name_en'] ?? ''),
                'national_id'       => $member['national_id'] ?? '',
                'primary_phone'     => $member['primary_phone'] ?? '',
                'primary_email'     => $member['primary_email'] ?? '',
                'membership_type'   => $isAr ? ($member['membership_type_ar'] ?? '') : ($member['membership_type_en'] ?? ''),
                'join_date'         => $member['join_date'] ?? '',
                'status'            => $member['status'] ?? '',
                'gender'            => $member['gender'] ?? '',
                'date_of_birth'     => $member['date_of_birth'] ?? '',
                'address'           => $member['address_line1'] ?? '',
            ]);
        }

        foreach ($replacements as $key => $val) {
            $body   = str_replace(['{{' . $key . '}}', '{' . $key . '}'], htmlspecialchars((string)$val, ENT_QUOTES, 'UTF-8'), $body);
            $header = str_replace(['{{' . $key . '}}', '{' . $key . '}'], htmlspecialchars((string)$val, ENT_QUOTES, 'UTF-8'), $header);
            $footer = str_replace(['{{' . $key . '}}', '{' . $key . '}'], htmlspecialchars((string)$val, ENT_QUOTES, 'UTF-8'), $footer);
        }

        $dir = $isAr ? 'rtl' : 'ltr';
        $orientation = $template['orientation'] ?? 'portrait';

        return <<<HTML
<!DOCTYPE html>
<html dir="{$dir}">
<head>
<meta charset="UTF-8">
<title>{$template['name_en']}</title>
<style>
@page { size: {$template['paper_size']} {$orientation}; margin: 20mm; }
body { font-family: 'Cairo', 'Segoe UI', Arial, sans-serif; font-size: 12pt; direction: {$dir}; color: #212121; }
.doc-header { border-bottom: 2px solid #1B2A4A; padding-bottom: 10px; margin-bottom: 20px; }
.doc-footer { border-top: 1px solid #ccc; padding-top: 10px; margin-top: 30px; font-size: 9pt; color: #757575; }
.doc-body { min-height: 500px; }
table { width: 100%; border-collapse: collapse; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: start; }
th { background: #f8f9fa; }
@media print { .no-print { display: none !important; } }
</style>
</head>
<body>
<div class="no-print" style="margin-bottom:15px">
<button onclick="window.print()" style="padding:8px 16px;background:#1B2A4A;color:#fff;border:none;border-radius:4px;cursor:pointer">🖨 Print</button>
<button onclick="window.close()" style="padding:8px 16px;background:#999;color:#fff;border:none;border-radius:4px;cursor:pointer;margin-left:5px">✕ Close</button>
</div>
<div class="doc-header">{$header}</div>
<div class="doc-body">{$body}</div>
<div class="doc-footer">{$footer}</div>
</body>
</html>
HTML;
    }
}