<?php

class CustomReportApiController extends Controller
{
    private static array $allowedSources = [
        'vw_active_members'             => 'Active Members',
        'vw_pending_applications'       => 'Pending Applications',
        'vw_overdue_invoices'           => 'Overdue Invoices',
        'vw_member_balance_summary'     => 'Member Balances',
        'vw_member_family_overview'     => 'Family Overview',
        'vw_subscription_renewal_status'=> 'Subscription Status',
        'vw_active_freezes'             => 'Active Freezes',
        'vw_violation_summary'          => 'Violations',
        'vw_complaint_dashboard'        => 'Complaints',
        'vw_installment_overview'       => 'Installments',
        'vw_expiring_cards'             => 'Expiring Cards',
        'vw_expiring_documents'         => 'Expiring Documents',
        'vw_sport_class_summary'        => 'Sport Classes',
        'vw_waiting_list_status'        => 'Waiting List',
    ];

    public function sources(Request $request): void
    {
        $result = [];
        foreach (self::$allowedSources as $view => $label) {
            $result[] = ['value' => $view, 'label' => $label];
        }
        $this->json(['success' => true, 'data' => $result]);
    }

    public function columns(Request $request): void
    {
        $db = $this->db();
        $source = $request->post('source', '');

        if (!isset(self::$allowedSources[$source])) {
            $this->json(['success' => false, 'message' => 'Invalid source'], 400);
            return;
        }

        try {
            $cols = $db->fetchAll("DESCRIBE `{$source}`");
            $result = [];
            foreach ($cols as $col) {
                $result[] = [
                    'name'    => $col['Field'],
                    'type'    => $col['Type'],
                    'null'    => $col['Null'],
                    'default' => $col['Default'],
                ];
            }
            $this->json(['success' => true, 'data' => $result]);
        } catch (\Exception $e) {
            $row = $db->fetchOne("SELECT * FROM `{$source}` LIMIT 1");
            if ($row) {
                $result = [];
                foreach (array_keys($row) as $colName) {
                    $result[] = ['name' => $colName, 'type' => 'varchar', 'null' => 'YES', 'default' => null];
                }
                $this->json(['success' => true, 'data' => $result]);
            } else {
                $this->json(['success' => true, 'data' => []]);
            }
        }
    }

    public function preview(Request $request): void
    {
        $db = $this->db();
        $source  = $request->post('source', '');
        $columns = $request->post('columns', []);
        $filters = $request->post('filters', []);
        $sortBy  = $request->post('sort_by', '');
        $sortDir = strtoupper($request->post('sort_dir', 'ASC')) === 'DESC' ? 'DESC' : 'ASC';

        if (!isset(self::$allowedSources[$source])) {
            $this->json(['success' => false, 'message' => 'Invalid source'], 400);
            return;
        }

        if (!is_array($columns) || empty($columns)) {
            $this->json(['success' => false, 'message' => 'Select at least one column'], 422);
            return;
        }

        $safeCols = [];
        foreach ($columns as $col) {
            $col = preg_replace('/[^a-zA-Z0-9_]/', '', $col);
            if ($col) $safeCols[] = "`{$col}`";
        }

        if (empty($safeCols)) {
            $this->json(['success' => false, 'message' => 'No valid columns'], 422);
            return;
        }

        $sql = "SELECT " . implode(', ', $safeCols) . " FROM `{$source}` WHERE 1=1";
        $bindings = [];

        if (is_array($filters)) {
            foreach ($filters as $f) {
                $col = preg_replace('/[^a-zA-Z0-9_]/', '', $f['column'] ?? '');
                $op = $f['operator'] ?? '=';
                $val = $f['value'] ?? '';
                if (!$col || $val === '') continue;

                if (!in_array($op, ['=', '!=', '>', '<', '>=', '<=', 'LIKE'])) $op = '=';

                if ($op === 'LIKE') {
                    $sql .= " AND `{$col}` LIKE ?";
                    $bindings[] = "%{$val}%";
                } else {
                    $sql .= " AND `{$col}` {$op} ?";
                    $bindings[] = $val;
                }
            }
        }

        if ($sortBy) {
            $sortBy = preg_replace('/[^a-zA-Z0-9_]/', '', $sortBy);
            if ($sortBy) {
                $sql .= " ORDER BY `{$sortBy}` {$sortDir}";
            }
        }

        $sql .= " LIMIT 100";

        $rows = $db->fetchAll($sql, $bindings);

        $this->json(['success' => true, 'data' => $rows, 'total' => count($rows), 'columns' => $columns]);
    }

    public function save(Request $request): void
    {
        $db = $this->db();

        $validator = new Validator($request->all(), [
            'report_name_en' => 'required|string|max:300',
            'report_name_ar' => 'nullable|string|max:300',
            'source'         => 'required|string',
            'columns'        => 'required',
            'description_en' => 'nullable|string',
        ]);

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

        $data = $validator->validated();
        $source = $data['source'];

        if (!isset(self::$allowedSources[$source])) {
            $this->json(['success' => false, 'message' => 'Invalid source'], 400);
            return;
        }

        $columns = $data['columns'];
        if (is_string($columns)) {
            $columns = json_decode($columns, true) ?: [];
        }

        $safeCols = [];
        $colDefs = [];
        foreach ($columns as $col) {
            $colName = preg_replace('/[^a-zA-Z0-9_]/', '', is_array($col) ? ($col['name'] ?? '') : $col);
            if ($colName) {
                $safeCols[] = "`{$colName}`";
                $colDefs[] = ['key' => $colName, 'label' => ucfirst(str_replace('_', ' ', $colName)), 'db' => $colName, 'searchable' => true];
            }
        }

        $queryTemplate = "SELECT " . implode(', ', $safeCols) . " FROM `{$source}` WHERE 1=1";

        $code = 'CUSTOM_' . strtoupper(substr(md5(time() . Auth::id()), 0, 8));

        $id = $db->insert('report_definitions', [
            'report_code'     => $code,
            'report_name_en'  => $data['report_name_en'],
            'report_name_ar'  => $data['report_name_ar'] ?? $data['report_name_en'],
            'category'        => 'custom',
            'description_en'  => $data['description_en'] ?? '',
            'description_ar'  => '',
            'query_template'  => $queryTemplate,
            'default_columns' => json_encode($colDefs),
            'available_filters' => json_encode([]),
            'default_sort'    => '',
            'print_layout'    => 'landscape',
            'is_schedulable'  => 0,
            'is_active'       => 1,
            'version'         => 1,
            'created_by'      => Auth::id(),
        ]);

        $this->audit('create', 'report_definitions', $id, "Created custom report: {$data['report_name_en']}");
        $this->json(['success' => true, 'message' => 'Custom report saved', 'id' => $id, 'redirect' => "/reports/{$id}"]);
    }
}