
CREATE TEMP TABLE tmp_model_contract_map (
    id_modelo BIGINT PRIMARY KEY,
    id_contrato BIGINT NOT NULL
);

CREATE TEMP TABLE tmp_model_contract_version_map (
    id_modelo BIGINT PRIMARY KEY,
    id_contrato BIGINT NOT NULL,
    id_contrato_version BIGINT NOT NULL
);

CREATE TEMP TABLE tmp_variant_contract_map (
    id_variante_modelo BIGINT PRIMARY KEY,
    id_contrato BIGINT NOT NULL
);

CREATE TEMP TABLE tmp_variant_contract_version_map (
    id_variante_modelo BIGINT PRIMARY KEY,
    id_contrato BIGINT NOT NULL,
    id_contrato_version BIGINT NOT NULL
);

WITH inserted_contracts AS (
    INSERT INTO commerce.contratos_planes_pago (
        business_id,
        sucursal,
        nombre_contrato,
        descripcion,
        activo,
        orden,
        metadata,
        created_at,
        created_by_login_id,
        updated_at,
        updated_by_login_id,
        soft_delete,
        soft_delete_date
    )
    SELECT
        m.business_id,
        m.sucursal,
        LEFT(TRIM(COALESCE(m.nombre_modelo, 'Contrato migrado')) || ' [Modelo ' || m.id_modelo || ']', 160),
        COALESCE(NULLIF(TRIM(COALESCE(m.descripcion, '')), ''), 'Contrato migrado desde el modelo legacy #' || m.id_modelo),
        TRUE,
        0,
        jsonb_build_object(
            'migration_source', 'model_legacy',
            'legacy_model_id', m.id_modelo
        ),
        COALESCE(m.created_at, NOW()),
        COALESCE(m.created_by_login_id, m.updated_by_login_id, 1),
        COALESCE(m.updated_at, m.created_at, NOW()),
        COALESCE(m.updated_by_login_id, m.created_by_login_id, 1),
        FALSE,
        NULL
    FROM commerce.modelos_planes_pago m
    WHERE m.soft_delete IS NOT TRUE
      AND m.id_contrato_default IS NULL
      AND (
          m.configuracion_contrato IS NOT NULL
          OR EXISTS (
              SELECT 1
              FROM commerce.contratos_modelos_archivos a
              WHERE a.business_id = m.business_id
                AND a.id_modelo = m.id_modelo
                AND a.soft_delete IS NOT TRUE
                AND a.es_activo = TRUE
          )
      )
    RETURNING id_contrato, (metadata->>'legacy_model_id')::BIGINT AS id_modelo
)
INSERT INTO tmp_model_contract_map (id_modelo, id_contrato)
SELECT id_modelo, id_contrato
FROM inserted_contracts;

WITH inserted_versions AS (
    INSERT INTO commerce.contratos_planes_pago_versiones (
        business_id,
        id_contrato,
        numero_version,
        estado_version,
        es_version_actual,
        origen_version,
        configuracion_contrato,
        resumen_version,
        created_at,
        created_by_login_id,
        updated_at,
        updated_by_login_id,
        soft_delete,
        soft_delete_date
    )
    SELECT
        m.business_id,
        map.id_contrato,
        1,
        'published',
        TRUE,
        'legacy_migrated',
        COALESCE(m.configuracion_contrato, '{}'::jsonb),
        jsonb_build_object(
            'migration_source', 'model_legacy',
            'legacy_model_id', m.id_modelo
        ),
        COALESCE(m.created_at, NOW()),
        COALESCE(m.created_by_login_id, m.updated_by_login_id, 1),
        COALESCE(m.updated_at, m.created_at, NOW()),
        COALESCE(m.updated_by_login_id, m.created_by_login_id, 1),
        FALSE,
        NULL
    FROM tmp_model_contract_map map
    INNER JOIN commerce.modelos_planes_pago m
        ON m.id_modelo = map.id_modelo
    RETURNING id_contrato_version, id_contrato
)
INSERT INTO tmp_model_contract_version_map (id_modelo, id_contrato, id_contrato_version)
SELECT
    map.id_modelo,
    map.id_contrato,
    inserted_versions.id_contrato_version
FROM inserted_versions
INNER JOIN tmp_model_contract_map map
    ON map.id_contrato = inserted_versions.id_contrato;

INSERT INTO commerce.contratos_planes_pago_versiones_archivos (
    business_id,
    sucursal,
    id_contrato,
    id_contrato_version,
    tipo_fuente,
    formato,
    mime_type,
    nombre_original,
    nombre_archivo,
    ruta_relativa,
    sha256,
    tamanio_bytes,
    metadata,
    es_activo,
    created_at,
    created_by_login_id,
    updated_at,
    updated_by_login_id,
    soft_delete,
    soft_delete_date
)
SELECT
    a.business_id,
    COALESCE(a.sucursal, m.sucursal),
    map.id_contrato,
    map.id_contrato_version,
    a.tipo_fuente,
    a.formato,
    a.mime_type,
    a.nombre_original,
    a.nombre_archivo,
    a.ruta_relativa,
    a.sha256,
    a.tamanio_bytes,
    COALESCE(a.metadata, '{}'::jsonb),
    TRUE,
    COALESCE(a.created_at, NOW()),
    COALESCE(a.created_by_login_id, a.updated_by_login_id, m.created_by_login_id, m.updated_by_login_id, 1),
    COALESCE(a.updated_at, a.created_at, NOW()),
    COALESCE(a.updated_by_login_id, a.created_by_login_id, m.updated_by_login_id, m.created_by_login_id, 1),
    FALSE,
    NULL
FROM tmp_model_contract_version_map map
INNER JOIN commerce.modelos_planes_pago m
    ON m.id_modelo = map.id_modelo
INNER JOIN LATERAL (
    SELECT
        a.id_archivo,
        a.business_id,
        a.sucursal,
        a.tipo_fuente,
        a.formato,
        a.mime_type,
        a.nombre_original,
        a.nombre_archivo,
        a.ruta_relativa,
        a.sha256,
        a.tamanio_bytes,
        a.metadata,
        a.created_at,
        a.created_by_login_id,
        a.updated_at,
        a.updated_by_login_id
    FROM commerce.contratos_modelos_archivos a
    WHERE a.business_id = m.business_id
      AND a.id_modelo = m.id_modelo
      AND a.soft_delete IS NOT TRUE
      AND a.es_activo = TRUE
    ORDER BY a.updated_at DESC NULLS LAST, a.created_at DESC NULLS LAST, a.id_archivo DESC
    LIMIT 1
) a ON TRUE;

UPDATE commerce.modelos_planes_pago m
SET id_contrato_default = map.id_contrato,
    updated_at = NOW(),
    updated_by_login_id = COALESCE(m.updated_by_login_id, m.created_by_login_id, 1)
FROM tmp_model_contract_map map
WHERE m.id_modelo = map.id_modelo
  AND m.id_contrato_default IS NULL;

WITH inserted_contracts AS (
    INSERT INTO commerce.contratos_planes_pago (
        business_id,
        sucursal,
        nombre_contrato,
        descripcion,
        activo,
        orden,
        metadata,
        created_at,
        created_by_login_id,
        updated_at,
        updated_by_login_id,
        soft_delete,
        soft_delete_date
    )
    SELECT
        v.business_id,
        v.sucursal,
        LEFT(
            TRIM(COALESCE(m.nombre_modelo, 'Contrato migrado'))
            || CASE
                WHEN COALESCE(v.es_variante_general, FALSE)
                    THEN ' [Variante general ' || v.id_variante_modelo || ']'
                ELSE ' [' || COALESCE(NULLIF(TRIM(COALESCE(cat.name, '')), ''), 'Categoria ' || v.category_id) || ' · Variante ' || v.id_variante_modelo || ']'
               END,
            160
        ),
        'Contrato migrado desde la variante legacy #' || v.id_variante_modelo,
        TRUE,
        0,
        jsonb_build_object(
            'migration_source', 'variant_legacy',
            'legacy_variant_id', v.id_variante_modelo,
            'legacy_model_id', v.id_modelo,
            'legacy_category_id', v.category_id,
            'es_variante_general', COALESCE(v.es_variante_general, FALSE)
        ),
        COALESCE(v.created_at, NOW()),
        COALESCE(v.created_by_login_id, v.updated_by_login_id, 1),
        COALESCE(v.updated_at, v.created_at, NOW()),
        COALESCE(v.updated_by_login_id, v.created_by_login_id, 1),
        FALSE,
        NULL
    FROM commerce.modelos_planes_pago_variantes_categoria v
    INNER JOIN commerce.modelos_planes_pago m
        ON m.id_modelo = v.id_modelo
       AND m.business_id = v.business_id
    LEFT JOIN catalog.categorias_combinables_v2 cat
        ON cat.business_id = v.business_id
       AND cat.category_id = v.category_id
    WHERE v.soft_delete IS NOT TRUE
      AND v.id_contrato_default IS NULL
      AND v.configuracion_contrato IS NOT NULL
    RETURNING id_contrato, (metadata->>'legacy_variant_id')::BIGINT AS id_variante_modelo
)
INSERT INTO tmp_variant_contract_map (id_variante_modelo, id_contrato)
SELECT id_variante_modelo, id_contrato
FROM inserted_contracts;

WITH inserted_versions AS (
    INSERT INTO commerce.contratos_planes_pago_versiones (
        business_id,
        id_contrato,
        numero_version,
        estado_version,
        es_version_actual,
        origen_version,
        configuracion_contrato,
        resumen_version,
        created_at,
        created_by_login_id,
        updated_at,
        updated_by_login_id,
        soft_delete,
        soft_delete_date
    )
    SELECT
        v.business_id,
        map.id_contrato,
        1,
        'published',
        TRUE,
        'legacy_migrated',
        COALESCE(v.configuracion_contrato, '{}'::jsonb),
        jsonb_build_object(
            'migration_source', 'variant_legacy',
            'legacy_variant_id', v.id_variante_modelo,
            'legacy_model_id', v.id_modelo,
            'legacy_category_id', v.category_id,
            'es_variante_general', COALESCE(v.es_variante_general, FALSE)
        ),
        COALESCE(v.created_at, NOW()),
        COALESCE(v.created_by_login_id, v.updated_by_login_id, 1),
        COALESCE(v.updated_at, v.created_at, NOW()),
        COALESCE(v.updated_by_login_id, v.created_by_login_id, 1),
        FALSE,
        NULL
    FROM tmp_variant_contract_map map
    INNER JOIN commerce.modelos_planes_pago_variantes_categoria v
        ON v.id_variante_modelo = map.id_variante_modelo
    RETURNING id_contrato_version, id_contrato
)
INSERT INTO tmp_variant_contract_version_map (id_variante_modelo, id_contrato, id_contrato_version)
SELECT
    map.id_variante_modelo,
    map.id_contrato,
    inserted_versions.id_contrato_version
FROM inserted_versions
INNER JOIN tmp_variant_contract_map map
    ON map.id_contrato = inserted_versions.id_contrato;

UPDATE commerce.modelos_planes_pago_variantes_categoria v
SET id_contrato_default = map.id_contrato,
    updated_at = NOW(),
    updated_by_login_id = COALESCE(v.updated_by_login_id, v.created_by_login_id, 1)
FROM tmp_variant_contract_map map
WHERE v.id_variante_modelo = map.id_variante_modelo
  AND v.id_contrato_default IS NULL;

ANALYZE commerce.contratos_planes_pago;
ANALYZE commerce.contratos_planes_pago_versiones;
ANALYZE commerce.contratos_planes_pago_versiones_archivos;
ANALYZE commerce.modelos_planes_pago;
ANALYZE commerce.modelos_planes_pago_variantes_categoria;

