<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;

class AiSalesService
{
    private string $baseUrl;
    private string $apiKey;
    private string $model;

    public function __construct(?string $baseUrl = null, ?string $apiKey = null, ?string $model = null)
    {
        $this->baseUrl = (string) ($baseUrl
            ?: config('services.litellm.base_url')
            ?: env('LITELLM_BASE_URL', ''));

        $this->apiKey = (string) ($apiKey
            ?: config('services.litellm.api_key')
            ?: env('LITELLM_API_KEY', ''));

        $this->model = (string) ($model
            ?: config('services.litellm.model')
            ?: env('LITELLM_MODEL', 'gpt-4o-mini'));
    }

    public function generateSql(string $question): array
    {
        if (!$this->apiKey) {
            throw new \RuntimeException(
                'Your credit limit has been exhausted.'
            );
        }

        if (!$this->baseUrl) {
            throw new \RuntimeException(
                'Your credit limit has been exhausted.'
            );
        }

        $schema = <<<SCHEMA
Database schema (MySQL):

Core:
- orders: id, invoice_number, customer_name, customer_phone, file_name, size, material, unit_price, total_price, paid_amount, change_amount,
         payment_status, paid_at, production_status, started_at, finished_at,
         designer_id, cashier_id, operator_id, is_credit, created_at, updated_at
- order_items: id, order_id, product_id, product_name, material_id, material_name, file_name, file_path,
             size_description, size_value, size_unit, qty, base_price, calculated_price, subtotal, finishing_note, created_at, updated_at

Payments:
- payments: id, order_id, amount, payment_method, payment_account_id, proof_path, notes, cashier_id, created_at, updated_at
- payment_accounts: id, name, type, provider, account_number, account_holder, is_active, created_at, updated_at

Relationship:
- payments.payment_account_id -> payment_accounts.id
- payments.order_id -> orders.id
- order_items.order_id -> orders.id

Rules:
- ONLY generate SELECT queries (read-only).
- Default revenue/penjualan uses orders.total_price (SUM).
- Use orders.created_at for "hari ini/minggu ini/bulan ini" unless user explicitly asks "berdasarkan pembayaran", then use orders.paid_at.
- ALWAYS LIMIT 200 for listing queries (not needed for single-row aggregate without GROUP BY).
- Prefer explicit column list (avoid SELECT *), unless user explicitly asks "tampilkan semua kolom".
- orders TIDAK memiliki kolom qty. Jika butuh qty order, gunakan order_items.qty.
- CTE / WITH query diperbolehkan, selama tetap read-only.

IMPORTANT PAYMENT RULE:
If the question is about:
- metode pembayaran / payment method
- pembayaran / paid / transaksi pembayaran
- rekening / bank / provider / account number / account holder
- transfer / qris / e-wallet / cash
Then you MUST query from payments and join payment_accounts:
FROM payments p
LEFT JOIN payment_accounts pa ON pa.id = p.payment_account_id

Output must be valid JSON exactly:
{"description":"...","sql":"..."}
No markdown. No ``` blocks. No extra keys.
SCHEMA;

        $messages = [
            [
                'role' => 'system',
                'content' => 'Kamu adalah asisten SQL untuk aplikasi POS percetakan. Tugasmu: buat SQL MySQL yang AMAN (SELECT-only) berdasarkan pertanyaan user. Jawab hanya JSON sesuai format.'
            ],
            ['role' => 'system', 'content' => $schema],
            ['role' => 'user', 'content' => $question],
        ];

        $payload = [
            'model' => $this->model,
            'messages' => $messages,
        ];

        $modelLower = strtolower($this->model);
        if (!str_contains($modelLower, 'gpt-5') && !str_contains($modelLower, 'codex')) {
            $payload['temperature'] = 0.1;
        }

        $res = Http::connectTimeout(10)
            ->timeout(35)
            ->retry(0, 0)
            ->withHeaders([
                'x-api-key' => $this->apiKey,
                'Accept' => 'application/json',
                'Content-Type' => 'application/json',
            ])
            ->post(rtrim($this->baseUrl, '/') . '/chat/completions', $payload);

        if (!$res->successful()) {
            $body = (string) $res->body();
            $bodyLower = strtolower($body);

            if (
                $res->status() === 401 ||
                str_contains($bodyLower, 'no api key passed in') ||
                str_contains($bodyLower, 'authentication error') ||
                str_contains($bodyLower, 'auth error') ||
                str_contains($bodyLower, 'invalid api key') ||
                str_contains($bodyLower, 'expected to start with \'sk-\'')
            ) {
                throw new \RuntimeException(
                    'Your credit limit has been exhausted.'
                );
            }

            throw new \RuntimeException(
                'Fitur AI sedang mengalami gangguan. Silakan hubungi developer.'
            );
        }

        $content = trim((string) data_get($res->json(), 'choices.0.message.content', ''));
        $json = json_decode($content, true);

        if (!is_array($json)) {
            if (preg_match('/```(?:json)?\s*(\{.*?\})\s*```/s', $content, $m)) {
                $json = json_decode($m[1], true);
            }
        }

        if (!is_array($json)) {
            $sql = $this->normalizeSql($this->extractSql($content));
            $this->guardSql($sql);
            $sql = $this->ensureLimit($sql);
            return ['description' => '', 'sql' => $sql];
        }

        $sql = (string) ($json['sql'] ?? '');
        $sql = $this->normalizeSql($this->extractSql($sql));
        $this->guardSql($sql);
        $sql = $this->ensureLimit($sql);

        return [
            'description' => (string) ($json['description'] ?? ''),
            'sql' => $sql,
        ];
    }

    public function execute(string $sql): array
    {
        return DB::select($sql);
    }

    private function extractSql(string $text): string
    {
        $t = trim($text);

        if (preg_match('/```sql\s*(.*?)\s*```/s', $t, $m)) {
            $t = trim($m[1]);
        }

        if (preg_match('/```\s*(.*?)\s*```/s', $t, $m)) {
            $t = trim($m[1]);
        }

        return trim($t);
    }

    private function normalizeSql(string $sql): string
    {
        $s = trim($sql);

        if ($s === '') {
            throw new \RuntimeException('AI tidak menghasilkan SQL.');
        }

        if (preg_match('/^\s*select\b/i', $s)) {
            return $s;
        }

        if (preg_match('/^\s*with\b/i', $s)) {
            return $s;
        }

        if (preg_match('/\bfrom\b/i', $s)) {
            return 'SELECT ' . $s;
        }

        return $s;
    }

    private function guardSql(string $sql): void
    {
        $s = strtolower($sql);

        if (!preg_match('/^\s*(select|with)\b/', $s)) {
            throw new \RuntimeException('SQL ditolak: hanya SELECT / WITH SELECT yang diizinkan.');
        }

        if (substr_count(trim($sql), ';') > 1) {
            throw new \RuntimeException('SQL ditolak: multi-statement tidak diizinkan.');
        }

        $blocked = [' insert ', ' update ', ' delete ', ' drop ', ' alter ', ' truncate ', ' create ', ' replace '];
        foreach ($blocked as $kw) {
            if (str_contains($s, $kw)) {
                throw new \RuntimeException('SQL ditolak: mengandung keyword terlarang.');
            }
        }

        foreach (['information_schema', 'mysql.', 'performance_schema', 'sys.'] as $kw) {
            if (str_contains($s, $kw)) {
                throw new \RuntimeException('SQL ditolak: akses schema sistem tidak diizinkan.');
            }
        }
    }

    private function ensureLimit(string $sql): string
    {
        $trimmed = rtrim($sql, " \t\n\r\0\x0B;");

        if (
            preg_match('/\b(sum|count|max|min|avg)\s*\(/i', $trimmed) &&
            !preg_match('/\bgroup\s+by\b/i', $trimmed)
        ) {
            return $trimmed;
        }

        if (!preg_match('/\blimit\b/i', $trimmed)) {
            return $trimmed . ' LIMIT 200';
        }

        return $trimmed;
    }
}
