ALTER TABLE audit_log
    ADD COLUMN IF NOT EXISTS resource_id UUID,
    ADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}';

CREATE TABLE IF NOT EXISTS pii_tenant_patterns (
    uuid          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL REFERENCES tenants(uuid),
    label         VARCHAR(100) NOT NULL,
    pattern       TEXT NOT NULL,
    replacement   VARCHAR(100) NOT NULL DEFAULT '[ID]',
    is_active     BOOLEAN NOT NULL DEFAULT true,
    created_at    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS knowledge_documents (
    uuid          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL REFERENCES tenants(uuid),
    external_id   VARCHAR(160) NOT NULL,
    title         VARCHAR(255) NOT NULL,
    category      VARCHAR(100) NOT NULL DEFAULT 'General',
    status        VARCHAR(40) NOT NULL DEFAULT 'ready',
    chunk_count   INT NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT NOW(),
    updated_at    TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, external_id)
);

CREATE TABLE IF NOT EXISTS knowledge_chunks (
    uuid          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL REFERENCES tenants(uuid),
    document_uuid UUID NOT NULL REFERENCES knowledge_documents(uuid) ON DELETE CASCADE,
    chunk_index   INT NOT NULL,
    content       TEXT NOT NULL,
    embedding     vector(64) NOT NULL,
    created_at    TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, document_uuid, chunk_index)
);

CREATE INDEX IF NOT EXISTS knowledge_chunks_embedding_idx
    ON knowledge_chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 16);

CREATE INDEX IF NOT EXISTS knowledge_chunks_document_idx
    ON knowledge_chunks (tenant_id, document_uuid, chunk_index);

DO $$
DECLARE
    table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'pii_tenant_patterns',
        'knowledge_documents',
        'knowledge_chunks'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
        EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', table_name);
        EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', table_name);
        EXECUTE format(
            'CREATE POLICY tenant_isolation ON %I USING (tenant_id = current_setting(''app.tenant_id'')::uuid)',
            table_name
        );
    END LOOP;
END $$;

GRANT SELECT, INSERT, UPDATE ON pii_tenant_patterns TO diwall_core_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON knowledge_documents TO diwall_core_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON knowledge_chunks TO diwall_core_app;
GRANT SELECT, INSERT ON audit_log TO diwall_core_app;
