-- ============================================================
--  CLINICALBIKE v4 — Tablas nuevas (ejecutar en phpMyAdmin)
-- ============================================================

-- Fotos por OT
CREATE TABLE IF NOT EXISTS ot_fotos (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    orden_id    VARCHAR(20) NOT NULL,
    archivo     VARCHAR(200) NOT NULL,
    descripcion VARCHAR(200),
    tipo        ENUM('ingreso','proceso','terminado') DEFAULT 'proceso',
    subida_por  VARCHAR(100),
    subida_en   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (orden_id) REFERENCES ordenes(id) ON DELETE CASCADE
);

-- Columna token en ordenes (si no existe)
ALTER TABLE ordenes ADD COLUMN IF NOT EXISTS token_consulta VARCHAR(64) AFTER web_obs;

-- Vista ingresos mensual (helper)
CREATE OR REPLACE VIEW vista_ingresos AS
SELECT
    DATE_FORMAT(o.actualizado_en,'%Y-%m') AS mes,
    COUNT(DISTINCT o.id)                  AS total_ots,
    SUM(s.precio)                         AS ingresos_sv,
    COUNT(DISTINCT o.cliente_id)          AS clientes
FROM ordenes o
LEFT JOIN orden_servicios s ON s.orden_id = o.id
WHERE o.estado >= 5
GROUP BY DATE_FORMAT(o.actualizado_en,'%Y-%m')
ORDER BY mes DESC;