<?php

namespace App\Controllers\Chart;

use App\Controllers\BaseController;
use App\Libraries\AuditLogger;
use App\Libraries\ChartRenderer;
use App\Libraries\SavedQueryRunner;
use App\Models\ChartExportModel;
use App\Models\ChartModel;
use App\Models\DataSourceModel;
use App\Models\QueryVariableModel;
use App\Models\SavedQueryModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Throwable;

class ChartController extends BaseController
{
    public function index()
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $charts = (new ChartModel())->forWorkspace($workspaceId);

        return view('chart/index', [
            'title'  => 'Charts | Chart-Board',
            'charts' => $charts,
        ]);
    }

    public function create()
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $queries = (new SavedQueryModel())->forWorkspace($workspaceId);
        $preselectQueryId = (int) ($this->request->getGet('query') ?? 0);

        return view('chart/builder', [
            'title'            => 'Create Chart | Chart-Board',
            'mode'             => 'create',
            'chart'            => null,
            'queries'          => $queries,
            'preselectQueryId' => $preselectQueryId,
            'displayConfig'    => [],
        ]);
    }

    public function edit(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $chartModel = new ChartModel();
        $chart = $chartModel->where('workspace_id', $workspaceId)->find($id);

        if (! $chart) {
            return redirect()->to('/chart')->with('error', 'Chart not found.');
        }

        $queries = (new SavedQueryModel())->forWorkspace($workspaceId);
        $displayConfig = $this->decodeJson($chart['display_config'] ?? null);

        return view('chart/builder', [
            'title'            => 'Edit Chart | Chart-Board',
            'mode'             => 'edit',
            'chart'            => $chart,
            'queries'          => $queries,
            'preselectQueryId' => (int) ($chart['saved_query_id'] ?? 0),
            'displayConfig'    => $displayConfig,
        ]);
    }

    public function store()
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $userId = (int) $this->session->get('user_id');

        $rules = [
            'name'            => 'required|min_length[2]|max_length[200]',
            'saved_query_id'  => 'required|integer',
            'chart_type'      => 'required|in_list[bar,line,area,pie,donut,scatter,table,kpi_card,funnel,gauge,heatmap,combo,spline,stepline,radar,bubble,polar_area,ranked_progress]',
            'x_field'         => 'permit_empty|max_length[150]',
            'y_field'         => 'permit_empty|max_length[150]',
            'group_field'     => 'permit_empty|max_length[150]',
            'value_field'     => 'permit_empty|max_length[150]',
            'display_config'  => 'permit_empty',
            'refresh_interval'=> 'permit_empty|integer',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
        }

        $savedQueryId = (int) $this->request->getPost('saved_query_id');
        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);

        if (! $savedQuery) {
            return redirect()->back()->withInput()->with('error', 'Selected query was not found.');
        }

        $displayConfig = $this->sanitizeDisplayConfig($this->request->getPost('display_config'));

        $payload = [
            'workspace_id'     => $workspaceId,
            'data_source_id'   => (int) $savedQuery['data_source_id'],
            'saved_query_id'   => $savedQueryId,
            'name'             => strip_tags((string) $this->request->getPost('name')),
            'description'      => trim((string) $this->request->getPost('description')) ?: null,
            'chart_type'       => (string) $this->request->getPost('chart_type'),
            'query_type'       => (string) $savedQuery['query_type'],
            'raw_sql'          => null,
            'visual_config'    => null,
            'api_endpoint'     => null,
            'api_params'       => null,
            'response_path'    => null,
            'field_map'        => null,
            'x_field'          => trim((string) $this->request->getPost('x_field')) ?: null,
            'y_field'          => trim((string) $this->request->getPost('y_field')) ?: null,
            'group_field'      => trim((string) $this->request->getPost('group_field')) ?: null,
            'value_field'      => trim((string) $this->request->getPost('value_field')) ?: null,
            'display_config'   => $displayConfig !== [] ? json_encode($displayConfig, JSON_UNESCAPED_UNICODE) : null,
            'refresh_interval' => max(0, (int) $this->request->getPost('refresh_interval')),
            'cache_ttl'        => max(0, (int) $savedQuery['cache_ttl']),
            'is_public'        => 0,
            'public_token'     => null,
            'created_by'       => $userId,
        ];

        $chartModel = new ChartModel();
        $newId = (int) $chartModel->insert($payload, true);

        AuditLogger::log(
            'chart.created',
            'chart',
            $newId,
            null,
            [
                'name'       => $payload['name'],
                'chart_type' => $payload['chart_type'],
            ],
            $workspaceId,
            $userId
        );

        return redirect()->to('/chart/edit/' . $newId)->with('success', 'Chart saved.');
    }

    public function update(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $chartModel = new ChartModel();
        $existing = $chartModel->where('workspace_id', $workspaceId)->find($id);

        if (! $existing) {
            return redirect()->to('/chart')->with('error', 'Chart not found.');
        }

        $rules = [
            'name'            => 'required|min_length[2]|max_length[200]',
            'saved_query_id'  => 'required|integer',
            'chart_type'      => 'required|in_list[bar,line,area,pie,donut,scatter,table,kpi_card,funnel,gauge,heatmap,combo,spline,stepline,radar,bubble,polar_area,ranked_progress]',
            'x_field'         => 'permit_empty|max_length[150]',
            'y_field'         => 'permit_empty|max_length[150]',
            'group_field'     => 'permit_empty|max_length[150]',
            'value_field'     => 'permit_empty|max_length[150]',
            'display_config'  => 'permit_empty',
            'refresh_interval'=> 'permit_empty|integer',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
        }

        $savedQueryId = (int) $this->request->getPost('saved_query_id');
        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);

        if (! $savedQuery) {
            return redirect()->back()->withInput()->with('error', 'Selected query was not found.');
        }

        $displayConfig = $this->sanitizeDisplayConfig($this->request->getPost('display_config'));

        $payload = [
            'data_source_id'   => (int) $savedQuery['data_source_id'],
            'saved_query_id'   => $savedQueryId,
            'name'             => strip_tags((string) $this->request->getPost('name')),
            'description'      => trim((string) $this->request->getPost('description')) ?: null,
            'chart_type'       => (string) $this->request->getPost('chart_type'),
            'query_type'       => (string) $savedQuery['query_type'],
            'x_field'          => trim((string) $this->request->getPost('x_field')) ?: null,
            'y_field'          => trim((string) $this->request->getPost('y_field')) ?: null,
            'group_field'      => trim((string) $this->request->getPost('group_field')) ?: null,
            'value_field'      => trim((string) $this->request->getPost('value_field')) ?: null,
            'display_config'   => $displayConfig !== [] ? json_encode($displayConfig, JSON_UNESCAPED_UNICODE) : null,
            'refresh_interval' => max(0, (int) $this->request->getPost('refresh_interval')),
            'cache_ttl'        => max(0, (int) $savedQuery['cache_ttl']),
        ];

        $oldSnap = [
            'name'       => $existing['name'] ?? null,
            'chart_type' => $existing['chart_type'] ?? null,
            'saved_query_id' => $existing['saved_query_id'] ?? null,
        ];
        $chartModel->update($id, $payload);

        AuditLogger::log(
            'chart.updated',
            'chart',
            $id,
            $oldSnap,
            [
                'name'       => $payload['name'],
                'chart_type' => $payload['chart_type'],
                'saved_query_id' => $payload['saved_query_id'],
            ],
            $workspaceId,
            (int) $this->session->get('user_id')
        );

        return redirect()->back()->with('success', 'Chart updated.');
    }

    public function delete(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $chartModel = new ChartModel();
        $chart = $chartModel->where('workspace_id', $workspaceId)->find($id);

        if (! $chart) {
            return redirect()->to('/chart')->with('error', 'Chart not found.');
        }

        AuditLogger::log(
            'chart.deleted',
            'chart',
            $id,
            [
                'name'       => $chart['name'] ?? null,
                'chart_type' => $chart['chart_type'] ?? null,
            ],
            null,
            $workspaceId,
            (int) $this->session->get('user_id')
        );

        $chartModel->delete($id);

        return redirect()->to('/chart')->with('success', 'Chart deleted.');
    }

    public function duplicate(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $userId = (int) $this->session->get('user_id');
        $chartModel = new ChartModel();
        $chart = $chartModel->where('workspace_id', $workspaceId)->find($id);

        if (! $chart) {
            return redirect()->to('/chart')->with('error', 'Chart not found.');
        }

        $newId = $chartModel->duplicateRow($chart, $userId);
        if ($newId === false) {
            return redirect()->to('/chart')->with('error', 'Could not duplicate chart.');
        }

        AuditLogger::log(
            'chart.duplicated',
            'chart',
            (int) $newId,
            ['source_chart_id' => $id],
            ['name' => $chart['name'] ?? null],
            $workspaceId,
            $userId
        );

        return redirect()->to('/chart/edit/' . $newId)->with('success', 'Chart duplicated.');
    }

    /**
     * Saved query variable definitions for the chart builder (JSON).
     */
    public function savedQueryVariables(int $savedQueryId)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);

        if (! $savedQuery) {
            return $this->response->setJSON(['success' => false, 'message' => 'Query not found.'])->setStatusCode(404);
        }

        $rows = (new QueryVariableModel())
            ->where('saved_query_id', $savedQueryId)
            ->where('workspace_id', $workspaceId)
            ->orderBy('sort_order', 'ASC')
            ->findAll();

        $vars = [];
        foreach ($rows as $row) {
            $optsRaw = $row['options_json'] ?? null;
            $options = [];
            if ($optsRaw !== null && $optsRaw !== '') {
                $decoded = json_decode((string) $optsRaw, true);
                $options = is_array($decoded) ? $decoded : [];
            }
            $vars[] = [
                'name'          => (string) $row['name'],
                'label'         => (string) ($row['label'] ?? ''),
                'type'          => (string) ($row['type'] ?? 'text'),
                'default_value' => (string) ($row['default_value'] ?? ''),
                'is_required'   => ! empty($row['is_required']),
                'options'       => $options,
            ];
        }

        return $this->response->setJSON([
            'success' => true,
            'variables' => $vars,
            'query' => [
                'id' => (int) $savedQuery['id'],
                'name' => (string) $savedQuery['name'],
                'query_type' => (string) $savedQuery['query_type'],
                'data_source_id' => (int) $savedQuery['data_source_id'],
            ],
        ]);
    }

    /**
     * Run a saved query and return preview columns/rows (for builder).
     */
    public function previewQuery()
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $savedQueryId = (int) $this->request->getPost('saved_query_id');

        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);
        if (! $savedQuery) {
            return $this->response->setJSON(['success' => false, 'message' => 'Query not found.'])->setStatusCode(404);
        }

        $dataSource = (new DataSourceModel())->where('workspace_id', $workspaceId)->find((int) $savedQuery['data_source_id']);
        if (! $dataSource) {
            return $this->response->setJSON(['success' => false, 'message' => 'Data source not found.'])->setStatusCode(404);
        }

        $variableValues = json_decode((string) $this->request->getPost('variables_json'), true);
        if (! is_array($variableValues)) {
            $variableValues = [];
        }

        try {
            $runner = new SavedQueryRunner();
            $result = $runner->run($workspaceId, $dataSource, $savedQuery, $variableValues);
            $rows = $result['rows'];
            $columns = [];
            if ($rows !== []) {
                $columns = array_keys($rows[0]);
            }
            $previewRows = array_slice($rows, 0, 100);

            return $this->response->setJSON([
                'success' => true,
                'data' => [
                    'columns' => $columns,
                    'rows' => $previewRows,
                    'row_count' => count($rows),
                    'execution_ms' => $result['execution_ms'],
                    'cache_hit' => $result['cache_hit'],
                    'truncated' => count($rows) > 100,
                ],
                'csrf' => [
                    'name' => csrf_token(),
                    'hash' => csrf_hash(),
                ],
            ]);
        } catch (Throwable $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => $e->getMessage(),
            ])->setStatusCode(422);
        }
    }

    /**
     * Run query + build render payload from unsaved builder state (live preview).
     */
    public function previewRender()
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $savedQueryId = (int) $this->request->getPost('saved_query_id');
        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);
        if (! $savedQuery) {
            return $this->response->setJSON(['success' => false, 'message' => 'Query not found.'])->setStatusCode(404);
        }

        $dataSource = (new DataSourceModel())->where('workspace_id', $workspaceId)->find((int) $savedQuery['data_source_id']);
        if (! $dataSource) {
            return $this->response->setJSON(['success' => false, 'message' => 'Data source not found.'])->setStatusCode(404);
        }

        $variableValues = json_decode((string) $this->request->getPost('variables_json'), true);
        if (! is_array($variableValues)) {
            $variableValues = [];
        }

        $chart = [
            'chart_type'     => (string) $this->request->getPost('chart_type'),
            'x_field'        => trim((string) $this->request->getPost('x_field')) ?: null,
            'y_field'        => trim((string) $this->request->getPost('y_field')) ?: null,
            'group_field'    => trim((string) $this->request->getPost('group_field')) ?: null,
            'value_field'    => trim((string) $this->request->getPost('value_field')) ?: null,
            'display_config' => $this->request->getPost('display_config'),
        ];

        try {
            $runner = new SavedQueryRunner();
            $result = $runner->run($workspaceId, $dataSource, $savedQuery, $variableValues);
            $rows = $result['rows'];
            $renderer = new ChartRenderer();
            $payload = $renderer->buildPayload($chart, $rows);

            return $this->response->setJSON([
                'success' => true,
                'meta' => [
                    'row_count' => count($rows),
                    'execution_ms' => $result['execution_ms'],
                    'cache_hit' => $result['cache_hit'],
                    'chart_type' => $chart['chart_type'],
                ],
                'payload' => $payload,
                'csrf' => [
                    'name' => csrf_token(),
                    'hash' => csrf_hash(),
                ],
            ]);
        } catch (Throwable $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => $e->getMessage(),
            ])->setStatusCode(422);
        }
    }

    /**
     * Fresh data + render payload for an existing chart (dashboards / refresh).
     */
    public function data(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $chartModel = new ChartModel();
        $chart = $chartModel->where('workspace_id', $workspaceId)->find($id);

        if (! $chart) {
            return $this->response->setJSON(['success' => false, 'message' => 'Chart not found.'])->setStatusCode(404);
        }

        $savedQueryId = (int) ($chart['saved_query_id'] ?? 0);
        if ($savedQueryId <= 0) {
            return $this->response->setJSON(['success' => false, 'message' => 'Chart has no linked query.'])->setStatusCode(422);
        }

        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);
        if (! $savedQuery) {
            return $this->response->setJSON(['success' => false, 'message' => 'Linked query not found.'])->setStatusCode(404);
        }

        $dataSource = (new DataSourceModel())->where('workspace_id', $workspaceId)->find((int) $savedQuery['data_source_id']);
        if (! $dataSource) {
            return $this->response->setJSON(['success' => false, 'message' => 'Data source not found.'])->setStatusCode(404);
        }

        $variableValues = json_decode((string) $this->request->getPost('variables_json'), true);
        if (! is_array($variableValues)) {
            $variableValues = [];
        }

        try {
            $runner = new SavedQueryRunner();
            $result = $runner->run($workspaceId, $dataSource, $savedQuery, $variableValues);
            $rows = $result['rows'];

            $renderer = new ChartRenderer();
            $payload = $renderer->buildPayload($chart, $rows);

            return $this->response->setJSON([
                'success' => true,
                'meta' => [
                    'row_count' => count($rows),
                    'execution_ms' => $result['execution_ms'],
                    'cache_hit' => $result['cache_hit'],
                    'chart_type' => (string) $chart['chart_type'],
                ],
                'payload' => $payload,
                'csrf' => [
                    'name' => csrf_token(),
                    'hash' => csrf_hash(),
                ],
            ]);
        } catch (Throwable $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => $e->getMessage(),
            ])->setStatusCode(422);
        }
    }

    /**
     * @return array<string, mixed>
     */
    private function decodeJson(mixed $raw): array
    {
        if ($raw === null || $raw === '') {
            return [];
        }
        if (is_array($raw)) {
            return $raw;
        }
        $d = json_decode((string) $raw, true);

        return is_array($d) ? $d : [];
    }

    /**
     * @return array<string, mixed>
     */
    private function sanitizeDisplayConfig(mixed $raw): array
    {
        $d = $this->decodeJson($raw);
        if ($d === []) {
            return [];
        }

        $allowedKeys = [
            'title', 'subtitle', 'show_legend', 'show_data_labels', 'show_grid',
            'palette', 'palette_colors', 'color_mode', 'number_format', 'decimal_places',
            'x_axis_label', 'y_axis_label', 'y_min', 'y_max', 'stacked',
            'smooth', 'stepline', 'horizontal_bar', 'legend_position',
            'secondary_y_field', 'gauge_max', 'kpi_columns', 'table_preview_limit',
            'ranked_progress_limit', 'ranked_progress_footer_count',
        ];

        $out = [];
        foreach ($allowedKeys as $k) {
            if (array_key_exists($k, $d)) {
                $out[$k] = $d[$k];
            }
        }

        if (isset($out['color_mode']) && ! in_array($out['color_mode'], ['preset', 'custom'], true)) {
            unset($out['color_mode']);
        }

        if (isset($out['palette_colors']) && is_array($out['palette_colors'])) {
            $clean = [];
            foreach ($out['palette_colors'] as $c) {
                $h = $this->sanitizeHexColor(is_scalar($c) ? (string) $c : '');
                if ($h !== null) {
                    $clean[] = $h;
                }
            }
            $out['palette_colors'] = array_slice($clean, 0, 12);
        }

        if (isset($out['ranked_progress_limit'])) {
            $out['ranked_progress_limit'] = max(1, min(200, (int) $out['ranked_progress_limit']));
        }
        if (isset($out['ranked_progress_footer_count'])) {
            $out['ranked_progress_footer_count'] = max(0, min(5, (int) $out['ranked_progress_footer_count']));
        }

        return $out;
    }

    private function sanitizeHexColor(string $raw): ?string
    {
        $t = trim($raw);
        if ($t === '') {
            return null;
        }
        if (preg_match('/^#([0-9A-Fa-f]{6})$/', $t)) {
            return '#' . strtolower(substr($t, 1));
        }

        return null;
    }

    /**
     * Download query result as CSV or Excel (authenticated).
     */
    public function export(int $id)
    {
        $workspaceId = (int) $this->session->get('active_workspace_id');
        $userId = (int) $this->session->get('user_id');
        $format = strtolower((string) $this->request->getGet('format'));
        if (! in_array($format, ['csv', 'excel'], true)) {
            return $this->response->setStatusCode(400)->setBody('Invalid format. Use csv or excel.');
        }

        $chartModel = new ChartModel();
        $chart = $chartModel->where('workspace_id', $workspaceId)->find($id);
        if (! $chart) {
            return $this->response->setStatusCode(404)->setBody('Chart not found.');
        }

        $savedQueryId = (int) ($chart['saved_query_id'] ?? 0);
        if ($savedQueryId <= 0) {
            return $this->response->setStatusCode(422)->setBody('Chart has no linked query.');
        }

        $savedQuery = (new SavedQueryModel())->where('workspace_id', $workspaceId)->find($savedQueryId);
        if (! $savedQuery) {
            return $this->response->setStatusCode(404)->setBody('Query not found.');
        }

        $dataSource = (new DataSourceModel())->where('workspace_id', $workspaceId)->find((int) $savedQuery['data_source_id']);
        if (! $dataSource) {
            return $this->response->setStatusCode(404)->setBody('Data source not found.');
        }

        try {
            $runner = new SavedQueryRunner();
            $result = $runner->run($workspaceId, $dataSource, $savedQuery, []);
            $rows = $result['rows'];
        } catch (Throwable $e) {
            return $this->response->setStatusCode(422)->setBody($e->getMessage());
        }

        $columns = [];
        if ($rows !== []) {
            $columns = array_keys($rows[0]);
        }

        $safeName = preg_replace('/[^a-zA-Z0-9_-]+/', '_', (string) $chart['name']) ?: 'chart';
        $exportType = $format === 'excel' ? 'excel' : 'csv';

        (new ChartExportModel())->logExport(
            $workspaceId,
            $userId,
            $exportType,
            $id,
            null,
            null,
            null
        );

        if ($format === 'csv') {
            $this->response->setHeader('Content-Type', 'text/csv; charset=UTF-8');
            $this->response->setHeader('Content-Disposition', 'attachment; filename="' . $safeName . '.csv"');

            $fh = fopen('php://temp', 'r+');
            if ($columns !== []) {
                fputcsv($fh, $columns);
            }
            foreach ($rows as $r) {
                $line = [];
                foreach ($columns as $c) {
                    $line[] = $r[$c] ?? '';
                }
                fputcsv($fh, $line);
            }
            rewind($fh);
            $csv = stream_get_contents($fh);
            fclose($fh);
            if (str_starts_with($csv, "\xEF\xBB\xBF") === false) {
                $csv = "\xEF\xBB\xBF" . $csv;
            }

            return $this->response->setBody($csv);
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $grid = [];
        if ($columns !== []) {
            $grid[] = $columns;
        }
        foreach ($rows as $r) {
            $line = [];
            foreach ($columns as $c) {
                $v = $r[$c] ?? '';
                $line[] = is_scalar($v) ? $v : json_encode($v);
            }
            $grid[] = $line;
        }
        if ($grid === []) {
            $sheet->setCellValue('A1', '');
        } else {
            $sheet->fromArray($grid);
        }

        $this->response->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $this->response->setHeader('Content-Disposition', 'attachment; filename="' . $safeName . '.xlsx"');

        ob_start();
        (new Xlsx($spreadsheet))->save('php://output');
        $bin = ob_get_clean();

        return $this->response->setBody($bin);
    }
}