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

CREATE SCHEMA IF NOT EXISTS platform;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS platform.website_info_items (
    id BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    prompt_key VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    content TEXT NOT NULL,
    url_pattern VARCHAR(255),
    include_knowledge BOOLEAN NOT NULL DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_website_info_items_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_website_info_items_sucursal_positive
        CHECK (sucursal_id > 0),
    CONSTRAINT chk_website_info_items_prompt_key_not_empty
        CHECK (length(trim(prompt_key)) > 0),
    CONSTRAINT chk_website_info_items_category
        CHECK (category IN ('knowledge', 'system-prompt')),
    CONSTRAINT chk_website_info_items_description_not_blank
        CHECK (description IS NULL OR length(trim(description)) > 0),
    CONSTRAINT chk_website_info_items_content_not_empty
        CHECK (length(trim(content)) > 0),
    CONSTRAINT chk_website_info_items_url_pattern_semantics
        CHECK (
            (category = 'system-prompt' AND url_pattern IS NOT NULL AND length(trim(url_pattern)) > 0)
            OR (category = 'knowledge' AND url_pattern IS NULL)
        ),
    CONSTRAINT chk_website_info_items_include_knowledge_semantics
        CHECK (
            (category = 'system-prompt')
            OR (category = 'knowledge' AND include_knowledge = FALSE)
        )
);

CREATE UNIQUE INDEX IF NOT EXISTS website_info_items_scope_prompt_key_uq
    ON platform.website_info_items (
        business_id,
        sucursal_id,
        category,
        prompt_key
    );

CREATE INDEX IF NOT EXISTS website_info_items_scope_category_active_idx
    ON platform.website_info_items (
        business_id,
        sucursal_id,
        category,
        is_active
    );

CREATE INDEX IF NOT EXISTS website_info_items_scope_url_pattern_idx
    ON platform.website_info_items (
        business_id,
        sucursal_id,
        url_pattern
    )
    WHERE category = 'system-prompt'
      AND is_active = TRUE
      AND url_pattern IS NOT NULL;

CREATE INDEX IF NOT EXISTS website_info_items_scope_created_idx
    ON platform.website_info_items (
        business_id,
        sucursal_id,
        created_at DESC
    );

COMMENT ON TABLE platform.website_info_items
    IS 'Inventario website-facing del widget web. Almacena knowledge reutilizable cross-page y system prompts especificos por pagina para cada negocio y sucursal.';
COMMENT ON COLUMN platform.website_info_items.id
    IS 'Identificador tecnico del website info item.';
COMMENT ON COLUMN platform.website_info_items.business_id
    IS 'Tenant del negocio propietario del widget web.';
COMMENT ON COLUMN platform.website_info_items.sucursal_id
    IS 'Sucursal exacta a la que pertenece esta configuracion website-facing.';
COMMENT ON COLUMN platform.website_info_items.prompt_key
    IS 'Clave tecnica estable del item dentro del scope business+sucursal+category.';
COMMENT ON COLUMN platform.website_info_items.category
    IS 'Tipo website-facing del item: knowledge reusable cross-page o system-prompt page-specific.';
COMMENT ON COLUMN platform.website_info_items.description
    IS 'Etiqueta administrativa legible para la UI de OpHub.';
COMMENT ON COLUMN platform.website_info_items.content
    IS 'Contenido textual del conocimiento o del system prompt.';
COMMENT ON COLUMN platform.website_info_items.url_pattern
    IS 'Patron de pagina para matching del widget. Solo aplica a category = system-prompt.';
COMMENT ON COLUMN platform.website_info_items.include_knowledge
    IS 'Si el system prompt debe incluir tambien el bloque knowledge del scope actual.';
COMMENT ON COLUMN platform.website_info_items.is_active
    IS 'Permite desactivar items website-facing sin eliminarlos de la configuracion.';
COMMENT ON COLUMN platform.website_info_items.created_at
    IS 'Timestamp de creacion del item.';
COMMENT ON COLUMN platform.website_info_items.updated_at
    IS 'Timestamp de ultima actualizacion del item. OpHub lo mantiene en cada write.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES (
    'website_info_items',
    'platform',
    'Fuente canonica website-facing para knowledge cross-page y system prompts page-specific del widget web'
)
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE platform.website_info_items;
