SET lock_timeout = '10s';
SET statement_timeout = '120000';

CREATE SCHEMA IF NOT EXISTS commerce;

CREATE TABLE IF NOT EXISTS commerce.contratos_planes_pago (
    id_contrato BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal INTEGER NOT NULL,
    nombre_contrato VARCHAR(160) NOT NULL,
    descripcion TEXT,
    activo BOOLEAN NOT NULL DEFAULT TRUE,
    orden INTEGER NOT NULL DEFAULT 0,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT chk_contratos_planes_pago_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_contratos_planes_pago_sucursal_positive
        CHECK (sucursal >= 1),
    CONSTRAINT chk_contratos_planes_pago_nombre_not_empty
        CHECK (length(trim(nombre_contrato)) > 0),
    CONSTRAINT chk_contratos_planes_pago_orden_non_negative
        CHECK (orden >= 0),
    CONSTRAINT chk_contratos_planes_pago_metadata_object
        CHECK (jsonb_typeof(metadata) = 'object'),
    CONSTRAINT chk_contratos_planes_pago_created_by_login_positive
        CHECK (created_by_login_id > 0),
    CONSTRAINT chk_contratos_planes_pago_updated_by_login_positive
        CHECK (updated_by_login_id IS NULL OR updated_by_login_id > 0)
);

CREATE TABLE IF NOT EXISTS commerce.contratos_planes_pago_versiones (
    id_contrato_version BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    id_contrato BIGINT NOT NULL,
    numero_version INTEGER NOT NULL,
    estado_version VARCHAR(24) NOT NULL DEFAULT 'draft',
    es_version_actual BOOLEAN NOT NULL DEFAULT FALSE,
    origen_version VARCHAR(32) NOT NULL DEFAULT 'manual',
    configuracion_contrato JSONB NOT NULL DEFAULT '{}'::jsonb,
    resumen_version JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_contratos_planes_pago_versiones_contrato
        FOREIGN KEY (id_contrato)
        REFERENCES commerce.contratos_planes_pago (id_contrato)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_contratos_pp_versiones_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_numero_positive
        CHECK (numero_version > 0),
    CONSTRAINT chk_contratos_pp_versiones_estado
        CHECK (estado_version IN ('draft', 'published', 'archived')),
    CONSTRAINT chk_contratos_pp_versiones_origen
        CHECK (origen_version IN ('manual', 'blank', 'imported', 'brain_generated', 'legacy_migrated', 'legacy_mirror')),
    CONSTRAINT chk_contratos_pp_versiones_config_object
        CHECK (jsonb_typeof(configuracion_contrato) = 'object'),
    CONSTRAINT chk_contratos_pp_versiones_resumen_object
        CHECK (jsonb_typeof(resumen_version) = 'object'),
    CONSTRAINT chk_contratos_pp_versiones_created_by_login_positive
        CHECK (created_by_login_id > 0),
    CONSTRAINT chk_contratos_pp_versiones_updated_by_login_positive
        CHECK (updated_by_login_id IS NULL OR updated_by_login_id > 0)
);

CREATE TABLE IF NOT EXISTS commerce.contratos_planes_pago_versiones_archivos (
    id_archivo_version BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal INTEGER NOT NULL,
    id_contrato BIGINT NOT NULL,
    id_contrato_version BIGINT NOT NULL,
    tipo_fuente VARCHAR(30) NOT NULL,
    formato VARCHAR(10) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    nombre_archivo VARCHAR(255) NOT NULL,
    ruta_relativa VARCHAR(500) NOT NULL,
    sha256 VARCHAR(64) NOT NULL,
    tamanio_bytes INTEGER NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    es_activo BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_contratos_pp_versiones_archivos_contrato
        FOREIGN KEY (id_contrato)
        REFERENCES commerce.contratos_planes_pago (id_contrato)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_contratos_pp_versiones_archivos_version
        FOREIGN KEY (id_contrato_version)
        REFERENCES commerce.contratos_planes_pago_versiones (id_contrato_version)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_contratos_pp_versiones_archivos_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_sucursal_positive
        CHECK (sucursal >= 1),
    CONSTRAINT chk_contratos_pp_versiones_archivos_tipo_fuente
        CHECK (tipo_fuente IN ('uploaded_pdf', 'uploaded_word', 'uploaded_document')),
    CONSTRAINT chk_contratos_pp_versiones_archivos_formato
        CHECK (formato IN ('pdf', 'doc', 'docx', 'odt', 'rtf', 'fodt', 'txt')),
    CONSTRAINT chk_contratos_pp_versiones_archivos_mime_not_empty
        CHECK (length(trim(mime_type)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_nombre_original_not_empty
        CHECK (length(trim(nombre_original)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_nombre_archivo_not_empty
        CHECK (length(trim(nombre_archivo)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_ruta_not_empty
        CHECK (length(trim(ruta_relativa)) > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_sha256_hex
        CHECK (sha256 ~ '^[a-f0-9]{64}$'),
    CONSTRAINT chk_contratos_pp_versiones_archivos_tamanio_non_negative
        CHECK (tamanio_bytes >= 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_metadata_object
        CHECK (jsonb_typeof(metadata) = 'object'),
    CONSTRAINT chk_contratos_pp_versiones_archivos_created_by_login_positive
        CHECK (created_by_login_id > 0),
    CONSTRAINT chk_contratos_pp_versiones_archivos_updated_by_login_positive
        CHECK (updated_by_login_id IS NULL OR updated_by_login_id > 0)
);

ALTER TABLE commerce.modelos_planes_pago
    ADD COLUMN IF NOT EXISTS id_contrato_default BIGINT;

ALTER TABLE commerce.modelos_planes_pago_variantes_categoria
    ADD COLUMN IF NOT EXISTS id_contrato_default BIGINT;

ALTER TABLE commerce.modelos_planes_pago
    DROP CONSTRAINT IF EXISTS fk_modelos_planes_pago_contrato_default;

ALTER TABLE commerce.modelos_planes_pago
    ADD CONSTRAINT fk_modelos_planes_pago_contrato_default
    FOREIGN KEY (id_contrato_default)
    REFERENCES commerce.contratos_planes_pago (id_contrato)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

ALTER TABLE commerce.modelos_planes_pago_variantes_categoria
    DROP CONSTRAINT IF EXISTS fk_modelos_pp_variantes_contrato_default;

ALTER TABLE commerce.modelos_planes_pago_variantes_categoria
    ADD CONSTRAINT fk_modelos_pp_variantes_contrato_default
    FOREIGN KEY (id_contrato_default)
    REFERENCES commerce.contratos_planes_pago (id_contrato)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

ALTER TABLE commerce.modelos_planes_pago
    DROP CONSTRAINT IF EXISTS chk_modelos_planes_pago_id_contrato_default_positive;

ALTER TABLE commerce.modelos_planes_pago
    ADD CONSTRAINT chk_modelos_planes_pago_id_contrato_default_positive
    CHECK (id_contrato_default IS NULL OR id_contrato_default > 0);

ALTER TABLE commerce.modelos_planes_pago_variantes_categoria
    DROP CONSTRAINT IF EXISTS chk_modelos_pp_variantes_id_contrato_default_positive;

ALTER TABLE commerce.modelos_planes_pago_variantes_categoria
    ADD CONSTRAINT chk_modelos_pp_variantes_id_contrato_default_positive
    CHECK (id_contrato_default IS NULL OR id_contrato_default > 0);

CREATE INDEX IF NOT EXISTS contratos_planes_pago_business_id_idx
    ON commerce.contratos_planes_pago (business_id);

CREATE INDEX IF NOT EXISTS contratos_planes_pago_sucursal_idx
    ON commerce.contratos_planes_pago (business_id, sucursal);

CREATE INDEX IF NOT EXISTS contratos_planes_pago_activo_idx
    ON commerce.contratos_planes_pago (business_id, activo)
    WHERE soft_delete IS NOT TRUE;

CREATE UNIQUE INDEX IF NOT EXISTS uq_contratos_planes_pago_nombre_activo
    ON commerce.contratos_planes_pago (business_id, sucursal, nombre_contrato)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_contrato_idx
    ON commerce.contratos_planes_pago_versiones (id_contrato, numero_version DESC);

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_business_id_idx
    ON commerce.contratos_planes_pago_versiones (business_id, id_contrato);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contratos_pp_versiones_numero
    ON commerce.contratos_planes_pago_versiones (business_id, id_contrato, numero_version);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contratos_pp_versiones_actual
    ON commerce.contratos_planes_pago_versiones (business_id, id_contrato)
    WHERE soft_delete IS NOT TRUE
      AND es_version_actual = TRUE;

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_estado_idx
    ON commerce.contratos_planes_pago_versiones (business_id, estado_version)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_archivos_contrato_idx
    ON commerce.contratos_planes_pago_versiones_archivos (id_contrato, id_contrato_version);

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_archivos_scope_idx
    ON commerce.contratos_planes_pago_versiones_archivos (business_id, sucursal, id_contrato_version, created_at DESC);

CREATE INDEX IF NOT EXISTS contratos_pp_versiones_archivos_sha256_idx
    ON commerce.contratos_planes_pago_versiones_archivos (business_id, id_contrato_version, sha256);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contratos_pp_versiones_archivos_activo
    ON commerce.contratos_planes_pago_versiones_archivos (business_id, id_contrato_version)
    WHERE soft_delete IS NOT TRUE
      AND es_activo = TRUE;

CREATE INDEX IF NOT EXISTS modelos_planes_pago_id_contrato_default_idx
    ON commerce.modelos_planes_pago (business_id, id_contrato_default)
    WHERE soft_delete IS NOT TRUE
      AND id_contrato_default IS NOT NULL;

CREATE INDEX IF NOT EXISTS modelos_pp_variantes_id_contrato_default_idx
    ON commerce.modelos_planes_pago_variantes_categoria (business_id, id_contrato_default)
    WHERE soft_delete IS NOT TRUE
      AND id_contrato_default IS NOT NULL;

COMMENT ON TABLE commerce.contratos_planes_pago IS 'Entidad maestra de contratos reutilizables de planes de pago, desacoplada del modelo financiero.';
COMMENT ON COLUMN commerce.contratos_planes_pago.nombre_contrato IS 'Nombre visible del contrato reusable dentro de gestionar-modelos.';
COMMENT ON COLUMN commerce.contratos_planes_pago.descripcion IS 'Descripcion operativa opcional del contrato reusable.';
COMMENT ON COLUMN commerce.contratos_planes_pago.metadata IS 'Metadata flexible para UI, filtros y futuras extensiones del dominio contractual.';

COMMENT ON TABLE commerce.contratos_planes_pago_versiones IS 'Versionado contractual desacoplado del modelo. configuracion_contrato es la fuente de verdad nueva.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones.estado_version IS 'Estado funcional de la version contractual: draft, published o archived.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones.es_version_actual IS 'Bandera de version actual activa para preview y vinculacion por default.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones.origen_version IS 'Origen operativo de la version: blank, imported, brain_generated, legacy_migrated, etc.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones.configuracion_contrato IS 'Snapshot contractual canónico compatible con el runtime generador-contratos-modelo.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones.resumen_version IS 'Proyeccion compacta de historial, source summary y metadata de auditoria funcional.';

COMMENT ON TABLE commerce.contratos_planes_pago_versiones_archivos IS 'Artefactos fuente originales asociados a una version contractual especifica.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones_archivos.tipo_fuente IS 'Tipo de fuente canonico del artefacto original: uploaded_pdf o uploaded_document.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones_archivos.ruta_relativa IS 'Ruta privada relativa del archivo fuente dentro de uploads/documentos.';
COMMENT ON COLUMN commerce.contratos_planes_pago_versiones_archivos.metadata IS 'Metadata operativa del archivo fuente versionado.';

COMMENT ON COLUMN commerce.modelos_planes_pago.id_contrato_default IS 'FK opcional al contrato reusable predeterminado del modelo.';
COMMENT ON COLUMN commerce.modelos_planes_pago_variantes_categoria.id_contrato_default IS 'FK opcional al contrato reusable que overridea la herencia contractual de la variante.';

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;
