CREATE TABLE IF NOT EXISTS inventario_variantes_categoria (
    id_variante BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    id_item INTEGER NOT NULL,
    category_id INTEGER,
    es_variante_general BOOLEAN NOT NULL DEFAULT FALSE,
    nombre_base_override VARCHAR(255),
    nombre_prefijo VARCHAR(100),
    nombre_postfijo VARCHAR(100),
    nombre_final_override VARCHAR(255),
    descripcion TEXT,
    precio NUMERIC(10,2),
    moneda VARCHAR(10) NOT NULL DEFAULT 'MXN',
    elementos_incluidos JSONB DEFAULT '[]'::jsonb,
    tipo_item INTEGER,
    vigencia_inicio TIMESTAMPTZ,
    vigencia_fin TIMESTAMPTZ,
    prioridad_resolucion INTEGER NOT NULL DEFAULT 100,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    historial_cambios JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_ivc_inventario FOREIGN KEY (id_item) REFERENCES inventario(id_item),
    CONSTRAINT fk_ivc_categoria FOREIGN KEY (category_id) REFERENCES categorias_combinables_v2(category_id),
    CONSTRAINT chk_ivc_vigencia CHECK (vigencia_fin IS NULL OR vigencia_inicio IS NULL OR vigencia_fin >= vigencia_inicio),
    CONSTRAINT chk_ivc_general_category CHECK ((es_variante_general = TRUE AND category_id IS NULL) OR (es_variante_general = FALSE AND category_id IS NOT NULL)),
    CONSTRAINT chk_ivc_tipo_item CHECK (tipo_item IS NULL OR tipo_item IN (1, 2, 3, 4)),
    CONSTRAINT chk_ivc_precio_non_negative CHECK (precio IS NULL OR precio >= 0)
);

CREATE TABLE IF NOT EXISTS inventario_variantes_resolucion_log (
    id_log BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    id_item INTEGER NOT NULL,
    id_variante BIGINT,
    id_cliente INTEGER,
    category_id_contexto INTEGER,
    strategy VARCHAR(20) NOT NULL,
    source_context VARCHAR(20) NOT NULL,
    nombre_final VARCHAR(500) NOT NULL,
    precio_final NUMERIC(10,2) NOT NULL,
    moneda VARCHAR(10) NOT NULL DEFAULT 'MXN',
    origen VARCHAR(50),
    payload_contexto JSONB,
    resultado_contexto JSONB,
    id_venta INTEGER,
    id_transaccion INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER,
    CONSTRAINT chk_ivrl_strategy CHECK (strategy IN ('EXACT_MATCH', 'DEFAULT_MATCH', 'BASE_FALLBACK')),
    CONSTRAINT chk_ivrl_source CHECK (source_context IN ('MANUAL_CONTEXT', 'CLIENT_PRIORITY', 'NO_CONTEXT'))
);

ALTER TABLE inventario_variantes_categoria ADD CONSTRAINT excl_ivc_overlap EXCLUDE USING gist ((business_id::text) WITH =, id_item WITH =, COALESCE(category_id, -1) WITH =, tstzrange(COALESCE(vigencia_inicio, '-infinity'::timestamptz), COALESCE(vigencia_fin, 'infinity'::timestamptz), '[]') WITH &&) WHERE (is_active = TRUE AND soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS idx_ivc_business_item ON inventario_variantes_categoria(business_id, id_item) WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ivc_category ON inventario_variantes_categoria(category_id) WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ivc_active_window ON inventario_variantes_categoria(business_id, id_item, is_active) WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ivc_soft_delete ON inventario_variantes_categoria(soft_delete) WHERE soft_delete = TRUE;

CREATE INDEX IF NOT EXISTS idx_ivrl_business ON inventario_variantes_resolucion_log(business_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_ivrl_item ON inventario_variantes_resolucion_log(id_item, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_ivrl_venta ON inventario_variantes_resolucion_log(id_venta) WHERE id_venta IS NOT NULL;
