<?php
class ApplicationController extends Controller
{
    public function index(Request $request): void
    {
        $db = $this->db();
        $membershipTypes = $db->fetchAll("SELECT id, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");
        $users = $db->fetchAll("SELECT id, full_name_en FROM auth_users WHERE is_active = 1 AND is_deleted = 0 ORDER BY full_name_en");

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

    public function create(Request $request): void
    {
        $db = $this->db();
        $membershipTypes = $db->fetchAll(
            "SELECT mt.*, mc.name_en as category_name_en, mc.name_ar as category_name_ar
             FROM membership_types mt
             LEFT JOIN membership_categories mc ON mt.category_id = mc.id
             WHERE mt.is_active = 1
             ORDER BY mt.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");
        $periods = $db->fetchAll("SELECT id, period_name_en, period_name_ar FROM application_periods WHERE is_active = 1 AND end_date >= CURDATE() ORDER BY start_date");

        $this->view('create', [
            'title' => lang('applications.create'),
            'breadcrumbs' => [
                ['label' => lang('applications.title'), 'url' => '/applications'],
                ['label' => lang('applications.create')]
            ],
            'membershipTypes' => $membershipTypes,
            'nationalities' => $nationalities,
            'countries' => $countries,
            'periods' => $periods,
            'application' => null,
        ]);
    }

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

        $validator = new Validator($request->all(), [
            'membership_type_id'  => 'required|integer|exists:membership_types,id',
            'first_name_en'       => 'required|string|max:100',
            'last_name_en'        => 'required|string|max:100',
            'gender'              => 'required|in:male,female',
            'primary_phone'       => 'required|string|max:20',
        ]);

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

        $db = $this->db();

        try {
            $db->beginTransaction();

            // Generate application number
            $db->execute("CALL sp_generate_next_sequence('application_number', @app_num)");
            $result = $db->fetchOne("SELECT @app_num AS val");
            $appNumber = $result['val'] ?? ('APP-' . time());

            $data = [
                'application_number'   => $appNumber,
                'application_period_id'=> $request->post('application_period_id') ?: null,
                'membership_type_id'   => $request->post('membership_type_id'),
                'form_number'          => $request->post('form_number') ?: null,
                'title'                => $request->post('title') ?: null,
                'first_name_en'        => $request->post('first_name_en'),
                'second_name_en'       => $request->post('second_name_en') ?: null,
                'third_name_en'        => $request->post('third_name_en') ?: null,
                'last_name_en'         => $request->post('last_name_en'),
                'full_name_en'         => '',
                'first_name_ar'        => $request->post('first_name_ar') ?: null,
                'second_name_ar'       => $request->post('second_name_ar') ?: null,
                'third_name_ar'        => $request->post('third_name_ar') ?: null,
                'last_name_ar'         => $request->post('last_name_ar') ?: null,
                'national_id'          => $request->post('national_id') ?: null,
                'passport_number'      => $request->post('passport_number') ?: null,
                'nationality_id'       => $request->post('nationality_id') ?: null,
                'date_of_birth'        => $request->post('date_of_birth') ?: null,
                'gender'               => $request->post('gender'),
                'marital_status'       => $request->post('marital_status') ?: null,
                'primary_phone'        => $request->post('primary_phone'),
                'secondary_phone'      => $request->post('secondary_phone') ?: null,
                'email'                => $request->post('email') ?: null,
                'address_line1'        => $request->post('address_line1') ?: null,
                'city_id'              => $request->post('city_id') ?: null,
                'country_id'           => $request->post('country_id') ?: null,
                'occupation'           => $request->post('occupation') ?: null,
                'employer'             => $request->post('employer') ?: null,
                'education_level'      => $request->post('education_level') ?: null,
                'priority'             => $request->post('priority') ?: 'normal',
                'source'               => $request->post('source') ?: 'walk_in',
                'status'               => 'draft',
                'ip_address'           => $request->ip(),
                'created_by'           => Auth::id(),
            ];

            // Handle photo upload
            $photoFile = $request->file('photo');
            if ($photoFile && $photoFile['error'] === UPLOAD_ERR_OK) {
                $upload = new FileUpload($photoFile);
                $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'webp'])
                       ->setMaxSize(5 * 1024 * 1024)
                       ->setDestination('uploads/applications/photos');
                if ($upload->validate()) {
                    $path = $upload->save();
                    if ($path) {
                        $data['photo_path'] = $path;
                    }
                }
            }

            $appId = $db->insert('applications', $data);

            // Handle sponsors
            $sponsor1 = $request->post('sponsor1_member_id');
            $sponsor2 = $request->post('sponsor2_member_id');
            if ($sponsor1) {
                $db->update('applications', ['sponsor1_member_id' => $sponsor1], 'id = ?', [$appId]);
                $db->insert('application_sponsors', [
                    'application_id'    => $appId,
                    'sponsor_member_id' => $sponsor1,
                    'sponsor_order'     => 1,
                    'status'            => 'pending',
                ]);
            }
            if ($sponsor2) {
                $db->update('applications', ['sponsor2_member_id' => $sponsor2], 'id = ?', [$appId]);
                $db->insert('application_sponsors', [
                    'application_id'    => $appId,
                    'sponsor_member_id' => $sponsor2,
                    'sponsor_order'     => 2,
                    'status'            => 'pending',
                ]);
            }

            // Calculate fees
            $fees = $db->fetchAll(
                "SELECT mtf.*, ft.fee_code, ft.name_en, ft.name_ar
                 FROM membership_type_fees mtf
                 JOIN fee_types ft ON mtf.fee_type_id = ft.id
                 WHERE mtf.membership_type_id = ? AND mtf.is_active = 1
                   AND mtf.effective_from <= CURDATE()
                   AND (mtf.effective_to IS NULL OR mtf.effective_to >= CURDATE())
                   AND mtf.is_mandatory = 1",
                [$request->post('membership_type_id')]
            );

            $totalFees = 0;
            foreach ($fees as $fee) {
                $amount = (float) $fee['amount'];
                $totalFees += $amount;
                $db->insert('application_fees', [
                    'application_id'  => $appId,
                    'fee_type_id'     => $fee['fee_type_id'],
                    'description_en'  => $fee['name_en'],
                    'description_ar'  => $fee['name_ar'],
                    'amount'          => $amount,
                    'is_paid'         => 0,
                ]);
            }

            if ($totalFees > 0) {
                $db->update('applications', ['total_fees_calculated' => $totalFees], 'id = ?', [$appId]);
            }

            $this->audit('create', 'applications', $appId, "Application created: {$appNumber}");

            $db->commit();

            $this->json([
                'success'  => true,
                'message'  => lang('applications.created_success'),
                'redirect' => '/applications/' . $appId,
            ]);

        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Application creation failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Failed to create application'], 500);
        }
    }

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

        $app = $db->fetchOne(
            "SELECT a.*,
                    mt.name_en as type_name_en, mt.name_ar as type_name_ar, mt.type_code,
                    mt.requires_sponsor, mt.sponsor_count, mt.requires_board_approval, mt.requires_interview,
                    mc.name_en as category_name_en, mc.name_ar as category_name_ar,
                    n.name_en as nationality_name_en, n.name_ar as nationality_name_ar,
                    c.name_en as city_name_en, co.name_en as country_name_en,
                    u.full_name_en as assigned_to_name,
                    rm.membership_number as resulting_member_number
             FROM applications a
             LEFT JOIN membership_types mt ON a.membership_type_id = mt.id
             LEFT JOIN membership_categories mc ON mt.category_id = mc.id
             LEFT JOIN sys_nationalities n ON a.nationality_id = n.id
             LEFT JOIN sys_cities c ON a.city_id = c.id
             LEFT JOIN sys_countries co ON a.country_id = co.id
             LEFT JOIN auth_users u ON a.assigned_to = u.id
             LEFT JOIN members rm ON a.resulting_member_id = rm.id
             WHERE a.id = ? AND a.is_deleted = 0",
            [$id]
        );

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

        $documents = $db->fetchAll(
            "SELECT ad.*, au.full_name_en as verified_by_name
             FROM application_documents ad
             LEFT JOIN auth_users au ON ad.verified_by = au.id
             WHERE ad.application_id = ? ORDER BY ad.uploaded_at DESC",
            [$id]
        );

        $sponsors = $db->fetchAll(
            "SELECT asp.*, m.membership_number, m.full_name_en as sponsor_name, m.primary_phone as sponsor_phone
             FROM application_sponsors asp
             JOIN members m ON asp.sponsor_member_id = m.id
             WHERE asp.application_id = ? ORDER BY asp.sponsor_order",
            [$id]
        );

        $reviews = $db->fetchAll(
            "SELECT ar.*, au.full_name_en as reviewer_name
             FROM application_reviews ar
             JOIN auth_users au ON ar.reviewer_id = au.id
             WHERE ar.application_id = ? ORDER BY ar.reviewed_at DESC",
            [$id]
        );

        $interviews = $db->fetchAll(
            "SELECT ai.*, au.full_name_en as recorded_by_name
             FROM application_interviews ai
             LEFT JOIN auth_users au ON ai.recorded_by = au.id
             WHERE ai.application_id = ? ORDER BY ai.scheduled_date DESC",
            [$id]
        );

        $fees = $db->fetchAll(
            "SELECT af.*, ft.fee_code
             FROM application_fees af
             JOIN fee_types ft ON af.fee_type_id = ft.id
             WHERE af.application_id = ? ORDER BY af.id",
            [$id]
        );

        $statusHistory = $db->fetchAll(
            "SELECT ash.*, au.full_name_en as changed_by_name
             FROM application_status_history ash
             LEFT JOIN auth_users au ON ash.changed_by = au.id
             WHERE ash.application_id = ? ORDER BY ash.changed_at DESC",
            [$id]
        );

        $boardVotes = $db->fetchAll(
            "SELECT abv.*, au.full_name_en as voter_name
             FROM application_board_votes abv
             JOIN auth_users au ON abv.board_member_user_id = au.id
             WHERE abv.application_id = ? ORDER BY abv.voted_at",
            [$id]
        );

        $users = $db->fetchAll("SELECT id, full_name_en FROM auth_users WHERE is_active = 1 AND is_deleted = 0 ORDER BY full_name_en");

        $requiredDocs = $db->fetchAll(
            "SELECT * FROM membership_type_documents_required
             WHERE membership_type_id = ? AND is_active = 1 ORDER BY sort_order",
            [$app['membership_type_id']]
        );

        $this->view('show', [
            'title' => lang('applications.view') . ' - ' . $app['application_number'],
            'breadcrumbs' => [
                ['label' => lang('applications.title'), 'url' => '/applications'],
                ['label' => $app['application_number']]
            ],
            'application'    => $app,
            'documents'      => $documents,
            'sponsors'       => $sponsors,
            'reviews'        => $reviews,
            'interviews'     => $interviews,
            'fees'           => $fees,
            'statusHistory'  => $statusHistory,
            'boardVotes'     => $boardVotes,
            'users'          => $users,
            'requiredDocs'   => $requiredDocs,
        ]);
    }

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

        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->abort(404, 'Application not found');
            return;
        }
        if (!in_array($app['status'], ['draft', 'submitted', 'documents_pending'])) {
            Session::flash('error', 'Cannot edit application in current status');
            Response::redirect('/applications/' . $id);
            return;
        }

        $membershipTypes = $db->fetchAll(
            "SELECT mt.*, mc.name_en as category_name_en
             FROM membership_types mt
             LEFT JOIN membership_categories mc ON mt.category_id = mc.id
             WHERE mt.is_active = 1 ORDER BY mt.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");
        $periods = $db->fetchAll("SELECT id, period_name_en, period_name_ar FROM application_periods WHERE is_active = 1 ORDER BY start_date");

        $this->view('create', [
            'title' => lang('applications.edit') . ' - ' . $app['application_number'],
            'breadcrumbs' => [
                ['label' => lang('applications.title'), 'url' => '/applications'],
                ['label' => $app['application_number'], 'url' => '/applications/' . $id],
                ['label' => lang('common.edit')]
            ],
            'membershipTypes' => $membershipTypes,
            'nationalities' => $nationalities,
            'countries' => $countries,
            'periods' => $periods,
            'application' => $app,
        ]);
    }

    public function update(Request $request): void
    {
        $id = (int) $request->routeParam('id');

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }

        $validator = new Validator($request->all(), [
            'membership_type_id' => 'required|integer',
            'first_name_en'      => 'required|string|max:100',
            'last_name_en'       => 'required|string|max:100',
            'gender'             => 'required|in:male,female',
            'primary_phone'      => 'required|string|max:20',
        ]);

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

        $data = [
            'membership_type_id'   => $request->post('membership_type_id'),
            'application_period_id'=> $request->post('application_period_id') ?: null,
            'form_number'          => $request->post('form_number') ?: null,
            'title'                => $request->post('title') ?: null,
            'first_name_en'        => $request->post('first_name_en'),
            'second_name_en'       => $request->post('second_name_en') ?: null,
            'third_name_en'        => $request->post('third_name_en') ?: null,
            'last_name_en'         => $request->post('last_name_en'),
            'first_name_ar'        => $request->post('first_name_ar') ?: null,
            'second_name_ar'       => $request->post('second_name_ar') ?: null,
            'third_name_ar'        => $request->post('third_name_ar') ?: null,
            'last_name_ar'         => $request->post('last_name_ar') ?: null,
            'national_id'          => $request->post('national_id') ?: null,
            'passport_number'      => $request->post('passport_number') ?: null,
            'nationality_id'       => $request->post('nationality_id') ?: null,
            'date_of_birth'        => $request->post('date_of_birth') ?: null,
            'gender'               => $request->post('gender'),
            'marital_status'       => $request->post('marital_status') ?: null,
            'primary_phone'        => $request->post('primary_phone'),
            'secondary_phone'      => $request->post('secondary_phone') ?: null,
            'email'                => $request->post('email') ?: null,
            'address_line1'        => $request->post('address_line1') ?: null,
            'city_id'              => $request->post('city_id') ?: null,
            'country_id'           => $request->post('country_id') ?: null,
            'occupation'           => $request->post('occupation') ?: null,
            'employer'             => $request->post('employer') ?: null,
            'education_level'      => $request->post('education_level') ?: null,
            'priority'             => $request->post('priority') ?: 'normal',
            'source'               => $request->post('source') ?: $app['source'],
            'updated_at'          => date('Y-m-d H:i:s'),
        ];

        $photoFile = $request->file('photo');
        if ($photoFile && $photoFile['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($photoFile);
            $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'webp'])
                   ->setMaxSize(5 * 1024 * 1024)
                   ->setDestination('uploads/applications/photos');
            if ($upload->validate()) {
                $path = $upload->save();
                if ($path) {
                    $data['photo_path'] = $path;
                }
            }
        }

        $db->update('applications', $data, 'id = ?', [$id]);
        $this->audit('update', 'applications', $id, "Application updated: {$app['application_number']}", $app, $data);

        $this->json([
            'success'  => true,
            'message'  => lang('applications.updated_success'),
            'redirect' => '/applications/' . $id,
        ]);
    }

    public function boardVote(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = $this->db();

        $app = $db->fetchOne(
            "SELECT a.*, mt.name_en as type_name_en
             FROM applications a
             JOIN membership_types mt ON a.membership_type_id = mt.id
             WHERE a.id = ? AND a.is_deleted = 0",
            [$id]
        );

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

        $currentTerm = $db->fetchOne("SELECT * FROM board_terms WHERE status = 'active' LIMIT 1");
        $boardMembers = [];
        if ($currentTerm) {
            $boardMembers = $db->fetchAll(
                "SELECT bm.*, bp.title_en as position_title, au.full_name_en as user_name
                 FROM board_members bm
                 JOIN board_positions bp ON bm.position_id = bp.id
                 LEFT JOIN auth_users au ON bm.user_id = au.id
                 WHERE bm.term_id = ? AND bm.status = 'active'
                 ORDER BY bp.sort_order",
                [$currentTerm['id']]
            );
        }

        $existingVotes = $db->fetchAll(
            "SELECT abv.*, au.full_name_en as voter_name
             FROM application_board_votes abv
             JOIN auth_users au ON abv.board_member_user_id = au.id
             WHERE abv.application_id = ?",
            [$id]
        );

        $this->view('board-vote', [
            'title' => lang('applications.board_vote') . ' - ' . $app['application_number'],
            'breadcrumbs' => [
                ['label' => lang('applications.title'), 'url' => '/applications'],
                ['label' => $app['application_number'], 'url' => '/applications/' . $id],
                ['label' => lang('applications.board_vote')]
            ],
            'application'   => $app,
            'boardMembers'  => $boardMembers,
            'existingVotes' => $existingVotes,
        ]);
    }

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

        $app = $db->fetchOne(
            "SELECT a.*, mt.name_en as type_name_en, mt.name_ar as type_name_ar,
                    n.name_en as nationality_name_en, n.name_ar as nationality_name_ar,
                    c.name_en as city_name_en, co.name_en as country_name_en
             FROM applications a
             LEFT JOIN membership_types mt ON a.membership_type_id = mt.id
             LEFT JOIN sys_nationalities n ON a.nationality_id = n.id
             LEFT JOIN sys_cities c ON a.city_id = c.id
             LEFT JOIN sys_countries co ON a.country_id = co.id
             WHERE a.id = ? AND a.is_deleted = 0",
            [$id]
        );

        if (!$app) {
            $this->abort(404);
            return;
        }

        $sponsors = $db->fetchAll(
            "SELECT asp.*, m.full_name_en as sponsor_name, m.membership_number
             FROM application_sponsors asp
             JOIN members m ON asp.sponsor_member_id = m.id
             WHERE asp.application_id = ? ORDER BY asp.sponsor_order",
            [$id]
        );

        $this->view('print', [
            'title' => $app['application_number'],
            'application' => $app,
            'sponsors' => $sponsors,
        ], 'print');
    }
}