<?php
declare(strict_types=1);

namespace App\Modules\Members\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;
use App\Modules\Archive\Services\ArchiveService;

class MemberArchiveController extends Controller
{
    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();

        $filters = [
            'search'    => trim((string) $request->get('q', '')),
            'status'    => $request->get('status', ''),
            'date_from' => $request->get('date_from', ''),
            'date_to'   => $request->get('date_to', ''),
            'number'    => trim((string) $request->get('number', '')),
            'nid'       => trim((string) $request->get('nid', '')),
            'phone'     => trim((string) $request->get('phone', '')),
            'operator'  => trim((string) $request->get('operator', '')),
        ];

        $where = ['m.is_archived = 1'];
        $params = [];

        if ($filters['search'] !== '') {
            $where[] = "(m.full_name_ar LIKE ? OR m.full_name_en LIKE ?)";
            $params[] = '%' . $filters['search'] . '%';
            $params[] = '%' . $filters['search'] . '%';
        }
        if ($filters['status'] !== '') {
            $where[] = "m.status = ?";
            $params[] = $filters['status'];
        }
        if ($filters['date_from'] !== '') {
            $where[] = "m.archived_at >= ?";
            $params[] = $filters['date_from'] . ' 00:00:00';
        }
        if ($filters['date_to'] !== '') {
            $where[] = "m.archived_at <= ?";
            $params[] = $filters['date_to'] . ' 23:59:59';
        }
        if ($filters['number'] !== '') {
            $where[] = "(m.membership_number = ? OR EXISTS (SELECT 1 FROM archive_snapshots sa WHERE sa.entity_type = 'members' AND sa.entity_id = m.id AND sa.membership_number = ?))";
            $params[] = $filters['number'];
            $params[] = $filters['number'];
        }
        if ($filters['nid'] !== '') {
            $where[] = "m.national_id LIKE ?";
            $params[] = '%' . $filters['nid'] . '%';
        }
        if ($filters['phone'] !== '') {
            $where[] = "(m.phone_mobile LIKE ? OR m.phone_home LIKE ?)";
            $params[] = '%' . $filters['phone'] . '%';
            $params[] = '%' . $filters['phone'] . '%';
        }
        if ($filters['operator'] !== '') {
            $where[] = "e.full_name_ar LIKE ?";
            $params[] = '%' . $filters['operator'] . '%';
        }

        $whereClause = implode(' AND ', $where);

        $page = max(1, (int) $request->get('page', 1));
        $perPage = 25;
        $offset = ($page - 1) * $perPage;

        $countSql = "SELECT COUNT(*) as total FROM members m
                     LEFT JOIN employees e ON e.id = m.archived_by
                     WHERE {$whereClause}";
        $total = (int) ($db->selectOne($countSql, $params)['total'] ?? 0);

        $sql = "SELECT m.id, m.full_name_ar, m.national_id, m.phone_mobile, m.membership_type, m.status,
                       m.archived_at, m.archived_by,
                       (SELECT sa.membership_number FROM archive_snapshots sa WHERE sa.entity_type = 'members' AND sa.entity_id = m.id ORDER BY sa.snapshot_taken_at DESC LIMIT 1) as archived_membership_number,
                       (SELECT sa.snapshot_reason FROM archive_snapshots sa WHERE sa.entity_type = 'members' AND sa.entity_id = m.id ORDER BY sa.snapshot_taken_at DESC LIMIT 1) as snapshot_reason,
                       (SELECT sa.id FROM archive_snapshots sa WHERE sa.entity_type = 'members' AND sa.entity_id = m.id ORDER BY sa.snapshot_taken_at DESC LIMIT 1) as snapshot_id,
                       e.full_name_ar as operator_name
                FROM members m
                LEFT JOIN employees e ON e.id = m.archived_by
                WHERE {$whereClause}
                ORDER BY m.archived_at DESC
                LIMIT {$perPage} OFFSET {$offset}";
        $rows = $db->select($sql, $params);

        $lastPage = max(1, (int) ceil($total / $perPage));
        $pagination = [
            'current_page' => $page,
            'last_page'    => $lastPage,
            'total'        => $total,
            'has_prev'     => $page > 1,
            'has_next'     => $page < $lastPage,
            'prev_page'    => $page - 1,
            'next_page'    => $page + 1,
            'pages'        => self::paginateRange($page, $lastPage),
        ];

        return $this->view('Members.Views.archive_index', [
            'rows'       => $rows,
            'pagination' => $pagination,
            'filters'    => $filters,
            'total'      => $total,
        ]);
    }

    public function show(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();

        $member = $db->selectOne("SELECT * FROM members WHERE id = ? AND is_archived = 1", [(int) $id]);
        if (!$member) return $this->redirect('/members/archive')->withError('العضو المؤرشف غير موجود');

        // Get all snapshots for this member
        $snapshots = $db->select(
            "SELECT * FROM archive_snapshots WHERE entity_type = 'members' AND entity_id = ? ORDER BY snapshot_taken_at DESC",
            [(int) $id]
        );

        // Primary snapshot (most recent)
        $primarySnapshot = null;
        if (!empty($snapshots)) {
            $primarySnapshot = ArchiveService::getSnapshot((int) $snapshots[0]['id']);
        }

        // Get number chain if membership number exists in snapshot
        $numberChain = [];
        $membershipNumber = $primarySnapshot['membership_number'] ?? null;
        if ($membershipNumber) {
            $numberChain = ArchiveService::getNumberChain($membershipNumber);
        }

        // Get linked new member (who received the number)
        $linkedMember = null;
        if ($membershipNumber) {
            $currentHolder = ArchiveService::getCurrentHolder($membershipNumber);
            if ($currentHolder && (int) $currentHolder['holder_entity_id'] !== (int) $id) {
                $linkedMember = $db->selectOne(
                    "SELECT id, full_name_ar, membership_number, status FROM members WHERE id = ?",
                    [(int) $currentHolder['holder_entity_id']]
                );
            }
        }

        // Get audit trail for this member
        $auditTrail = $db->select(
            "SELECT * FROM audit_trail WHERE entity_type = 'members' AND entity_id = ? ORDER BY created_at DESC LIMIT 50",
            [(int) $id]
        );

        // Dependents: prefer snapshot data (live data may have been moved during transfer)
        $related = $primarySnapshot['related_data'] ?? [];
        $dependents = [
            'spouses'   => !empty($related['spouses']) ? $related['spouses'] : $db->select("SELECT * FROM spouses WHERE member_id = ?", [(int) $id]),
            'children'  => !empty($related['children']) ? $related['children'] : $db->select("SELECT * FROM children WHERE member_id = ?", [(int) $id]),
            'temporary' => !empty($related['temporary_members']) ? $related['temporary_members'] : $db->select("SELECT * FROM temporary_members WHERE member_id = ?", [(int) $id]),
        ];

        // Financial data: use snapshot data (complete at time of archive) + live DB for anything still linked
        $payments = !empty($related['payments']) ? $related['payments'] : $db->select(
            "SELECT * FROM payments WHERE member_id = ? AND is_voided = 0 ORDER BY payment_date DESC", [(int) $id]
        );
        $subscriptions = !empty($related['subscriptions']) ? $related['subscriptions'] : $db->select(
            "SELECT * FROM subscriptions WHERE member_id = ? ORDER BY financial_year DESC", [(int) $id]
        );
        $installmentPlans = !empty($related['installment_plans']) ? $related['installment_plans'] : $db->select(
            "SELECT * FROM installment_plans WHERE member_id = ? ORDER BY created_at DESC", [(int) $id]
        );
        $fines = !empty($related['fines']) ? $related['fines'] : $db->select(
            "SELECT * FROM fines WHERE member_id = ? ORDER BY created_at DESC", [(int) $id]
        );
        $documents = !empty($related['documents']) ? $related['documents'] : $db->select(
            "SELECT * FROM documents WHERE member_id = ? AND is_archived = 0 ORDER BY created_at DESC", [(int) $id]
        );

        // For installment plans, get their schedules and cheques
        $installmentSchedules = [];
        $installmentCheques = [];
        if (!empty($installmentPlans)) {
            // Prefer snapshot data for cheques/schedules
            $installmentSchedules = !empty($related['installment_schedule']) ? $related['installment_schedule'] : [];
            $installmentCheques = !empty($related['installment_cheques']) ? $related['installment_cheques'] : [];

            // Fall back to live DB if snapshot didn't have them
            $planIds = array_column($installmentPlans, 'id');
            if (!empty($planIds)) {
                $placeholders = implode(',', array_fill(0, count($planIds), '?'));
                if (empty($installmentSchedules)) {
                    $installmentSchedules = $db->select(
                        "SELECT * FROM installment_schedule WHERE installment_plan_id IN ({$placeholders}) ORDER BY due_date ASC",
                        $planIds
                    );
                }
                if (empty($installmentCheques)) {
                    $installmentCheques = $db->select(
                        "SELECT * FROM installment_cheques WHERE installment_plan_id IN ({$placeholders}) ORDER BY cheque_date ASC",
                        $planIds
                    );
                }
            }
        }

        // Operator info
        $operator = null;
        if ($member['archived_by']) {
            $operator = $db->selectOne("SELECT id, full_name_ar FROM employees WHERE id = ?", [(int) $member['archived_by']]);
        }

        return $this->view('Members.Views.archive_show', [
            'member'               => $member,
            'snapshots'            => $snapshots,
            'primarySnapshot'      => $primarySnapshot,
            'numberChain'          => $numberChain,
            'linkedMember'         => $linkedMember,
            'auditTrail'           => $auditTrail,
            'dependents'           => $dependents,
            'payments'             => $payments,
            'subscriptions'        => $subscriptions,
            'installmentPlans'     => $installmentPlans,
            'installmentSchedules' => $installmentSchedules,
            'installmentCheques'   => $installmentCheques,
            'fines'                => $fines,
            'documents'            => $documents,
            'operator'             => $operator,
            'membershipNumber'     => $membershipNumber,
        ]);
    }

    private static function paginateRange(int $current, int $last): array
    {
        if ($last <= 7) return range(1, $last);
        $pages = [1];
        $start = max(2, $current - 2);
        $end = min($last - 1, $current + 2);
        if ($start > 2) $pages[] = '...';
        for ($i = $start; $i <= $end; $i++) $pages[] = $i;
        if ($end < $last - 1) $pages[] = '...';
        $pages[] = $last;
        return $pages;
    }
}
