USE db_abos_v0.1;

-- =============================================================================================
-- agent_context_module.sql — AI agent "context space" (RAG knowledge base).
--
-- dim_agent_context_documents holds small, free-text, admin-editable knowledge chunks an AI
-- agent can be grounded on (e.g. a public site's chat widget) — see AgentContextService's
-- lexical retrieval and SiteChatService. Deliberately separate from dim_web_entries: this is
-- unstructured knowledge (policy notes, positioning, one-off facts) rather than structured
-- collection content. business_id NULL = applies tenant-wide; set = scoped to one business
-- (e.g. TechMAC), matching dim_web_sites' own scoping convention.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS, WHERE NOT EXISTS seed guards.
-- =============================================================================================

CREATE TABLE IF NOT EXISTS dim_agent_context_documents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    title VARCHAR(255) NOT NULL,
    content MEDIUMTEXT NOT NULL,
    tags VARCHAR(255) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    KEY idx_dim_agent_context_documents_scope (tenant_id, business_id, is_active, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Seed TechMAC's initial context so Amber can answer from day one ─────────────────────────
SET @tenant_id := 1;
SET @techmac_business_id := (SELECT id FROM businesses WHERE tenant_id = @tenant_id AND name = 'TechMAC' LIMIT 1);

INSERT INTO dim_agent_context_documents (tenant_id, business_id, title, content, tags, is_active, is_deleted)
SELECT @tenant_id, @techmac_business_id,
       'What TechMAC does',
       'TechMAC builds and operates ABOS, a micro-ERP platform for small and mid-sized businesses. Our four solutions are GO DIGITAL (a fast, guided digitisation package for businesses starting from spreadsheets/paper), RDABS (rapid deployment of ABOS for businesses that want the full ERP quickly), IADA (integration and data automation for businesses whose data is fragmented across tools), and ABOS itself (the full system-of-record ERP: CRM, inventory, finance, projects, and a public web/store module). Every package has a published starting price — there is no "contact us for pricing" on our core solutions.',
       'company,overview', 1, 0
WHERE @techmac_business_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_agent_context_documents WHERE tenant_id = @tenant_id AND business_id = @techmac_business_id AND title = 'What TechMAC does'
);

INSERT INTO dim_agent_context_documents (tenant_id, business_id, title, content, tags, is_active, is_deleted)
SELECT @tenant_id, @techmac_business_id,
       'How to get started / book a call',
       'A visitor can book a discovery call via the "Book a call" button in the header or the closing call-to-action, which opens the discovery-call form. General questions or support requests go through the contact form. We respond to both within one business day. There is no self-serve signup yet — every engagement starts with a short discovery call so we can recommend the right solution (GO DIGITAL, RDABS, IADA, or ABOS) for the visitor''s situation.',
       'onboarding,contact', 1, 0
WHERE @techmac_business_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_agent_context_documents WHERE tenant_id = @tenant_id AND business_id = @techmac_business_id AND title = 'How to get started / book a call'
);

INSERT INTO dim_agent_context_documents (tenant_id, business_id, title, content, tags, is_active, is_deleted)
SELECT @tenant_id, @techmac_business_id,
       'Pricing philosophy',
       'TechMAC publishes every price up front across its four solutions and services list — visitors never have to ask "how much" for a base package, only for custom scope on top of it. Solutions are priced "from" a starting figure because most engagements include some tailoring (data migration, integrations, custom reporting); services (implementation, training, ongoing support) are priced separately and can be combined with any solution.',
       'pricing', 1, 0
WHERE @techmac_business_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_agent_context_documents WHERE tenant_id = @tenant_id AND business_id = @techmac_business_id AND title = 'Pricing philosophy'
);