<?php

namespace serviciosms;

require_once __DIR__ . '/../conexion_bbdd/vendor/autoload.php';
require_once __DIR__ . '/../conexion_bbdd/Config/Database.php';
require_once __DIR__ . '/../Logger.php';

use Utils\Database;
use Utils\Logger;

try {
    $pdo = Database::conectar();


    $consultadesms = "
    WITH datos AS (
        SELECT DISTINCT 
            initcap(split_part(ch.attorney, ' ', 1)) || ' ' || initcap(split_part(ch.attorney, ' ', 2)) AS nombre_cliente,
            cd.date AS fecha_vencimiento,
            CONCAT('51', ch.cellphone) AS n_celular,
            cd.amount AS monto,
            CASE 
                WHEN cd.date = CURRENT_DATE - INTERVAL '7 days' THEN '7'
                WHEN cd.date = CURRENT_DATE - INTERVAL '5 days' THEN '5'
                WHEN cd.date = CURRENT_DATE - INTERVAL '3 days' THEN '3'
                WHEN cd.date = CURRENT_DATE - INTERVAL '1 days' THEN '1'
                WHEN ((cd.date - CURRENT_DATE)% 7 = 0) THEN '7+'
                ELSE 'Otro'
            END AS dias_restantes,
            CASE 
                WHEN maa.id IS NOT NULL THEN '_vist'
                ELSE 'ok'
            END AS estado_pago,
		c.state_valide AS validacion
        FROM clinic_history ch
        LEFT JOIN contract c ON ch.id = c.idclinichistory
        LEFT JOIN contract_detail cd ON c.id = cd.idcontract
        LEFT JOIN medical_act_attention_contracts_pays maa ON cd.id = maa.idcontractdetail
		WHERE c.state_valide = 1
        AND cd.description ILIKE ('%Cuota%')
        AND cd.state = 1
    )
    SELECT DISTINCT ON (n_celular) nombre_cliente, fecha_vencimiento, n_celular, monto
    FROM datos
    WHERE dias_restantes <> 'Otro' AND estado_pago = 'ok'and fecha_vencimiento < CURRENT_DATE";

    $consultadesmsejecutar = $pdo->query($consultadesms)->fetchAll();

    
    echo json_encode(['success' => true, 'data' => $consultadesmsejecutar]);
} catch (\PDOException $e) {
    $mensajeError = "Error al conectar con la base de datos: " . $e->getMessage();
    Logger::logError('ERROR', $mensajeError, __FILE__, __LINE__);
    echo json_encode(['success' => false, 'message' => 'Error ' . $mensajeError]);
} catch (\Exception $e) {
    $mensajeError = "Error no capturado: " . $e->getMessage();
    Logger::logError('ERROR', $mensajeError, __FILE__, __LINE__);
    echo json_encode(['success' => false, 'message' => 'Error ' . $mensajeError]);
}
