<?php

require_once __DIR__ . '/../../core/database.php';

class CustomerLifecycleService
{
    private $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: db();
    }

    public function getCustomer($customerId, $includeDeleted = false)
    {
        $sql = "SELECT * FROM crm_customers WHERE id = ?";
        if (!$includeDeleted) {
            $sql .= " AND deleted_at IS NULL";
        }
        $sql .= " LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array((int)$customerId));
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getHistoryCounts($customerId)
    {
        $customerId = (int)$customerId;

        $counts = array(
            'opportunities' => 0,
            'quotes' => 0,
            'documents' => 0,
            'activities' => 0,
            'open_opportunities' => 0,
            'total' => 0
        );

        $queries = array(
            'opportunities' => "SELECT COUNT(*) FROM crm_opportunities WHERE customer_id = ?",
            'quotes' => "SELECT COUNT(*) FROM crm_quotes WHERE customer_id = ?",
            'documents' => "SELECT COUNT(*) FROM crm_documents WHERE customer_id = ?",
            'activities' => "SELECT COUNT(*) FROM crm_activities WHERE customer_id = ?",
            'open_opportunities' => "SELECT COUNT(*) FROM crm_opportunities WHERE customer_id = ? AND deleted_at IS NULL AND stage NOT IN ('ganada', 'perdida', 'facturada')"
        );

        foreach ($queries as $key => $sql) {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array($customerId));
            $counts[$key] = (int)$stmt->fetchColumn();
        }

        $counts['total'] = $counts['opportunities'] + $counts['quotes'] + $counts['documents'] + $counts['activities'];

        return $counts;
    }

    public function softDelete($customerId, $deletedBy = 'Sistema', $reason = '')
    {
        $customerId = (int)$customerId;
        $deletedBy = trim((string)$deletedBy);
        $reason = trim((string)$reason);

        if ($deletedBy === '') {
            $deletedBy = 'Sistema';
        }

        $this->pdo->beginTransaction();

        try {
            $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id = ? LIMIT 1 FOR UPDATE");
            $stmt->execute(array($customerId));
            $customer = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$customer) {
                throw new RuntimeException('Cliente no encontrado.');
            }

            if (!empty($customer['deleted_at'])) {
                $this->pdo->commit();
                return array('customer' => $customer, 'counts' => $this->getHistoryCounts($customerId), 'already_deleted' => true);
            }

            $counts = $this->getHistoryCounts($customerId);

            $stmtUpdate = $this->pdo->prepare("\n                UPDATE crm_customers\n                SET deleted_at = NOW(),\n                    deleted_by = ?,\n                    deleted_reason = ?\n                WHERE id = ?\n                  AND deleted_at IS NULL\n            ");
            $stmtUpdate->execute(array($deletedBy, $reason !== '' ? $reason : null, $customerId));

            $activityDescription = "Cliente desactivado mediante soft delete.\n\n" .
                "Historial preservado:\n" .
                "Oportunidades: " . $counts['opportunities'] . "\n" .
                "Cotizaciones: " . $counts['quotes'] . "\n" .
                "Documentos: " . $counts['documents'] . "\n" .
                "Actividades previas: " . $counts['activities'] . ".";

            if ($reason !== '') {
                $activityDescription .= "\n\nMotivo: " . $reason;
            }

            $stmtActivity = $this->pdo->prepare("\n                INSERT INTO crm_activities\n                    (opportunity_id, customer_id, customer_name, activity_type, title, description, owner_name, activity_date)\n                VALUES\n                    (NULL, ?, ?, 'sistema', 'Cliente desactivado', ?, ?, NOW())\n            ");
            $stmtActivity->execute(array(
                $customerId,
                $customer['customer_name'] ?? 'Cliente',
                $activityDescription,
                $deletedBy
            ));

            $this->pdo->commit();

            return array('customer' => $customer, 'counts' => $counts, 'already_deleted' => false);
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function restore($customerId, $restoredBy = 'Sistema')
    {
        $customerId = (int)$customerId;
        $restoredBy = trim((string)$restoredBy);

        if ($restoredBy === '') {
            $restoredBy = 'Sistema';
        }

        $this->pdo->beginTransaction();

        try {
            $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id = ? LIMIT 1 FOR UPDATE");
            $stmt->execute(array($customerId));
            $customer = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$customer) {
                throw new RuntimeException('Cliente no encontrado.');
            }

            $stmtUpdate = $this->pdo->prepare("\n                UPDATE crm_customers\n                SET deleted_at = NULL,\n                    deleted_by = NULL,\n                    deleted_reason = NULL\n                WHERE id = ?\n            ");
            $stmtUpdate->execute(array($customerId));

            $stmtActivity = $this->pdo->prepare("\n                INSERT INTO crm_activities\n                    (opportunity_id, customer_id, customer_name, activity_type, title, description, owner_name, activity_date)\n                VALUES\n                    (NULL, ?, ?, 'sistema', 'Cliente reactivado', 'Cliente reactivado y disponible nuevamente para operaciones comerciales.', ?, NOW())\n            ");
            $stmtActivity->execute(array(
                $customerId,
                $customer['customer_name'] ?? 'Cliente',
                $restoredBy
            ));

            $this->pdo->commit();

            return $customer;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }
}
