<?php
namespace App\Core;

use PDO;

class ProspectClientService
{
    public static function ensureSchema(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS prospect_clients (
            id INT AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(120) NULL,
            last_name VARCHAR(120) NULL,
            full_name VARCHAR(190) NOT NULL,
            city VARCHAR(120) NULL,
            commune VARCHAR(120) NULL,
            address TEXT NULL,
            latitude DECIMAL(10,7) NULL,
            longitude DECIMAL(10,7) NULL,
            geolocation_mode ENUM('manual','auto') NOT NULL DEFAULT 'manual',
            phone VARCHAR(60) NOT NULL,
            status ENUM('prospect','client') NOT NULL DEFAULT 'prospect',
            receipt_path VARCHAR(255) NULL,
            receipt_uploaded_at DATETIME NULL,
            recommended_otb_id INT NULL,
            recommended_otb_code VARCHAR(120) NULL,
            recommended_port_number INT NULL,
            recommended_distance_meters DECIMAL(10,2) NULL,
            recommendation_payload JSON NULL,
            raccordement_ticket_id INT NULL,
            transferred_at DATETIME NULL,
            created_by INT NULL,
            updated_by INT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_prospect_status (status),
            INDEX idx_prospect_city (city),
            INDEX idx_prospect_created_by (created_by),
            INDEX idx_prospect_raccordement_ticket (raccordement_ticket_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        self::addColumnIfMissing($pdo, 'prospect_clients', 'first_name', 'ALTER TABLE prospect_clients ADD COLUMN first_name VARCHAR(120) NULL AFTER id');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'last_name', 'ALTER TABLE prospect_clients ADD COLUMN last_name VARCHAR(120) NULL AFTER first_name');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'full_name', 'ALTER TABLE prospect_clients ADD COLUMN full_name VARCHAR(190) NOT NULL DEFAULT "" AFTER last_name');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'city', 'ALTER TABLE prospect_clients ADD COLUMN city VARCHAR(120) NULL AFTER full_name');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'commune', 'ALTER TABLE prospect_clients ADD COLUMN commune VARCHAR(120) NULL AFTER city');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'address', 'ALTER TABLE prospect_clients ADD COLUMN address TEXT NULL AFTER commune');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'latitude', 'ALTER TABLE prospect_clients ADD COLUMN latitude DECIMAL(10,7) NULL AFTER address');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'longitude', 'ALTER TABLE prospect_clients ADD COLUMN longitude DECIMAL(10,7) NULL AFTER latitude');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'geolocation_mode', 'ALTER TABLE prospect_clients ADD COLUMN geolocation_mode ENUM("manual","auto") NOT NULL DEFAULT "manual" AFTER longitude');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'phone', 'ALTER TABLE prospect_clients ADD COLUMN phone VARCHAR(60) NOT NULL DEFAULT "" AFTER geolocation_mode');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'status', 'ALTER TABLE prospect_clients ADD COLUMN status ENUM("prospect","client") NOT NULL DEFAULT "prospect" AFTER phone');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'receipt_path', 'ALTER TABLE prospect_clients ADD COLUMN receipt_path VARCHAR(255) NULL AFTER status');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'receipt_uploaded_at', 'ALTER TABLE prospect_clients ADD COLUMN receipt_uploaded_at DATETIME NULL AFTER receipt_path');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'recommended_otb_id', 'ALTER TABLE prospect_clients ADD COLUMN recommended_otb_id INT NULL AFTER receipt_uploaded_at');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'recommended_otb_code', 'ALTER TABLE prospect_clients ADD COLUMN recommended_otb_code VARCHAR(120) NULL AFTER recommended_otb_id');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'recommended_port_number', 'ALTER TABLE prospect_clients ADD COLUMN recommended_port_number INT NULL AFTER recommended_otb_code');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'recommended_distance_meters', 'ALTER TABLE prospect_clients ADD COLUMN recommended_distance_meters DECIMAL(10,2) NULL AFTER recommended_port_number');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'recommendation_payload', 'ALTER TABLE prospect_clients ADD COLUMN recommendation_payload JSON NULL AFTER recommended_distance_meters');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'raccordement_ticket_id', 'ALTER TABLE prospect_clients ADD COLUMN raccordement_ticket_id INT NULL AFTER recommendation_payload');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'transferred_at', 'ALTER TABLE prospect_clients ADD COLUMN transferred_at DATETIME NULL AFTER raccordement_ticket_id');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'created_by', 'ALTER TABLE prospect_clients ADD COLUMN created_by INT NULL AFTER transferred_at');
        self::addColumnIfMissing($pdo, 'prospect_clients', 'updated_by', 'ALTER TABLE prospect_clients ADD COLUMN updated_by INT NULL AFTER created_by');
    }

    public static function list(PDO $pdo, array $filters, array $user, string $scope = 'all'): array
    {
        self::ensureSchema($pdo);

        $where = ['1=1'];
        $params = [];

        $search = trim((string)($filters['search'] ?? ''));
        $status = trim((string)($filters['status'] ?? ''));

        if ($search !== '') {
            $where[] = '(full_name LIKE ? OR phone LIKE ? OR city LIKE ? OR commune LIKE ? OR recommended_otb_code LIKE ?)';
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like, $like);
        }

        if (in_array($status, ['prospect', 'client'], true)) {
            $where[] = 'status = ?';
            $params[] = $status;
        }

        if ($scope === 'own') {
            $where[] = 'created_by = ?';
            $params[] = (int)($user['id'] ?? 0);
        }

        $stmt = $pdo->prepare('SELECT * FROM prospect_clients WHERE ' . implode(' AND ', $where) . ' ORDER BY created_at DESC, id DESC');
        $stmt->execute($params);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public static function summary(PDO $pdo, array $user, string $scope = 'all'): array
    {
        self::ensureSchema($pdo);

        $where = ['1=1'];
        $params = [];
        if ($scope === 'own') {
            $where[] = 'created_by = ?';
            $params[] = (int)($user['id'] ?? 0);
        }

        $stmt = $pdo->prepare('SELECT
                COUNT(*) AS total_count,
                SUM(CASE WHEN status = "prospect" THEN 1 ELSE 0 END) AS prospect_count,
                SUM(CASE WHEN status = "client" THEN 1 ELSE 0 END) AS client_count,
                SUM(CASE WHEN receipt_uploaded_at IS NOT NULL THEN 1 ELSE 0 END) AS receipt_count,
                SUM(CASE WHEN raccordement_ticket_id IS NOT NULL THEN 1 ELSE 0 END) AS transmitted_count
            FROM prospect_clients
            WHERE ' . implode(' AND ', $where));
        $stmt->execute($params);

        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [
            'total_count' => 0,
            'prospect_count' => 0,
            'client_count' => 0,
            'receipt_count' => 0,
            'transmitted_count' => 0,
        ];
    }

    public static function find(PDO $pdo, int $id): ?array
    {
        self::ensureSchema($pdo);
        if ($id <= 0) {
            return null;
        }

        $stmt = $pdo->prepare('SELECT * FROM prospect_clients WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;

        return $row ?: null;
    }

    public static function create(PDO $pdo, array $data, int $userId): int
    {
        self::ensureSchema($pdo);
        RaccordementOtbService::ensureSchema($pdo);

        $firstName = trim((string)($data['first_name'] ?? ''));
        $lastName = trim((string)($data['last_name'] ?? ''));
        $fullName = trim((string)($data['full_name'] ?? ''));
        if ($fullName === '') {
            $fullName = trim($lastName . ' ' . $firstName);
        }

        $latitude = self::normalizeCoordinate($data['latitude'] ?? null);
        $longitude = self::normalizeCoordinate($data['longitude'] ?? null);
        $recommendation = self::buildRecommendationSnapshot($pdo, $latitude, $longitude);

        $stmt = $pdo->prepare('INSERT INTO prospect_clients (
                first_name, last_name, full_name, city, commune, address,
                latitude, longitude, geolocation_mode, phone, status,
                receipt_path, receipt_uploaded_at,
                recommended_otb_id, recommended_otb_code, recommended_port_number, recommended_distance_meters,
                recommendation_payload, created_by, updated_by
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([
            $firstName !== '' ? $firstName : null,
            $lastName !== '' ? $lastName : null,
            $fullName,
            self::nullableString($data['city'] ?? null),
            self::nullableString($data['commune'] ?? null),
            self::nullableString($data['address'] ?? null),
            $latitude,
            $longitude,
            in_array(($data['geolocation_mode'] ?? ''), ['manual', 'auto'], true) ? $data['geolocation_mode'] : 'manual',
            trim((string)($data['phone'] ?? '')),
            'prospect',
            null,
            null,
            $recommendation['recommended_otb_id'],
            $recommendation['recommended_otb_code'],
            $recommendation['recommended_port_number'],
            $recommendation['recommended_distance_meters'],
            $recommendation['payload'],
            $userId > 0 ? $userId : null,
            $userId > 0 ? $userId : null,
        ]);

        return (int)$pdo->lastInsertId();
    }

    public static function buildRecommendationSnapshot(PDO $pdo, ?float $latitude, ?float $longitude): array
    {
        RaccordementOtbService::ensureSchema($pdo);

        $payload = RaccordementOtbService::buildRecommendation($pdo, $latitude, $longitude, null);
        $recommended = is_array($payload['recommended'] ?? null) ? $payload['recommended'] : null;
        $recommendedPortNumber = null;

        if (!empty($recommended['id'])) {
            $ports = RaccordementOtbService::getAvailablePorts($pdo, (int)$recommended['id']);
            foreach ($ports as $port) {
                if (!empty($port['available'])) {
                    $recommendedPortNumber = (int)($port['number'] ?? 0);
                    break;
                }
            }
        }

        return [
            'recommended_otb_id' => !empty($recommended['id']) ? (int)$recommended['id'] : null,
            'recommended_otb_code' => trim((string)($recommended['box_code'] ?? '')) ?: null,
            'recommended_port_number' => $recommendedPortNumber ?: null,
            'recommended_distance_meters' => isset($recommended['distance_meters']) ? (float)$recommended['distance_meters'] : null,
            'payload' => json_encode($payload, JSON_UNESCAPED_UNICODE),
        ];
    }

    public static function saveReceipt(PDO $pdo, int $prospectId, string $receiptPath, int $userId): void
    {
        self::ensureSchema($pdo);

        $stmt = $pdo->prepare('UPDATE prospect_clients
            SET status = "client",
                receipt_path = ?,
                receipt_uploaded_at = NOW(),
                updated_by = ?,
                updated_at = NOW()
            WHERE id = ?');
        $stmt->execute([$receiptPath, $userId > 0 ? $userId : null, $prospectId]);
    }

    public static function createRaccordementTicket(PDO $pdo, int $prospectId, array $data, array $user): int
    {
        self::ensureSchema($pdo);

        $prospect = self::find($pdo, $prospectId);
        if (!$prospect) {
            throw new \RuntimeException('Prospect introuvable.');
        }
        if ((string)($prospect['status'] ?? '') !== 'client') {
            throw new \RuntimeException('Le prospect doit d\'abord devenir client via le reçu de paiement.');
        }
        if (!empty($prospect['raccordement_ticket_id'])) {
            return (int)$prospect['raccordement_ticket_id'];
        }

        $ticketId = 0;
        $assignedTechId = 0;
        $createdBy = (int)($user['id'] ?? 0);

        $clientCode = trim((string)($data['client_code'] ?? ''));
        $refCode = trim((string)($data['ref_code'] ?? ''));
        if ($refCode === '') {
            $refCode = 'PRC-' . date('YmdHis');
        }

        $priority = trim((string)($data['priority'] ?? 'Moyenne'));
        if (!in_array($priority, ['Moyenne', 'Haute', 'Urgent', 'Basse'], true)) {
            $priority = 'Moyenne';
        }

        $status = trim((string)($data['status'] ?? 'nouveau'));
        if (!in_array($status, ['nouveau', 'assigné', 'en_cours', 'attente_planification', 'traité', 'validé', 'clôturé'], true)) {
            $status = 'nouveau';
        }

        $latitude = self::normalizeCoordinate($data['latitude'] ?? ($prospect['latitude'] ?? null));
        $longitude = self::normalizeCoordinate($data['longitude'] ?? ($prospect['longitude'] ?? null));
        $recommendation = self::buildRecommendationSnapshot($pdo, $latitude, $longitude);
        $recommendedBoxCode = trim((string)($recommendation['recommended_otb_code'] ?? ($prospect['recommended_otb_code'] ?? '')));
        $recommendedPort = (int)($recommendation['recommended_port_number'] ?? ($prospect['recommended_port_number'] ?? 0));

        $fullName = trim((string)($prospect['full_name'] ?? ''));
        $city = trim((string)(($data['city'] ?? '') ?: ($prospect['city'] ?? '')));
        $commune = trim((string)(($data['commune'] ?? '') ?: ($prospect['commune'] ?? '')));
        $address = trim((string)(($data['address'] ?? '') ?: ($prospect['address'] ?? '')));
        $phone = trim((string)(($data['phone'] ?? '') ?: ($prospect['phone'] ?? '')));
        $pcoClient = trim((string)($data['pco_client'] ?? ''));
        if ($pcoClient === '') {
            $pcoClient = $recommendedBoxCode;
        }

        $extraFields = array_filter([
            'source' => 'prospect-clients',
            'module' => 'raccordement-clients',
            'prospect_client_id' => $prospectId,
            'NumeroAbonnement' => $clientCode,
            'NomClient' => $fullName,
            'Ville' => $city,
            'Commune' => $commune,
            'Contact1' => $phone,
            'Localisation' => $address,
            'Latitude_Terrai' => $latitude,
            'Longitude_Terrain' => $longitude,
            'PLAQUE' => trim((string)($data['sro_client'] ?? '')),
            'JDV' => trim((string)($data['jdv_client'] ?? '')),
            'PCO' => $pcoClient,
            'SNONT' => trim((string)($data['snont'] ?? '')),
            'ND' => trim((string)($data['nd'] ?? '')),
            'EntrepriseFTTH' => trim((string)($data['company_name'] ?? '')),
            'Commentaires' => trim((string)($data['description'] ?? '')),
            'suggested_pco' => $recommendedBoxCode,
            'suggested_port_number' => $recommendedPort > 0 ? $recommendedPort : null,
            'payment_receipt_path' => trim((string)($prospect['receipt_path'] ?? '')),
        ], static fn($value) => $value !== null && $value !== '');

        $technicianId = (int)($data['technician_id'] ?? 0);
        if ($technicianId > 0) {
            $techStmt = $pdo->prepare("SELECT id FROM users WHERE id = ? AND active = 1 AND role_key IN ('technicien','technician') AND technician_type IN ('etude','etude_raccordement') LIMIT 1");
            $techStmt->execute([$technicianId]);
            $assignedTechId = (int)($techStmt->fetchColumn() ?: 0);
        }

        $pdo->beginTransaction();
        try {
            $ticketStmt = $pdo->prepare('INSERT INTO maintenance_ftth_tickets
                (ref_code, fixed_line, client_name, client_code, client_phone, client_phone2,
                 client_address, client_lat, client_lng, sro_client, jdv_client, pco_client,
                 company_name, nature_intervention, description, cause_comment, priority, status,
                 avancement, equipes, date_cloture, extra_fields, imported_at, imported_by)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)');
            $ticketStmt->execute([
                $refCode,
                null,
                $fullName,
                $clientCode !== '' ? $clientCode : null,
                $phone !== '' ? $phone : null,
                null,
                $address !== '' ? $address : null,
                $latitude,
                $longitude,
                self::nullableString($data['sro_client'] ?? null),
                self::nullableString($data['jdv_client'] ?? null),
                $pcoClient !== '' ? $pcoClient : null,
                self::nullableString($data['company_name'] ?? null),
                'Raccordement client',
                self::nullableString($data['description'] ?? null),
                null,
                $priority,
                $assignedTechId > 0 ? 'assigné' : $status,
                self::nullableString($data['avancement'] ?? null),
                null,
                null,
                json_encode($extraFields, JSON_UNESCAPED_UNICODE),
                $createdBy > 0 ? $createdBy : null,
            ]);

            $ticketId = (int)$pdo->lastInsertId();

            if ($assignedTechId > 0) {
                $assignStmt = $pdo->prepare('INSERT INTO maintenance_ftth_assignments (ticket_id, technician_id, assigned_by, notes) VALUES (?,?,?,?)');
                $assignStmt->execute([
                    $ticketId,
                    $assignedTechId,
                    $createdBy > 0 ? $createdBy : 0,
                    trim((string)($data['assignment_notes'] ?? '')),
                ]);

                $title = '[Raccordement] Nouvelle intervention assignée';
                $body = 'Vous avez été assigné au dossier de raccordement pour le client ' . $fullName . '.';
                if ($clientCode !== '') {
                    $body .= ' Abonnement : ' . $clientCode . '.';
                }
                $url = '/raccordement-clients/' . $ticketId;
                Notifier::notifyUsers($pdo, [$assignedTechId], $title, $body, $url);
                Notifier::pushUsers($pdo, [$assignedTechId], $title, $body, $url);
            }

            $prospectStmt = $pdo->prepare('UPDATE prospect_clients
                SET raccordement_ticket_id = ?, transferred_at = NOW(), updated_by = ?, updated_at = NOW(),
                    recommended_otb_id = COALESCE(?, recommended_otb_id),
                    recommended_otb_code = COALESCE(?, recommended_otb_code),
                    recommended_port_number = COALESCE(?, recommended_port_number),
                    recommended_distance_meters = COALESCE(?, recommended_distance_meters),
                    recommendation_payload = COALESCE(?, recommendation_payload)
                WHERE id = ?');
            $prospectStmt->execute([
                $ticketId,
                $createdBy > 0 ? $createdBy : null,
                $recommendation['recommended_otb_id'],
                $recommendation['recommended_otb_code'],
                $recommendation['recommended_port_number'],
                $recommendation['recommended_distance_meters'],
                $recommendation['payload'],
                $prospectId,
            ]);

            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }

        return $ticketId;
    }

    private static function addColumnIfMissing(PDO $pdo, string $table, string $column, string $sql): void
    {
        if (self::columnExists($pdo, $table, $column)) {
            return;
        }

        try {
            $pdo->exec($sql);
        } catch (\Throwable $e) {
        }
    }

    private static function columnExists(PDO $pdo, string $table, string $column): bool
    {
        $stmt = $pdo->prepare('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1');
        $stmt->execute([$table, $column]);

        return (bool)$stmt->fetchColumn();
    }

    private static function normalizeCoordinate($value): ?float
    {
        if ($value === null) {
            return null;
        }

        $normalized = trim(str_replace(',', '.', (string)$value));
        if ($normalized === '' || !is_numeric($normalized)) {
            return null;
        }

        return (float)$normalized;
    }

    private static function nullableString($value): ?string
    {
        $normalized = trim((string)$value);
        return $normalized === '' ? null : $normalized;
    }
}