<?php
namespace Modules\Clientes\Controllers;

use Gestoreasy\Core\Http\Request;
use Gestoreasy\Core\Http\Response;
use Gestoreasy\Core\Tenant\ConnectionManager;
use PDO;

class ClientsReportsController {
    private PDO $db;

    public function __construct() {
        $this->db = ConnectionManager::forTenant();
    }

    private function tableExists(string $table): bool {
        try {
            return $this->db->query("SHOW TABLES LIKE '" . str_replace("'", "''", $table) . "'")->rowCount() > 0;
        } catch (\Throwable $e) { return false; }
    }

    /**
     * GET /api/relatorios/clientes/list
     * Parâmetros: start, end, q (busca), status (todos|ativos|inativos|bloqueados)
     */
    public function list(Request $r): Response {
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $q = trim((string)($r->query['q'] ?? ''));
        $clienteId = (int)($r->query['cliente_id'] ?? 0);
        $status = (string)($r->query['status'] ?? 'todos');
        $perPage = min(200, max(1, (int)($r->query['per_page'] ?? 50)));
        $page = max(1, (int)($r->query['page'] ?? 1));
        $sort = (string)($r->query['sort'] ?? 'total_vendas');
        $dir = strtolower((string)($r->query['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';

        $where = ['1=1'];
        $params = [];
        if ($clienteId > 0) { $where[] = 'c.id = :cid'; $params['cid'] = $clienteId; }
        if ($q !== '') {
            $where[] = '(c.nome LIKE :q OR c.nome_fantasia LIKE :q OR c.cnpj LIKE :q OR c.cpf LIKE :q)';
            $params['q'] = "%$q%";
        }
        if ($status === 'inativos') { $where[] = 'c.inativo = 1'; }
        else if ($status === 'bloqueados') { $where[] = 'c.bloqueado = 1'; }
        else if ($status === 'ativos') { $where[] = 'c.inativo = 0 AND c.bloqueado = 0'; }

        $wc = implode(' AND ', $where);

        $hasSO = $this->tableExists('sales_orders');
        $hasPS = $this->tableExists('pos_sales');

        $joinSO = '';
        $joinPS = '';
        if ($hasSO && $start !== '' && $end !== '') {
            $joinSO = "LEFT JOIN (
                SELECT cliente_id, COUNT(*) AS so_count, COALESCE(SUM(valor_total),0) AS so_total, MAX(created_at) AS so_last
                FROM sales_orders WHERE DATE(created_at) BETWEEN :so_start AND :so_end GROUP BY cliente_id
            ) so ON so.cliente_id = c.id";
            $params['so_start'] = $start; $params['so_end'] = $end;
        }
        if ($hasPS && $start !== '' && $end !== '') {
            $joinPS = "LEFT JOIN (
                SELECT cliente_id, COUNT(*) AS ps_count, COALESCE(SUM(valor_total),0) AS ps_total, MAX(created_at) AS ps_last
                FROM pos_sales WHERE DATE(created_at) BETWEEN :ps_start AND :ps_end GROUP BY cliente_id
            ) ps ON ps.cliente_id = c.id";
            $params['ps_start'] = $start; $params['ps_end'] = $end;
        }

        $offset = ($page - 1) * $perPage;
        // Ordenação segura por alias/colunas conhecidas
        $allowedSort = [
            'total_vendas' => 'total_vendas',
            'pedidos' => 'pedidos',
            'ultima_compra' => 'ultima_compra',
            'nome' => 'c.nome',
            'id' => 'c.id'
        ];
        $orderBy = $allowedSort[$sort] ?? 'total_vendas';

        // Consulta principal
        $sql = "SELECT c.id, c.nome, c.nome_fantasia, c.cnpj, c.cpf, c.inativo, c.bloqueado,
                COALESCE(so.so_count,0) + COALESCE(ps.ps_count,0) AS pedidos,
                COALESCE(so.so_total,0) + COALESCE(ps.ps_total,0) AS total_vendas,
                GREATEST(COALESCE(so.so_last,'0000-00-00 00:00:00'), COALESCE(ps.ps_last,'0000-00-00 00:00:00')) AS ultima_compra
            FROM clients c
            $joinSO
            $joinPS
            WHERE $wc
            ORDER BY $orderBy $dir
            LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k=>$v) { $stmt->bindValue(":".$k, $v); }
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        // Total de registros para paginação (sem joins, conta clientes conforme filtros)
        try {
            $countStmt = $this->db->prepare("SELECT COUNT(*) FROM clients c WHERE $wc");
            foreach ($params as $k=>$v) {
                // Apenas parâmetros de clients (ignora so_/ps_ usados nos joins)
                if (in_array($k, ['cid','q','so_start','so_end','ps_start','ps_end'], true)) {
                    if ($k === 'q') $countStmt->bindValue(':'.$k, $v);
                    if ($k === 'cid') $countStmt->bindValue(':'.$k, $v, PDO::PARAM_INT);
                    // so_/ps_ não são usados no count
                } else {
                    $countStmt->bindValue(':'.$k, $v);
                }
            }
            $countStmt->execute();
            $total = (int)($countStmt->fetchColumn() ?: 0);
        } catch (\Throwable $e) { $total = 0; }

        return Response::json(['data' => $rows, 'page' => $page, 'per_page' => $perPage, 'total' => $total, 'sort' => $sort, 'dir' => strtolower($dir)]);
    }

    /**
     * GET /api/relatorios/clientes/resumo
     * Totais de clientes ativos, inativos e bloqueados no período (com movimentação)
     */
    public function summary(Request $r): Response {
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $hasSO = $this->tableExists('sales_orders');
        $hasPS = $this->tableExists('pos_sales');

        // Base: totais por status
        $totais = [
            'ativos' => 0,
            'inativos' => 0,
            'bloqueados' => 0,
            'clientes_com_movimentacao' => 0,
            'valor_total' => 0.0,
        ];

        try {
            $totais['ativos'] = (int)($this->db->query('SELECT COUNT(*) FROM clients WHERE inativo = 0 AND bloqueado = 0')->fetchColumn() ?: 0);
            $totais['inativos'] = (int)($this->db->query('SELECT COUNT(*) FROM clients WHERE inativo = 1')->fetchColumn() ?: 0);
            $totais['bloqueados'] = (int)($this->db->query('SELECT COUNT(*) FROM clients WHERE bloqueado = 1')->fetchColumn() ?: 0);
        } catch (\Throwable $e) { /* silencioso */ }

        if ($start !== '' && $end !== '' && ($hasSO || $hasPS)) {
            $sqlMov = [];
            $params = ['start' => $start, 'end' => $end];
            if ($hasSO) {
                $sqlMov[] = "SELECT cliente_id, SUM(valor_total) AS total FROM sales_orders WHERE DATE(created_at) BETWEEN :start AND :end GROUP BY cliente_id";
            }
            if ($hasPS) {
                $sqlMov[] = "SELECT cliente_id, SUM(valor_total) AS total FROM pos_sales WHERE DATE(created_at) BETWEEN :start AND :end GROUP BY cliente_id";
            }
            $union = implode(" UNION ALL ", $sqlMov);
            try {
                $st = $this->db->prepare("SELECT COUNT(DISTINCT cliente_id) AS c, COALESCE(SUM(total),0) AS s FROM ($union) t");
                $st->execute($params);
                $row = $st->fetch(PDO::FETCH_ASSOC) ?: ['c' => 0, 's' => 0.0];
                $totais['clientes_com_movimentacao'] = (int)$row['c'];
                $totais['valor_total'] = (float)$row['s'];
            } catch (\Throwable $e) { /* tolerante */ }
        }

        return Response::json(['totais' => $totais]);
    }

    /**
     * GET /api/relatorios/clientes/inativos
     * Parâmetros: dias (padrão 90)
     */
    public function inactive(Request $r): Response {
        $dias = max(1, (int)($r->query['dias'] ?? 90));
        $perPage = min(200, max(1, (int)($r->query['per_page'] ?? 50)));
        $page = max(1, (int)($r->query['page'] ?? 1));
        $sort = (string)($r->query['sort'] ?? 'ultima_compra');
        $dir = strtolower((string)($r->query['dir'] ?? 'asc')) === 'desc' ? 'DESC' : 'ASC';
        $hasSO = $this->tableExists('sales_orders');
        $hasPS = $this->tableExists('pos_sales');

        $cutDate = (new \DateTimeImmutable())->modify("-$dias days")->format('Y-m-d');
        $params = ['cut' => $cutDate];
        $joinSO = $hasSO ? "LEFT JOIN (
            SELECT cliente_id, MAX(created_at) AS so_last FROM sales_orders GROUP BY cliente_id
        ) so ON so.cliente_id = c.id" : '';
        $joinPS = $hasPS ? "LEFT JOIN (
            SELECT cliente_id, MAX(created_at) AS ps_last FROM pos_sales GROUP BY cliente_id
        ) ps ON ps.cliente_id = c.id" : '';
        // Ordenação segura
        $sortMap = [
            'ultima_compra' => 'ultima_compra',
            'id' => 'c.id',
            'nome' => 'c.nome'
        ];
        $sortCol = $sortMap[$sort] ?? 'ultima_compra';
        $offset = ($page - 1) * $perPage;

        $baseSql = "FROM clients c
            $joinSO
            $joinPS
            WHERE (COALESCE(so.so_last,'0000-00-00 00:00:00') < :cut AND COALESCE(ps.ps_last,'0000-00-00 00:00:00') < :cut) OR (c.inativo = 1)";
        // Dados paginados
        $sql = "SELECT c.id, c.nome, c.inativo, c.bloqueado,
                GREATEST(COALESCE(so.so_last,'0000-00-00 00:00:00'), COALESCE(ps.ps_last,'0000-00-00 00:00:00')) AS ultima_compra
            $baseSql
            ORDER BY $sortCol $dir
            LIMIT $perPage OFFSET $offset";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':cut', $cutDate);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        // Total
        $countSql = "SELECT COUNT(*) AS c $baseSql";
        $countStmt = $this->db->prepare($countSql);
        $countStmt->bindValue(':cut', $cutDate);
        $countStmt->execute();
        $total = (int)($countStmt->fetch(PDO::FETCH_ASSOC)['c'] ?? 0);
        $totalPages = (int)max(1, ceil($total / $perPage));
        return Response::json(['data' => $rows, 'dias' => $dias, 'total' => $total, 'page' => $page, 'per_page' => $perPage, 'total_pages' => $totalPages]);
    }

    /**
     * GET /api/relatorios/clientes/ranking
     * Parâmetros: start, end
     */
    public function ranking(Request $r): Response {
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        if ($start === '' || $end === '') {
            return Response::json(['error' => 'Parâmetros start e end são obrigatórios'], 422);
        }
        $perPage = min(200, max(1, (int)($r->query['per_page'] ?? 25)));
        $page = max(1, (int)($r->query['page'] ?? 1));
        $sort = (string)($r->query['sort'] ?? 'total');
        $dir = strtolower((string)($r->query['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';
        $hasSO = $this->tableExists('sales_orders');
        $hasPS = $this->tableExists('pos_sales');
        if (!$hasSO && !$hasPS) {
            return Response::json(['data' => [], 'total' => 0, 'page' => $page, 'per_page' => $perPage, 'total_pages' => 1]);
        }
        $sqlParts = [];
        $params = ['start' => $start, 'end' => $end];
        if ($hasSO) {
            $sqlParts[] = "SELECT c.id, c.nome, COUNT(so.id) AS pedidos, COALESCE(SUM(so.valor_total),0) AS total
                FROM clients c LEFT JOIN sales_orders so ON so.cliente_id = c.id AND DATE(so.created_at) BETWEEN :start AND :end
                GROUP BY c.id, c.nome";
        }
        if ($hasPS) {
            $sqlParts[] = "SELECT c.id, c.nome, COUNT(ps.id) AS pedidos, COALESCE(SUM(ps.valor_total),0) AS total
                FROM clients c LEFT JOIN pos_sales ps ON ps.cliente_id = c.id AND DATE(ps.created_at) BETWEEN :start AND :end
                GROUP BY c.id, c.nome";
        }
        $union = implode(" UNION ALL ", $sqlParts);
        $offset = ($page - 1) * $perPage;
        // Ordenação segura
        $sortMap = [
            'total' => 'total',
            'pedidos' => 'pedidos',
            'nome' => 'nome',
            'id' => 'id'
        ];
        $sortCol = $sortMap[$sort] ?? 'total';
        // Total
        $countSql = "SELECT COUNT(*) AS c FROM (
            SELECT id FROM ($union) u GROUP BY id, nome
        ) t";
        $countStmt = $this->db->prepare($countSql);
        $countStmt->execute($params);
        $total = (int)($countStmt->fetch(PDO::FETCH_ASSOC)['c'] ?? 0);
        $totalPages = (int)max(1, ceil($total / $perPage));
        // Página
        $sql = "SELECT id, nome, SUM(pedidos) AS pedidos, SUM(total) AS total FROM ($union) u GROUP BY id, nome ORDER BY $sortCol $dir LIMIT $perPage OFFSET $offset";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return Response::json(['data' => $rows, 'total' => $total, 'page' => $page, 'per_page' => $perPage, 'total_pages' => $totalPages]);
    }

    /**
     * GET /api/relatorios/clientes/export.csv
     * Parâmetros: start, end, sep, status, q
     */
    public function exportCsv(Request $r): Response {
        $sep = (string)($r->query['sep'] ?? ';');
        if (!in_array($sep, [';', ',', '\t'], true)) $sep = ';';
        if ($sep === '\t') $sep = "\t";
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $r->query['per_page'] = 1000; $r->query['page'] = 1; // exportar até 1000 linhas
        $list = $this->list($r);
        $payload = $list->body;
        if (is_string($payload)) { $data = json_decode($payload, true); }
        else { $data = $payload; }
        $rows = (array)($data['data'] ?? []);

        $csv = 'ID' . $sep . 'Cliente' . $sep . 'Pedidos' . $sep . 'Total Vendas' . $sep . 'Última Compra' . $sep . 'Status' . "\n";
        foreach ($rows as $row) {
            $status = ($row['bloqueado'] ? 'Bloqueado' : ($row['inativo'] ? 'Inativo' : 'Ativo'));
            $csv .= ($row['id'] ?? '') . $sep . ($row['nome'] ?? '') . $sep . (int)($row['pedidos'] ?? 0) . $sep . number_format((float)($row['total_vendas'] ?? 0.0), 2, ',', '.') . $sep . ($row['ultima_compra'] ?? '') . $sep . $status . "\n";
        }

        $fname = 'relatorio_clientes_' . ($start ?: 'inicio') . '_' . ($end ?: 'fim') . '.csv';
        return new Response(200, $csv, [
            'Content-Type' => 'text/csv; charset=UTF-8',
            'Content-Disposition' => 'attachment; filename=' . $fname
        ]);
    }
}