<?php
class CardApiController extends Controller
{
    public function datatable(Request $request): void
    {
        $db = $this->db();
        $draw   = (int)$request->get('draw', 1);
        $start  = (int)$request->get('start', 0);
        $length = (int)$request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';

        $statusFilter = $request->get('status', '');
        $typeFilter   = $request->get('card_type_id', '');

        $where = "1=1";
        $params = [];

        if ($statusFilter) {
            $where .= " AND mc.status = ?";
            $params[] = $statusFilter;
        }
        if ($typeFilter) {
            $where .= " AND mc.card_type_id = ?";
            $params[] = (int)$typeFilter;
        }
        if ($search) {
            $where .= " AND (mc.card_number LIKE ? OR m.full_name_en LIKE ? OR m.full_name_ar LIKE ? OR m.membership_number LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s, $s]);
        }

        $totalAll = (int)$db->fetchColumn("SELECT COUNT(*) FROM member_cards mc LEFT JOIN members m ON mc.member_id = m.id");
        $totalFiltered = (int)$db->fetchColumn(
            "SELECT COUNT(*) FROM member_cards mc LEFT JOIN members m ON mc.member_id = m.id WHERE {$where}", $params
        );

        $orderCol = $request->get('order')[0]['column'] ?? 0;
        $orderDir = ($request->get('order')[0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';
        $cols = ['mc.id', 'mc.card_number', 'm.full_name_en', 'ct.name_en', 'mc.issue_date', 'mc.expiry_date', 'mc.status'];
        $orderBy = ($cols[$orderCol] ?? 'mc.id') . ' ' . $orderDir;

        $rows = $db->fetchAll(
            "SELECT mc.id, mc.card_number, mc.status, mc.issue_date, mc.expiry_date,
                    ct.name_en AS type_name_en, ct.name_ar AS type_name_ar,
                    m.membership_number, m.full_name_en AS member_name_en, m.full_name_ar AS member_name_ar
             FROM member_cards mc
             JOIN card_types ct ON mc.card_type_id = ct.id
             LEFT JOIN members m ON mc.member_id = m.id
             WHERE {$where}
             ORDER BY {$orderBy}
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

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

    public function requestsDatatable(Request $request): void
    {
        $db = $this->db();
        $draw   = (int)$request->get('draw', 1);
        $start  = (int)$request->get('start', 0);
        $length = (int)$request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $statusFilter = $request->get('status', '');

        $where = "1=1";
        $params = [];

        if ($statusFilter) {
            $where .= " AND cr.status = ?";
            $params[] = $statusFilter;
        }
        if ($search) {
            $where .= " AND (cr.request_number LIKE ? OR m.full_name_en LIKE ? OR m.full_name_ar LIKE ? OR m.membership_number LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s, $s]);
        }

        $totalAll = (int)$db->fetchColumn("SELECT COUNT(*) FROM card_requests cr LEFT JOIN members m ON cr.member_id = m.id");
        $totalFiltered = (int)$db->fetchColumn(
            "SELECT COUNT(*) FROM card_requests cr LEFT JOIN members m ON cr.member_id = m.id WHERE {$where}", $params
        );

        $rows = $db->fetchAll(
            "SELECT cr.id, cr.request_number, cr.request_type, cr.status, cr.fee_amount, cr.fee_paid, cr.created_at,
                    ct.name_en AS type_name_en, ct.name_ar AS type_name_ar,
                    m.membership_number, m.full_name_en AS member_name_en, m.full_name_ar AS member_name_ar
             FROM card_requests cr
             JOIN card_types ct ON cr.card_type_id = ct.id
             LEFT JOIN members m ON cr.member_id = m.id
             WHERE {$where}
             ORDER BY cr.id DESC
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

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

    public function typesList(Request $request): void
    {
        $db = $this->db();
        $types = $db->fetchAll("SELECT id, type_code, name_en, name_ar, fee, replacement_fee, applicable_to, is_active FROM card_types ORDER BY name_en");
        $this->json(['success' => true, 'data' => $types]);
    }

    public function printDatatable(Request $request): void
    {
        $db = $this->db();
        $draw   = (int)$request->get('draw', 1);
        $start  = (int)$request->get('start', 0);
        $length = (int)$request->get('length', 25);

        $totalAll = (int)$db->fetchColumn("SELECT COUNT(*) FROM card_print_batches");

        $rows = $db->fetchAll(
            "SELECT cpb.*, u.full_name_en AS printed_by_name
             FROM card_print_batches cpb
             LEFT JOIN auth_users u ON cpb.printed_by = u.id
             ORDER BY cpb.id DESC
             LIMIT " . (int)$length . " OFFSET " . (int)$start
        );

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

    public function batchItems(Request $request): void
    {
        $batchId = (int)$request->routeParam('id');
        $db = $this->db();
        $items = $db->fetchAll(
            "SELECT cbi.*, mc.card_number, m.full_name_en AS member_name_en, m.full_name_ar AS member_name_ar, m.membership_number
             FROM card_print_batch_items cbi
             LEFT JOIN member_cards mc ON cbi.card_id = mc.id
             LEFT JOIN members m ON mc.member_id = m.id
             WHERE cbi.batch_id = ?
             ORDER BY cbi.id", [$batchId]
        );
        $this->json(['success' => true, 'data' => $items]);
    }
}