-- ============================================================
-- quoERP — Módulo de Control de Mantenimiento
-- Migración: 002_maintenance_module.sql
-- Versión: 1.0.0 | Fecha: 2026-06-02
-- ============================================================
-- Ejecutar en: qpsec_quoerp
-- Requiere: 001_create_all_tables.sql (users, companies, customers)
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- ─────────────────────────────────────────────────────────────
-- TABLA 1: maintenance_teams
-- Equipos de trabajo (≠ equipo/maquinaria).
-- Un team atiende uno o varios tipos de equipo.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS maintenance_teams (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED    NOT NULL,
    name        VARCHAR(100)    NOT NULL,
    description TEXT,
    leader_id   INT UNSIGNED    DEFAULT NULL COMMENT 'Usuario líder del team',
    color       VARCHAR(7)      NOT NULL DEFAULT '#1B3A6B' COMMENT 'Hex para UI',
    status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by  INT UNSIGNED    DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company  (company_id),
    INDEX idx_leader   (leader_id),
    INDEX idx_status   (status),

    CONSTRAINT fk_mt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT,
    CONSTRAINT fk_mt_leader  FOREIGN KEY (leader_id)  REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Equipos de trabajo de técnicos (no confundir con maquinaria)';


-- ─────────────────────────────────────────────────────────────
-- TABLA 2: equipment_types
-- Tipos de equipo/maquinaria.
-- Un tipo → un solo team. Un team → varios tipos.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS equipment_types (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED    NOT NULL,
    team_id     INT UNSIGNED    DEFAULT NULL COMMENT 'Team responsable de este tipo',
    name        VARCHAR(100)    NOT NULL,
    description TEXT,
    icon        VARCHAR(50)     DEFAULT 'ti-device-desktop' COMMENT 'Clase Tabler Icons',
    status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by  INT UNSIGNED    DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company  (company_id),
    INDEX idx_team     (team_id),

    CONSTRAINT fk_et_company FOREIGN KEY (company_id) REFERENCES companies(id)          ON DELETE RESTRICT,
    CONSTRAINT fk_et_team    FOREIGN KEY (team_id)    REFERENCES maintenance_teams(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Tipos de equipo/maquinaria. Cada tipo vinculado a un team.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 3: equipment
-- Registro de máquinas/equipos que reciben mantenimiento.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS equipment (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    equipment_type_id   INT UNSIGNED    NOT NULL,
    name                VARCHAR(150)    NOT NULL,
    brand               VARCHAR(100)    DEFAULT NULL,
    model               VARCHAR(100)    DEFAULT NULL,
    serial_number       VARCHAR(100)    DEFAULT NULL,
    photo               VARCHAR(255)    DEFAULT NULL COMMENT 'Ruta en public/uploads/equipment/',

    -- Propiedad actual (se registra historial en equipment_ownership_history)
    current_customer_id     INT UNSIGNED DEFAULT NULL COMMENT 'FK a customers (propietario actual)',
    current_customer_name   VARCHAR(200) DEFAULT NULL COMMENT 'Snapshot del nombre al momento',
    current_location        VARCHAR(255) DEFAULT NULL COMMENT 'Descripción de ubicación',
    location_notes          TEXT         DEFAULT NULL,

    notes   TEXT            DEFAULT NULL,
    status  ENUM('active','inactive','decommissioned') NOT NULL DEFAULT 'active',

    created_by  INT UNSIGNED    DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company      (company_id),
    INDEX idx_type         (equipment_type_id),
    INDEX idx_customer     (current_customer_id),
    INDEX idx_serial       (serial_number),
    INDEX idx_status       (status),

    CONSTRAINT fk_eq_company FOREIGN KEY (company_id)         REFERENCES companies(id)       ON DELETE RESTRICT,
    CONSTRAINT fk_eq_type    FOREIGN KEY (equipment_type_id)  REFERENCES equipment_types(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Equipos/maquinaria que reciben servicio de mantenimiento';


-- ─────────────────────────────────────────────────────────────
-- TABLA 4: equipment_ownership_history
-- Historial de cambios de propiedad de un equipo.
-- Las OTs guardan el customer_id al momento de creación (inmutable).
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS equipment_ownership_history (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    equipment_id        INT UNSIGNED    NOT NULL,
    customer_id         INT UNSIGNED    DEFAULT NULL,
    customer_name_snap  VARCHAR(200)    NOT NULL COMMENT 'Snapshot: nombre del cliente en ese momento',
    location_snap       VARCHAR(255)    DEFAULT NULL COMMENT 'Snapshot: ubicación en ese momento',
    started_at          DATETIME        NOT NULL COMMENT 'Inicio de esta propiedad',
    ended_at            DATETIME        DEFAULT NULL COMMENT 'NULL = propietario actual',
    reason              VARCHAR(255)    DEFAULT NULL COMMENT 'Motivo del cambio',
    notes               TEXT            DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_equipment (equipment_id),
    INDEX idx_customer  (customer_id),
    INDEX idx_dates     (started_at, ended_at),

    CONSTRAINT fk_eoh_equipment FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Historial inmutable de cambios de propiedad por equipo';


-- ─────────────────────────────────────────────────────────────
-- TABLA 5: predefined_jobs
-- Trabajos de mantenimiento estandarizados (catálogo).
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS predefined_jobs (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    name                VARCHAR(150)    NOT NULL,
    slug                VARCHAR(150)    NOT NULL COMMENT 'Identificador interno único por empresa',
    description         TEXT            DEFAULT NULL COMMENT 'Proceso de ejecución detallado',
    frequency_days      SMALLINT UNSIGNED NOT NULL DEFAULT 30 COMMENT 'Frecuencia en días (60 = cada 2 meses)',
    estimated_minutes   SMALLINT UNSIGNED NOT NULL DEFAULT 360 COMMENT 'Duración estimada en minutos (360 = 6h)',
    supplies            TEXT            DEFAULT NULL COMMENT 'Insumos requeridos (texto libre o JSON)',
    status              ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_pj_slug (company_id, slug),
    INDEX idx_company (company_id),
    INDEX idx_status  (status),

    CONSTRAINT fk_pj_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Catálogo de trabajos de mantenimiento predefinidos y reutilizables';


-- ─────────────────────────────────────────────────────────────
-- TABLA 6: job_equipment_links
-- Asociación entre un trabajo predefinido y un equipo específico.
-- Hereda frecuencia y duración del trabajo; permite override local.
-- Controla el calendario de próximos mantenimientos.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS job_equipment_links (
    id                      INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    equipment_id            INT UNSIGNED    NOT NULL,
    predefined_job_id       INT UNSIGNED    NOT NULL,

    -- Override de parámetros (NULL = usar el del trabajo predefinido)
    custom_frequency_days   SMALLINT UNSIGNED DEFAULT NULL COMMENT 'NULL = heredar de predefined_jobs',
    custom_estimated_minutes SMALLINT UNSIGNED DEFAULT NULL COMMENT 'NULL = heredar de predefined_jobs',

    -- Control de fechas
    last_maintenance_at     DATE            DEFAULT NULL COMMENT 'Fecha del último mantenimiento ejecutado',
    next_maintenance_at     DATE            DEFAULT NULL COMMENT 'Fecha del próximo mantenimiento programado',

    notes                   TEXT            DEFAULT NULL,
    status                  ENUM('active','inactive','paused') NOT NULL DEFAULT 'active',
    created_by              INT UNSIGNED    DEFAULT NULL,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_jel_link (equipment_id, predefined_job_id),
    INDEX idx_equipment      (equipment_id),
    INDEX idx_job            (predefined_job_id),
    INDEX idx_next_maint     (next_maintenance_at) COMMENT 'Usado por cron diario',
    INDEX idx_status         (status),

    CONSTRAINT fk_jel_equipment FOREIGN KEY (equipment_id)      REFERENCES equipment(id)       ON DELETE CASCADE,
    CONSTRAINT fk_jel_job       FOREIGN KEY (predefined_job_id) REFERENCES predefined_jobs(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Vínculo equipo-trabajo con calendario de mantenimientos. El cron usa next_maintenance_at.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 7: work_orders
-- Orden de trabajo (OT): entidad central del módulo.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_orders (
    id                      INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id              INT UNSIGNED    NOT NULL,
    folio                   VARCHAR(20)     NOT NULL COMMENT 'Número legible: OT-2026-000001',

    -- Origen
    type        ENUM('preventivo','correctivo','adicional') NOT NULL DEFAULT 'correctivo',
    job_link_id INT UNSIGNED DEFAULT NULL COMMENT 'FK job_equipment_links (OTs preventivas)',
    parent_wo_id INT UNSIGNED DEFAULT NULL COMMENT 'OT padre (para visitas adicionales)',

    -- Equipo y cliente (snapshots al momento de creación)
    equipment_id            INT UNSIGNED    NOT NULL,
    equipment_name_snap     VARCHAR(150)    NOT NULL COMMENT 'Snapshot nombre del equipo',
    customer_id             INT UNSIGNED    DEFAULT NULL,
    customer_name_snap      VARCHAR(200)    DEFAULT NULL COMMENT 'Snapshot nombre del cliente',
    location_snap           VARCHAR(255)    DEFAULT NULL COMMENT 'Snapshot ubicación del equipo',

    -- Contenido de la OT
    summary                 TEXT            NOT NULL COMMENT 'Descripción del problema o motivo',

    -- Estado principal del flujo
    status  ENUM('nueva','atendiendo','cerrada','facturada','desechada') NOT NULL DEFAULT 'nueva',

    -- Condición / etiqueta adicional (puede cambiar sin afectar status)
    condition_label ENUM(
        'en_observacion','visita_adicional','cotizado',
        'garantia','para_descuento','por_facturar','concluida'
    ) DEFAULT NULL,

    -- Tiempo de trabajo
    started_at              DATETIME        DEFAULT NULL,
    finished_at             DATETIME        DEFAULT NULL,
    effective_minutes       SMALLINT UNSIGNED DEFAULT NULL COMMENT 'Minutos enteros ajustables post-cierre',

    -- Facturación
    quote_amount            DECIMAL(10,2)   DEFAULT NULL,
    quote_file              VARCHAR(255)    DEFAULT NULL COMMENT 'Ruta archivo de cotización',
    invoice_number          VARCHAR(50)     DEFAULT NULL,
    invoice_file            VARCHAR(255)    DEFAULT NULL COMMENT 'Ruta archivo factura',

    -- Aprobación digital del cliente (hora de servicio)
    service_form_data       JSON            DEFAULT NULL COMMENT 'Campos del formulario de hora de servicio',
    confirmation_token      VARCHAR(64)     DEFAULT NULL COMMENT 'Token único para link de firma del cliente',
    confirmation_token_exp  DATETIME        DEFAULT NULL COMMENT 'Expiración del token',
    confirmed_at            DATETIME        DEFAULT NULL,
    confirmed_by_name       VARCHAR(150)    DEFAULT NULL COMMENT 'Nombre del cliente que aprobó',
    confirmed_by_email      VARCHAR(150)    DEFAULT NULL,
    pdf_path                VARCHAR(255)    DEFAULT NULL COMMENT 'Ruta del PDF generado tras confirmación',

    -- Auditoría
    created_by              INT UNSIGNED    DEFAULT NULL,
    closed_by               INT UNSIGNED    DEFAULT NULL,
    discarded_by            INT UNSIGNED    DEFAULT NULL,
    discarded_reason        TEXT            DEFAULT NULL,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_wo_folio   (company_id, folio),
    UNIQUE KEY uq_wo_token   (confirmation_token),
    INDEX idx_company        (company_id),
    INDEX idx_equipment      (equipment_id),
    INDEX idx_customer       (customer_id),
    INDEX idx_status         (status),
    INDEX idx_type           (type),
    INDEX idx_parent         (parent_wo_id),
    INDEX idx_job_link       (job_link_id),
    INDEX idx_created        (created_at),

    CONSTRAINT fk_wo_company   FOREIGN KEY (company_id)   REFERENCES companies(id)          ON DELETE RESTRICT,
    CONSTRAINT fk_wo_equipment FOREIGN KEY (equipment_id) REFERENCES equipment(id)           ON DELETE RESTRICT,
    CONSTRAINT fk_wo_job_link  FOREIGN KEY (job_link_id)  REFERENCES job_equipment_links(id) ON DELETE SET NULL,
    CONSTRAINT fk_wo_parent    FOREIGN KEY (parent_wo_id) REFERENCES work_orders(id)         ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Orden de Trabajo (OT): entidad central del módulo de mantenimiento';


-- ─────────────────────────────────────────────────────────────
-- TABLA 8: work_order_technicians
-- Técnicos asignados a una OT (principal + adicionales).
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_order_technicians (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    work_order_id   INT UNSIGNED    NOT NULL,
    user_id         INT UNSIGNED    NOT NULL,
    role            ENUM('principal','adicional') NOT NULL DEFAULT 'adicional',
    added_by        INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_wot_user (work_order_id, user_id),
    INDEX idx_wo   (work_order_id),
    INDEX idx_user (user_id),

    CONSTRAINT fk_wot_wo   FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_wot_user FOREIGN KEY (user_id)       REFERENCES users(id)       ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Técnicos asignados a una OT. Un principal y N adicionales.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 9: work_order_log
-- Log de auditoría inmutable. Registra toda operación sobre una OT.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_order_log (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    work_order_id   INT UNSIGNED    NOT NULL,
    user_id         INT UNSIGNED    DEFAULT NULL,
    user_name_snap  VARCHAR(150)    DEFAULT NULL COMMENT 'Snapshot nombre del usuario',

    -- Tipo de evento
    action  ENUM(
        'created','status_changed','condition_changed','technician_added',
        'technician_removed','time_updated','quote_uploaded','invoice_added',
        'service_form_completed','client_confirmed','pdf_generated',
        'comment','quality_control','claim','feedback','other'
    ) NOT NULL,

    description     TEXT            NOT NULL COMMENT 'Descripción legible del evento',
    old_value       VARCHAR(255)    DEFAULT NULL COMMENT 'Valor anterior (ej: status previo)',
    new_value       VARCHAR(255)    DEFAULT NULL COMMENT 'Valor nuevo',
    metadata        JSON            DEFAULT NULL COMMENT 'Datos adicionales del evento',

    -- NO tiene updated_at: los logs son inmutables
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_wo      (work_order_id),
    INDEX idx_user    (user_id),
    INDEX idx_action  (action),
    INDEX idx_created (created_at),

    CONSTRAINT fk_wol_wo   FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_wol_user FOREIGN KEY (user_id)       REFERENCES users(id)       ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Log de auditoría inmutable de cada OT. No se elimina ni modifica.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 10: work_order_attachments
-- Archivos adjuntos a una OT (fotos, cotizaciones, repuestos, etc.)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_order_attachments (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    work_order_id   INT UNSIGNED    NOT NULL,
    category        ENUM('photo','quote','invoice','parts','report','other') NOT NULL DEFAULT 'other',
    filename        VARCHAR(255)    NOT NULL COMMENT 'Nombre en disco (public/uploads/wo_attachments/)',
    original_name   VARCHAR(255)    NOT NULL COMMENT 'Nombre original del archivo',
    mime_type       VARCHAR(100)    NOT NULL,
    size_bytes      INT UNSIGNED    NOT NULL,
    description     VARCHAR(255)    DEFAULT NULL,
    uploaded_by     INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_wo       (work_order_id),
    INDEX idx_category (category),

    CONSTRAINT fk_woa_wo   FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_woa_user FOREIGN KEY (uploaded_by)   REFERENCES users(id)       ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Archivos adjuntos a órdenes de trabajo (fotos, cotizaciones, facturas, etc.)';


-- ─────────────────────────────────────────────────────────────
-- TABLA 11: daily_reports
-- Informe de cierre de jornada por técnico.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS daily_reports (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    user_id         INT UNSIGNED    NOT NULL COMMENT 'Técnico que genera el informe',
    report_date     DATE            NOT NULL COMMENT 'Fecha de la jornada',

    -- Plantilla y datos
    template_notes  TEXT            DEFAULT NULL COMMENT 'Encabezado/plantilla configurable',
    general_notes   TEXT            DEFAULT NULL COMMENT 'Observaciones generales del técnico',

    -- Estado y exportación
    status      ENUM('borrador','enviado') NOT NULL DEFAULT 'borrador',
    pdf_path    VARCHAR(255)    DEFAULT NULL COMMENT 'Ruta del PDF generado',
    submitted_at DATETIME       DEFAULT NULL,

    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_dr_date (company_id, user_id, report_date) COMMENT 'Un informe por técnico por día',
    INDEX idx_company (company_id),
    INDEX idx_user    (user_id),
    INDEX idx_date    (report_date),
    INDEX idx_status  (status),

    CONSTRAINT fk_dr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT,
    CONSTRAINT fk_dr_user    FOREIGN KEY (user_id)    REFERENCES users(id)     ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Informe de cierre de jornada por técnico. Máximo uno por técnico por día.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 12: daily_report_orders
-- OTs incluidas en un informe de cierre de jornada.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS daily_report_orders (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    daily_report_id INT UNSIGNED    NOT NULL,
    work_order_id   INT UNSIGNED    NOT NULL,
    order_index     TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Orden de presentación en el informe',
    comment         TEXT            DEFAULT NULL COMMENT 'Comentario específico del técnico sobre esta OT',
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_dro_wo (daily_report_id, work_order_id),
    INDEX idx_report (daily_report_id),
    INDEX idx_wo     (work_order_id),

    CONSTRAINT fk_dro_report FOREIGN KEY (daily_report_id) REFERENCES daily_reports(id) ON DELETE CASCADE,
    CONSTRAINT fk_dro_wo     FOREIGN KEY (work_order_id)   REFERENCES work_orders(id)   ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='OTs vinculadas a un informe de cierre de jornada';


-- ─────────────────────────────────────────────────────────────
-- ALTER: Agregar team_id a users
-- Los técnicos se asocian a un maintenance_team
-- ─────────────────────────────────────────────────────────────
-- ADD COLUMN IF NOT EXISTS y ADD INDEX IF NOT EXISTS son soportados en MariaDB 10.x
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS maintenance_team_id INT UNSIGNED DEFAULT NULL
        COMMENT 'Team de mantenimiento al que pertenece el usuario';

ALTER TABLE users
    ADD INDEX IF NOT EXISTS idx_users_team (maintenance_team_id);

-- Para el FK: eliminar si ya existe y recrear (evita error de duplicado)
ALTER TABLE users
    DROP FOREIGN KEY IF EXISTS fk_users_team;

ALTER TABLE users
    ADD CONSTRAINT fk_users_team
        FOREIGN KEY (maintenance_team_id)
        REFERENCES maintenance_teams(id)
        ON DELETE SET NULL;


-- ─────────────────────────────────────────────────────────────
-- DATOS INICIALES: Catálogo de acciones del log
-- (referencia para el código PHP)
-- ─────────────────────────────────────────────────────────────
-- work_order_log.action valores:
-- created            | OT creada (manual o automática)
-- status_changed     | Cambio de estado (nueva→atendiendo, etc.)
-- condition_changed  | Cambio de etiqueta/condición
-- technician_added   | Técnico agregado a la OT
-- technician_removed | Técnico removido de la OT
-- time_updated       | Horas efectivas ajustadas
-- quote_uploaded     | Cotización subida
-- invoice_added      | Factura registrada
-- service_form_completed | Formulario de hora de servicio llenado
-- client_confirmed   | Cliente aprobó digitalmente el servicio
-- pdf_generated      | PDF de informe generado
-- comment            | Comentario libre del técnico
-- quality_control    | Revisión de calidad
-- claim              | Reclamo registrado
-- feedback           | Retroalimentación del cliente
-- other              | Otro evento


-- ─────────────────────────────────────────────────────────────
-- VIEWS útiles para consultas frecuentes
-- ─────────────────────────────────────────────────────────────

CREATE OR REPLACE VIEW v_work_orders_full AS
SELECT
    wo.id,
    wo.folio,
    wo.company_id,
    wo.type,
    wo.status,
    wo.condition_label,
    wo.summary,
    wo.effective_minutes,
    wo.quote_amount,
    wo.invoice_number,
    wo.confirmed_at,
    wo.created_at,
    wo.updated_at,

    -- Equipo
    eq.id         AS equipment_id,
    eq.name       AS equipment_name,
    et.name       AS equipment_type_name,

    -- Cliente (snapshot)
    wo.customer_name_snap,
    wo.location_snap,

    -- Técnico principal
    u.first_name  AS tech_first_name,
    u.last_name   AS tech_last_name,
    u.email       AS tech_email,

    -- Team
    mt.name       AS team_name,
    mt.color      AS team_color

FROM work_orders wo
LEFT JOIN equipment          eq  ON eq.id  = wo.equipment_id
LEFT JOIN equipment_types    et  ON et.id  = eq.equipment_type_id
LEFT JOIN work_order_technicians wot ON wot.work_order_id = wo.id AND wot.role = 'principal'
LEFT JOIN users              u   ON u.id   = wot.user_id
LEFT JOIN maintenance_teams  mt  ON mt.id  = u.maintenance_team_id;


CREATE OR REPLACE VIEW v_upcoming_maintenance AS
SELECT
    jel.id              AS link_id,
    jel.next_maintenance_at,
    jel.last_maintenance_at,
    COALESCE(jel.custom_frequency_days, pj.frequency_days) AS effective_frequency_days,
    COALESCE(jel.custom_estimated_minutes, pj.estimated_minutes) AS effective_minutes,

    -- Equipo
    eq.id               AS equipment_id,
    eq.name             AS equipment_name,
    eq.company_id,
    eq.current_customer_name,
    eq.current_location,

    -- Tipo y team
    et.name             AS equipment_type,
    mt.name             AS team_name,

    -- Trabajo
    pj.id               AS job_id,
    pj.name             AS job_name,

    -- Días hasta el próximo mantenimiento
    DATEDIFF(jel.next_maintenance_at, CURDATE()) AS days_until

FROM job_equipment_links jel
JOIN equipment        eq  ON eq.id  = jel.equipment_id
JOIN equipment_types  et  ON et.id  = eq.equipment_type_id
JOIN predefined_jobs  pj  ON pj.id  = jel.predefined_job_id
LEFT JOIN maintenance_teams mt ON mt.id = et.team_id
WHERE jel.status = 'active'
  AND eq.status  = 'active'
  AND jel.next_maintenance_at IS NOT NULL
ORDER BY jel.next_maintenance_at ASC;


SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACIÓN
-- Tablas creadas: 12
-- Vistas creadas: 2 (v_work_orders_full, v_upcoming_maintenance)
-- ALTER TABLE: users (maintenance_team_id)
-- ─────────────────────────────────────────────────────────────