Migrate to Supabase

-- ============================================================
--  Agent Builder — Supabase Table Creation Script
--  Run in: Supabase Dashboard → SQL Editor → New Query → Run
-- ============================================================

-- 1. ACCOUNTS
create table if not exists sb_accounts (
  id          text primary key,
  name        text not null,
  slug        text unique,
  created_at  bigint default extract(epoch from now())*1000
);

-- 2. USERS
create table if not exists sb_users (
  id          text primary key,
  account_id  text references sb_accounts(id) on delete cascade,
  name        text,
  email       text unique,
  password    text,
  role        text check (role in ('owner','admin','agent')),
  active      boolean default true
);

-- 3. SUPERADMINS
create table if not exists sb_superadmins (
  id          text primary key,
  name        text,
  email       text unique,
  password    text,
  active      boolean default true
);

-- 4. FLOW CATEGORIES
create table if not exists flow_categories (
  id               text primary key,
  account_id       text references sb_accounts(id) on delete cascade,
  name             text,
  description      text,
  icon             text,
  color            text,
  sort_order       integer default 0,
  starting_flow_id text,
  created_at       bigint default extract(epoch from now())*1000
);

-- 5. CHATBOT FLOWS
create table if not exists chatbot_flows (
  id            text primary key,
  account_id    text references sb_accounts(id) on delete cascade,
  name          text,
  description   text,
  category      text,
  category_id   text references flow_categories(id) on delete set null,
  nodes         jsonb default '[]'::jsonb,
  start_node_id text,
  status        text default 'draft',
  color         text default '#6366f1',
  steps_count   integer default 0,
  created_at    bigint default extract(epoch from now())*1000,
  updated_at    bigint default extract(epoch from now())*1000
);

-- 6. CONVERSATIONS
create table if not exists conversations (
  id          text primary key,
  account_id  text references sb_accounts(id) on delete cascade,
  agent_id    text,
  agent_name  text,
  flow_id     text,
  flow_name   text,
  started_at  bigint,
  ended_at    bigint,
  duration_ms bigint default 0,
  outcome     text default 'incomplete',
  steps_count integer default 0,
  path        jsonb default '[]'::jsonb,
  variables   jsonb default '{}'::jsonb,
  simulator   text,
  created_at  bigint default extract(epoch from now())*1000
);

-- 7. CHATBOT SETTINGS
create table if not exists chatbot_settings (
  id               text primary key,
  account_id       text unique references sb_accounts(id) on delete cascade,
  welcome_headline text default 'How can we help?',
  welcome_subtitle text default 'Choose a category below.',
  welcome_tagline  text default '',
  public_bot       boolean default false,
  default_flow_id  text default ''
);

-- 8. KB ARTICLES
create table if not exists kb_articles (
  id            text primary key,
  account_id    text references sb_accounts(id) on delete cascade,
  title         text,
  slug          text,
  category      text,
  content       jsonb,
  excerpt       text,
  tags          text,
  status        text default 'draft',
  linked_flow_id text default '',
  helpful_yes   integer default 0,
  helpful_no    integer default 0,
  view_count    integer default 0,
  created_at    bigint default extract(epoch from now())*1000,
  updated_at    bigint default extract(epoch from now())*1000
);

-- 9. API INTEGRATIONS (legacy — kept for migration compatibility)
create table if not exists api_integrations (
  id               text primary key,
  account_id       text references sb_accounts(id) on delete cascade,
  name             text,
  description      text,
  base_url         text,
  auth_type        text default 'none',
  auth_header      text,
  auth_value       text,
  default_headers  text,
  endpoints        jsonb default '[]'::jsonb,
  icon             text default '🔌',
  color            text default '#6366f1',
  status           text default 'active',
  last_tested_at   bigint,
  last_test_status text default 'untested',
  created_at       bigint default extract(epoch from now())*1000
);

-- 10. PLATFORM SETTINGS (global — one row, id = 'global')
create table if not exists platform_settings (
  id                  text primary key default 'global',
  openrouter_api_key  text default '',
  openrouter_model    text default 'openai/gpt-4o-mini',
  updated_at          bigint default extract(epoch from now())*1000
);

-- 11. API COLLECTIONS (MCP Collection manager — replaces api_integrations)
create table if not exists api_collections (
  id          text primary key,
  account_id  text references sb_accounts(id) on delete cascade,
  name        text not null,
  description text default '',
  base_url    text default '',
  auth_type   text default 'none',
  auth_key    text default '',
  auth_value  text default '',
  icon        text default '🔌',
  color       text default '#6366f1',
  sort_order  integer default 0,
  created_at  bigint default extract(epoch from now())*1000
);

-- 12. API REQUESTS (child of api_collections)
create table if not exists api_requests (
  id             text primary key,
  account_id     text references sb_accounts(id) on delete cascade,
  collection_id  text references api_collections(id) on delete cascade,
  name           text default 'New Request',
  method         text default 'GET',
  path           text default '/',
  headers        text default '[]',
  params         text default '[]',
  body           text default '',
  auth_override  text default null,
  purpose        text default '',
  outcome_map    text default '[]',
  last_response  text default '',
  mcp_tool_def   text default '',
  sort_order     integer default 0,
  created_at     bigint default extract(epoch from now())*1000,
  updated_at     bigint default extract(epoch from now())*1000
);

-- ============================================================
--  INDEXES for performance
-- ============================================================
create index if not exists idx_users_account    on sb_users(account_id);
create index if not exists idx_users_email      on sb_users(email);
create index if not exists idx_flows_account    on chatbot_flows(account_id);
create index if not exists idx_flows_cat        on chatbot_flows(category_id);
create index if not exists idx_cats_account     on flow_categories(account_id);
create index if not exists idx_convs_account    on conversations(account_id);
create index if not exists idx_convs_agent      on conversations(agent_id);
create index if not exists idx_kb_account       on kb_articles(account_id);
create index if not exists idx_api_account      on api_integrations(account_id);
create index if not exists idx_api_col_account  on api_collections(account_id);
create index if not exists idx_api_req_col      on api_requests(collection_id);
create index if not exists idx_api_req_account  on api_requests(account_id);

-- ============================================================
--  ROW LEVEL SECURITY — allow anon key full access
--  (Your app handles auth in JS — Supabase just stores data)
-- ============================================================
alter table sb_accounts        enable row level security;
alter table sb_users           enable row level security;
alter table sb_superadmins     enable row level security;
alter table flow_categories    enable row level security;
alter table chatbot_flows      enable row level security;
alter table conversations      enable row level security;
alter table chatbot_settings   enable row level security;
alter table kb_articles        enable row level security;
alter table api_integrations   enable row level security;
alter table platform_settings  enable row level security;
alter table api_collections    enable row level security;
alter table api_requests       enable row level security;

-- Grant anon role full CRUD access
-- NOTE: CREATE POLICY does not support IF NOT EXISTS in PostgreSQL.
-- We use DROP IF EXISTS first, then CREATE — safe to re-run.
drop policy if exists "anon_all" on sb_accounts;
create policy "anon_all" on sb_accounts
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on sb_users;
create policy "anon_all" on sb_users
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on sb_superadmins;
create policy "anon_all" on sb_superadmins
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on flow_categories;
create policy "anon_all" on flow_categories
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on chatbot_flows;
create policy "anon_all" on chatbot_flows
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on conversations;
create policy "anon_all" on conversations
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on chatbot_settings;
create policy "anon_all" on chatbot_settings
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on kb_articles;
create policy "anon_all" on kb_articles
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on api_integrations;
create policy "anon_all" on api_integrations
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on platform_settings;
create policy "anon_all" on platform_settings
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on api_collections;
create policy "anon_all" on api_collections
  for all to anon using (true) with check (true);

drop policy if exists "anon_all" on api_requests;
create policy "anon_all" on api_requests
  for all to anon using (true) with check (true);

-- ============================================================
--  Done! All 12 tables ready.
-- ============================================================
select 'Agent Builder tables created successfully!' as status;