<?php
class MemberController extends Controller
{
    public function index(Request $request): void
    {
        $db = Database::getInstance();
        $types = $db->fetchAll("SELECT id, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");

        $this->view('index', [
            'title'       => lang('members.title'),
            'breadcrumbs' => [['label' => lang('members.title')]],
            'types'       => $types,
        ]);
    }

    public function create(Request $request): void
    {
        $db = Database::getInstance();
        $types         = $db->fetchAll("SELECT id, name_en, name_ar, is_family_membership, max_dependents, requires_sponsor, sponsor_count FROM membership_types WHERE is_active = 1 ORDER BY sort_order");
        $nationalities = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_nationalities WHERE is_active = 1 ORDER BY name_en");
        $countries     = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_countries WHERE is_active = 1 ORDER BY sort_order, name_en");
        $relTypes      = $db->fetchAll("SELECT id, relationship_code, name_en, name_ar FROM dependent_relationship_types WHERE is_active = 1 ORDER BY sort_order");

        $this->view('create', [
            'title'         => lang('members.create'),
            'breadcrumbs'   => [
                ['label' => lang('members.title'), 'url' => '/members'],
                ['label' => lang('members.create')],
            ],
            'types'         => $types,
            'nationalities' => $nationalities,
            'countries'     => $countries,
            'relTypes'      => $relTypes,
        ]);
    }

    public function show(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();

        $member = $db->fetchOne(
            "SELECT m.*, mt.name_en AS type_name_en, mt.name_ar AS type_name_ar, mt.type_code,
                    mt.is_family_membership, mt.max_dependents,
                    n.name_en AS nationality_en, n.name_ar AS nationality_ar,
                    c.name_en AS city_en, c.name_ar AS city_ar,
                    co.name_en AS country_en, co.name_ar AS country_ar
             FROM members m
             LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
             LEFT JOIN sys_nationalities n ON m.nationality_id = n.id
             LEFT JOIN sys_cities c ON m.city_id = c.id
             LEFT JOIN sys_countries co ON m.country_id = co.id
             WHERE m.id = ? AND m.is_deleted = 0", [$id]
        );

        if (!$member) {
            $this->abort(404, 'Member not found');
            return;
        }

        $family = null;
        if ($member['family_id']) {
            $family = $db->fetchOne("SELECT * FROM member_families WHERE id = ?", [$member['family_id']]);
        }

        $dependents = $db->fetchAll(
            "SELECT d.*, rt.name_en AS rel_type_en, rt.name_ar AS rel_type_ar
             FROM member_dependents d
             LEFT JOIN dependent_relationship_types rt ON d.relationship_type_id = rt.id
             WHERE d.primary_member_id = ? AND d.is_deleted = 0
             ORDER BY d.add_date", [$id]
        );

        $balances = $db->fetchOne("SELECT * FROM member_balances WHERE member_id = ?", [$id]);

        $recentInvoices = $db->fetchAll(
            "SELECT id, invoice_number, invoice_type, invoice_date, total_amount, paid_amount, balance_due, status
             FROM invoices WHERE member_id = ? AND is_deleted = 0 ORDER BY invoice_date DESC LIMIT 10", [$id]
        );

        $recentPayments = $db->fetchAll(
            "SELECT p.id, p.payment_number, p.amount, p.payment_date, p.status, pm.name_en AS method_en
             FROM payments p LEFT JOIN payment_methods pm ON p.payment_method_id = pm.id
             WHERE p.member_id = ? AND p.is_deleted = 0 ORDER BY p.payment_date DESC LIMIT 10", [$id]
        );

        $documents = $db->fetchAll(
            "SELECT * FROM member_documents WHERE member_id = ? AND is_deleted = 0 ORDER BY uploaded_at DESC", [$id]
        );

        $subscriptions = $db->fetchAll(
            "SELECT ms.*, sp.name_en AS period_name_en, mt2.name_en AS sub_type_en
             FROM member_subscriptions ms
             LEFT JOIN subscription_periods sp ON ms.subscription_period_id = sp.id
             LEFT JOIN membership_types mt2 ON ms.membership_type_id = mt2.id
             WHERE ms.member_id = ? ORDER BY ms.start_date DESC LIMIT 20", [$id]
        );

        $cards = $db->fetchAll(
            "SELECT mc.*, ct.name_en AS card_type_en
             FROM member_cards mc LEFT JOIN card_types ct ON mc.card_type_id = ct.id
             WHERE mc.member_id = ? ORDER BY mc.issue_date DESC", [$id]
        );

        $freezes = $db->fetchAll(
            "SELECT * FROM member_freezes WHERE member_id = ? ORDER BY start_date DESC", [$id]
        );

        $violations = $db->fetchAll(
            "SELECT v.*, vt.name_en AS type_en, vt.severity
             FROM violations v LEFT JOIN violation_types vt ON v.violation_type_id = vt.id
             WHERE v.member_id = ? ORDER BY v.incident_date DESC LIMIT 20", [$id]
        );

        $notes = $db->fetchAll(
            "SELECT mn.*, u.full_name_en AS author_name
             FROM member_notes mn LEFT JOIN auth_users u ON mn.created_by = u.id
             WHERE mn.member_id = ? AND mn.is_deleted = 0 ORDER BY mn.is_pinned DESC, mn.created_at DESC", [$id]
        );

        $tags = $db->fetchAll("SELECT * FROM member_tags WHERE member_id = ?", [$id]);

        $statusHistory = $db->fetchAll(
            "SELECT msh.*, u.full_name_en AS changed_by_name
             FROM member_status_history msh LEFT JOIN auth_users u ON msh.changed_by = u.id
             WHERE msh.member_id = ? ORDER BY msh.changed_at DESC LIMIT 20", [$id]
        );

        $endorsements = $db->fetchAll(
            "SELECT me.*, et.name_en AS type_en, m2.full_name_en AS endorser_name, m2.membership_number AS endorser_number
             FROM member_endorsements me
             LEFT JOIN endorsement_types et ON me.endorsement_type_id = et.id
             LEFT JOIN members m2 ON me.endorser_member_id = m2.id
             WHERE me.endorser_member_id = ? OR me.endorsed_person_national_id = ?
             ORDER BY me.endorsement_date DESC", [$id, $member['national_id'] ?? '']
        );

        $accessLog = $db->fetchAll(
            "SELECT mal.*, ap.name_en AS point_name
             FROM member_access_log mal LEFT JOIN access_points ap ON mal.access_point_id = ap.id
             WHERE mal.member_id = ? ORDER BY mal.access_time DESC LIMIT 20", [$id]
        );

        $relTypes = $db->fetchAll("SELECT id, name_en, name_ar, relationship_code, gender_restriction, max_count_per_member FROM dependent_relationship_types WHERE is_active = 1 ORDER BY sort_order");
        $nationalities = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_nationalities WHERE is_active = 1 ORDER BY name_en");
        $endorsementTypes = $db->fetchAll("SELECT id, name_en, name_ar, type_code FROM endorsement_types WHERE is_active = 1 ORDER BY id");

        $this->view('show', [
            'title'            => $member['full_name_en'] . ' (' . $member['membership_number'] . ')',
            'breadcrumbs'      => [
                ['label' => lang('members.title'), 'url' => '/members'],
                ['label' => $member['membership_number']],
            ],
            'member'           => $member,
            'family'           => $family,
            'dependents'       => $dependents,
            'balances'         => $balances,
            'recentInvoices'   => $recentInvoices,
            'recentPayments'   => $recentPayments,
            'documents'        => $documents,
            'subscriptions'    => $subscriptions,
            'cards'            => $cards,
            'freezes'          => $freezes,
            'violations'       => $violations,
            'notes'            => $notes,
            'tags'             => $tags,
            'statusHistory'    => $statusHistory,
            'endorsements'     => $endorsements,
            'accessLog'        => $accessLog,
            'relTypes'         => $relTypes,
            'nationalities'    => $nationalities,
            'endorsementTypes' => $endorsementTypes,
        ]);
    }

    public function edit(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();

        $member = $db->fetchOne("SELECT * FROM members WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$member) {
            $this->abort(404, 'Member not found');
            return;
        }

        $types         = $db->fetchAll("SELECT id, name_en, name_ar, is_family_membership FROM membership_types WHERE is_active = 1 ORDER BY sort_order");
        $nationalities = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_nationalities WHERE is_active = 1 ORDER BY name_en");
        $countries     = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_countries WHERE is_active = 1 ORDER BY sort_order, name_en");
        $cities        = [];
        if ($member['country_id']) {
            $cities = $db->fetchAll("SELECT id, name_en, name_ar FROM sys_cities WHERE country_id = ? AND is_active = 1 ORDER BY name_en", [$member['country_id']]);
        }

        $this->view('edit', [
            'title'         => lang('members.edit') . ': ' . $member['membership_number'],
            'breadcrumbs'   => [
                ['label' => lang('members.title'), 'url' => '/members'],
                ['label' => $member['membership_number'], 'url' => '/members/' . $id],
                ['label' => lang('common.edit')],
            ],
            'member'        => $member,
            'types'         => $types,
            'nationalities' => $nationalities,
            'countries'     => $countries,
            'cities'        => $cities,
        ]);
    }

    public function printView(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();

        $member = $db->fetchOne(
            "SELECT m.*, mt.name_en AS type_name_en, mt.name_ar AS type_name_ar,
                    n.name_en AS nationality_en, c.name_en AS city_en, co.name_en AS country_en
             FROM members m
             LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
             LEFT JOIN sys_nationalities n ON m.nationality_id = n.id
             LEFT JOIN sys_cities c ON m.city_id = c.id
             LEFT JOIN sys_countries co ON m.country_id = co.id
             WHERE m.id = ? AND m.is_deleted = 0", [$id]
        );

        if (!$member) {
            $this->abort(404, 'Member not found');
            return;
        }

        $dependents = $db->fetchAll(
            "SELECT d.*, rt.name_en AS rel_type_en
             FROM member_dependents d LEFT JOIN dependent_relationship_types rt ON d.relationship_type_id = rt.id
             WHERE d.primary_member_id = ? AND d.is_deleted = 0", [$id]
        );

        $this->view('print', [
            'title'      => 'Print - ' . $member['membership_number'],
            'member'     => $member,
            'dependents' => $dependents,
        ], 'print');
    }
}