-- ========================================
-- Verify OT Creation Data
-- ========================================
-- Usage: mysql -u root -p revisa < verify_ot_data.sql
-- ========================================

-- Get the latest OT
SELECT '========================================' as '';
SELECT 'LATEST OT CREATED' as '';
SELECT '========================================' as '';
SELECT 
    id_ot,
    DATE_FORMAT(fecha, '%Y-%m-%d %H:%i:%s') as fecha,
    rut_cliente,
    rut_tecnico,
    empresa,
    taller,
    direccion_instalacion,
    comuna_instalacion,
    telefono_instalacion,
    email_instalacion,
    estado_ot,
    motivo,
    logo_taller,
    logo_servicio,
    DATE_FORMAT(fecha_serv, '%Y-%m-%d') as fecha_serv,
    DATE_FORMAT(creado_en, '%Y-%m-%d %H:%i:%s') as creado_en
FROM OT 
ORDER BY id_ot DESC 
LIMIT 1;

-- Get the client info
SELECT '' as '';
SELECT '========================================' as '';
SELECT 'CLIENT INFO' as '';
SELECT '========================================' as '';
SELECT 
    rut_cliente,
    nombre,
    direccion,
    comuna,
    telefono,
    email,
    DATE_FORMAT(creado_en, '%Y-%m-%d %H:%i:%s') as creado_en,
    DATE_FORMAT(actualizado_en, '%Y-%m-%d %H:%i:%s') as actualizado_en
FROM Clientes 
WHERE rut_cliente = (SELECT rut_cliente FROM OT ORDER BY id_ot DESC LIMIT 1);

-- Get vehicles linked to latest OT
SELECT '' as '';
SELECT '========================================' as '';
SELECT 'VEHICLES LINKED TO OT' as '';
SELECT '========================================' as '';
SELECT 
    ov.id_ot_vehiculos,
    ov.id_ot,
    ov.patente,
    v.marca,
    v.modelo,
    v.anio,
    v.color,
    v.vin,
    DATE_FORMAT(v.creado_en, '%Y-%m-%d %H:%i:%s') as creado_en,
    DATE_FORMAT(v.actualizado_en, '%Y-%m-%d %H:%i:%s') as actualizado_en
FROM OT_Vehiculos ov
INNER JOIN Vehiculos v ON ov.patente = v.patente
WHERE ov.id_ot = (SELECT id_ot FROM OT ORDER BY id_ot DESC LIMIT 1);

-- Get technician info
SELECT '' as '';
SELECT '========================================' as '';
SELECT 'TECHNICIAN INFO' as '';
SELECT '========================================' as '';
SELECT 
    rut_tecnico,
    nombre,
    idgpsimple,
    DATE_FORMAT(creado_en, '%Y-%m-%d %H:%i:%s') as creado_en
FROM Tecnicos 
WHERE rut_tecnico = (SELECT rut_tecnico FROM OT ORDER BY id_ot DESC LIMIT 1);

-- Summary count
SELECT '' as '';
SELECT '========================================' as '';
SELECT 'SUMMARY' as '';
SELECT '========================================' as '';
SELECT 
    (SELECT COUNT(*) FROM OT WHERE DATE(creado_en) = CURDATE()) as ots_created_today,
    (SELECT COUNT(*) FROM Vehiculos WHERE DATE(creado_en) = CURDATE()) as new_vehicles_today,
    (SELECT COUNT(*) FROM Clientes WHERE DATE(creado_en) = CURDATE()) as new_clients_today,
    (SELECT COUNT(*) FROM OT_Vehiculos WHERE id_ot = (SELECT id_ot FROM OT ORDER BY id_ot DESC LIMIT 1)) as vehicles_in_last_ot;

SELECT '' as '';
SELECT '========================================' as '';

