Links Setup

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users table (extends Supabase Auth)
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE,
  full_name TEXT,
  role TEXT NOT NULL DEFAULT 'customer' CHECK (role IN ('admin', 'agent', 'customer')),
  access_role TEXT NOT NULL DEFAULT 'workspace_admin' CHECK (
    access_role IN (
      'workspace_admin',
      'support_manager',
      'support_agent',
      'analyst',
      'knowledge_manager',
      'viewer',
      'customer'
    )
  ),
  permissions JSONB DEFAULT '[]'::jsonb,
  workspace_id UUID,
  invited_by UUID,
  job_title TEXT,
  avatar_url TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.users ADD COLUMN IF NOT EXISTS access_role TEXT DEFAULT 'workspace_admin';
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '[]'::jsonb;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS workspace_id UUID;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS invited_by UUID;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS job_title TEXT;

-- Customers table
CREATE TABLE IF NOT EXISTS public.customers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  phone TEXT,
  company TEXT,
  avatar_url TEXT,
  crm_id TEXT,
  crm_platform TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Agents table
CREATE TABLE IF NOT EXISTS public.agents (
  id UUID PRIMARY KEY REFERENCES public.users(id) ON DELETE CASCADE,
  department TEXT,
  max_concurrent_conversations INTEGER DEFAULT 5,
  status TEXT NOT NULL DEFAULT 'offline' CHECK (status IN ('online', 'away', 'offline', 'busy')),
  last_activity_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Workspaces table (must exist before conversations/messages reference it)
CREATE TABLE IF NOT EXISTS public.workspaces (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  owner_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  plan_slug TEXT NOT NULL DEFAULT 'starter',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Conversations table
CREATE TABLE IF NOT EXISTS public.conversations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID,
  customer_id UUID NOT NULL REFERENCES public.customers(id) ON DELETE CASCADE,
  assigned_agent_id UUID REFERENCES public.agents(id) ON DELETE SET NULL,
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'pending', 'resolved', 'closed', 'archived')),
  priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  subject TEXT,
  channel TEXT NOT NULL DEFAULT 'chat' CHECK (channel IN ('chat', 'email', 'phone', 'widget')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  resolved_at TIMESTAMP WITH TIME ZONE,
  first_response_at TIMESTAMP WITH TIME ZONE,
  archived_at TIMESTAMP WITH TIME ZONE,
  tags TEXT[] DEFAULT ARRAY[]::TEXT[]
);

ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS workspace_id UUID;
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS assigned_agent_id UUID REFERENCES public.agents(id) ON DELETE SET NULL;
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS channel TEXT NOT NULL DEFAULT 'chat' CHECK (channel IN ('chat', 'email', 'phone', 'widget'));
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS first_response_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE public.conversations ADD COLUMN IF NOT EXISTS tags TEXT[] DEFAULT ARRAY[]::TEXT[];
ALTER TABLE public.conversations DROP CONSTRAINT IF EXISTS conversations_status_check;
ALTER TABLE public.conversations ADD CONSTRAINT conversations_status_check CHECK (status IN ('open', 'pending', 'resolved', 'closed', 'archived'));

-- Backfill workspace_id on conversations from the assigned agent's workspace
-- Only set values that exist in the workspaces table
UPDATE public.conversations c
SET workspace_id = u.workspace_id
FROM public.users u
JOIN public.workspaces w ON w.id = u.workspace_id
WHERE u.id = c.assigned_agent_id
  AND c.workspace_id IS NULL;

-- Now safe to add the FK constraint
ALTER TABLE public.conversations
  DROP CONSTRAINT IF EXISTS conversations_workspace_id_fkey;
ALTER TABLE public.conversations
  ADD CONSTRAINT conversations_workspace_id_fkey
  FOREIGN KEY (workspace_id) REFERENCES public.workspaces(id) ON DELETE CASCADE;

-- Messages table
CREATE TABLE IF NOT EXISTS public.messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID,
  conversation_id UUID NOT NULL REFERENCES public.conversations(id) ON DELETE CASCADE,
  sender_id UUID NOT NULL,
  sender_type TEXT NOT NULL CHECK (sender_type IN ('agent', 'customer')),
  content TEXT NOT NULL,
  attachments JSONB DEFAULT '[]'::jsonb,
  metadata JSONB DEFAULT '{}'::jsonb,
  is_internal_note BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS workspace_id UUID;
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS sender_type TEXT NOT NULL DEFAULT 'customer' CHECK (sender_type IN ('agent', 'customer'));
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS attachments JSONB DEFAULT '[]'::jsonb;
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS is_internal_note BOOLEAN DEFAULT false;
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

-- Backfill workspace_id on messages from the parent conversation
UPDATE public.messages m
SET workspace_id = c.workspace_id
FROM public.conversations c
WHERE c.id = m.conversation_id
  AND m.workspace_id IS NULL
  AND c.workspace_id IS NOT NULL;

-- Now safe to add the FK constraint
ALTER TABLE public.messages
  DROP CONSTRAINT IF EXISTS messages_workspace_id_fkey;
ALTER TABLE public.messages
  ADD CONSTRAINT messages_workspace_id_fkey
  FOREIGN KEY (workspace_id) REFERENCES public.workspaces(id) ON DELETE CASCADE;

-- Canned responses
CREATE TABLE IF NOT EXISTS public.canned_responses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  agent_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  category TEXT,
  shortcut TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.canned_responses ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES public.workspaces(id) ON DELETE CASCADE;

-- Knowledge base articles
CREATE TABLE IF NOT EXISTS public.knowledge_base_articles (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  category TEXT NOT NULL,
  is_published BOOLEAN DEFAULT false,
  view_count INTEGER DEFAULT 0,
  created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.knowledge_base_articles ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES public.workspaces(id) ON DELETE CASCADE;

-- SLA rules
CREATE TABLE IF NOT EXISTS public.sla_rules (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  priority TEXT NOT NULL CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  response_time_minutes INTEGER NOT NULL,
  resolution_time_minutes INTEGER NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.sla_rules ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE public.sla_rules ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE public.sla_rules ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES public.workspaces(id) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS public.qa_scoring_metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID NOT NULL,
  label TEXT NOT NULL,
  description TEXT,
  rule_type TEXT NOT NULL CHECK (
    rule_type IN (
      'first_response_within_sla',
      'resolution_within_sla',
      'fast_handling',
      'internal_note_for_priority',
      'engagement_depth',
      'sla_adherence',
      'has_internal_note',
      'agent_replied',
      'resolved_conversation',
      'conversation_touched'
    )
  ),
  weight INTEGER NOT NULL DEFAULT 10 CHECK (weight >= 0 AND weight <= 100),
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  is_default BOOLEAN DEFAULT false,
  created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS workspace_id UUID;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS rule_type TEXT DEFAULT 'agent_replied';
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS weight INTEGER DEFAULT 10;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS sort_order INTEGER DEFAULT 0;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS is_default BOOLEAN DEFAULT false;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES public.users(id) ON DELETE SET NULL;
ALTER TABLE public.qa_scoring_metrics ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

-- Automation rules
CREATE TABLE IF NOT EXISTS public.automation_rules (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  trigger TEXT NOT NULL,
  action TEXT NOT NULL,
  action_data JSONB DEFAULT '{}'::jsonb,
  is_active BOOLEAN DEFAULT true,
  priority INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.automation_rules ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES public.workspaces(id) ON DELETE CASCADE;

-- User Pinned Conversations
CREATE TABLE IF NOT EXISTS public.user_pinned_conversations (
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  conversation_id UUID NOT NULL REFERENCES public.conversations(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, conversation_id)
);

ALTER TABLE public.user_pinned_conversations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own pinned conversations" ON public.user_pinned_conversations
  FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- Email notification preferences
CREATE TABLE IF NOT EXISTS public.email_notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  is_enabled BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.email_notifications ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES public.users(id) ON DELETE CASCADE;
ALTER TABLE public.email_notifications ADD COLUMN IF NOT EXISTS event_type TEXT DEFAULT 'conversation.new';
ALTER TABLE public.email_notifications ADD COLUMN IF NOT EXISTS is_enabled BOOLEAN DEFAULT true;
ALTER TABLE public.email_notifications ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;
UPDATE public.email_notifications SET event_type = 'conversation.new' WHERE event_type IS NULL;
ALTER TABLE public.email_notifications ALTER COLUMN event_type SET NOT NULL;
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'email_notifications'
      AND column_name = 'type'
  ) THEN
    ALTER TABLE public.email_notifications
      ALTER COLUMN type SET DEFAULT 'conversation.new';

    UPDATE public.email_notifications
    SET type = event_type
    WHERE type IS NULL OR type <> event_type;
  END IF;

  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'email_notifications'
      AND column_name = 'enabled'
  ) THEN
    UPDATE public.email_notifications
    SET enabled = is_enabled
    WHERE enabled IS DISTINCT FROM is_enabled;
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS public.ai_provider_configs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL UNIQUE REFERENCES public.users(id) ON DELETE CASCADE,
  workspace_id UUID,
  provider TEXT NOT NULL DEFAULT 'google-ai-studio' CHECK (
    provider IN (
      'google-ai-studio',
      'openai',
      'anthropic',
      'deepseek',
      'mistral',
      'groq',
      'openrouter',
      'xai',
      'perplexity',
      'together',
      'deepl',
      'openai-compatible'
    )
  ),
  model TEXT NOT NULL DEFAULT 'gemini-2.5-flash',
  assist_model TEXT,
  translation_model TEXT,
  api_key_encrypted TEXT,
  api_key_hint TEXT,
  base_url TEXT,
  is_enabled BOOLEAN DEFAULT true,
  agent_language_code TEXT DEFAULT 'en',
  agent_language_name TEXT DEFAULT 'English',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS workspace_id UUID;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS assist_model TEXT;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS translation_model TEXT;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS api_key_hint TEXT;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS base_url TEXT;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS is_enabled BOOLEAN DEFAULT true;
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS agent_language_code TEXT DEFAULT 'en';
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS agent_language_name TEXT DEFAULT 'English';
ALTER TABLE public.ai_provider_configs ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE public.ai_provider_configs DROP CONSTRAINT IF EXISTS ai_provider_configs_provider_check;
ALTER TABLE public.ai_provider_configs ADD CONSTRAINT ai_provider_configs_provider_check CHECK (
  provider IN (
    'google-ai-studio',
    'openai',
    'anthropic',
    'deepseek',
    'mistral',
    'groq',
    'openrouter',
    'xai',
    'perplexity',
    'together',
    'deepl',
    'openai-compatible'
  )
);

-- Activity logs
CREATE TABLE IF NOT EXISTS public.activity_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  action TEXT NOT NULL,
  resource_type TEXT,
  resource_id UUID,
  changes JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public.agent_productivity_snapshots (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID,
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  agent_id UUID REFERENCES public.agents(id) ON DELETE SET NULL,
  productivity_score INTEGER NOT NULL CHECK (productivity_score >= 0 AND productivity_score <= 100),
  label TEXT NOT NULL,
  metrics JSONB DEFAULT '{}'::jsonb,
  signals JSONB DEFAULT '[]'::jsonb,
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public.widget_settings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID NOT NULL,
  widget_id TEXT NOT NULL UNIQUE,
  position TEXT NOT NULL DEFAULT 'bottom-right' CHECK (position IN ('bottom-right', 'bottom-left')),
  primary_color TEXT NOT NULL DEFAULT '#0f766e',
  theme TEXT NOT NULL DEFAULT 'light' CHECK (theme IN ('light', 'dark')),
  appearance JSONB DEFAULT '{"template":"concierge","headerColor":"#0f766e","headerTextColor":"#ffffff","panelColor":"#ffffff","surfaceColor":"#f3f7f8","cardColor":"#ffffff","textColor":"#0f172a","mutedTextColor":"#64748b","borderColor":"#d8e2ed","userMessageColor":"#0f766e","buttonTextColor":"#ffffff","radius":"soft"}'::jsonb,
  widget_name TEXT NOT NULL DEFAULT 'Links Concierge',
  greeting TEXT NOT NULL DEFAULT 'Share what you need and we will route it to the right teammate with the full conversation intact.',
  is_enabled BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.widget_settings ADD COLUMN IF NOT EXISTS appearance JSONB DEFAULT '{"template":"concierge","headerColor":"#0f766e","headerTextColor":"#ffffff","panelColor":"#ffffff","surfaceColor":"#f3f7f8","cardColor":"#ffffff","textColor":"#0f172a","mutedTextColor":"#64748b","borderColor":"#d8e2ed","userMessageColor":"#0f766e","buttonTextColor":"#ffffff","radius":"soft"}'::jsonb;

CREATE TABLE IF NOT EXISTS public.subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  workspace_id UUID NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  plan_slug TEXT NOT NULL,
  billing_cycle TEXT NOT NULL DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'annual', 'custom')),
  status TEXT NOT NULL DEFAULT 'trialing' CHECK (status IN ('active', 'past_due', 'canceled', 'trialing', 'incomplete')),
  current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
  current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  cancel_at_period_end BOOLEAN DEFAULT false,
  trial_end TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public.payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  subscription_id UUID NOT NULL REFERENCES public.subscriptions(id) ON DELETE CASCADE,
  amount DECIMAL(10, 2) NOT NULL,
  currency TEXT NOT NULL DEFAULT 'USD',
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('succeeded', 'pending', 'failed', 'refunded')),
  payment_method TEXT NOT NULL CHECK (payment_method IN ('card', 'bank_transfer', 'paypal')),
  payment_intent_id TEXT,
  invoice_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_conversations_workspace_id ON public.conversations(workspace_id);
CREATE INDEX IF NOT EXISTS idx_conversations_customer_id ON public.conversations(customer_id);
CREATE INDEX IF NOT EXISTS idx_conversations_assigned_agent_id ON public.conversations(assigned_agent_id);
CREATE INDEX IF NOT EXISTS idx_conversations_status ON public.conversations(status);
CREATE INDEX IF NOT EXISTS idx_conversations_priority ON public.conversations(priority);
CREATE INDEX IF NOT EXISTS idx_conversations_archived_at ON public.conversations(archived_at);
CREATE INDEX IF NOT EXISTS idx_messages_workspace_id ON public.messages(workspace_id);
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON public.messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_sender_id ON public.messages(sender_id);
CREATE INDEX IF NOT EXISTS idx_agents_status ON public.agents(status);
CREATE INDEX IF NOT EXISTS idx_users_workspace_id ON public.users(workspace_id);
CREATE INDEX IF NOT EXISTS idx_canned_responses_agent_id ON public.canned_responses(agent_id);
CREATE INDEX IF NOT EXISTS idx_knowledge_base_category ON public.knowledge_base_articles(category);
CREATE INDEX IF NOT EXISTS idx_qa_scoring_metrics_workspace_id ON public.qa_scoring_metrics(workspace_id);
CREATE INDEX IF NOT EXISTS idx_qa_scoring_metrics_sort_order ON public.qa_scoring_metrics(workspace_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_automation_rules_active ON public.automation_rules(is_active);
CREATE UNIQUE INDEX IF NOT EXISTS idx_ai_provider_configs_user_id_unique ON public.ai_provider_configs(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_provider_configs_workspace_id ON public.ai_provider_configs(workspace_id);
CREATE INDEX IF NOT EXISTS idx_agent_productivity_snapshots_user_id ON public.agent_productivity_snapshots(user_id, recorded_at DESC);
CREATE INDEX IF NOT EXISTS idx_agent_productivity_snapshots_workspace_id ON public.agent_productivity_snapshots(workspace_id, recorded_at DESC);
CREATE INDEX IF NOT EXISTS idx_widget_settings_workspace_id ON public.widget_settings(workspace_id);
CREATE INDEX IF NOT EXISTS idx_workspaces_owner_id ON public.workspaces(owner_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON public.subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_workspace_id ON public.subscriptions(workspace_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON public.subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_payments_user_id ON public.payments(user_id);
CREATE INDEX IF NOT EXISTS idx_payments_subscription_id ON public.payments(subscription_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON public.payments(status);

-- Enable RLS
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.canned_responses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.knowledge_base_articles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sla_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.qa_scoring_metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.automation_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.email_notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_provider_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.activity_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_productivity_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.widget_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;

-- Drop existing policies
DROP POLICY IF EXISTS "Users can read all user profiles" ON public.users;
DROP POLICY IF EXISTS "Users can update own profile" ON public.users;
DROP POLICY IF EXISTS "Authenticated users can read customers" ON public.customers;
DROP POLICY IF EXISTS "Authenticated users can write customers" ON public.customers;
DROP POLICY IF EXISTS "Authenticated users can read conversations" ON public.conversations;
DROP POLICY IF EXISTS "Authenticated users can update conversations" ON public.conversations;
DROP POLICY IF EXISTS "Authenticated users can read messages" ON public.messages;
DROP POLICY IF EXISTS "Authenticated users can insert messages" ON public.messages;
DROP POLICY IF EXISTS "Authenticated users can read canned responses" ON public.canned_responses;
DROP POLICY IF EXISTS "Authenticated users can manage knowledge base" ON public.knowledge_base_articles;
DROP POLICY IF EXISTS "Public can read published knowledge base" ON public.knowledge_base_articles;
DROP POLICY IF EXISTS "Authenticated users can manage SLA rules" ON public.sla_rules;
DROP POLICY IF EXISTS "Authenticated users can manage QA scoring metrics" ON public.qa_scoring_metrics;
DROP POLICY IF EXISTS "Authenticated users can manage automation rules" ON public.automation_rules;
DROP POLICY IF EXISTS "Users can manage their notifications" ON public.email_notifications;
DROP POLICY IF EXISTS "Users can manage own AI provider config" ON public.ai_provider_configs;
DROP POLICY IF EXISTS "Authenticated users can manage productivity snapshots" ON public.agent_productivity_snapshots;
DROP POLICY IF EXISTS "Authenticated users can read agents" ON public.agents;
DROP POLICY IF EXISTS "Users can update own agent profile" ON public.agents;
DROP POLICY IF EXISTS "Users can create own agent profile" ON public.agents;
DROP POLICY IF EXISTS "Authenticated users can manage widget settings" ON public.widget_settings;
DROP POLICY IF EXISTS "Authenticated users can manage workspaces" ON public.workspaces;
DROP POLICY IF EXISTS "Authenticated users can manage subscriptions" ON public.subscriptions;
DROP POLICY IF EXISTS "Authenticated users can manage payments" ON public.payments;

-- Users
CREATE POLICY "Users can read all user profiles" ON public.users
  FOR SELECT TO authenticated USING (true);

CREATE POLICY "Users can update own profile" ON public.users
  FOR UPDATE TO authenticated USING (auth.uid() = id);

-- Customers: readable by agents/admins in the same workspace via direct conversation.workspace_id
CREATE POLICY "Authenticated users can read customers" ON public.customers
  FOR SELECT TO authenticated USING (
    EXISTS (
      SELECT 1 FROM public.conversations c
      JOIN public.users u ON u.id = auth.uid()
      WHERE c.customer_id = customers.id
        AND c.workspace_id = u.workspace_id
    )
    OR EXISTS (
      SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin'
    )
  );

CREATE POLICY "Authenticated users can write customers" ON public.customers
  FOR ALL TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('admin', 'agent'))
  )
  WITH CHECK (
    EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('admin', 'agent'))
  );

-- Conversations: direct workspace_id column match
CREATE POLICY "Authenticated users can read conversations" ON public.conversations
  FOR SELECT TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can update conversations" ON public.conversations
  FOR ALL TO authenticated
  USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  )
  WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

-- Messages: direct workspace_id column match
CREATE POLICY "Authenticated users can read messages" ON public.messages
  FOR SELECT TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can insert messages" ON public.messages
  FOR INSERT TO authenticated WITH CHECK (
    sender_id = auth.uid()
    AND (
      workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
      OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
    )
  );

-- Agents: scoped to same workspace via users join
CREATE POLICY "Authenticated users can read agents" ON public.agents
  FOR SELECT TO authenticated USING (
    id = auth.uid()
    OR EXISTS (
      SELECT 1 FROM public.users u
      JOIN public.users target ON target.id = agents.id
      WHERE u.id = auth.uid()
        AND u.workspace_id IS NOT NULL
        AND target.workspace_id = u.workspace_id
    )
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Users can create own agent profile" ON public.agents
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own agent profile" ON public.agents
  FOR UPDATE TO authenticated USING (auth.uid() = id) WITH CHECK (auth.uid() = id);

CREATE POLICY "Authenticated users can read canned responses" ON public.canned_responses
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage knowledge base" ON public.knowledge_base_articles
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Public can read published knowledge base" ON public.knowledge_base_articles
  FOR SELECT USING (is_published = true);

CREATE POLICY "Authenticated users can manage SLA rules" ON public.sla_rules
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage QA scoring metrics" ON public.qa_scoring_metrics
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage automation rules" ON public.automation_rules
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Users can manage their notifications" ON public.email_notifications
  FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can manage own AI provider config" ON public.ai_provider_configs
  FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Authenticated users can manage productivity snapshots" ON public.agent_productivity_snapshots
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage widget settings" ON public.widget_settings
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage workspaces" ON public.workspaces
  FOR ALL TO authenticated USING (
    id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage subscriptions" ON public.subscriptions
  FOR ALL TO authenticated USING (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR user_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    workspace_id = (SELECT workspace_id FROM public.users WHERE id = auth.uid())
    OR user_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Authenticated users can manage payments" ON public.payments
  FOR ALL TO authenticated USING (
    user_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  ) WITH CHECK (
    user_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'admin')
  );

NOTIFY pgrst, 'reload schema';