<?php
namespace Modules\Vendas\Controllers;

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

class SalesCommissionsController {
    /**
     * Retorna comissões por vendedor no período informado.
     * Base: vendas por vendedor (pedidos e PDV) consolidadas por audit_logs.
     * Comissão: obtida do cadastro de vendedores (tabela vendedores) por email do usuário.
     */
    public function bySeller(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        if ($start === '' || $end === '') {
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        // Receita por vendedor (user_id) a partir de pedidos
        $by = [];
        try {
            $stmt = $pdo->prepare(
                "SELECT al.user_id AS uid, u.name AS seller_name, u.email AS seller_email,
                        COALESCE(SUM(so.valor_total),0) AS revenue
                 FROM sales_orders so
                 LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                 LEFT JOIN users u ON u.id = al.user_id
                 WHERE DATE(so.created_at) BETWEEN ? AND ?
                 GROUP BY al.user_id, u.name, u.email"
            );
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'seller_email' => null, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['seller_email'] = $by[$uid]['seller_email'] ?? ($row['seller_email'] ?? null);
                $rev = (float)($row['revenue'] ?? 0);
                $by[$uid]['revenue_cents'] += (int)round($rev * 100);
            }
        } catch (\Throwable $e) { /* tolerante */ }

        // Receita por vendedor (PDV)
        try {
            $stmt = $pdo->prepare(
                "SELECT al.user_id AS uid, u.name AS seller_name, u.email AS seller_email,
                        COALESCE(SUM(ps.valor_total),0) AS revenue
                 FROM pos_sales ps
                 LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                 LEFT JOIN users u ON u.id = al.user_id
                 WHERE DATE(ps.created_at) BETWEEN ? AND ?
                 GROUP BY al.user_id, u.name, u.email"
            );
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'seller_email' => null, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['seller_email'] = $by[$uid]['seller_email'] ?? ($row['seller_email'] ?? null);
                $rev = (float)($row['revenue'] ?? 0);
                $by[$uid]['revenue_cents'] += (int)round($rev * 100);
            }
        } catch (\Throwable $e) { /* tolerante */ }

        // Mapa de comissão por email (cadastro de vendedores)
        $percentByEmail = [];
        try {
            $stmt = $pdo->query("SELECT email, comissao_percent FROM vendedores WHERE comissao_percent IS NOT NULL AND comissao_percent > 0");
            foreach ($stmt->fetchAll() as $row) {
                $email = strtolower(trim((string)($row['email'] ?? '')));
                if ($email !== '') $percentByEmail[$email] = (float)($row['comissao_percent'] ?? 0);
            }
        } catch (\Throwable $e) { /* tolerante */ }

        // Montar saída calculando comissão
        $out = [];
        foreach ($by as $uid => $acc) {
            $name = $acc['seller_name'] ?? null;
            $email = strtolower(trim((string)($acc['seller_email'] ?? '')));
            $revCents = (int)$acc['revenue_cents'];
            $percent = ($percentByEmail[$email] ?? 0.0);
            $commCents = (int)round(($revCents * $percent) / 100.0);
            $out[] = [
                'seller_name' => $name ?: ($uid !== '' ? ('Usuário #' . $uid) : '(não informado)'),
                'seller_email' => $email ?: null,
                'revenue_cents' => $revCents,
                'commission_percent' => $percent,
                'commission_cents' => $commCents,
            ];
        }

        // Ordenar por comissão desc
        usort($out, function($a,$b){ return ($b['commission_cents'] <=> $a['commission_cents']); });
        return Response::json(['data' => $out, 'period' => ['start' => $start, 'end' => $end]]);
    }

    /** Retorna listas de seleção (vendedores e empresas) e períodos suportados */
    public function config(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $vendors = [];
        $companies = [];
        try {
            $stmt = $pdo->query("SELECT id, nome, email, comissao_percent FROM vendedores ORDER BY nome");
            foreach ($stmt->fetchAll() as $row) {
                $vendors[] = [
                    'id' => (int)($row['id'] ?? 0),
                    'nome' => (string)($row['nome'] ?? ''),
                    'email' => (string)($row['email'] ?? ''),
                    'comissao_percent' => (float)($row['comissao_percent'] ?? 0)
                ];
            }
        } catch (\Throwable $e) { /* tolerante */ }
        try {
            $stmt = $pdo->query("SELECT id, nome FROM empresas ORDER BY nome");
            foreach ($stmt->fetchAll() as $row) {
                $companies[] = [
                    'id' => (int)($row['id'] ?? 0),
                    'nome' => (string)($row['nome'] ?? '')
                ];
            }
        } catch (\Throwable $e) { /* tolerante */ }
        $periods = [
            ['key' => 'mensal', 'label' => 'Mensal'],
            ['key' => 'quinzenal', 'label' => 'Quinzenal'],
            ['key' => 'semanal', 'label' => 'Semanal']
        ];
        return Response::json(['vendors' => $vendors, 'companies' => $companies, 'periods' => $periods]);
    }

    /** Gera um run de comissões com filtros e persiste histórico quando possível */
    public function generate(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $body = is_array($r->body) ? $r->body : [];
        $sellerEmails = array_values(array_filter(array_map(function($v){ return strtolower(trim((string)$v)); }, (array)($body['seller_emails'] ?? [])), function($v){ return $v !== ''; }));
        $empresaIds = array_values(array_filter(array_map(function($v){ return (int)$v; }, (array)($body['empresa_ids'] ?? [])), function($v){ return $v > 0; }));
        $periodType = (string)($body['period_type'] ?? 'mensal');
        $start = (string)($body['start'] ?? '');
        $end = (string)($body['end'] ?? '');
        if ($start === '' || $end === '') {
            // fallback: últimos 30 dias
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        // Helper para checar colunas
        $hasCol = function(string $table, string $col) use ($pdo): bool {
            try {
                $stmt = $pdo->prepare("SHOW COLUMNS FROM {$table} LIKE ?");
                $stmt->execute([$col]);
                return (bool)$stmt->fetch();
            } catch (\Throwable $e) { return false; }
        };

        // Receita consolidada com filtros
        $by = [];
        // Pedidos
        try {
            $sql = "SELECT al.user_id AS uid, u.name AS seller_name, u.email AS seller_email, COALESCE(SUM(so.valor_total),0) AS revenue
                    FROM sales_orders so
                    LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                    LEFT JOIN users u ON u.id = al.user_id
                    WHERE DATE(so.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if (!empty($sellerEmails)) { $sql .= " AND LOWER(u.email) IN (" . str_repeat('?,', count($sellerEmails)-1) . "?)"; $params = array_merge($params, $sellerEmails); }
            if (!empty($empresaIds) && $hasCol('sales_orders','empresa_id')) { $place = implode(',', array_fill(0, count($empresaIds), '?')); $sql .= " AND so.empresa_id IN ($place)"; $params = array_merge($params, $empresaIds); }
            $sql .= " GROUP BY al.user_id, u.name, u.email";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'seller_email' => null, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['seller_email'] = $by[$uid]['seller_email'] ?? ($row['seller_email'] ?? null);
                $by[$uid]['revenue_cents'] += (int)round(((float)($row['revenue'] ?? 0)) * 100);
            }
        } catch (\Throwable $e) { /* tolerante */ }
        // PDV
        try {
            $sql = "SELECT al.user_id AS uid, u.name AS seller_name, u.email AS seller_email, COALESCE(SUM(ps.valor_total),0) AS revenue
                    FROM pos_sales ps
                    LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                    LEFT JOIN users u ON u.id = al.user_id
                    WHERE DATE(ps.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if (!empty($sellerEmails)) { $sql .= " AND LOWER(u.email) IN (" . str_repeat('?,', count($sellerEmails)-1) . "?)"; $params = array_merge($params, $sellerEmails); }
            if (!empty($empresaIds) && $hasCol('pos_sales','empresa_id')) { $place = implode(',', array_fill(0, count($empresaIds), '?')); $sql .= " AND ps.empresa_id IN ($place)"; $params = array_merge($params, $empresaIds); }
            $sql .= " GROUP BY al.user_id, u.name, u.email";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'seller_email' => null, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['seller_email'] = $by[$uid]['seller_email'] ?? ($row['seller_email'] ?? null);
                $by[$uid]['revenue_cents'] += (int)round(((float)($row['revenue'] ?? 0)) * 100);
            }
        } catch (\Throwable $e) { /* tolerante */ }

        // Percentual por e-mail
        $percentByEmail = [];
        try {
            $stmt = $pdo->query("SELECT email, comissao_percent FROM vendedores WHERE comissao_percent IS NOT NULL AND comissao_percent > 0");
            foreach ($stmt->fetchAll() as $row) { $percentByEmail[strtolower(trim((string)($row['email'] ?? '')))] = (float)($row['comissao_percent'] ?? 0); }
        } catch (\Throwable $e) { /* tolerante */ }

        // Montar itens e totais
        $items = [];
        $totalRevenue = 0; $totalComm = 0;
        foreach ($by as $uid => $acc) {
            $name = $acc['seller_name'] ?? null;
            $email = strtolower(trim((string)($acc['seller_email'] ?? '')));
            $revCents = (int)$acc['revenue_cents'];
            $percent = ($percentByEmail[$email] ?? 0.0);
            $commCents = (int)round(($revCents * $percent) / 100.0);
            $items[] = [
                'seller_name' => $name ?: ($uid !== '' ? ('Usuário #' . $uid) : '(não informado)'),
                'seller_email' => $email ?: null,
                'revenue_cents' => $revCents,
                'commission_percent' => $percent,
                'commission_cents' => $commCents,
            ];
            $totalRevenue += $revCents; $totalComm += $commCents;
        }
        usort($items, function($a,$b){ return ($b['commission_cents'] <=> $a['commission_cents']); });

        $status = 'gerado';
        $runId = null;
        // Persistir quando possível
        try {
            $pdo->exec("CREATE TABLE IF NOT EXISTS commission_runs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                period_type VARCHAR(20) NULL,
                start_date DATE NULL,
                end_date DATE NULL,
                seller_emails_json TEXT NULL,
                empresa_ids_json TEXT NULL,
                status VARCHAR(20) NULL,
                total_revenue_cents INT NULL,
                total_commission_cents INT NULL,
                created_at DATETIME NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            $pdo->exec("CREATE TABLE IF NOT EXISTS commission_items (
                id INT AUTO_INCREMENT PRIMARY KEY,
                run_id INT NOT NULL,
                seller_email VARCHAR(200) NULL,
                seller_name VARCHAR(200) NULL,
                revenue_cents INT NULL,
                commission_percent DECIMAL(10,2) NULL,
                commission_cents INT NULL,
                created_at DATETIME NULL,
                INDEX (run_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            $ins = $pdo->prepare("INSERT INTO commission_runs (period_type, start_date, end_date, seller_emails_json, empresa_ids_json, status, total_revenue_cents, total_commission_cents, created_at) VALUES (?,?,?,?,?,?,?,?,?)");
            $ins->execute([$periodType, $start, $end, json_encode($sellerEmails), json_encode($empresaIds), $status, $totalRevenue, $totalComm, date('Y-m-d H:i:s')]);
            $runId = (int)$pdo->lastInsertId();
            if ($runId > 0) {
                $ii = $pdo->prepare("INSERT INTO commission_items (run_id, seller_email, seller_name, revenue_cents, commission_percent, commission_cents, created_at) VALUES (?,?,?,?,?,?,?)");
                foreach ($items as $it) {
                    $ii->execute([$runId, (string)($it['seller_email'] ?? ''), (string)($it['seller_name'] ?? ''), (int)($it['revenue_cents'] ?? 0), (float)($it['commission_percent'] ?? 0), (int)($it['commission_cents'] ?? 0), date('Y-m-d H:i:s')]);
                }
            }
        } catch (\Throwable $e) { $status = 'preview'; }

        return Response::json(['status' => $status, 'run_id' => $runId, 'period' => ['start' => $start, 'end' => $end, 'type' => $periodType], 'summary' => ['total_revenue_cents' => $totalRevenue, 'total_commission_cents' => $totalComm], 'items' => $items]);
    }

    /** Lista históricos de apuração */
    public function listRuns(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        try {
            $stmt = $pdo->query("SELECT id, period_type, start_date, end_date, status, total_revenue_cents, total_commission_cents, created_at FROM commission_runs ORDER BY id DESC LIMIT 100");
            return Response::json(['data' => $stmt->fetchAll()]);
        } catch (\Throwable $e) {
            return Response::json(['data' => []]);
        }
    }

    /** Detalhe de um run */
    public function getRun(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $id = (int)($r->params['id'] ?? 0);
        if ($id <= 0) return Response::json(['error' => 'id inválido'], 400);
        $run = null; $items = [];
        try {
            $st = $pdo->prepare("SELECT * FROM commission_runs WHERE id = ? LIMIT 1");
            $st->execute([$id]);
            $run = $st->fetch() ?: null;
        } catch (\Throwable $e) { }
        try {
            $st = $pdo->prepare("SELECT seller_name, seller_email, revenue_cents, commission_percent, commission_cents FROM commission_items WHERE run_id = ? ORDER BY commission_cents DESC");
            $st->execute([$id]);
            $items = $st->fetchAll();
        } catch (\Throwable $e) { }
        return Response::json(['run' => $run, 'items' => $items]);
    }

    /** Registra pagamento vinculado ao run */
    public function createPayment(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $id = (int)($r->params['id'] ?? 0);
        $body = is_array($r->body) ? $r->body : [];
        $amount = (int)($body['amount_cents'] ?? 0);
        $status = (string)($body['status'] ?? 'pendente');
        $occurred = (string)($body['occurred_at'] ?? date('Y-m-d H:i:s'));
        if ($id <= 0 || $amount <= 0) return Response::json(['error' => 'parâmetros inválidos'], 400);
        try {
            $pdo->exec("CREATE TABLE IF NOT EXISTS commission_payments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                run_id INT NOT NULL,
                amount_cents INT NOT NULL,
                status VARCHAR(30) NULL,
                occurred_at DATETIME NULL,
                notes TEXT NULL,
                created_at DATETIME NULL,
                INDEX (run_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            $st = $pdo->prepare("INSERT INTO commission_payments (run_id, amount_cents, status, occurred_at, created_at) VALUES (?,?,?,?,?)");
            $st->execute([$id, $amount, $status, $occurred, date('Y-m-d H:i:s')]);
            return Response::json(['ok' => true]);
        } catch (\Throwable $e) {
            return Response::json(['error' => 'não foi possível registrar pagamento'], 500);
        }
    }

    /** Exporta CSV (por vendedor) com filtros opcionais */
    public function exportCsv(Request $r): Response {
        $res = $this->collectForExport($r);
        $sep = (string)($r->query['sep'] ?? ';');
        if (!in_array($sep, [';', ',', '\t'], true)) { $sep = ';'; }
        if ($sep === '\t') { $sep = "\t"; }
        $lines = [];
        $lines[] = implode($sep, ['Vendedor','Email','Receita','% Comissão','Comissão']);
        foreach ($res['items'] as $rw) {
            $lines[] = implode($sep, [
                (string)($rw['seller_name'] ?? ''),
                (string)($rw['seller_email'] ?? ''),
                number_format(((int)$rw['revenue_cents'])/100, 2, ',', ''),
                number_format((float)$rw['commission_percent'], 2, ',', ''),
                number_format(((int)$rw['commission_cents'])/100, 2, ',', '')
            ]);
        }
        $csv = implode("\n", $lines);
        return Response::raw($csv, 200, ['Content-Type' => 'text/csv; charset=utf-8']);
    }

    /** Exporta XLSX simplificado (CSV com content-type do Excel) */
    public function exportXlsx(Request $r): Response {
        $res = $this->collectForExport($r);
        $sep = ";"; // Excel BR aceita CSV com ';'
        $lines = [];
        $lines[] = implode($sep, ['Vendedor','Email','Receita','% Comissão','Comissão']);
        foreach ($res['items'] as $rw) {
            $lines[] = implode($sep, [
                (string)($rw['seller_name'] ?? ''),
                (string)($rw['seller_email'] ?? ''),
                number_format(((int)$rw['revenue_cents'])/100, 2, ',', ''),
                number_format((float)$rw['commission_percent'], 2, ',', ''),
                number_format(((int)$rw['commission_cents'])/100, 2, ',', '')
            ]);
        }
        $csv = implode("\n", $lines);
        return Response::raw($csv, 200, ['Content-Type' => 'application/vnd.ms-excel']);
    }

    /** Coleta dados por vendedor considerando filtros (reuso para export) */
    private function collectForExport(Request $r): array {
        // Reaproveita lógica de generate sem persistir
        $tmp = new Request($r->headers, $r->query, $r->cookies, $r->files, 'POST', $r->path, [
            'seller_emails' => (array)($r->query['seller_emails'] ?? []),
            'empresa_ids' => (array)($r->query['empresa_ids'] ?? []),
            'period_type' => (string)($r->query['period_type'] ?? 'mensal'),
            'start' => (string)($r->query['start'] ?? ''),
            'end' => (string)($r->query['end'] ?? ''),
        ]);
        $resp = $this->generate($tmp);
        $body = is_array($resp->body) ? $resp->body : ['items' => []];
        return ['items' => (array)($body['items'] ?? [])];
    }
}