<?php

class SubscriptionController extends Controller
{
    public function index(Request $request): void
    {
        $db = $this->db();

        $totalActive = (int) $db->fetchColumn("SELECT COUNT(*) FROM member_subscriptions WHERE status = 'active'");
        $totalExpired = (int) $db->fetchColumn("SELECT COUNT(*) FROM member_subscriptions WHERE status = 'expired'");
        $totalPending = (int) $db->fetchColumn("SELECT COUNT(*) FROM member_subscriptions WHERE status = 'pending'");
        $expiringIn30 = (int) $db->fetchColumn("SELECT COUNT(*) FROM member_subscriptions WHERE status = 'active' AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)");

        $periods = $db->fetchAll("SELECT id, period_code, name_en, name_ar FROM subscription_periods WHERE is_active = 1 ORDER BY start_date DESC");
        $types = $db->fetchAll("SELECT id, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");

        $this->view('subscriptions/index', [
            'title'        => lang('subs.subscriptions'),
            'breadcrumbs'  => [
                ['label' => lang('subs.subscriptions')],
            ],
            'totalActive'  => $totalActive,
            'totalExpired' => $totalExpired,
            'totalPending' => $totalPending,
            'expiringIn30' => $expiringIn30,
            'periods'      => $periods,
            'types'        => $types,
        ]);
    }

    public function create(Request $request): void
    {
        $db = $this->db();
        $periods = $db->fetchAll("SELECT id, period_code, name_en, name_ar, start_date, end_date FROM subscription_periods WHERE is_active = 1 ORDER BY start_date DESC");
        $types = $db->fetchAll("SELECT id, type_code, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");

        $memberId = $request->get('member_id');
        $member = null;
        if ($memberId) {
            $member = $db->fetchOne("SELECT id, membership_number, full_name_en, full_name_ar, membership_type_id, status FROM members WHERE id = ? AND is_deleted = 0", [$memberId]);
        }

        $this->view('subscriptions/create', [
            'title'       => lang('subs.create_subscription'),
            'breadcrumbs' => [
                ['label' => lang('subs.subscriptions'), 'url' => '/subscriptions'],
                ['label' => lang('subs.create_subscription')],
            ],
            'periods' => $periods,
            'types'   => $types,
            'member'  => $member,
        ]);
    }

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

        $sub = $db->fetchOne(
            "SELECT ms.*, sp.name_en AS period_name_en, sp.name_ar AS period_name_ar, sp.period_code,
                    mt.name_en AS type_name_en, mt.name_ar AS type_name_ar, mt.type_code,
                    m.membership_number, m.full_name_en AS member_name_en, m.full_name_ar AS member_name_ar,
                    m.primary_phone, m.primary_email
             FROM member_subscriptions ms
             JOIN subscription_periods sp ON ms.subscription_period_id = sp.id
             JOIN membership_types mt ON ms.membership_type_id = mt.id
             JOIN members m ON ms.member_id = m.id
             WHERE ms.id = ?",
            [$id]
        );

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

        $renewalLog = $db->fetchAll(
            "SELECT srl.*, u.full_name_en AS created_by_name
             FROM subscription_renewal_log srl
             LEFT JOIN auth_users u ON srl.created_by = u.id
             WHERE srl.member_id = ? AND (srl.old_subscription_id = ? OR srl.new_subscription_id = ?)
             ORDER BY srl.created_at DESC",
            [$sub['member_id'], $id, $id]
        );

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

        $invoice = null;
        if ($sub['invoice_id']) {
            $invoice = $db->fetchOne("SELECT id, invoice_number, total_amount, paid_amount, balance_due, status FROM invoices WHERE id = ?", [$sub['invoice_id']]);
        }

        $this->view('subscriptions/show', [
            'title'       => lang('subs.view_subscription') . ' #' . $id,
            'breadcrumbs' => [
                ['label' => lang('subs.subscriptions'), 'url' => '/subscriptions'],
                ['label' => lang('subs.view_subscription')],
            ],
            'sub'        => $sub,
            'renewalLog' => $renewalLog,
            'freezes'    => $freezes,
            'invoice'    => $invoice,
        ]);
    }

    public function expiring(Request $request): void
    {
        $this->view('subscriptions/expiring', [
            'title'       => lang('subs.expiring_subscriptions'),
            'breadcrumbs' => [
                ['label' => lang('subs.subscriptions'), 'url' => '/subscriptions'],
                ['label' => lang('subs.expiring_subscriptions')],
            ],
        ]);
    }

    public function bulkRenew(Request $request): void
    {
        $db = $this->db();
        $periods = $db->fetchAll("SELECT id, period_code, name_en, name_ar, start_date, end_date FROM subscription_periods WHERE is_active = 1 ORDER BY start_date DESC");
        $types = $db->fetchAll("SELECT id, type_code, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");

        $this->view('subscriptions/bulk_renew', [
            'title'       => lang('subs.bulk_renewal'),
            'breadcrumbs' => [
                ['label' => lang('subs.subscriptions'), 'url' => '/subscriptions'],
                ['label' => lang('subs.bulk_renewal')],
            ],
            'periods' => $periods,
            'types'   => $types,
        ]);
    }

    public function periods(Request $request): void
    {
        $this->view('periods/index', [
            'title'       => lang('subs.subscription_periods'),
            'breadcrumbs' => [
                ['label' => lang('subs.subscriptions'), 'url' => '/subscriptions'],
                ['label' => lang('subs.subscription_periods')],
            ],
        ]);
    }
}