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

CREATE SCHEMA IF NOT EXISTS commerce;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS commerce.contratos_modelos_archivos (
    id_archivo BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal INTEGER NOT NULL,
    id_modelo INTEGER 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_modelos_archivos_modelo
        FOREIGN KEY (id_modelo)
        REFERENCES commerce.modelos_planes_pago (id_modelo)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_contratos_modelos_archivos_sucursal_positive
        CHECK (sucursal >= 1),
    CONSTRAINT chk_contratos_modelos_archivos_tipo_fuente
        CHECK (tipo_fuente IN ('uploaded_pdf', 'uploaded_word')),
    CONSTRAINT chk_contratos_modelos_archivos_formato
        CHECK (formato IN ('pdf', 'doc', 'docx')),
    CONSTRAINT chk_contratos_modelos_archivos_nombre_original_not_empty
        CHECK (length(trim(nombre_original)) > 0),
    CONSTRAINT chk_contratos_modelos_archivos_nombre_archivo_not_empty
        CHECK (length(trim(nombre_archivo)) > 0),
    CONSTRAINT chk_contratos_modelos_archivos_ruta_relativa_not_empty
        CHECK (length(trim(ruta_relativa)) > 0),
    CONSTRAINT chk_contratos_modelos_archivos_sha256_hex
        CHECK (sha256 ~ '^[a-f0-9]{64}$'),
    CONSTRAINT chk_contratos_modelos_archivos_tamanio_bytes_non_negative
        CHECK (tamanio_bytes >= 0),
    CONSTRAINT chk_contratos_modelos_archivos_metadata_object
        CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contratos_modelos_archivos_activo
    ON commerce.contratos_modelos_archivos (
        business_id,
        id_modelo
    )
    WHERE (soft_delete IS NOT TRUE AND es_activo = TRUE);

CREATE INDEX IF NOT EXISTS contratos_modelos_archivos_scope_created_idx
    ON commerce.contratos_modelos_archivos (
        business_id,
        sucursal,
        id_modelo,
        created_at DESC
    );

CREATE INDEX IF NOT EXISTS contratos_modelos_archivos_sha256_idx
    ON commerce.contratos_modelos_archivos (
        business_id,
        id_modelo,
        sha256
    );

CREATE INDEX IF NOT EXISTS contratos_modelos_archivos_modelo_idx
    ON commerce.contratos_modelos_archivos (
        id_modelo
    );

COMMENT ON TABLE commerce.contratos_modelos_archivos IS 'Artefactos originales importados para contratos de modelos de planes de pago.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.id_archivo IS 'Identificador tecnico del archivo importado.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.business_id IS 'Tenant del negocio para aislamiento multiempresa.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.sucursal IS 'Sucursal operativa del modelo asociado al contrato importado.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.id_modelo IS 'Modelo de plan de pago al que pertenece el artefacto original.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.tipo_fuente IS 'Tipo de fuente activa derivada del archivo importado.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.formato IS 'Formato fisico persistido del contrato original.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.mime_type IS 'MIME type detectado del archivo original.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.nombre_original IS 'Nombre original entregado por el operador al cargar el contrato.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.nombre_archivo IS 'Nombre interno generado por la aplicacion para persistencia segura.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.ruta_relativa IS 'Ruta relativa del archivo privado dentro de uploads/documentos.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.sha256 IS 'Hash SHA-256 del archivo original para auditoria e integridad.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.tamanio_bytes IS 'Tamano final almacenado del archivo original en bytes.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.metadata IS 'Metadata operativa adicional del archivo importado.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.es_activo IS 'Bandera de archivo activo actual para el modelo.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.created_at IS 'Timestamp oficial del upload del archivo.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.created_by_login_id IS 'Login que realizo la carga original.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.updated_at IS 'Timestamp de la ultima actualizacion del registro.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.updated_by_login_id IS 'Login que realizo la ultima actualizacion del registro.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.soft_delete IS 'Bandera de borrado logico para preservar trazabilidad de versiones previas.';
COMMENT ON COLUMN commerce.contratos_modelos_archivos.soft_delete_date IS 'Timestamp de borrado logico del archivo reemplazado o retirado.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES (
    'contratos_modelos_archivos',
    'commerce',
    'Fuente canonica de artefactos originales PDF o Word importados para modelos de planes de pago'
)
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE commerce.contratos_modelos_archivos;
