-- Quick Setup: Copy and paste this entire file into Supabase Studio SQL Editor
-- URL: http://127.0.0.1:54323 → SQL Editor → New Query → Paste → Run

-- Company Profiles Table for Supabase
-- Stores company information collected during user onboarding

CREATE TABLE IF NOT EXISTS public.company_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- User reference (from Prisma PostgreSQL)
  user_email VARCHAR(255) NOT NULL UNIQUE,
  user_id VARCHAR(255), -- Optional CUID from Prisma

  -- Company Information
  company_name VARCHAR(255) NOT NULL,
  industry VARCHAR(100) NOT NULL,
  company_size VARCHAR(50) NOT NULL,
  gcc_country VARCHAR(100) NOT NULL,
  phone_number VARCHAR(50) NOT NULL,

  -- Optional Details
  address TEXT,
  website VARCHAR(255),
  agent_count VARCHAR(50),
  agent_use_case TEXT,
  company_description TEXT,
  services TEXT[], -- Array of service IDs

  -- Subscription Context
  selected_plan VARCHAR(50), -- tier1, tier2, tier3, tier4, tier5
  billing_period VARCHAR(20), -- monthly, yearly
  currency VARCHAR(10), -- GBP, USD, SAR, etc.
  stripe_session_id VARCHAR(255), -- Reference to Stripe checkout session

  -- Status Tracking
  onboarding_complete BOOLEAN DEFAULT false,
  verified BOOLEAN DEFAULT false,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_company_profiles_user_email ON public.company_profiles(user_email);
CREATE INDEX IF NOT EXISTS idx_company_profiles_industry ON public.company_profiles(industry);
CREATE INDEX IF NOT EXISTS idx_company_profiles_company_size ON public.company_profiles(company_size);
CREATE INDEX IF NOT EXISTS idx_company_profiles_created_at ON public.company_profiles(created_at);

-- Enable Row Level Security (RLS)
ALTER TABLE public.company_profiles ENABLE ROW LEVEL SECURITY;

-- RLS Policies
-- Allow authenticated users to read their own company profile
DROP POLICY IF EXISTS "Users can read own company profile" ON public.company_profiles;
CREATE POLICY "Users can read own company profile"
  ON public.company_profiles
  FOR SELECT
  USING (true); -- Public read for admin dashboard (can be restricted later)

-- Allow insert for new company profiles (during signup)
DROP POLICY IF EXISTS "Allow insert for new companies" ON public.company_profiles;
CREATE POLICY "Allow insert for new companies"
  ON public.company_profiles
  FOR INSERT
  WITH CHECK (true); -- Anyone can insert (validation done in API)

-- Allow users to update their own company profile
DROP POLICY IF EXISTS "Users can update own company profile" ON public.company_profiles;
CREATE POLICY "Users can update own company profile"
  ON public.company_profiles
  FOR UPDATE
  USING (true); -- Can be restricted based on auth later

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_company_profiles_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS company_profiles_updated_at ON public.company_profiles;
CREATE TRIGGER company_profiles_updated_at
  BEFORE UPDATE ON public.company_profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_company_profiles_updated_at();

-- Comments for documentation
COMMENT ON TABLE public.company_profiles IS 'Company information collected during user onboarding process';
COMMENT ON COLUMN public.company_profiles.user_email IS 'Email address linking to Prisma user account';
COMMENT ON COLUMN public.company_profiles.services IS 'Array of selected Mawidi services (AI agents, SMS, etc.)';
COMMENT ON COLUMN public.company_profiles.stripe_session_id IS 'Reference to Stripe checkout session if payment was processed';