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

CREATE SCHEMA IF NOT EXISTS catalog;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS ops.canonical_table_map (
    table_name text PRIMARY KEY,
    canonical_schema text NOT NULL,
    notes text
);

CREATE TABLE IF NOT EXISTS catalog.campos_dinamicos_def (
    id_campo BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal_id INTEGER NOT NULL DEFAULT 0,
    entity_type VARCHAR(64) NOT NULL,
    clave_campo VARCHAR(120) NOT NULL,
    nombre_campo VARCHAR(160) NOT NULL,
    tipo_dato VARCHAR(32) NOT NULL DEFAULT 'texto',
    configuracion_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    activo BOOLEAN NOT NULL DEFAULT TRUE,
    orden INTEGER NOT NULL DEFAULT 100,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT uq_campos_dinamicos_def UNIQUE (business_id, entity_type, sucursal_id, clave_campo),
    CONSTRAINT uq_campos_dinamicos_def_id_biz UNIQUE (id_campo, business_id),
    CONSTRAINT chk_campos_dinamicos_def_sucursal_non_negative CHECK (sucursal_id >= 0),
    CONSTRAINT chk_campos_dinamicos_def_entity_type_not_empty CHECK (length(trim(entity_type)) > 0),
    CONSTRAINT chk_campos_dinamicos_def_clave_not_empty CHECK (length(trim(clave_campo)) > 0),
    CONSTRAINT chk_campos_dinamicos_def_nombre_not_empty CHECK (length(trim(nombre_campo)) > 0),
    CONSTRAINT chk_campos_dinamicos_def_tipo_dato CHECK (tipo_dato IN ('texto', 'multilinea', 'numero', 'fecha', 'booleano', 'json'))
);

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_def_lookup
    ON catalog.campos_dinamicos_def (business_id, entity_type, sucursal_id, activo, orden, id_campo)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_def_nombre
    ON catalog.campos_dinamicos_def (business_id, entity_type, sucursal_id, nombre_campo)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_def_config_gin
    ON catalog.campos_dinamicos_def USING gin (configuracion_jsonb);

CREATE TABLE IF NOT EXISTS catalog.campos_dinamicos_valor (
    id_valor BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal_id INTEGER NOT NULL DEFAULT 0,
    entity_type VARCHAR(64) NOT NULL,
    entity_id BIGINT NOT NULL,
    id_campo BIGINT NOT NULL,
    valor_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    source_mode VARCHAR(32) NOT NULL DEFAULT 'MANUAL',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT uq_campos_dinamicos_valor UNIQUE (business_id, entity_type, entity_id, id_campo),
    CONSTRAINT fk_campos_dinamicos_valor_def
        FOREIGN KEY (id_campo, business_id)
        REFERENCES catalog.campos_dinamicos_def (id_campo, business_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_campos_dinamicos_valor_sucursal_non_negative CHECK (sucursal_id >= 0),
    CONSTRAINT chk_campos_dinamicos_valor_entity_type_not_empty CHECK (length(trim(entity_type)) > 0),
    CONSTRAINT chk_campos_dinamicos_valor_entity_id_positive CHECK (entity_id > 0),
    CONSTRAINT chk_campos_dinamicos_valor_source_mode CHECK (source_mode IN ('MANUAL', 'SYSTEM', 'IMPORT', 'MIGRATION'))
);

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_valor_lookup_entity
    ON catalog.campos_dinamicos_valor (business_id, entity_type, entity_id, id_campo)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_valor_lookup_field
    ON catalog.campos_dinamicos_valor (business_id, id_campo, entity_type)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_campos_dinamicos_valor_gin
    ON catalog.campos_dinamicos_valor USING gin (valor_jsonb);

COMMENT ON TABLE catalog.campos_dinamicos_def IS 'Catalogo multiproposito de definiciones de campos dinamicos por business, entidad y sucursal. Permite reutilizar campos para clientes, empleados, inventario y otras entidades.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.business_id IS 'Tenant del negocio.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.sucursal_id IS 'Sucursal del campo. 0 representa campo global del negocio.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.entity_type IS 'Tipo de entidad objetivo (cliente, empleado, inventario_item, servicio, venta, modelo_plan_pago, etc).';
COMMENT ON COLUMN catalog.campos_dinamicos_def.clave_campo IS 'Clave estable y unica del campo dentro de business/entity/sucursal.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.nombre_campo IS 'Etiqueta visible del campo para UI.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.tipo_dato IS 'Tipo logico del valor esperado para el campo.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.configuracion_jsonb IS 'Configuracion extensible del campo (validaciones, opciones, metadata de UI).';
COMMENT ON COLUMN catalog.campos_dinamicos_def.activo IS 'Indica si el campo esta disponible para captura en UI.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.orden IS 'Orden de despliegue sugerido en interfaces.';
COMMENT ON COLUMN catalog.campos_dinamicos_def.soft_delete IS 'Soft delete del catalogo de campos dinamicos.';

COMMENT ON TABLE catalog.campos_dinamicos_valor IS 'Valores de campos dinamicos por entidad concreta. Tabla multiproposito para guardar valores por cliente, empleado, inventario, servicio, venta u otras entidades.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.business_id IS 'Tenant del negocio.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.sucursal_id IS 'Sucursal contextual del valor. 0 representa valor global del negocio.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.entity_type IS 'Tipo de entidad a la que pertenece el valor.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.entity_id IS 'ID de la entidad dentro del tipo indicado.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.id_campo IS 'Referencia al catalogo de campos dinamicos.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.valor_jsonb IS 'Valor almacenado en JSONB para soportar tipos heterogeneos y evolucion futura.';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.source_mode IS 'Origen del valor (captura manual, sistema, importacion o migracion).';
COMMENT ON COLUMN catalog.campos_dinamicos_valor.soft_delete IS 'Soft delete del valor dinamico.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES
    ('campos_dinamicos_def', 'catalog', 'Catalog domain source of truth'),
    ('campos_dinamicos_valor', 'catalog', 'Catalog domain source of truth')
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE catalog.campos_dinamicos_def;
ANALYZE catalog.campos_dinamicos_valor;
