<?php

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

/**
 * Motor de reposicion predictiva ICYLAB OS.
 *
 * Principios:
 * - FACTO solo aporta evidencia de compra; sincronizar no crea oportunidades.
 * - Una factura/oportunidad facturada crea o actualiza un pronostico separado.
 * - El cron diario convierte pronosticos elegibles en oportunidades accionables.
 * - Los procesos son idempotentes y dejan trazabilidad.
 */
class RestockForecastService
{
    private PDO $pdo;
    private array $settingsCache = array();

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

    /**
     * Evalua clientes con compras recientes y crea/actualiza sus pronosticos.
     * Este proceso pertenece al motor BI diario y permanece desacoplado de FACTO sync.
     */
    public function refreshRecentForecasts(int $days = 0): array
    {
        if (!$this->tableExists('crm_restock_forecasts')) {
            throw new RuntimeException('Falta ejecutar la migracion del motor de reposicion predictiva.');
        }

        $days = $days > 0
            ? max(30, min(365, $days))
            : $this->settingInt('restock_recent_purchase_days', 90, 30, 365);

        $validDocument = $this->validPurchaseDocumentWhere('fd');
        $taxMatch = $this->normalizedTaxSql('fd.receiver_tax_id_code') . ' = ' . $this->normalizedTaxSql('c.customer_tax_id');
        $sql = "
            SELECT c.id AS customer_id,
                   (
                       SELECT fd.document_id
                       FROM facto_documents fd
                       WHERE $validDocument
                         AND fd.issue_date >= DATE_SUB(CURDATE(), INTERVAL " . (int)$days . " DAY)
                         AND (fd.crm_customer_id = c.id OR $taxMatch)
                       ORDER BY fd.issue_date DESC, fd.id DESC
                       LIMIT 1
                   ) AS facto_document_id
            FROM crm_customers c
            WHERE c.deleted_at IS NULL
              AND COALESCE(c.counterparty_type, 'customer') <> 'supplier'
              AND EXISTS (
                  SELECT 1
                  FROM facto_documents fd
                  WHERE $validDocument
                    AND fd.issue_date >= DATE_SUB(CURDATE(), INTERVAL " . (int)$days . " DAY)
                    AND (fd.crm_customer_id = c.id OR $taxMatch)
              )
            ORDER BY c.id
        ";

        $rows = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $result = array(
            'customers_evaluated' => count($rows),
            'forecasts_refreshed' => 0,
            'forecasts_skipped' => 0,
            'forecast_refresh_errors' => 0,
            'skip_reasons' => array(),
        );

        foreach ($rows as $row) {
            try {
                $refresh = $this->refreshFromCustomer(
                    (int)$row['customer_id'],
                    !empty($row['facto_document_id']) ? (string)$row['facto_document_id'] : null,
                    'daily_bi_refresh'
                );
                if (!empty($refresh['created'])) {
                    $result['forecasts_refreshed']++;
                } else {
                    $result['forecasts_skipped']++;
                    $reason = trim((string)($refresh['reason'] ?? 'Regla no cumplida'));
                    $result['skip_reasons'][$reason] = (int)($result['skip_reasons'][$reason] ?? 0) + 1;
                }
            } catch (Throwable $e) {
                $result['forecast_refresh_errors']++;
                error_log('Restock daily refresh customer ' . (int)$row['customer_id'] . ': ' . $e->getMessage());
            }
        }

        arsort($result['skip_reasons']);
        return $result;
    }

    public function refreshFromOpportunity(int $opportunityId, ?string $preferredFactoDocumentId = null, string $source = 'opportunity_stage'): array
    {
        if ($opportunityId <= 0 || !$this->tableExists('crm_restock_forecasts')) {
            return array('created' => false, 'reason' => 'Motor de pronosticos no disponible.');
        }

        $stmt = $this->pdo->prepare("\n            SELECT o.*, c.counterparty_type, c.is_supplier, c.deleted_at AS customer_deleted_at,\n                   c.restock_days, c.restock_cycle_source, c.restock_confidence, c.customer_tax_id AS current_customer_tax_id,\n                   c.owner_name AS customer_owner_name\n            FROM crm_opportunities o\n            JOIN crm_customers c ON c.id = o.customer_id\n            WHERE o.id = ? AND o.deleted_at IS NULL\n            LIMIT 1\n        ");
        $stmt->execute(array($opportunityId));
        $opportunity = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$opportunity || !empty($opportunity['customer_deleted_at'])) {
            return array('created' => false, 'reason' => 'Oportunidad o cliente no disponible.');
        }
        if ($this->isSupplierOnly($opportunity)) {
            return array('created' => false, 'reason' => 'Los proveedores puros no generan reposiciones comerciales.');
        }

        $document = $this->findIssuedDocumentForOpportunity($opportunity, $preferredFactoDocumentId);
        if (!$document) {
            return array('created' => false, 'reason' => 'No existe un DTE de venta emitido y vigente asociado.');
        }

        return $this->upsertForecastFromDocument($opportunity, $document, $source);
    }

    public function refreshFromCustomer(int $customerId, ?string $preferredFactoDocumentId = null, string $source = 'historical_backfill'): array
    {
        if ($customerId <= 0 || !$this->tableExists('crm_restock_forecasts')) {
            return array('created' => false, 'reason' => 'Motor de pronosticos no disponible.');
        }

        $stmt = $this->pdo->prepare("
            SELECT c.*, c.customer_tax_id AS current_customer_tax_id, c.owner_name AS customer_owner_name,
                   COALESCE(
                       (SELECT o.owner_user_id FROM crm_opportunities o WHERE o.customer_id=c.id AND o.owner_user_id IS NOT NULL ORDER BY o.updated_at DESC,o.id DESC LIMIT 1),
                       (SELECT u.id FROM users u WHERE u.is_active=1 AND (u.name=c.owner_name OR u.email=c.owner_name) ORDER BY u.id LIMIT 1)
                   ) AS resolved_owner_user_id,
                   COALESCE(
                       (SELECT o.owner_name FROM crm_opportunities o WHERE o.customer_id=c.id AND o.owner_name IS NOT NULL AND o.owner_name<>'' ORDER BY o.updated_at DESC,o.id DESC LIMIT 1),
                       c.owner_name
                   ) AS resolved_owner_name
            FROM crm_customers c
            WHERE c.id=? AND c.deleted_at IS NULL
            LIMIT 1
        ");
        $stmt->execute(array($customerId));
        $customer = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$customer || $this->isSupplierOnly($customer)) {
            return array('created' => false, 'reason' => 'Cliente no disponible o proveedor puro.');
        }

        $document = null;
        $preferredFactoDocumentId = trim((string)$preferredFactoDocumentId);
        if ($preferredFactoDocumentId !== '') {
            $normalizedTax = $this->normalizeTaxId((string)($customer['customer_tax_id'] ?? ''));
            $conditions = array('document_id=?');
            $params = array($preferredFactoDocumentId);
            if ($customerId > 0) {
                $conditions[] = 'crm_customer_id=?';
                $params[] = $customerId;
            }
            if ($normalizedTax !== '') {
                $conditions[] = $this->normalizedTaxSql('receiver_tax_id_code') . '=?';
                $params[] = $normalizedTax;
            }
            $stmt = $this->pdo->prepare("SELECT * FROM facto_documents WHERE " . $this->validPurchaseDocumentWhere() . " AND document_id=? AND (" . implode(' OR ', array_slice($conditions, 1)) . ") LIMIT 1");
            // document_id is always the first parameter; customer/RUT prove ownership.
            $stmt->execute($params);
            $document = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        }
        if (!$document) {
            $document = $this->latestCustomerPurchase($customerId, (string)($customer['customer_tax_id'] ?? ''));
        }
        if (!$document) {
            return array('created' => false, 'reason' => 'No existe una compra emitida y vigente para el cliente.');
        }

        $context = array_merge($customer, array(
            'id' => null,
            'customer_id' => $customerId,
            'customer_name' => $customer['customer_name'] ?? '',
            'customer_tax_id' => $customer['customer_tax_id'] ?? '',
            'current_customer_tax_id' => $customer['customer_tax_id'] ?? '',
            'owner_user_id' => !empty($customer['resolved_owner_user_id']) ? (int)$customer['resolved_owner_user_id'] : null,
            'owner_name' => $customer['resolved_owner_name'] ?? ($customer['owner_name'] ?? null),
            'expected_value' => $this->documentAmountClp($document),
        ));
        return $this->upsertForecastFromDocument($context, $document, $source);
    }

    public function cancelCustomerForecasts(int $customerId, string $reason = 'Ciclo de reposicion desactivado'): int
    {
        if ($customerId <= 0 || !$this->tableExists('crm_restock_forecasts')) return 0;
        $stmt = $this->pdo->prepare("
            UPDATE crm_restock_forecasts
            SET status='cancelled', cancelled_at=NOW(),
                notes=CONCAT(COALESCE(notes,''), CASE WHEN COALESCE(notes,'')='' THEN '' ELSE ' | ' END, ?),
                updated_at=NOW()
            WHERE customer_id=? AND status IN ('pending','ready')
        ");
        $stmt->execute(array(substr(trim($reason), 0, 255), $customerId));
        return $stmt->rowCount();
    }

    public function recalculateCustomerCycle(int $customerId, string $customerTaxId = '')
    {
        if ($customerId <= 0) return false;

        $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id = ? AND deleted_at IS NULL LIMIT 1");
        $stmt->execute(array($customerId));
        $customer = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$customer || $this->isSupplierOnly($customer)) return false;

        $taxId = trim($customerTaxId) !== '' ? $customerTaxId : (string)($customer['customer_tax_id'] ?? '');
        $documents = $this->customerPurchaseDocuments($customerId, $taxId, 20);
        if (count($documents) < 2) {
            $this->updateCustomerPurchaseSnapshot($customerId, $documents[0]['issue_date'] ?? null);
            return false;
        }

        $dates = array();
        foreach ($documents as $document) {
            $date = trim((string)($document['issue_date'] ?? ''));
            if ($date !== '') $dates[$date] = $date;
        }
        $dates = array_values($dates);
        sort($dates);
        if (count($dates) < 2) {
            $this->updateCustomerPurchaseSnapshot($customerId, $documents[0]['issue_date'] ?? null);
            return false;
        }

        $intervals = array();
        for ($i = 1; $i < count($dates); $i++) {
            $days = (int)((strtotime($dates[$i]) - strtotime($dates[$i - 1])) / 86400);
            if ($days >= 3 && $days <= 730) $intervals[] = $days;
        }
        if (!$intervals) {
            $this->updateCustomerPurchaseSnapshot($customerId, $documents[0]['issue_date'] ?? null);
            return false;
        }

        sort($intervals);
        $count = count($intervals);
        $middle = intdiv($count, 2);
        $cycle = $count % 2 === 1
            ? (int)$intervals[$middle]
            : (int)round(($intervals[$middle - 1] + $intervals[$middle]) / 2);
        $cycle = max(3, min(730, $cycle));
        $confidence = min(0.95, 0.45 + (0.10 * $count));
        $lastPurchaseDate = end($dates);
        $nextRestockDate = date('Y-m-d', strtotime($lastPurchaseDate . ' +' . $cycle . ' days'));

        $fields = array('restock_days = ?', 'next_restock_date = ?', 'last_purchase_date = ?', 'updated_at = NOW()');
        $params = array($cycle, $nextRestockDate, $lastPurchaseDate);
        if ($this->columnExists('crm_customers', 'restock_cycle_source')) {
            $fields[] = "restock_cycle_source = 'facto_calculated'";
            $fields[] = 'restock_confidence = ?';
            $fields[] = 'restock_observations = ?';
            $params[] = $confidence;
            $params[] = $count;
        }
        $params[] = $customerId;
        $this->pdo->prepare('UPDATE crm_customers SET ' . implode(', ', $fields) . ' WHERE id = ?')->execute($params);

        return array(
            'restock_days' => $cycle,
            'next_restock_date' => $nextRestockDate,
            'last_purchase_date' => $lastPurchaseDate,
            'documents_used' => count($documents),
            'intervals_used' => $count,
            'confidence' => $confidence,
            'source' => 'facto_calculated',
        );
    }

    public function projectCustomerRestockDate(int $customerId, string $customerTaxId = '')
    {
        if ($customerId <= 0) return false;
        $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id = ? AND deleted_at IS NULL LIMIT 1");
        $stmt->execute(array($customerId));
        $customer = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$customer || $this->isSupplierOnly($customer)) return false;

        $taxId = trim($customerTaxId) !== '' ? $customerTaxId : (string)($customer['customer_tax_id'] ?? '');
        $documents = $this->customerPurchaseDocuments($customerId, $taxId, 20);
        if (!$documents || empty($documents[0]['issue_date'])) return false;

        $cycle = (int)($customer['restock_days'] ?? 0);
        $source = (string)($customer['restock_cycle_source'] ?? 'legacy');
        if ($cycle <= 0 || in_array($source, array('none', 'legacy'), true)) {
            $calculated = $this->recalculateCustomerCycle($customerId, $taxId);
            if (!$calculated) return false;
            return $calculated;
        }

        $lastPurchaseDate = (string)$documents[0]['issue_date'];
        $nextRestockDate = date('Y-m-d', strtotime($lastPurchaseDate . ' +' . $cycle . ' days'));
        $this->pdo->prepare("UPDATE crm_customers SET last_purchase_date=?, next_restock_date=?, updated_at=NOW() WHERE id=?")
            ->execute(array($lastPurchaseDate, $nextRestockDate, $customerId));

        return array(
            'restock_days' => $cycle,
            'next_restock_date' => $nextRestockDate,
            'base_date' => $lastPurchaseDate,
            'last_purchase_date' => $lastPurchaseDate,
            'documents_used' => count($documents),
            'confidence' => isset($customer['restock_confidence']) ? (float)$customer['restock_confidence'] : null,
            'source' => $source,
        );
    }

    public function processDaily(): array
    {
        if (!$this->tableExists('crm_restock_forecasts')) {
            throw new RuntimeException('Falta ejecutar la migracion del motor de reposicion predictiva.');
        }

        $refresh = $this->refreshRecentForecasts();
        $lifecycle = $this->updateCustomerLifecycleStatuses();
        $reconciled = $this->reconcileConvertedPurchases();
        $converted = $this->convertDueForecasts();
        $expired = $this->closeStaleRestockOpportunities();

        return array_merge($refresh, array(
            'customer_lifecycle_updated' => $lifecycle,
            'purchases_reconciled' => $reconciled,
            'forecasts_converted' => $converted,
            'stale_opportunities_closed' => $expired,
        ));
    }

    public function updateCustomerLifecycleStatuses(): int
    {
        $recentDays = $this->settingInt('restock_recent_purchase_days', 90, 30, 365);
        $minimumLostDays = $this->settingInt('customer_lost_min_days', 180, 90, 730);
        $maximumLostDays = $this->settingInt('customer_lost_max_days', 365, $minimumLostDays, 1460);
        $multiplier = $this->settingFloat('customer_lost_cycle_multiplier', 2.0, 1.0, 5.0);

        $customers = $this->pdo->query("\n            SELECT id, customer_tax_id, counterparty_type, is_supplier, restock_days,\n                   last_purchase_date, customer_lifecycle_status, next_restock_date\n            FROM crm_customers\n            WHERE deleted_at IS NULL\n        ")->fetchAll(PDO::FETCH_ASSOC);

        $updated = 0;
        foreach ($customers as $customer) {
            $customerId = (int)$customer['id'];
            if ($this->isSupplierOnly($customer)) {
                $needsUpdate = !empty($customer['last_purchase_date'])
                    || (string)($customer['customer_lifecycle_status'] ?? 'unknown') !== 'unknown'
                    || !empty($customer['next_restock_date']);
                if ($needsUpdate) {
                    $stmt = $this->pdo->prepare("UPDATE crm_customers SET last_purchase_date=NULL, customer_lifecycle_status='unknown', customer_lifecycle_updated_at=NOW(), next_restock_date=NULL, updated_at=NOW() WHERE id=?");
                    $stmt->execute(array($customerId));
                    $updated += $stmt->rowCount();
                }
                continue;
            }

            $latest = $this->latestCustomerPurchase($customerId, (string)($customer['customer_tax_id'] ?? ''));
            $lastDate = $latest['issue_date'] ?? null;
            if (!$lastDate) {
                $status = 'unknown';
                $nextDate = null;
            } else {
                $age = max(0, (int)floor((strtotime(date('Y-m-d')) - strtotime($lastDate)) / 86400));
                $cycleSource = (string)($customer['restock_cycle_source'] ?? 'none');
                $cycle = in_array($cycleSource, array('manual','facto_calculated'), true)
                    ? max(0, (int)($customer['restock_days'] ?? 0))
                    : 0;
                $lostAfter = max($minimumLostDays, $cycle > 0 ? (int)ceil($cycle * $multiplier) : $minimumLostDays);
                $lostAfter = min($maximumLostDays, $lostAfter);
                $status = $age <= $recentDays ? 'active' : ($age <= $lostAfter ? 'at_risk' : 'lost');
                $nextDate = $cycle > 0 ? date('Y-m-d', strtotime($lastDate . ' +' . $cycle . ' days')) : null;
            }

            $needsUpdate = (string)($customer['last_purchase_date'] ?? '') !== (string)($lastDate ?? '')
                || (string)($customer['customer_lifecycle_status'] ?? 'unknown') !== $status
                || (string)($customer['next_restock_date'] ?? '') !== (string)($nextDate ?? '');
            if ($needsUpdate) {
                $stmt = $this->pdo->prepare("
                    UPDATE crm_customers
                    SET last_purchase_date=?, customer_lifecycle_status=?, customer_lifecycle_updated_at=NOW(),
                        next_restock_date=?, updated_at=NOW()
                    WHERE id=?
                ");
                $stmt->execute(array($lastDate, $status, $nextDate, $customerId));
                $updated += $stmt->rowCount();
            }
        }
        return $updated;
    }

    public function convertDueForecasts(): int
    {
        $today = date('Y-m-d');
        $recentDays = $this->settingInt('restock_recent_purchase_days', 90, 30, 365);
        $stmt = $this->pdo->prepare("\n            SELECT f.*, c.customer_name, c.customer_tax_id, c.counterparty_type, c.is_supplier, c.deleted_at AS customer_deleted_at\n            FROM crm_restock_forecasts f\n            JOIN crm_customers c ON c.id=f.customer_id\n            WHERE f.status IN ('pending','ready')\n              AND f.activation_date <= ?\n            ORDER BY f.activation_date ASC, f.id ASC\n            LIMIT 500\n        ");
        $stmt->execute(array($today));
        $forecasts = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $converted = 0;

        foreach ($forecasts as $forecast) {
            try {
                if (!empty($forecast['customer_deleted_at']) || $this->isSupplierOnly($forecast)) {
                    $this->markForecast((int)$forecast['id'], 'cancelled', 'Cliente eliminado o proveedor puro.');
                    continue;
                }

                $latest = $this->latestCustomerPurchase((int)$forecast['customer_id'], (string)$forecast['customer_tax_id']);
                if ($latest && (string)$latest['issue_date'] > (string)$forecast['last_purchase_date']) {
                    $this->markForecast((int)$forecast['id'], 'superseded', 'Existe una compra posterior al pronostico.');
                    continue;
                }

                $age = (int)floor((strtotime($today) - strtotime((string)$forecast['last_purchase_date'])) / 86400);
                if ($age > $recentDays) {
                    $this->markForecast((int)$forecast['id'], 'expired', 'La ultima compra supera la ventana activa de ' . $recentDays . ' dias.');
                    continue;
                }

                if (!$this->isValidIssuedDocumentId((string)$forecast['source_facto_document_id'])) {
                    $this->markForecast((int)$forecast['id'], 'cancelled', 'El DTE de origen ya no esta vigente.');
                    continue;
                }

                $existing = $this->findExistingRestockOpportunity($forecast);
                if ($existing) {
                    $linkedForecastId = (int)($existing['restock_forecast_id'] ?? 0);
                    if ($linkedForecastId > 0 && $linkedForecastId !== (int)$forecast['id']) {
                        $this->markForecast((int)$forecast['id'], 'superseded', 'Ya existe una oportunidad predictiva activa #' . (int)$existing['id'] . '.');
                    } else {
                        $this->linkForecastToOpportunity((int)$forecast['id'], (int)$existing['id']);
                    }
                    continue;
                }

                $this->pdo->beginTransaction();
                $lock = $this->pdo->prepare("SELECT * FROM crm_restock_forecasts WHERE id=? FOR UPDATE");
                $lock->execute(array((int)$forecast['id']));
                $current = $lock->fetch(PDO::FETCH_ASSOC);
                if (!$current || !in_array($current['status'], array('pending','ready'), true)) {
                    $this->pdo->rollBack();
                    continue;
                }

                $title = 'Reposicion predictiva - ' . date('m/Y', strtotime((string)$forecast['projected_restock_date']));
                $nextActionDate = max($today, (string)$forecast['activation_date']);
                $notes = 'Creada automaticamente desde pronostico de reposicion #' . (int)$forecast['id'] . '. '
                    . 'Compra base: ' . $forecast['last_purchase_date'] . '. Ciclo: ' . (int)$forecast['restock_days'] . ' dias. '
                    . 'Confianza: ' . number_format(((float)$forecast['confidence_score']) * 100, 0, ',', '.') . '%.';

                $insert = $this->pdo->prepare("\n                    INSERT INTO crm_opportunities\n                    (customer_id,title,customer_name,customer_tax_id,stage,payment_status,expected_value,probability,\n                     owner_name,owner_user_id,source,expected_close_date,next_action,next_action_date,notes,restock_forecast_id,created_at,updated_at)\n                    VALUES (?,?,?,?,'nuevo','no_facturado',?,20,?,?, 'Reposicion predictiva',?,?,?,?,?,NOW(),NOW())\n                ");
                $insert->execute(array(
                    (int)$forecast['customer_id'], $title, $forecast['customer_name'], $forecast['customer_tax_id'],
                    (float)$forecast['expected_value'], $forecast['owner_name'] ?: null,
                    !empty($forecast['owner_user_id']) ? (int)$forecast['owner_user_id'] : null,
                    $forecast['projected_restock_date'], 'Contactar por reposicion', $nextActionDate, $notes, (int)$forecast['id']
                ));
                $opportunityId = (int)$this->pdo->lastInsertId();

                $this->pdo->prepare("UPDATE crm_restock_forecasts SET status='converted', opportunity_id=?, converted_at=NOW(), updated_at=NOW() WHERE id=?")
                    ->execute(array($opportunityId, (int)$forecast['id']));

                $this->insertActivity($opportunityId, (int)$forecast['customer_id'], $forecast['customer_name'], 'Pronostico convertido en oportunidad', $notes, 'Sistema BI');
                OpportunityStageHistoryService::record($this->pdo, $opportunityId, null, 'nuevo', null, 'Sistema BI', 'system');
                OpportunityStageHistoryService::lifecycle($this->pdo, $opportunityId, 'restock_forecast_conversion', null, array('forecast_id' => (int)$forecast['id'], 'stage' => 'nuevo'), null, 'Sistema BI', 'restock');
                $this->pdo->commit();
                $converted++;
            } catch (Throwable $e) {
                if ($this->pdo->inTransaction()) $this->pdo->rollBack();
                error_log('Restock forecast conversion #' . (int)$forecast['id'] . ': ' . $e->getMessage());
            }
        }

        return $converted;
    }

    public function reconcileConvertedPurchases(): int
    {
        $stmt = $this->pdo->query("\n            SELECT o.*, f.last_purchase_date AS forecast_last_purchase_date, f.id AS forecast_id, c.customer_tax_id AS current_tax_id\n            FROM crm_opportunities o\n            JOIN crm_restock_forecasts f ON f.id=o.restock_forecast_id\n            JOIN crm_customers c ON c.id=o.customer_id\n            WHERE o.deleted_at IS NULL AND o.archived_at IS NULL\n              AND o.source='Reposicion predictiva'\n              AND o.stage IN ('nuevo','contactado','cotizando','negociacion','ganada')\n        ");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $count = 0;
        foreach ($rows as $row) {
            $latest = $this->latestCustomerPurchase((int)$row['customer_id'], (string)$row['current_tax_id']);
            if (!$latest || (string)$latest['issue_date'] <= (string)$row['forecast_last_purchase_date']) continue;

            try {
                $this->pdo->beginTransaction();
                $opportunityUpdate = $this->pdo->prepare("\n                    UPDATE crm_opportunities\n                    SET stage='facturada', probability=100, facto_document_id=?,\n                        payment_status=CASE WHEN ?='pagado' THEN 'pagado' ELSE 'por_cobrar' END,\n                        payment_due_date=?, payment_paid_date=?, commercial_closed_at=NOW(), closed_at=NOW(),\n                        closure_outcome='invoiced', closure_reason='Reposicion confirmada por nueva compra',\n                        archived_at=NOW(), archived_by_user_id=NULL, archive_source='system', updated_at=NOW()\n                    WHERE id=? AND archived_at IS NULL\n                ");
                $opportunityUpdate->execute(array(
                    $latest['document_id'], $latest['payment_status'] ?? null,
                    $latest['payment_due_date'] ?? null, $latest['payment_paid_date'] ?? null, (int)$row['id']
                ));
                if ($opportunityUpdate->rowCount() !== 1) {
                    $this->pdo->rollBack();
                    continue;
                }
                $this->pdo->prepare("UPDATE crm_restock_forecasts SET status='converted', notes=CONCAT(COALESCE(notes,''),' | Compra confirmada: ',?), updated_at=NOW() WHERE id=?")
                    ->execute(array($latest['document_id'], (int)$row['forecast_id']));
                OpportunityStageHistoryService::record($this->pdo, (int)$row['id'], (string)$row['stage'], 'facturada', null, 'Sistema BI', 'system');
                OpportunityStageHistoryService::lifecycle($this->pdo, (int)$row['id'], 'restock_purchase_reconciled', array('stage' => $row['stage']), array('stage' => 'facturada', 'facto_document_id' => $latest['document_id']), null, 'Sistema BI', 'restock');
                $this->insertActivity((int)$row['id'], (int)$row['customer_id'], (string)$row['customer_name'], 'Reposicion confirmada', 'Se detecto una nueva compra emitida el ' . $latest['issue_date'] . '. La oportunidad fue facturada y archivada.', 'Sistema BI');
                $this->pdo->commit();
                try {
                    $this->refreshFromOpportunity((int)$row['id'], (string)$latest['document_id'], 'restock_purchase_reconciled');
                } catch (Throwable $forecastError) {
                    error_log('Next restock forecast after purchase ' . (int)$row['id'] . ': ' . $forecastError->getMessage());
                }
                $count++;
            } catch (Throwable $e) {
                if ($this->pdo->inTransaction()) $this->pdo->rollBack();
                error_log('Restock purchase reconciliation opportunity ' . (int)$row['id'] . ': ' . $e->getMessage());
            }
        }
        return $count;
    }

    public function closeStaleRestockOpportunities(): int
    {
        $graceDays = $this->settingInt('restock_opportunity_grace_days', 30, 7, 180);
        $cutoff = date('Y-m-d', strtotime('-' . $graceDays . ' days'));
        $stmt = $this->pdo->prepare("\n            SELECT o.*, f.id AS forecast_id, f.last_purchase_date AS forecast_last_purchase_date\n            FROM crm_opportunities o\n            LEFT JOIN crm_restock_forecasts f ON f.id=o.restock_forecast_id\n            WHERE o.deleted_at IS NULL AND o.archived_at IS NULL\n              AND o.source='Reposicion predictiva'\n              AND o.stage IN ('nuevo','contactado','cotizando','negociacion')\n              AND o.expected_close_date IS NOT NULL AND o.expected_close_date < ?\n        ");
        $stmt->execute(array($cutoff));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $closed = 0;

        foreach ($rows as $row) {
            if ($this->hasOpenQuote((int)$row['id']) || $this->hasRecentHumanActivity((int)$row['id'], $graceDays)) continue;
            $latest = $this->latestCustomerPurchase((int)$row['customer_id'], (string)$row['customer_tax_id']);
            if ($latest && !empty($row['forecast_last_purchase_date']) && (string)$latest['issue_date'] > (string)$row['forecast_last_purchase_date']) continue;

            try {
                $this->pdo->beginTransaction();
                $update = $this->pdo->prepare("\n                    UPDATE crm_opportunities\n                    SET stage='perdida', probability=0, lost_reason='Reposicion proyectada no concretada',\n                        commercial_closed_at=NOW(), closed_at=NOW(), closure_outcome='lost',\n                        closure_reason='Reposicion proyectada no concretada',\n                        archived_at=NOW(), archived_by_user_id=NULL, archive_source='system', updated_at=NOW()\n                    WHERE id=? AND archived_at IS NULL\n                ");
                $update->execute(array((int)$row['id']));
                if ($update->rowCount() !== 1) {
                    $this->pdo->rollBack();
                    continue;
                }
                if (!empty($row['forecast_id'])) {
                    $this->pdo->prepare("UPDATE crm_restock_forecasts SET status='expired', cancelled_at=NOW(), notes=CONCAT(COALESCE(notes,''),' | Oportunidad vencida sin actividad.'), updated_at=NOW() WHERE id=?")
                        ->execute(array((int)$row['forecast_id']));
                }
                OpportunityStageHistoryService::record($this->pdo, (int)$row['id'], (string)$row['stage'], 'perdida', null, 'Sistema BI', 'system');
                OpportunityStageHistoryService::lifecycle($this->pdo, (int)$row['id'], 'restock_expired', array('stage' => $row['stage']), array('stage' => 'perdida', 'reason' => 'Reposicion proyectada no concretada'), null, 'Sistema BI', 'restock');
                $this->insertActivity((int)$row['id'], (int)$row['customer_id'], (string)$row['customer_name'], 'Reposicion cerrada automaticamente', 'Sin compra, cotizacion ni actividad comercial reciente luego de ' . $graceDays . ' dias de gracia.', 'Sistema BI');
                $this->pdo->commit();
                $closed++;
            } catch (Throwable $e) {
                if ($this->pdo->inTransaction()) $this->pdo->rollBack();
                error_log('Restock stale close opportunity ' . (int)$row['id'] . ': ' . $e->getMessage());
            }
        }
        return $closed;
    }

    private function upsertForecastFromDocument(array $opportunity, array $document, string $source): array
    {
        $customerId = (int)$opportunity['customer_id'];
        $taxId = (string)($opportunity['current_customer_tax_id'] ?? $opportunity['customer_tax_id'] ?? '');
        $latestDocument = $this->latestCustomerPurchase($customerId, $taxId);
        if ($latestDocument && (string)($latestDocument['issue_date'] ?? '') >= (string)($document['issue_date'] ?? '')) {
            $document = $latestDocument;
        }
        $lastPurchaseDate = trim((string)($document['issue_date'] ?? ''));
        if ($lastPurchaseDate === '' || strtotime($lastPurchaseDate) === false) {
            return array('created' => false, 'reason' => 'El DTE de origen no tiene una fecha de emision valida.');
        }
        $recentDays = $this->settingInt('restock_recent_purchase_days', 90, 30, 365);
        $age = (int)floor((strtotime(date('Y-m-d')) - strtotime($lastPurchaseDate)) / 86400);
        if ($age > $recentDays) {
            return array('created' => false, 'reason' => 'La ultima compra supera ' . $recentDays . ' dias.');
        }

        $cycleSource = (string)($opportunity['restock_cycle_source'] ?? 'none');
        $projection = $cycleSource === 'manual'
            ? $this->projectCustomerRestockDate($customerId, $taxId)
            : $this->recalculateCustomerCycle($customerId, $taxId);
        if (!$projection || empty($projection['restock_days'])) {
            $this->updateCustomerPurchaseSnapshot($customerId, $lastPurchaseDate);
            return array('created' => false, 'reason' => 'Faltan compras repetidas o un ciclo manual valido.');
        }

        $restockDays = (int)$projection['restock_days'];
        $projectedDate = date('Y-m-d', strtotime($lastPurchaseDate . ' +' . $restockDays . ' days'));
        $leadDays = $this->settingInt('restock_activation_lead_days', 14, 1, 60);
        $activationDate = date('Y-m-d', strtotime($projectedDate . ' -' . $leadDays . ' days'));
        $expectedValue = $this->medianRecentPurchaseValue($customerId, $taxId, 3);
        $confidence = isset($projection['confidence']) && $projection['confidence'] !== null
            ? (float)$projection['confidence']
            : (($projection['source'] ?? '') === 'manual' ? 1.0 : 0.60);
        $sourceDocumentId = (int)$document['id'];
        $factoDocumentId = (string)$document['document_id'];
        $automationKey = hash('sha256', 'restock|' . $customerId . '|' . $factoDocumentId . '|' . $projectedDate);
        $status = $activationDate <= date('Y-m-d') ? 'ready' : 'pending';

        $this->pdo->prepare("\n            UPDATE crm_restock_forecasts\n            SET status='superseded', cancelled_at=NOW(), notes=CONCAT(COALESCE(notes,''),' | Sustituido por pronostico ', ?), updated_at=NOW()\n            WHERE customer_id=? AND status IN ('pending','ready') AND automation_key<>?\n        ")->execute(array($factoDocumentId, $customerId, $automationKey));

        $stmt = $this->pdo->prepare("\n            INSERT INTO crm_restock_forecasts\n            (customer_id,source_opportunity_id,source_quote_id,source_document_id,source_facto_document_id,\n             last_purchase_date,restock_days,projected_restock_date,activation_date,expected_value,owner_user_id,owner_name,\n             confidence_score,purchase_observations,status,automation_key,source_event,created_at,updated_at)\n            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())\n            ON DUPLICATE KEY UPDATE\n              source_opportunity_id=VALUES(source_opportunity_id), source_quote_id=VALUES(source_quote_id),\n              source_document_id=VALUES(source_document_id), last_purchase_date=VALUES(last_purchase_date),\n              restock_days=VALUES(restock_days), projected_restock_date=VALUES(projected_restock_date),\n              activation_date=VALUES(activation_date), expected_value=VALUES(expected_value),\n              owner_user_id=VALUES(owner_user_id), owner_name=VALUES(owner_name), confidence_score=VALUES(confidence_score),\n              purchase_observations=VALUES(purchase_observations),\n              status=CASE WHEN status IN ('converted','cancelled','expired') THEN status ELSE VALUES(status) END,\n              source_event=VALUES(source_event), updated_at=NOW()\n        ");
        $stmt->execute(array(
            $customerId, !empty($opportunity['id']) ? (int)$opportunity['id'] : null, !empty($document['crm_quote_id']) ? (int)$document['crm_quote_id'] : null,
            $sourceDocumentId, $factoDocumentId, $lastPurchaseDate, $restockDays, $projectedDate, $activationDate,
            $expectedValue, !empty($opportunity['owner_user_id']) ? (int)$opportunity['owner_user_id'] : null,
            $opportunity['owner_name'] ?: ($opportunity['customer_owner_name'] ?? null), $confidence,
            (int)($projection['documents_used'] ?? 1), $status, $automationKey, substr($source, 0, 80)
        ));

        $forecastId = (int)$this->pdo->lastInsertId();
        if ($forecastId <= 0) {
            $lookup = $this->pdo->prepare("SELECT id FROM crm_restock_forecasts WHERE automation_key=? LIMIT 1");
            $lookup->execute(array($automationKey));
            $forecastId = (int)$lookup->fetchColumn();
        }

        $this->pdo->prepare("UPDATE crm_customers SET last_purchase_date=?, next_restock_date=?, customer_lifecycle_status='active', customer_lifecycle_updated_at=NOW(), updated_at=NOW() WHERE id=?")
            ->execute(array($lastPurchaseDate, $projectedDate, $customerId));

        return array(
            'created' => true,
            'forecast_id' => $forecastId,
            'status' => $status,
            'last_purchase_date' => $lastPurchaseDate,
            'projected_restock_date' => $projectedDate,
            'activation_date' => $activationDate,
            'restock_days' => $restockDays,
            'expected_value' => $expectedValue,
        );
    }

    private function findIssuedDocumentForOpportunity(array $opportunity, ?string $preferredFactoDocumentId): ?array
    {
        $opportunityId = (int)$opportunity['id'];
        $customerId = (int)$opportunity['customer_id'];
        $taxId = $this->normalizeTaxId((string)($opportunity['current_customer_tax_id'] ?? $opportunity['customer_tax_id'] ?? ''));
        $ids = array_values(array_filter(array_unique(array(
            trim((string)$preferredFactoDocumentId),
            trim((string)($opportunity['facto_document_id'] ?? '')),
        ))));

        if ($ids) {
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $stmt = $this->pdo->prepare("SELECT * FROM facto_documents WHERE document_id IN ($placeholders) AND " . $this->validPurchaseDocumentWhere() . " ORDER BY issue_date DESC,id DESC LIMIT 1");
            $stmt->execute($ids);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row) return $row;
        }

        $stmt = $this->pdo->prepare("\n            SELECT fd.* FROM facto_documents fd\n            WHERE " . $this->validPurchaseDocumentWhere('fd') . "\n              AND (fd.crm_opportunity_id=?\n                   OR fd.crm_quote_id IN (SELECT id FROM crm_quotes WHERE opportunity_id=?)\n                   OR fd.document_id IN (SELECT facto_document_id FROM crm_documents WHERE opportunity_id=? AND facto_document_id IS NOT NULL))\n            ORDER BY fd.issue_date DESC, fd.id DESC LIMIT 1\n        ");
        $stmt->execute(array($opportunityId, $opportunityId, $opportunityId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) return $row;

        if ($customerId > 0 || $taxId !== '') {
            $conditions = array();
            $params = array();
            if ($customerId > 0) {
                $conditions[] = 'fd.crm_customer_id=?';
                $params[] = $customerId;
            }
            if ($taxId !== '') {
                $conditions[] = $this->normalizedTaxSql('fd.receiver_tax_id_code') . '=?';
                $params[] = $taxId;
            }
            $eventDate = substr((string)($opportunity['commercial_closed_at'] ?? $opportunity['closed_at'] ?? $opportunity['updated_at'] ?? date('Y-m-d')), 0, 10);
            $params[] = date('Y-m-d', strtotime($eventDate . ' -7 days'));
            $params[] = date('Y-m-d', strtotime($eventDate . ' +7 days'));
            $stmt = $this->pdo->prepare("\n                SELECT fd.* FROM facto_documents fd\n                WHERE " . $this->validPurchaseDocumentWhere('fd') . "\n                  AND (" . implode(' OR ', $conditions) . ")\n                  AND fd.issue_date BETWEEN ? AND ?\n                ORDER BY fd.issue_date DESC, fd.id DESC\n                LIMIT 3\n            ");
            $stmt->execute($params);
            $candidates = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $expectedValue = (float)($opportunity['expected_value'] ?? 0);
            if ($expectedValue > 0) {
                $best = null;
                $bestDiff = INF;
                foreach ($candidates as $candidate) {
                    $diff = abs($this->documentAmountClp($candidate) - $expectedValue);
                    if ($diff < $bestDiff) {
                        $best = $candidate;
                        $bestDiff = $diff;
                    }
                }
                $tolerance = max(2.0, $expectedValue * 0.05);
                if ($best && $bestDiff <= $tolerance) return $best;
            } elseif (count($candidates) === 1) {
                return $candidates[0];
            }
        }

        return null;
    }

    private function customerPurchaseDocuments(int $customerId, string $taxId, int $limit): array
    {
        $conditions = array();
        $params = array();
        if ($customerId > 0) {
            $conditions[] = 'crm_customer_id=?';
            $params[] = $customerId;
        }
        $normalized = $this->normalizeTaxId($taxId);
        if ($normalized !== '') {
            $conditions[] = $this->normalizedTaxSql('receiver_tax_id_code') . '=?';
            $params[] = $normalized;
        }
        if (!$conditions) return array();
        $limit = max(1, min(100, $limit));
        $stmt = $this->pdo->prepare("SELECT * FROM facto_documents WHERE " . $this->validPurchaseDocumentWhere() . " AND (" . implode(' OR ', $conditions) . ") ORDER BY issue_date DESC,id DESC LIMIT " . $limit);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function latestCustomerPurchase(int $customerId, string $taxId): ?array
    {
        $rows = $this->customerPurchaseDocuments($customerId, $taxId, 1);
        return $rows[0] ?? null;
    }

    private function medianRecentPurchaseValue(int $customerId, string $taxId, int $limit): float
    {
        $documents = $this->customerPurchaseDocuments($customerId, $taxId, max(1, $limit));
        $values = array();
        foreach ($documents as $document) {
            $value = $this->documentAmountClp($document);
            if ($value > 0) $values[] = $value;
        }
        if (!$values) return 0.0;
        sort($values, SORT_NUMERIC);
        $count = count($values);
        $mid = intdiv($count, 2);
        return $count % 2 ? (float)$values[$mid] : ((float)$values[$mid - 1] + (float)$values[$mid]) / 2;
    }

    private function findExistingRestockOpportunity(array $forecast): ?array
    {
        if (!empty($forecast['opportunity_id'])) {
            $stmt = $this->pdo->prepare("SELECT * FROM crm_opportunities WHERE id=? AND deleted_at IS NULL AND archived_at IS NULL LIMIT 1");
            $stmt->execute(array((int)$forecast['opportunity_id']));
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row) return $row;
        }
        $stmt = $this->pdo->prepare("\n            SELECT * FROM crm_opportunities\n            WHERE customer_id=? AND deleted_at IS NULL AND archived_at IS NULL\n              AND (restock_forecast_id=? OR source='Reposicion predictiva')\n            ORDER BY id DESC LIMIT 1\n        ");
        $stmt->execute(array((int)$forecast['customer_id'], (int)$forecast['id']));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function linkForecastToOpportunity(int $forecastId, int $opportunityId): void
    {
        $this->pdo->prepare("UPDATE crm_restock_forecasts SET status='converted', opportunity_id=?, converted_at=COALESCE(converted_at,NOW()), updated_at=NOW() WHERE id=?")
            ->execute(array($opportunityId, $forecastId));
        if ($this->columnExists('crm_opportunities', 'restock_forecast_id')) {
            $this->pdo->prepare("UPDATE crm_opportunities SET restock_forecast_id=COALESCE(restock_forecast_id,?) WHERE id=?")
                ->execute(array($forecastId, $opportunityId));
        }
    }

    private function markForecast(int $id, string $status, string $reason): void
    {
        $this->pdo->prepare("UPDATE crm_restock_forecasts SET status=?, cancelled_at=CASE WHEN ? IN ('cancelled','expired','superseded') THEN NOW() ELSE cancelled_at END, notes=CONCAT(COALESCE(notes,''), CASE WHEN COALESCE(notes,'')='' THEN '' ELSE ' | ' END, ?), updated_at=NOW() WHERE id=?")
            ->execute(array($status, $status, $reason, $id));
    }

    private function isValidIssuedDocumentId(string $documentId): bool
    {
        if ($documentId === '') return false;
        $stmt = $this->pdo->prepare("SELECT 1 FROM facto_documents WHERE document_id=? AND " . $this->validPurchaseDocumentWhere() . " LIMIT 1");
        $stmt->execute(array($documentId));
        return (bool)$stmt->fetchColumn();
    }

    private function documentAmountClp(array $document): float
    {
        if (isset($document['total_amount_clp']) && $document['total_amount_clp'] !== null) {
            return (float)$document['total_amount_clp'];
        }
        $currency = strtoupper(trim((string)($document['currency_code'] ?? 'CLP'))) ?: 'CLP';
        $amount = (float)($document['total_amount'] ?? 0);
        if ($currency === 'CLP') return $amount;
        $rate = (float)($document['exchange_rate_value'] ?? 0);
        return $rate > 0 ? round($amount * $rate, 2) : 0.0;
    }

    private function validPurchaseDocumentWhere(string $alias = ''): string
    {
        $p = $alias !== '' ? $alias . '.' : '';
        return $p . "received_issued_flag=1 AND " . $p . "document_status=1 AND COALESCE(" . $p . "payment_status,'')<>'anulado' AND COALESCE(" . $p . "document_type_taxbureau,'') IN ('33','34','39','110')";
    }

    private function normalizedTaxSql(string $column): string
    {
        return "REPLACE(REPLACE(REPLACE(UPPER($column),'.',''),'-',''),' ','')";
    }

    private function normalizeTaxId(string $taxId): string
    {
        return strtoupper(preg_replace('/[^0-9K]/i', '', trim($taxId)) ?: '');
    }

    private function isSupplierOnly(array $row): bool
    {
        // counterparty_type es la fuente de verdad. Un cliente puede ser tambien proveedor
        // (is_supplier=1) sin perder su elegibilidad comercial.
        return (string)($row['counterparty_type'] ?? '') === 'supplier';
    }

    private function updateCustomerPurchaseSnapshot(int $customerId, ?string $lastPurchaseDate): void
    {
        if (!$this->columnExists('crm_customers', 'last_purchase_date')) return;
        $this->pdo->prepare("UPDATE crm_customers SET last_purchase_date=?, customer_lifecycle_status=CASE WHEN ? IS NULL THEN 'unknown' ELSE 'active' END, customer_lifecycle_updated_at=NOW(), updated_at=NOW() WHERE id=?")
            ->execute(array($lastPurchaseDate, $lastPurchaseDate, $customerId));
    }

    private function hasOpenQuote(int $opportunityId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM crm_quotes WHERE opportunity_id=? AND deleted_at IS NULL AND status NOT IN ('Rechazada','Vencida','Anulada') LIMIT 1");
        $stmt->execute(array($opportunityId));
        return (bool)$stmt->fetchColumn();
    }

    private function hasRecentHumanActivity(int $opportunityId, int $days): bool
    {
        $cutoff = date('Y-m-d H:i:s', strtotime('-' . max(1, $days) . ' days'));
        $stmt = $this->pdo->prepare("\n            SELECT 1 FROM crm_activities\n            WHERE opportunity_id=?\n              AND activity_type IN ('nota','llamada','correo','reunion','seguimiento','cotizacion','factura','documento')\n              AND COALESCE(activity_date,created_at) >= ?\n            LIMIT 1\n        ");
        $stmt->execute(array($opportunityId, $cutoff));
        return (bool)$stmt->fetchColumn();
    }

    private function insertActivity(int $opportunityId, int $customerId, string $customerName, string $title, string $description, string $owner): void
    {
        $stmt = $this->pdo->prepare("\n            INSERT INTO crm_activities\n            (opportunity_id,customer_id,customer_name,activity_type,title,description,activity_date,owner_name,created_at)\n            VALUES (?,?,?,'sistema',?,?,NOW(),?,NOW())\n        ");
        $stmt->execute(array($opportunityId, $customerId, $customerName, $title, $description, $owner));
    }

    private function settingInt(string $key, int $default, int $min, int $max): int
    {
        $value = (int)$this->setting($key, (string)$default);
        return max($min, min($max, $value));
    }

    private function settingFloat(string $key, float $default, float $min, float $max): float
    {
        $value = (float)$this->setting($key, (string)$default);
        return max($min, min($max, $value));
    }

    private function setting(string $key, string $default): string
    {
        if (array_key_exists($key, $this->settingsCache)) return $this->settingsCache[$key];
        try {
            $stmt = $this->pdo->prepare("SELECT setting_value FROM dashboard_settings WHERE setting_key=? AND status='approved' AND (valid_from IS NULL OR valid_from<=CURDATE()) AND (valid_to IS NULL OR valid_to>=CURDATE()) LIMIT 1");
            $stmt->execute(array($key));
            $value = $stmt->fetchColumn();
            return $this->settingsCache[$key] = ($value === false ? $default : (string)$value);
        } catch (Throwable $e) {
            return $this->settingsCache[$key] = $default;
        }
    }

    private function tableExists(string $table): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=? LIMIT 1");
        $stmt->execute(array($table));
        return (bool)$stmt->fetchColumn();
    }

    private function columnExists(string $table, string $column): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=? AND column_name=? LIMIT 1");
        $stmt->execute(array($table, $column));
        return (bool)$stmt->fetchColumn();
    }
}
