-- BYTE AI - SUPABASE SCHEMA SETUP (2026)
-- Run this in your Supabase SQL Editor to fix PGRST205 errors.

-- 1. Enable pgvector extension for Semantic Memory
create extension if not exists vector;

-- 2. Tables for Core Configuration and Persona
create table if not exists public.channels_config (
    channel_id text primary key,
    temperature float,
    top_p float,
    agent_paused boolean default false,
    is_active boolean default true,
    updated_at timestamptz default now()
);

create table if not exists public.persona_profiles (
    channel_id text primary key,
    base_identity jsonb default '{}'::jsonb,
    tonality_engine jsonb default '{}'::jsonb,
    behavioral_constraints jsonb default '{}'::jsonb,
    model_routing jsonb default '{}'::jsonb,
    updated_at timestamptz default now()
);

create table if not exists public.channel_identity (
    channel_id text primary key,
    persona_name text,
    tone text,
    emote_vocab text[],
    lore text,
    updated_at timestamptz default now()
);

create table if not exists public.agent_notes (
    channel_id text primary key,
    notes text,
    updated_at timestamptz default now()
);

-- 3. Tables for Observability and History
create table if not exists public.observability_rollups (
    id uuid primary key default gen_random_uuid(),
    captured_at timestamptz default now(),
    snapshot jsonb not null
);

create table if not exists public.observability_channel_history (
    id bigint generated always as identity primary key,
    channel_id text not null,
    captured_at timestamptz default now(),
    snapshot jsonb not null
);

create table if not exists public.channel_state (
    channel_id text primary key,
    vibe text,
    observability jsonb default '{}'::jsonb,
    last_reply text,
    updated_at timestamptz default now()
);

create table if not exists public.channel_history (
    id bigint generated always as identity primary key,
    channel_id text not null,
    author text not null,
    message text not null,
    ts timestamptz default now()
);

-- 4. Tables for Specialized Features (Clips, Webhooks, Memory)
create table if not exists public.post_stream_reports (
    id bigint generated always as identity primary key,
    channel_id text not null,
    report jsonb not null,
    generated_at timestamptz default now(),
    trigger text
);

create table if not exists public.revenue_conversions (
    id bigint generated always as identity primary key,
    channel_id text not null,
    conversion jsonb not null,
    timestamp timestamptz default now()
);

create table if not exists public.outbound_webhooks (
    id uuid primary key default gen_random_uuid(),
    channel_id text not null,
    url text not null,
    secret text,
    event_types text[] default '{}',
    is_active boolean default true,
    created_at timestamptz default now(),
    updated_at timestamptz default now()
);

create table if not exists public.outbound_webhook_deliveries (
    id uuid primary key default gen_random_uuid(),
    webhook_id uuid not null,
    channel_id text not null,
    event_type text not null,
    status_code int,
    success boolean,
    latency_ms int,
    timestamp timestamptz default now()
);

-- 5. Semantic Memory with pgvector
create table if not exists public.semantic_memory_entries (
    entry_id uuid primary key default gen_random_uuid(),
    channel_id text not null,
    memory_type text not null,
    content text not null,
    tags text[] default '{}',
    context jsonb default '{}'::jsonb,
    embedding vector(1536), -- Standard OpenAI/Kimi embedding size
    created_at timestamptz default now(),
    updated_at timestamptz default now()
);

-- 6. RPC Functions for Semantic Search
create or replace function public.semantic_memory_search_pgvector(
    p_channel_id text,
    p_query_embedding vector(1536),
    p_limit int,
    p_search_limit int
) returns table (
    entry_id uuid,
    memory_type text,
    content text,
    tags text[],
    context jsonb,
    embedding vector(1536),
    created_at timestamptz,
    updated_at timestamptz,
    similarity float
) language plpgsql as $$
begin
    return query
    select
        s.entry_id,
        s.memory_type,
        s.content,
        s.tags,
        s.context,
        s.embedding,
        s.created_at,
        s.updated_at,
        1 - (s.embedding <=> p_query_embedding) as similarity
    from public.semantic_memory_entries s
    where s.channel_id = p_channel_id
    order by similarity desc
    limit p_limit;
end;
$$;

-- Enable Realtime for observability (optional but recommended)
alter publication supabase_realtime add table public.observability_channel_history;