Supabase Migration v2

-- ============================================================
--  Agent Builder — Migration v7
--  Creates: staff_status + status_logs tables WITH RLS
--  Safe to re-run (uses IF NOT EXISTS throughout)
-- ============================================================

-- ── 1. STAFF STATUS (one row per user — current live status) ──
create table if not exists staff_status (
  id           text primary key,
  user_id      text not null,
  account_id   text,
  user_name    text,
  status_key   text not null default 'available',
  location     text,
  note         text,
  since_at     bigint,
  updated_at   bigint,
  last_seen_at bigint default null
);

-- ── 2. STATUS LOGS (append-only history) ──────────────────────
create table if not exists status_logs (
  id          text primary key,
  user_id     text not null,
  account_id  text,
  user_name   text,
  status_key  text not null,
  location    text,
  note        text,
  logged_at   bigint not null
);

-- ── Indexes ───────────────────────────────────────────────────
create index if not exists idx_staff_status_account  on staff_status(account_id);
create index if not exists idx_staff_status_user     on staff_status(user_id);
create index if not exists idx_staff_status_last_seen on staff_status(last_seen_at);
create index if not exists idx_status_logs_account   on status_logs(account_id);
create index if not exists idx_status_logs_user      on status_logs(user_id);
create index if not exists idx_status_logs_logged_at on status_logs(logged_at);

-- ── Row Level Security ────────────────────────────────────────
alter table staff_status  enable row level security;
alter table status_logs   enable row level security;

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

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

-- ── Verify ────────────────────────────────────────────────────
select tablename, policyname, cmd
  from pg_policies
 where tablename in ('staff_status','status_logs')
 order by tablename;

-- ============================================================
--  RLS Fix — run ONLY if tables already exist in Table Editor
--  but workforce.html still shows 0 rows / "No Status"
-- ============================================================

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

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

-- Verify:
select tablename, policyname, cmd
  from pg_policies
 where tablename in ('staff_status','status_logs')
 order by tablename;

-- ============================================================
--  Agent Builder — Migration v9 (run AFTER v7/v8)
--  Creates: team_messages — internal staff chat messages
--  Used by: team-chat.html — WhatsApp-style team messaging
--  Safe to re-run: uses IF NOT EXISTS throughout
-- ============================================================

create table if not exists team_messages (
  id           text primary key,
  conv_id      text not null,       -- sorted user_id pair: "uid1__uid2"
  sender_id    text not null,       -- sb_users.id of sender
  sender_name  text,
  receiver_id  text,                -- null = group/broadcast
  body         text not null,
  sent_at      bigint not null,     -- Date.now() milliseconds
  read         boolean default false,
  account_id   text                 -- for multi-tenant isolation
);

-- Indexes for fast conversation queries
create index if not exists idx_team_messages_conv    on team_messages(conv_id);
create index if not exists idx_team_messages_sent    on team_messages(sent_at);
create index if not exists idx_team_messages_sender  on team_messages(sender_id);

-- Row Level Security (allow anon key — same as other tables)
alter table team_messages enable row level security;

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

-- Verify
select tablename, policyname, cmd
  from pg_policies
 where tablename = 'team_messages';

-- ============================================================
--  Agent Builder — Migration v10
--  Creates: skill_matrix — admin-set skill ratings per user
--  Used by: skill-matrix.html (admin) + team-chat.html profile panel (read-only)
--  Safe to re-run: uses IF NOT EXISTS throughout
-- ============================================================

create table if not exists skill_matrix (
  id           text primary key,
  account_id   text not null,
  user_id      text not null,
  category_id  text not null,
  rating       integer not null default 0 check (rating >= 0 and rating <= 5),
  note         text default null,
  updated_at   bigint default extract(epoch from now())*1000
);

-- Unique constraint: one rating per user per category per account
-- This allows the UPSERT (merge-duplicates) to work correctly
create unique index if not exists idx_skill_matrix_unique
  on skill_matrix(account_id, user_id, category_id);

-- Additional indexes for fast lookups
create index if not exists idx_skill_matrix_account  on skill_matrix(account_id);
create index if not exists idx_skill_matrix_user     on skill_matrix(user_id);
create index if not exists idx_skill_matrix_category on skill_matrix(category_id);

-- Row Level Security (allow anon key — same as all other tables)
alter table skill_matrix enable row level security;

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

-- Verify
select tablename, policyname, cmd
  from pg_policies
 where tablename = 'skill_matrix';

-- ============================================================
--  Agent Builder — Migration v11
--  Creates: shift_templates, user_shifts, calendar_events
--  Used by: team-calendar.html
--  Safe to re-run: uses IF NOT EXISTS throughout
-- ============================================================

-- 1. Shift templates (recurring schedules)
create table if not exists shift_templates (
  id           text primary key,
  account_id   text not null,
  name         text not null,
  start_time   text not null default '09:00',
  end_time     text not null default '17:00',
  days         jsonb default '[]',          -- array of day numbers [0-6]
  color        text default '#34d399',
  updated_at   bigint default extract(epoch from now())*1000
);

create index if not exists idx_shift_templates_account on shift_templates(account_id);

alter table shift_templates enable row level security;
drop policy if exists "anon_all" on shift_templates;
create policy "anon_all" on shift_templates
  for all to anon using (true) with check (true);

-- 2. User–shift assignments
create table if not exists user_shifts (
  id           text primary key,
  account_id   text not null,
  user_id      text not null,
  template_id  text not null references shift_templates(id) on delete cascade,
  updated_at   bigint default extract(epoch from now())*1000
);

create unique index if not exists idx_user_shifts_unique
  on user_shifts(account_id, user_id, template_id);

create index if not exists idx_user_shifts_account on user_shifts(account_id);

alter table user_shifts enable row level security;
drop policy if exists "anon_all" on user_shifts;
create policy "anon_all" on user_shifts
  for all to anon using (true) with check (true);

-- 3. Calendar events
create table if not exists calendar_events (
  id           text primary key,
  account_id   text not null,
  user_id      text not null,
  type         text not null default 'other',
  title        text,
  start_ts     bigint not null,             -- epoch ms
  end_ts       bigint not null,
  start_time   text,                        -- HH:MM string for easy filtering
  end_time     text,
  note         text,
  recur        text default 'none',         -- none | daily | weekly | weekdays
  visibility   text default 'team',         -- team | private
  updated_at   bigint default extract(epoch from now())*1000
);

create index if not exists idx_calendar_events_account on calendar_events(account_id);
create index if not exists idx_calendar_events_user    on calendar_events(user_id);
create index if not exists idx_calendar_events_time    on calendar_events(start_ts);

alter table calendar_events enable row level security;
drop policy if exists "anon_all" on calendar_events;
create policy "anon_all" on calendar_events
  for all to anon using (true) with check (true);

-- Verify all 3 tables created
select tablename, rowsecurity
  from pg_tables
 where tablename in ('shift_templates','user_shifts','calendar_events')
 order by tablename;

-- ============================================================
--  Agent Builder — Migration v12
--  Extends: sb_users — adds 7 profile columns
--  Used by: admin.html (Edit Member), team-chat.html (profile panel),
--           staff-status.html (profile settings)
--  Safe to re-run: uses ADD COLUMN IF NOT EXISTS throughout
-- ============================================================

alter table sb_users
  add column if not exists about          text    default null,
  add column if not exists phone          text    default null,
  add column if not exists extension      text    default null,
  add column if not exists mobile_cc      text    default null,
  add column if not exists mobile_num     text    default null,
  add column if not exists personal_email text    default null,
  add column if not exists timezone       text    default null;

-- Force PostgREST to reload its schema cache immediately
-- (run this in the SQL editor after the ALTER TABLE above)
notify pgrst, 'reload schema';

-- Verify columns were added
select column_name, data_type, is_nullable
  from information_schema.columns
 where table_name = 'sb_users'
   and column_name in ('about','phone','extension','mobile_cc','mobile_num','personal_email','timezone')
 order by column_name;

-- ============================================================
--  Agent Builder — Migration v13
--  Timezone-Aware Calendar
--  Extends: calendar_events, shift_templates, sb_users
--  Used by: team-calendar.html (per-employee TZ rendering)
--  Safe to re-run: uses IF NOT EXISTS / ADD COLUMN IF NOT EXISTS
--  Requires: Migration v11 (calendar tables) + v12 (sb_users tz col)
-- ============================================================

-- 1. Add timezone column to calendar_events
--    Stores the IANA timezone of the assigned user at the time
--    the event was created (e.g. 'America/New_York', 'Asia/Kolkata').
--    start_time / end_time are already stored in that local timezone.
alter table calendar_events
  add column if not exists timezone text default null;

-- 2. Add optional timezone override to shift_templates
--    Normally the user's own sb_users.timezone is used; this column
--    lets you override per template (e.g. a night-shift template
--    explicitly in 'Asia/Kolkata' regardless of who is assigned).
alter table shift_templates
  add column if not exists timezone text default null;

-- 3. Performance index — sb_users.timezone
--    Speeds up the employee list query that groups by timezone
--    when the calendar renders the viewer-TZ dropdown.
create index if not exists idx_sb_users_timezone
  on sb_users(timezone)
  where timezone is not null;

-- 4. Composite index — calendar_events per-user date-range scan
--    The calendar fetches events with:
--      WHERE account_id = ? AND user_id = ? AND start_ts BETWEEN ? AND ?
--    This index covers that pattern directly.
create index if not exists idx_calendar_events_user_ts
  on calendar_events(account_id, user_id, start_ts);

-- 5. Composite index — shift_templates by account + name
--    Speeds up the shift manager list sort.
create index if not exists idx_shift_templates_account_name
  on shift_templates(account_id, name);

-- 6. Reload PostgREST schema cache so new columns are visible immediately
notify pgrst, 'reload schema';

-- 7. Verify — should return 2 rows (calendar_events + shift_templates)
select table_name, column_name, data_type, is_nullable
  from information_schema.columns
 where table_name in ('calendar_events', 'shift_templates')
   and column_name = 'timezone'
 order by table_name;

-- ============================================================
--  Agent Builder — Migration v14
--  Adds: path, variables, messages_log JSONB columns
--  to the conversations table.
--  Required for: Step Path tab + Variables tab in Conversation Details
--  Safe to re-run: uses ADD COLUMN IF NOT EXISTS throughout
-- ============================================================

-- Step 1: Add path column (step audit trail — array of {nodeId,title,type,answer,ts})
ALTER TABLE conversations
  ADD COLUMN IF NOT EXISTS path jsonb DEFAULT '[]'::jsonb;

-- Step 2: Add variables column (captured flow variables — {key: value} object)
ALTER TABLE conversations
  ADD COLUMN IF NOT EXISTS variables jsonb DEFAULT '{}'::jsonb;

-- Step 3: Add messages_log column (full chat transcript)
ALTER TABLE conversations
  ADD COLUMN IF NOT EXISTS messages_log jsonb DEFAULT '[]'::jsonb;

-- Step 4: GIN indexes for fast JSON search
CREATE INDEX IF NOT EXISTS idx_convs_path         ON conversations USING gin(path);
CREATE INDEX IF NOT EXISTS idx_convs_variables    ON conversations USING gin(variables);
CREATE INDEX IF NOT EXISTS idx_conv_messages_log  ON conversations USING gin(messages_log);

-- Step 5: Force PostgREST schema cache reload (required after ALTER TABLE)
NOTIFY pgrst, 'reload schema';

-- Step 6: Verify — should return 3 rows
SELECT column_name, data_type
  FROM information_schema.columns
 WHERE table_name = 'conversations'
   AND column_name IN ('path', 'variables', 'messages_log')
 ORDER BY column_name;