<?php
/**
 * Conexion al CRM vTiger (entorno de prueba) - lectura de visitas (SalesOrder).
 * Requiere inc/db.php cargado (CONFIG).
 */
require_once __DIR__ . '/db.php';

function crm_conn(): PDO {
    static $pdo = null;
    if ($pdo instanceof PDO) return $pdo;
    global $CONFIG;
    $c = $CONFIG['crm'];
    $dsn = "mysql:host={$c['host']};dbname={$c['name']};port={$c['port']};charset={$c['charset']}";
    try {
        $pdo = new PDO($dsn, $c['user'], $c['pass'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_TIMEOUT            => 8,
        ]);
    } catch (PDOException $e) {
        http_response_code(502);
        die('No se pudo conectar al CRM.');
    }
    return $pdo;
}

function _crm_select_visitas(): string {
    $cols = "so.salesorderid AS crmid, so.salesorder_no AS salesorder_no, so.subject AS subject, ";
    $cols .= "so.sostatus AS estado_crm, a.accountname AS cliente, sh.ship_street AS direccion, ";
    $cols .= "sh.ship_city AS comuna, cf.cf_1030 AS region, cf.cf_900 AS tecnico, ";
    $cols .= "u.email1 AS vendedor_email, u.first_name AS vend_nombre, u.last_name AS vend_apellido, ";
    $cols .= "ce.createdtime AS creado";
    $from = "FROM vtiger_salesorder so ";
    $from .= "JOIN vtiger_crmentity ce ON ce.crmid = so.salesorderid AND ce.deleted = 0 ";
    $from .= "LEFT JOIN vtiger_salesordercf cf ON cf.salesorderid = so.salesorderid ";
    $from .= "LEFT JOIN vtiger_account a ON a.accountid = so.accountid ";
    $from .= "LEFT JOIN vtiger_soshipads sh ON sh.soshipaddressid = so.salesorderid ";
    $from .= "LEFT JOIN vtiger_users u ON u.id = ce.smcreatorid";
    return "SELECT " . $cols . " " . $from;
}

function vendedor_nombre(array $row): string {
    return trim(($row['vend_nombre'] ?? '') . ' ' . ($row['vend_apellido'] ?? ''));
}

function get_visitas(?string $tecnico = null, int $limit = 200): array {
    $sql = _crm_select_visitas() . " WHERE so.subject LIKE '%VISITA%ECOSOLAR%'";
    $params = [];
    if ($tecnico !== null && $tecnico !== '') {
        $sql .= " AND cf.cf_900 LIKE ?";
        $params[] = '%' . $tecnico . '%';
    }
    $sql .= " ORDER BY ce.createdtime DESC LIMIT " . (int)$limit;
    $st = crm_conn()->prepare($sql);
    $st->execute($params);
    return $st->fetchAll();
}

function get_visita(string $crmid): ?array {
    $sql = _crm_select_visitas() . " WHERE so.salesorderid = ? LIMIT 1";
    $st = crm_conn()->prepare($sql);
    $st->execute([$crmid]);
    $r = $st->fetch();
    return $r ?: null;
}
