BEGIN;

SET LOCAL lock_timeout = '10s';
SET LOCAL statement_timeout = '900000';
SET LOCAL idle_in_transaction_session_timeout = '900000';

UPDATE platform.public_embed_widgets erp
SET sucursal_id_default = public_scope.sucursal_id_default,
    metadata_json = jsonb_set(
        COALESCE(erp.metadata_json, '{}'::jsonb),
        '{page_variant}',
        to_jsonb('default'::text),
        true
    ),
    updated_at = NOW()
FROM (
    SELECT
        business_id,
        MIN(sucursal_id_default) AS sucursal_id_default
    FROM platform.public_embed_widgets
    WHERE COALESCE(metadata_json->>'surface', '') = 'public_embed'
      AND sucursal_id_default IS NOT NULL
    GROUP BY business_id
    HAVING COUNT(DISTINCT sucursal_id_default) = 1
) AS public_scope
WHERE erp.business_id = public_scope.business_id
  AND COALESCE(erp.metadata_json->>'surface', '') = 'erp_embed'
  AND (
      erp.sucursal_id_default IS NULL
      OR COALESCE(erp.metadata_json->>'page_variant', '') = ''
  );

INSERT INTO platform.public_embed_widget_allowed_origins (
    widget_id,
    origin,
    status,
    notes,
    created_at,
    updated_at
)
SELECT
    w.widget_id,
    'https://opalit-ia.com',
    'active',
    '[system-managed][erp-wrapper-origin]',
    NOW(),
    NOW()
FROM platform.public_embed_widgets w
LEFT JOIN platform.public_embed_widget_allowed_origins o
    ON o.widget_id = w.widget_id
   AND o.origin = 'https://opalit-ia.com'
WHERE w.status IN ('live', 'canary', 'internal')
  AND o.id IS NULL
ON CONFLICT (widget_id, origin) DO UPDATE
SET status = EXCLUDED.status,
    notes = EXCLUDED.notes,
    updated_at = NOW();

CREATE UNIQUE INDEX IF NOT EXISTS public_embed_widgets_canonical_runtime_scope_uq
    ON platform.public_embed_widgets (
        business_id,
        assistant_id,
        (COALESCE(metadata_json->>'surface', '')),
        (COALESCE(sucursal_id_default, -1))
    )
    WHERE status IN ('live', 'canary', 'internal');

CREATE INDEX IF NOT EXISTS public_embed_widgets_runtime_resolution_idx
    ON platform.public_embed_widgets (
        business_id,
        assistant_id,
        (COALESCE(metadata_json->>'surface', '')),
        (COALESCE(sucursal_id_default, -1)),
        updated_at DESC,
        widget_id
    )
    WHERE status IN ('live', 'canary', 'internal');

COMMENT ON INDEX platform.public_embed_widgets_canonical_runtime_scope_uq IS 'Garantiza una sola fila runtime por tenant + assistant + surface + sucursal en el registry publico.';
COMMENT ON INDEX platform.public_embed_widgets_runtime_resolution_idx IS 'Acelera la resolucion canonica de widgets publicos por tenant + surface + sucursal para Brain y wrappers ERP.';

COMMIT;
