<?php
// ─────────────────────────────────────────────
// includes/db.php  —  Datenbankverbindung (PDO)
// ─────────────────────────────────────────────

require_once __DIR__ . '/config.php';

class C2CDatabase {
    private static ?PDO $pdo = null;

    public static function get(): PDO {
        if (self::$pdo === null) {
            try {
                self::$pdo = new PDO(
                    'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4',
                    DB_USER,
                    DB_PASS,
                    [
                        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                        PDO::ATTR_EMULATE_PREPARES   => false,
                    ]
                );
            } catch (PDOException $e) {
                if (C2C_DEBUG) {
                    throw $e;
                }
                error_log('C2C DB Error: ' . $e->getMessage());
                http_response_code(500);
                die(json_encode(['error' => 'Database connection failed']));
            }
        }
        return self::$pdo;
    }

    // Client anlegen oder aktualisieren
    public static function upsertClient(array $data): int {
        $db = self::get();
        if (!empty($data['id'])) {
            $stmt = $db->prepare("
                UPDATE clients SET
                    email=:email, vorname=:vorname, nachname=:nachname,
                    phone=:phone, canton=:canton, form_data=:form_data,
                    service_type=:service_type, status=:status, updated_at=NOW()
                WHERE id=:id
            ");
            $data['form_data'] = is_array($data['form_data']) ? json_encode($data['form_data'], JSON_UNESCAPED_UNICODE) : $data['form_data'];
            $stmt->execute($data);
            return (int)$data['id'];
        }

        $uuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0,0xffff), mt_rand(0,0xffff), mt_rand(0,0xffff),
            mt_rand(0,0x0fff)|0x4000, mt_rand(0,0x3fff)|0x8000,
            mt_rand(0,0xffff), mt_rand(0,0xffff), mt_rand(0,0xffff));

        $stmt = $db->prepare("
            INSERT INTO clients (uuid, email, vorname, nachname, phone, canton, form_data, service_type, status)
            VALUES (:uuid, :email, :vorname, :nachname, :phone, :canton, :form_data, :service_type, :status)
        ");
        $data['uuid'] = $uuid;
        $data['form_data'] = is_array($data['form_data']) ? json_encode($data['form_data'], JSON_UNESCAPED_UNICODE) : $data['form_data'];
        $data['status'] = $data['status'] ?? 'new';
        $stmt->execute($data);
        return (int)$db->lastInsertId();
    }

    // Client laden
    public static function getClient(int $id): ?array {
        $db = self::get();
        $stmt = $db->prepare("SELECT * FROM clients WHERE id=?");
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        if (!$row) return null;
        if (!empty($row['form_data'])) {
            $row['form_data'] = json_decode($row['form_data'], true);
        }
        return $row;
    }

    // Task in Queue einreihen
    public static function enqueue(int $clientId, string $taskType): void {
        $db = self::get();
        // Prüfen ob bereits vorhanden
        $stmt = $db->prepare("SELECT id FROM processing_queue WHERE client_id=? AND task_type=? AND status='pending'");
        $stmt->execute([$clientId, $taskType]);
        if ($stmt->fetch()) return;

        $db->prepare("INSERT INTO processing_queue (client_id, task_type) VALUES (?,?)")
           ->execute([$clientId, $taskType]);
    }

    // Nächsten offenen Task holen
    public static function dequeueTask(): ?array {
        $db = self::get();
        $db->beginTransaction();
        $stmt = $db->prepare("
            SELECT * FROM processing_queue
            WHERE status='pending' AND attempts < 3
            ORDER BY created_at ASC
            LIMIT 1
            FOR UPDATE
        ");
        $stmt->execute();
        $task = $stmt->fetch();
        if ($task) {
            $db->prepare("UPDATE processing_queue SET status='running', started_at=NOW(), attempts=attempts+1 WHERE id=?")
               ->execute([$task['id']]);
        }
        $db->commit();
        return $task ?: null;
    }

    public static function completeTask(int $taskId, bool $success = true, string $error = ''): void {
        $db = self::get();
        if ($success) {
            $db->prepare("UPDATE processing_queue SET status='done', completed_at=NOW() WHERE id=?")
               ->execute([$taskId]);
        } else {
            $db->prepare("UPDATE processing_queue SET status='failed', completed_at=NOW(), error_message=? WHERE id=?")
               ->execute([$error, $taskId]);
        }
    }

    // Analyse speichern
    public static function saveAnalysis(int $clientId, array $analysis): int {
        $db = self::get();
        $stmt = $db->prepare("
            INSERT INTO immigration_analysis
            (client_id, pathways, profile_summary, swiss_advantages, risk_factors,
             recommended_provinces, recommended_pathway, crs_estimate, noc_codes, raw_ai_response, model_used)
            VALUES (:client_id,:pathways,:profile_summary,:swiss_advantages,:risk_factors,
                    :recommended_provinces,:recommended_pathway,:crs_estimate,:noc_codes,:raw_ai_response,:model_used)
        ");
        $stmt->execute([
            'client_id'           => $clientId,
            'pathways'            => json_encode($analysis['pathways'] ?? [], JSON_UNESCAPED_UNICODE),
            'profile_summary'     => $analysis['profile_summary'] ?? '',
            'swiss_advantages'    => json_encode($analysis['swiss_advantages'] ?? [], JSON_UNESCAPED_UNICODE),
            'risk_factors'        => json_encode($analysis['risk_factors'] ?? [], JSON_UNESCAPED_UNICODE),
            'recommended_provinces' => implode(', ', $analysis['recommended_provinces'] ?? []),
            'recommended_pathway' => $analysis['recommended_pathway'] ?? '',
            'crs_estimate'        => $analysis['crs_estimate'] ?? null,
            'noc_codes'           => implode(', ', $analysis['noc_codes'] ?? []),
            'raw_ai_response'     => $analysis['raw_ai_response'] ?? '',
            'model_used'          => ANTHROPIC_MODEL,
        ]);
        return (int)$db->lastInsertId();
    }

    // Story-Variante speichern
    public static function saveStoryVariant(int $clientId, int $analysisId, array $story): int {
        $db = self::get();
        // bestehende Variante löschen wenn gleicher Key
        $db->prepare("DELETE FROM story_variants WHERE client_id=? AND variant_key=?")
           ->execute([$clientId, $story['variant_key']]);

        $stmt = $db->prepare("
            INSERT INTO story_variants
            (client_id, analysis_id, variant_key, title, subtitle, teaser,
             full_story, story_en, advantages_block, call_to_action)
            VALUES
            (:client_id,:analysis_id,:variant_key,:title,:subtitle,:teaser,
             :full_story,:story_en,:advantages_block,:call_to_action)
        ");
        $stmt->execute([
            'client_id'       => $clientId,
            'analysis_id'     => $analysisId,
            'variant_key'     => $story['variant_key'],
            'title'           => $story['title'] ?? '',
            'subtitle'        => $story['subtitle'] ?? '',
            'teaser'          => $story['teaser'] ?? '',
            'full_story'      => $story['full_story'] ?? '',
            'story_en'        => $story['story_en'] ?? '',
            'advantages_block'=> $story['advantages_block'] ?? '',
            'call_to_action'  => $story['call_to_action'] ?? '',
        ]);
        return (int)$db->lastInsertId();
    }
}
