<?php

class ReportSeeder
{
    public static function seed(Database $db): void
    {
        $existing = (int) $db->fetchColumn("SELECT COUNT(*) FROM report_definitions");
        if ($existing > 0) return;

        $reports = self::getReportDefinitions();

        foreach ($reports as $rpt) {
            $params = $rpt['_params'] ?? [];
            unset($rpt['_params']);

            $reportId = $db->insert('report_definitions', $rpt);

            foreach ($params as $i => $p) {
                $p['report_id'] = $reportId;
                $p['sort_order'] = $i + 1;
                $db->insert('report_parameters', $p);
            }
        }
    }

    private static function getReportDefinitions(): array
    {
        return [
            // ── 1. Members Report ──
            [
                'report_code'    => 'RPT_MEMBERS',
                'report_name_en' => 'Members Report',
                'report_name_ar' => 'تقرير الأعضاء',
                'category'       => 'membership',
                'description_en' => 'Comprehensive member listing with flexible filters',
                'description_ar' => 'قائمة شاملة بالأعضاء مع فلاتر مرنة',
                'query_template' => "SELECT m.id, m.membership_number, m.full_name_en, m.full_name_ar, m.national_id, m.gender, m.primary_phone, m.primary_email, m.status, m.join_date, m.activation_date, m.outstanding_balance, mt.name_en AS membership_type_en, mt.name_ar AS membership_type_ar, n.name_en AS nationality_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 WHERE m.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'membership_number', 'label' => 'Membership #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Name (EN)', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'full_name_ar', 'label' => 'Name (AR)', 'db' => 'm.full_name_ar', 'searchable' => true],
                    ['key' => 'national_id', 'label' => 'National ID', 'db' => 'm.national_id', 'searchable' => true],
                    ['key' => 'primary_phone', 'label' => 'Phone', 'db' => 'm.primary_phone', 'searchable' => true],
                    ['key' => 'membership_type_en', 'label' => 'Type', 'db' => 'mt.name_en', 'searchable' => true],
                    ['key' => 'status', 'label' => 'Status', 'db' => 'm.status', 'searchable' => true],
                    ['key' => 'join_date', 'label' => 'Join Date', 'db' => 'm.join_date', 'searchable' => false],
                    ['key' => 'outstanding_balance', 'label' => 'Balance', 'db' => 'm.outstanding_balance', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'status', 'column' => 'm.status', 'operator' => '='],
                    ['name' => 'membership_type_id', 'column' => 'm.membership_type_id', 'operator' => '='],
                    ['name' => 'gender', 'column' => 'm.gender', 'operator' => '='],
                    ['name' => 'date_from', 'column' => 'm.join_date', 'operator' => '>='],
                    ['name' => 'date_to', 'column' => 'm.join_date', 'operator' => '<='],
                    ['name' => 'search_text', 'column' => 'm.full_name_en,m.full_name_ar,m.membership_number,m.national_id', 'operator' => 'LIKE'],
                ]),
                'default_sort'    => 'm.membership_number ASC',
                'chart_type'      => null,
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'status', 'parameter_label_en' => 'Status', 'parameter_label_ar' => 'الحالة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'active', 'label' => 'Active'],['value' => 'suspended', 'label' => 'Suspended'],['value' => 'frozen', 'label' => 'Frozen'],['value' => 'terminated', 'label' => 'Terminated']]), 'is_required' => 0],
                    ['parameter_name' => 'membership_type_id', 'parameter_label_en' => 'Membership Type', 'parameter_label_ar' => 'نوع العضوية', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'membership_types']), 'is_required' => 0],
                    ['parameter_name' => 'gender', 'parameter_label_en' => 'Gender', 'parameter_label_ar' => 'الجنس', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'male', 'label' => 'Male'],['value' => 'female', 'label' => 'Female']]), 'is_required' => 0],
                    ['parameter_name' => 'date_from', 'parameter_label_en' => 'Join Date From', 'parameter_label_ar' => 'تاريخ الانضمام من', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'date_to', 'parameter_label_en' => 'Join Date To', 'parameter_label_ar' => 'تاريخ الانضمام إلى', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'search_text', 'parameter_label_en' => 'Search', 'parameter_label_ar' => 'بحث', 'parameter_type' => 'text', 'is_required' => 0],
                ],
            ],

            // ── 2. Financial Summary ──
            [
                'report_code'    => 'RPT_FINANCIAL',
                'report_name_en' => 'Financial Summary',
                'report_name_ar' => 'الملخص المالي',
                'category'       => 'financial',
                'description_en' => 'Revenue summary by payment method and fee category',
                'description_ar' => 'ملخص الإيرادات حسب طريقة الدفع وفئة الرسوم',
                'query_template' => "SELECT p.payment_date, pm.name_en AS payment_method, p.amount, p.status, m.membership_number, m.full_name_en FROM payments p LEFT JOIN payment_methods pm ON p.payment_method_id = pm.id LEFT JOIN members m ON p.member_id = m.id WHERE p.status = 'completed' AND p.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'payment_date', 'label' => 'Date', 'db' => 'p.payment_date', 'searchable' => false],
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Member', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'payment_method', 'label' => 'Method', 'db' => 'pm.name_en', 'searchable' => true],
                    ['key' => 'amount', 'label' => 'Amount', 'db' => 'p.amount', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'date_from', 'column' => 'p.payment_date', 'operator' => '>='],
                    ['name' => 'date_to', 'column' => 'p.payment_date', 'operator' => '<='],
                    ['name' => 'payment_method_id', 'column' => 'p.payment_method_id', 'operator' => '='],
                ]),
                'default_sort'    => 'p.payment_date DESC',
                'chart_type'      => 'bar',
                'chart_config'    => json_encode(['label_column' => 'payment_date', 'value_column' => 'amount', 'group_by' => 'payment_method']),
                'print_layout'    => 'portrait',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'date_from', 'parameter_label_en' => 'Date From', 'parameter_label_ar' => 'من تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'date_to', 'parameter_label_en' => 'Date To', 'parameter_label_ar' => 'إلى تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'payment_method_id', 'parameter_label_en' => 'Payment Method', 'parameter_label_ar' => 'طريقة الدفع', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'payment_methods']), 'is_required' => 0],
                ],
            ],

            // ── 3. Outstanding Balances ──
            [
                'report_code'    => 'RPT_OUTSTANDING',
                'report_name_en' => 'Outstanding Balances',
                'report_name_ar' => 'الأرصدة المستحقة',
                'category'       => 'financial',
                'description_en' => 'Members with outstanding balances and aging analysis',
                'description_ar' => 'الأعضاء ذوو الأرصدة المستحقة وتحليل التقادم',
                'query_template' => "SELECT mb.member_id, m.membership_number, m.full_name_en, m.full_name_ar, m.primary_phone, mt.name_en AS membership_type, mb.total_invoiced, mb.total_paid, mb.outstanding_balance, mb.overdue_amount, mb.oldest_overdue_date, mb.last_payment_date FROM member_balances mb JOIN members m ON mb.member_id = m.id LEFT JOIN membership_types mt ON m.membership_type_id = mt.id WHERE m.is_deleted = 0 AND mb.outstanding_balance > 0",
                'default_columns' => json_encode([
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Name', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'primary_phone', 'label' => 'Phone', 'db' => 'm.primary_phone', 'searchable' => true],
                    ['key' => 'membership_type', 'label' => 'Type', 'db' => 'mt.name_en', 'searchable' => true],
                    ['key' => 'total_invoiced', 'label' => 'Invoiced', 'db' => 'mb.total_invoiced', 'searchable' => false],
                    ['key' => 'total_paid', 'label' => 'Paid', 'db' => 'mb.total_paid', 'searchable' => false],
                    ['key' => 'outstanding_balance', 'label' => 'Outstanding', 'db' => 'mb.outstanding_balance', 'searchable' => false],
                    ['key' => 'overdue_amount', 'label' => 'Overdue', 'db' => 'mb.overdue_amount', 'searchable' => false],
                    ['key' => 'oldest_overdue_date', 'label' => 'Oldest Overdue', 'db' => 'mb.oldest_overdue_date', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'min_balance', 'column' => 'mb.outstanding_balance', 'operator' => '>='],
                    ['name' => 'membership_type_id', 'column' => 'm.membership_type_id', 'operator' => '='],
                ]),
                'default_sort'    => 'mb.outstanding_balance DESC',
                'chart_type'      => null,
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'min_balance', 'parameter_label_en' => 'Min Balance', 'parameter_label_ar' => 'الحد الأدنى', 'parameter_type' => 'number', 'is_required' => 0],
                    ['parameter_name' => 'membership_type_id', 'parameter_label_en' => 'Membership Type', 'parameter_label_ar' => 'نوع العضوية', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'membership_types']), 'is_required' => 0],
                ],
            ],

            // ── 4. Subscription Status ──
            [
                'report_code'    => 'RPT_SUBSCRIPTIONS',
                'report_name_en' => 'Subscription Status',
                'report_name_ar' => 'حالة الاشتراكات',
                'category'       => 'membership',
                'description_en' => 'Active, expired, and expiring subscriptions',
                'description_ar' => 'الاشتراكات النشطة والمنتهية والقريبة الانتهاء',
                'query_template' => "SELECT ms.id, m.membership_number, m.full_name_en, mt.name_en AS membership_type, sp.name_en AS period_name, ms.start_date, ms.end_date, ms.net_amount, ms.paid_amount, ms.balance_due, ms.status, DATEDIFF(ms.end_date, CURDATE()) AS days_until_expiry FROM member_subscriptions ms JOIN members m ON ms.member_id = m.id LEFT JOIN membership_types mt ON ms.membership_type_id = mt.id LEFT JOIN subscription_periods sp ON ms.subscription_period_id = sp.id WHERE m.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Name', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'membership_type', 'label' => 'Type', 'db' => 'mt.name_en', 'searchable' => true],
                    ['key' => 'start_date', 'label' => 'Start', 'db' => 'ms.start_date', 'searchable' => false],
                    ['key' => 'end_date', 'label' => 'End', 'db' => 'ms.end_date', 'searchable' => false],
                    ['key' => 'net_amount', 'label' => 'Amount', 'db' => 'ms.net_amount', 'searchable' => false],
                    ['key' => 'balance_due', 'label' => 'Due', 'db' => 'ms.balance_due', 'searchable' => false],
                    ['key' => 'status', 'label' => 'Status', 'db' => 'ms.status', 'searchable' => true],
                    ['key' => 'days_until_expiry', 'label' => 'Days Left', 'db' => 'days_until_expiry', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'sub_status', 'column' => 'ms.status', 'operator' => '='],
                    ['name' => 'membership_type_id', 'column' => 'ms.membership_type_id', 'operator' => '='],
                ]),
                'default_sort'    => 'ms.end_date ASC',
                'chart_type'      => 'doughnut',
                'chart_config'    => json_encode(['group_column' => 'status']),
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'sub_status', 'parameter_label_en' => 'Status', 'parameter_label_ar' => 'الحالة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'active', 'label' => 'Active'],['value' => 'expired', 'label' => 'Expired'],['value' => 'pending', 'label' => 'Pending'],['value' => 'cancelled', 'label' => 'Cancelled']]), 'is_required' => 0],
                    ['parameter_name' => 'membership_type_id', 'parameter_label_en' => 'Membership Type', 'parameter_label_ar' => 'نوع العضوية', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'membership_types']), 'is_required' => 0],
                ],
            ],

            // ── 5. Violations Report ──
            [
                'report_code'    => 'RPT_VIOLATIONS',
                'report_name_en' => 'Violations Report',
                'report_name_ar' => 'تقرير المخالفات',
                'category'       => 'operational',
                'description_en' => 'Violations by type, severity, and status',
                'description_ar' => 'المخالفات حسب النوع والشدة والحالة',
                'query_template' => "SELECT v.id, v.violation_number, m.membership_number, m.full_name_en, vt.name_en AS violation_type, vc.name_en AS category, vt.severity, v.incident_date, v.status, v.occurrence_number FROM violations v JOIN members m ON v.member_id = m.id JOIN violation_types vt ON v.violation_type_id = vt.id JOIN violation_categories vc ON vt.category_id = vc.id WHERE 1=1",
                'default_columns' => json_encode([
                    ['key' => 'violation_number', 'label' => 'Violation #', 'db' => 'v.violation_number', 'searchable' => true],
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Member', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'violation_type', 'label' => 'Type', 'db' => 'vt.name_en', 'searchable' => true],
                    ['key' => 'category', 'label' => 'Category', 'db' => 'vc.name_en', 'searchable' => true],
                    ['key' => 'severity', 'label' => 'Severity', 'db' => 'vt.severity', 'searchable' => true],
                    ['key' => 'incident_date', 'label' => 'Date', 'db' => 'v.incident_date', 'searchable' => false],
                    ['key' => 'status', 'label' => 'Status', 'db' => 'v.status', 'searchable' => true],
                    ['key' => 'occurrence_number', 'label' => 'Occurrence', 'db' => 'v.occurrence_number', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'vio_status', 'column' => 'v.status', 'operator' => '='],
                    ['name' => 'severity', 'column' => 'vt.severity', 'operator' => '='],
                    ['name' => 'date_from', 'column' => 'v.incident_date', 'operator' => '>='],
                    ['name' => 'date_to', 'column' => 'v.incident_date', 'operator' => '<='],
                ]),
                'default_sort'    => 'v.incident_date DESC',
                'chart_type'      => 'bar',
                'chart_config'    => json_encode(['group_column' => 'severity']),
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'vio_status', 'parameter_label_en' => 'Status', 'parameter_label_ar' => 'الحالة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'reported', 'label' => 'Reported'],['value' => 'under_review', 'label' => 'Under Review'],['value' => 'confirmed', 'label' => 'Confirmed'],['value' => 'closed', 'label' => 'Closed'],['value' => 'dismissed', 'label' => 'Dismissed']]), 'is_required' => 0],
                    ['parameter_name' => 'severity', 'parameter_label_en' => 'Severity', 'parameter_label_ar' => 'الشدة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'minor', 'label' => 'Minor'],['value' => 'moderate', 'label' => 'Moderate'],['value' => 'major', 'label' => 'Major'],['value' => 'critical', 'label' => 'Critical']]), 'is_required' => 0],
                    ['parameter_name' => 'date_from', 'parameter_label_en' => 'Date From', 'parameter_label_ar' => 'من تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'date_to', 'parameter_label_en' => 'Date To', 'parameter_label_ar' => 'إلى تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                ],
            ],

            // ── 6. Complaint Analysis ──
            [
                'report_code'    => 'RPT_COMPLAINTS',
                'report_name_en' => 'Complaint Analysis',
                'report_name_ar' => 'تحليل الشكاوى',
                'category'       => 'operational',
                'description_en' => 'Complaints by category, priority, and SLA compliance',
                'description_ar' => 'الشكاوى حسب الفئة والأولوية والالتزام بالمستوى',
                'query_template' => "SELECT c.id, c.complaint_number, c.subject, c.status, cc.name_en AS category, cp.name_en AS priority, c.sla_deadline, c.sla_breached, m.membership_number, m.full_name_en, c.created_at FROM complaints c JOIN complaint_categories cc ON c.category_id = cc.id JOIN complaint_priorities cp ON c.priority_id = cp.id LEFT JOIN members m ON c.member_id = m.id WHERE 1=1",
                'default_columns' => json_encode([
                    ['key' => 'complaint_number', 'label' => 'Complaint #', 'db' => 'c.complaint_number', 'searchable' => true],
                    ['key' => 'subject', 'label' => 'Subject', 'db' => 'c.subject', 'searchable' => true],
                    ['key' => 'category', 'label' => 'Category', 'db' => 'cc.name_en', 'searchable' => true],
                    ['key' => 'priority', 'label' => 'Priority', 'db' => 'cp.name_en', 'searchable' => true],
                    ['key' => 'status', 'label' => 'Status', 'db' => 'c.status', 'searchable' => true],
                    ['key' => 'sla_breached', 'label' => 'SLA Breached', 'db' => 'c.sla_breached', 'searchable' => false],
                    ['key' => 'membership_number', 'label' => 'Member', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'created_at', 'label' => 'Created', 'db' => 'c.created_at', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'compl_status', 'column' => 'c.status', 'operator' => '='],
                    ['name' => 'category_id', 'column' => 'c.category_id', 'operator' => '='],
                    ['name' => 'sla_breached', 'column' => 'c.sla_breached', 'operator' => '='],
                ]),
                'default_sort'    => 'c.created_at DESC',
                'chart_type'      => 'pie',
                'chart_config'    => json_encode(['group_column' => 'status']),
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'compl_status', 'parameter_label_en' => 'Status', 'parameter_label_ar' => 'الحالة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'submitted', 'label' => 'Submitted'],['value' => 'in_progress', 'label' => 'In Progress'],['value' => 'resolved', 'label' => 'Resolved'],['value' => 'closed', 'label' => 'Closed']]), 'is_required' => 0],
                    ['parameter_name' => 'category_id', 'parameter_label_en' => 'Category', 'parameter_label_ar' => 'الفئة', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'complaint_categories']), 'is_required' => 0],
                    ['parameter_name' => 'sla_breached', 'parameter_label_en' => 'SLA Breached Only', 'parameter_label_ar' => 'المتجاوزة فقط', 'parameter_type' => 'boolean', 'is_required' => 0],
                ],
            ],

            // ── 7. Overdue Invoices ──
            [
                'report_code'    => 'RPT_OVERDUE',
                'report_name_en' => 'Overdue Invoices',
                'report_name_ar' => 'الفواتير المتأخرة',
                'category'       => 'financial',
                'description_en' => 'Invoices past due date with aging analysis',
                'description_ar' => 'الفواتير المتجاوزة لتاريخ الاستحقاق',
                'query_template' => "SELECT i.id, i.invoice_number, i.invoice_type, i.invoice_date, i.due_date, i.total_amount, i.paid_amount, i.balance_due, DATEDIFF(CURDATE(), i.due_date) AS days_overdue, m.membership_number, m.full_name_en, m.primary_phone, mt.name_en AS membership_type FROM invoices i JOIN members m ON i.member_id = m.id LEFT JOIN membership_types mt ON m.membership_type_id = mt.id WHERE i.status IN ('issued','partially_paid','overdue') AND i.due_date < CURDATE() AND i.balance_due > 0 AND i.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'invoice_number', 'label' => 'Invoice #', 'db' => 'i.invoice_number', 'searchable' => true],
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Name', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'invoice_type', 'label' => 'Type', 'db' => 'i.invoice_type', 'searchable' => true],
                    ['key' => 'due_date', 'label' => 'Due Date', 'db' => 'i.due_date', 'searchable' => false],
                    ['key' => 'total_amount', 'label' => 'Total', 'db' => 'i.total_amount', 'searchable' => false],
                    ['key' => 'balance_due', 'label' => 'Balance', 'db' => 'i.balance_due', 'searchable' => false],
                    ['key' => 'days_overdue', 'label' => 'Days Overdue', 'db' => 'days_overdue', 'searchable' => false],
                    ['key' => 'primary_phone', 'label' => 'Phone', 'db' => 'm.primary_phone', 'searchable' => true],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'invoice_type', 'column' => 'i.invoice_type', 'operator' => '='],
                    ['name' => 'min_overdue_days', 'column' => 'DATEDIFF(CURDATE(), i.due_date)', 'operator' => '>='],
                ]),
                'default_sort'    => 'days_overdue DESC',
                'chart_type'      => null,
                'print_layout'    => 'landscape',
                'is_schedulable'  => 1,
                'is_active'       => 1,
                'version'         => 1,
                '_params' => [
                    ['parameter_name' => 'invoice_type', 'parameter_label_en' => 'Invoice Type', 'parameter_label_ar' => 'نوع الفاتورة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'registration', 'label' => 'Registration'],['value' => 'subscription', 'label' => 'Subscription'],['value' => 'renewal', 'label' => 'Renewal'],['value' => 'penalty', 'label' => 'Penalty']]), 'is_required' => 0],
                    ['parameter_name' => 'min_overdue_days', 'parameter_label_en' => 'Min Overdue Days', 'parameter_label_ar' => 'أقل أيام تأخير', 'parameter_type' => 'number', 'is_required' => 0],
                ],
            ],

            // ── 8. Collection Report ──
            [
                'report_code'    => 'RPT_COLLECTIONS',
                'report_name_en' => 'Collection Report',
                'report_name_ar' => 'تقرير التحصيل',
                'category'       => 'financial',
                'description_en' => 'Payments collected by cashier, method, and period',
                'description_ar' => 'المدفوعات المحصلة حسب الموظف والطريقة والفترة',
                'query_template' => "SELECT p.payment_number, p.payment_date, p.amount, pm.name_en AS method, m.membership_number, m.full_name_en, u.full_name_en AS collected_by_name FROM payments p LEFT JOIN payment_methods pm ON p.payment_method_id = pm.id LEFT JOIN members m ON p.member_id = m.id LEFT JOIN auth_users u ON p.collected_by = u.id WHERE p.status = 'completed' AND p.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'payment_number', 'label' => 'Payment #', 'db' => 'p.payment_number', 'searchable' => true],
                    ['key' => 'payment_date', 'label' => 'Date', 'db' => 'p.payment_date', 'searchable' => false],
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Member', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'method', 'label' => 'Method', 'db' => 'pm.name_en', 'searchable' => true],
                    ['key' => 'amount', 'label' => 'Amount', 'db' => 'p.amount', 'searchable' => false],
                    ['key' => 'collected_by_name', 'label' => 'Collected By', 'db' => 'u.full_name_en', 'searchable' => true],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'date_from', 'column' => 'p.payment_date', 'operator' => '>='],
                    ['name' => 'date_to', 'column' => 'p.payment_date', 'operator' => '<='],
                    ['name' => 'payment_method_id', 'column' => 'p.payment_method_id', 'operator' => '='],
                    ['name' => 'collected_by', 'column' => 'p.collected_by', 'operator' => '='],
                ]),
                'default_sort' => 'p.payment_date DESC',
                'chart_type'   => null,
                'print_layout' => 'landscape',
                'is_schedulable' => 1,
                'is_active' => 1,
                'version' => 1,
                '_params' => [
                    ['parameter_name' => 'date_from', 'parameter_label_en' => 'Date From', 'parameter_label_ar' => 'من تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'date_to', 'parameter_label_en' => 'Date To', 'parameter_label_ar' => 'إلى تاريخ', 'parameter_type' => 'date', 'is_required' => 0],
                    ['parameter_name' => 'payment_method_id', 'parameter_label_en' => 'Method', 'parameter_label_ar' => 'طريقة الدفع', 'parameter_type' => 'select', 'options' => json_encode(['source' => 'payment_methods']), 'is_required' => 0],
                ],
            ],

            // ── 9. Expiring Cards ──
            [
                'report_code'    => 'RPT_EXPIRING_CARDS',
                'report_name_en' => 'Expiring Cards',
                'report_name_ar' => 'البطاقات المنتهية',
                'category'       => 'operational',
                'description_en' => 'Cards expiring within a configurable time window',
                'description_ar' => 'البطاقات المنتهية خلال فترة قابلة للتحديد',
                'query_template' => "SELECT mc.card_number, ct.name_en AS card_type, mc.expiry_date, DATEDIFF(mc.expiry_date, CURDATE()) AS days_until_expiry, m.membership_number, m.full_name_en, m.primary_phone 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 mc.status = 'active' AND mc.expiry_date IS NOT NULL AND mc.expiry_date >= CURDATE()",
                'default_columns' => json_encode([
                    ['key' => 'card_number', 'label' => 'Card #', 'db' => 'mc.card_number', 'searchable' => true],
                    ['key' => 'card_type', 'label' => 'Type', 'db' => 'ct.name_en', 'searchable' => true],
                    ['key' => 'membership_number', 'label' => 'Member #', 'db' => 'm.membership_number', 'searchable' => true],
                    ['key' => 'full_name_en', 'label' => 'Name', 'db' => 'm.full_name_en', 'searchable' => true],
                    ['key' => 'expiry_date', 'label' => 'Expiry', 'db' => 'mc.expiry_date', 'searchable' => false],
                    ['key' => 'days_until_expiry', 'label' => 'Days Left', 'db' => 'days_until_expiry', 'searchable' => false],
                    ['key' => 'primary_phone', 'label' => 'Phone', 'db' => 'm.primary_phone', 'searchable' => true],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'max_days', 'column' => 'DATEDIFF(mc.expiry_date, CURDATE())', 'operator' => '<='],
                ]),
                'default_sort' => 'mc.expiry_date ASC',
                'chart_type' => null,
                'print_layout' => 'portrait',
                'is_schedulable' => 1,
                'is_active' => 1,
                'version' => 1,
                '_params' => [
                    ['parameter_name' => 'max_days', 'parameter_label_en' => 'Expiring Within (days)', 'parameter_label_ar' => 'تنتهي خلال (أيام)', 'parameter_type' => 'number', 'default_value' => '60', 'is_required' => 0],
                ],
            ],

            // ── 10. Membership Distribution ──
            [
                'report_code'    => 'RPT_DISTRIBUTION',
                'report_name_en' => 'Membership Distribution',
                'report_name_ar' => 'توزيع العضويات',
                'category'       => 'membership',
                'description_en' => 'Members per membership type with counts',
                'description_ar' => 'توزيع الأعضاء حسب أنواع العضوية',
                'query_template' => "SELECT mt.name_en AS membership_type, mt.name_ar AS membership_type_ar, m.status, COUNT(*) AS member_count FROM members m JOIN membership_types mt ON m.membership_type_id = mt.id WHERE m.is_deleted = 0",
                'default_columns' => json_encode([
                    ['key' => 'membership_type', 'label' => 'Type', 'db' => 'mt.name_en', 'searchable' => true],
                    ['key' => 'status', 'label' => 'Status', 'db' => 'm.status', 'searchable' => true],
                    ['key' => 'member_count', 'label' => 'Count', 'db' => 'member_count', 'searchable' => false],
                ]),
                'available_filters' => json_encode([
                    ['name' => 'status', 'column' => 'm.status', 'operator' => '='],
                ]),
                'available_grouping' => json_encode(['mt.name_en', 'm.status']),
                'default_sort' => 'member_count DESC',
                'chart_type' => 'doughnut',
                'chart_config' => json_encode(['label_column' => 'membership_type', 'value_column' => 'member_count']),
                'print_layout' => 'portrait',
                'is_schedulable' => 1,
                'is_active' => 1,
                'version' => 1,
                '_params' => [
                    ['parameter_name' => 'status', 'parameter_label_en' => 'Status', 'parameter_label_ar' => 'الحالة', 'parameter_type' => 'select', 'options' => json_encode([['value' => '', 'label' => 'All'],['value' => 'active', 'label' => 'Active'],['value' => 'suspended', 'label' => 'Suspended']]), 'is_required' => 0],
                ],
            ],
        ];
    }
}