-- =============================================================================
-- Migración:    0032_create_contratos_y_secuencias_tables
-- Sprint:       1.5a — Necesidad Inmediata (NI)
-- Tablas:       contratos_secuencias, contratos
-- Aplica en:    BD de cada tenant (innovium_<slug>)
-- Dependencias: 0009 (productos), 0029 (capillas/tipos_carroza),
--               0030 (convenios/sucursales/velatorios), 0031 (clientes/
--               fallecidos), 0001 (users).
--
-- Descripción:
--   El corazón del módulo NI:
--
--   · contratos_secuencias — generación de número correlativo por
--     tenant + tipo. Hasta 1 fila por (tipo_contrato). El wizard hace
--     SELECT FOR UPDATE + UPDATE atómico para evitar race conditions.
--
--   · contratos — el documento principal. ~50 columnas que reflejan
--     todos los datos del contrato funerario chileno (replica del
--     legacy systemserp_sgc pero modernizado).
--
-- Decisión clave (ADR pendiente para sprint cierre):
--   Muchas columnas en contratos NO se normalizan en N tablas porque
--   los datos siempre vienen juntos (medidas del cofre, toggles del
--   servicio, valores monetarios). Normalizar agregaría complejidad
--   sin beneficio. Lo que SÍ se normaliza: aportes previsionales y
--   servicios extra (pueden ser N por contrato), van en commit 4.
-- =============================================================================

-- -------------- contratos_secuencias --------------
-- Generación atómica de número. Patrón:
--   START TRANSACTION;
--   SELECT ultimo_numero FROM contratos_secuencias WHERE tipo_contrato = 'NI' FOR UPDATE;
--   UPDATE contratos_secuencias SET ultimo_numero = ultimo_numero + 1 WHERE tipo_contrato = 'NI';
--   -- usar el nuevo valor como número del contrato
--   COMMIT;

CREATE TABLE contratos_secuencias (
    tipo_contrato   ENUM('NI', 'NF') NOT NULL
                    COMMENT 'Tipo de contrato. PK.',
    ultimo_numero   INT UNSIGNED     NOT NULL DEFAULT 0
                    COMMENT 'Último número emitido. Siguiente = ultimo_numero + 1.',
    actualizado_en  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
                                              ON UPDATE CURRENT_TIMESTAMP
                    COMMENT 'UTC del último cambio.',
    PRIMARY KEY (tipo_contrato)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Generación correlativa de números de contrato por tenant + tipo.';


-- -------------- contratos --------------

CREATE TABLE contratos (
    id                              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                                    COMMENT 'PK auto-incremental.',
    numero                          VARCHAR(20)     NOT NULL
                                    COMMENT 'Número correlativo (ej: "1149", "1150"). UNIQUE.',
    tipo                            ENUM('NI', 'NF') NOT NULL DEFAULT 'NI'
                                    COMMENT 'NI=Necesidad Inmediata, NF=Necesidad Futura.',
    estado                          ENUM('firmado', 'anulado', 'completado') NOT NULL DEFAULT 'firmado'
                                    COMMENT 'firmado=al crearse, completado=cuando finaliza el servicio, anulado=Sprint 1.5c.',
    fecha_contrato                  DATE            NOT NULL
                                    COMMENT 'Fecha de la firma (zona horaria del tenant).',
    hora_contrato                   TIME            NOT NULL
                                    COMMENT 'Hora de la firma.',

    -- Personas
    cliente_id                      BIGINT UNSIGNED NOT NULL
                                    COMMENT 'FK → clientes.id (RESTRICT). Titular pagador.',
    fallecido_id                    BIGINT UNSIGNED     NULL
                                    COMMENT 'FK → fallecidos.id (RESTRICT). Solo NI; NF lo deja NULL.',
    vendedor_id                     BIGINT UNSIGNED NOT NULL
                                    COMMENT 'FK → users.id (RESTRICT). Usuario que vendió.',
    sucursal_id                     BIGINT UNSIGNED NOT NULL
                                    COMMENT 'FK → sucursales.id (RESTRICT). Donde se vendió.',
    velatorio_id                    BIGINT UNSIGNED     NULL
                                    COMMENT 'FK → velatorios.id (SET NULL).',

    -- Convenio (descuento institucional)
    convenio_id                     BIGINT UNSIGNED     NULL
                                    COMMENT 'FK → convenios.id (SET NULL).',
    convenio_descuento_pct          DECIMAL(5,2)    NOT NULL DEFAULT 0
                                    COMMENT 'Snapshot del % aplicado.',

    -- Plan + cofre (snapshot de datos al momento del contrato)
    plan_id                         BIGINT UNSIGNED     NULL
                                    COMMENT 'FK → productos.id (SET NULL). Si compró un plan.',
    plan_nombre_snapshot            VARCHAR(150)        NULL
                                    COMMENT 'Snapshot del nombre del plan.',
    cofre_codigo                    VARCHAR(50)         NULL
                                    COMMENT 'Código del cofre (ej: "1014", "URN-OLM-001").',
    cofre_descripcion               VARCHAR(200)        NULL
                                    COMMENT 'Descripción del cofre (ej: "TERCIADO LISO").',
    cofre_color                     VARCHAR(50)         NULL,
    cofre_valor_clp                 INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Snapshot del precio del cofre en CLP.',
    cofre_alto_cm                   INT UNSIGNED        NULL,
    cofre_ancho_cm                  INT UNSIGNED        NULL,
    cofre_largo_cm                  INT UNSIGNED        NULL,

    -- Servicio funerario
    capilla_id                      INT UNSIGNED        NULL
                                    COMMENT 'FK → capillas.id (SET NULL).',
    tipo_carroza_id                 INT UNSIGNED        NULL
                                    COMMENT 'FK → tipos_carroza.id (SET NULL).',
    cantidad_auto                   INT UNSIGNED    NOT NULL DEFAULT 0,
    cantidad_van                    INT UNSIGNED    NOT NULL DEFAULT 0,
    cruz                            TINYINT(1)      NOT NULL DEFAULT 0,
    tarjetero                       TINYINT(1)      NOT NULL DEFAULT 0,
    libro_condolencias              TINYINT(1)      NOT NULL DEFAULT 0,
    tarjeta_condolencias            TINYINT(1)      NOT NULL DEFAULT 0,
    arreglo_floral                  TINYINT(1)      NOT NULL DEFAULT 0,
    cafeteria                       TINYINT(1)      NOT NULL DEFAULT 0,
    tramitacion_registro_civil      TINYINT(1)      NOT NULL DEFAULT 0,
    certificacion_medica            TINYINT(1)      NOT NULL DEFAULT 0,

    -- Complemento del servicio
    lugar_velacion                  VARCHAR(200)        NULL,
    lugar_sepultacion               VARCHAR(200)        NULL,
    fecha_funeral                   DATE                NULL
                                    COMMENT 'NULL si por_confirmar=1.',
    hora_funeral                    TIME                NULL,
    fecha_funeral_por_confirmar     TINYINT(1)      NOT NULL DEFAULT 1,

    -- Valores
    valor_lista_clp                 INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Suma de todo a precio lista (sin descuentos).',
    total_servicios_clp             INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Total con descuentos aplicados.',
    aporte_previsional_total_clp    INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Suma de aportes (denormalizado para queries rápidos).',
    abono_cliente_clp               INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Lo que paga cash el cliente al firmar.',
    descuento_clp                   INT UNSIGNED    NOT NULL DEFAULT 0
                                    COMMENT 'Descuento extra (no convenio).',
    pendiente_pago_clp              INT             NOT NULL DEFAULT 0
                                    COMMENT 'Calculado: total - aportes - abono. Puede ser negativo (pagó de más).',

    -- Firma y PDF
    firma_r2_path                   VARCHAR(500)        NULL
                                    COMMENT 'Path en R2 (ej: "tenants/demo/firmas/CT-NI-1149.png").',
    pdf_r2_path                     VARCHAR(500)        NULL
                                    COMMENT 'Path en R2 del PDF generado.',
    fecha_firma                     DATETIME            NULL
                                    COMMENT 'UTC de cuando se confirmó.',
    firmado_desde_ip                VARCHAR(45)         NULL,
    observaciones                   TEXT                NULL,

    -- Auditoría de anulación (Sprint 1.5c)
    anulado_en                      DATETIME            NULL,
    anulado_por_user_id             BIGINT UNSIGNED     NULL,
    razon_anulacion                 TEXT                NULL,

    -- Timestamps
    creado_en                       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en                  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                                             ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_contratos_numero (numero),
    KEY idx_contratos_tipo_estado (tipo, estado),
    KEY idx_contratos_fecha (fecha_contrato),
    KEY idx_contratos_cliente (cliente_id),
    KEY idx_contratos_vendedor (vendedor_id),
    KEY idx_contratos_sucursal (sucursal_id),

    CONSTRAINT fk_contratos_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_fallecido
        FOREIGN KEY (fallecido_id) REFERENCES fallecidos (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_vendedor
        FOREIGN KEY (vendedor_id) REFERENCES users (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursales (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_velatorio
        FOREIGN KEY (velatorio_id) REFERENCES velatorios (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_convenio
        FOREIGN KEY (convenio_id) REFERENCES convenios (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_plan
        FOREIGN KEY (plan_id) REFERENCES productos (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_capilla
        FOREIGN KEY (capilla_id) REFERENCES capillas (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_tipo_carroza
        FOREIGN KEY (tipo_carroza_id) REFERENCES tipos_carroza (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_contratos_anulado_por
        FOREIGN KEY (anulado_por_user_id) REFERENCES users (id)
        ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Contratos funerarios. NI=Necesidad Inmediata, NF=Necesidad Futura. Nunca se borran físicamente.';
