-- SaaS starter seed: a multi-tenant SaaS application with RLS, functions, and triggers
-- Demonstrates Supabase best practices for user isolation and server-side logic

-- Users table (auth.users equivalent for data layer)
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  full_name text NOT NULL,
  avatar_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Profiles table (public profile information)
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  username text UNIQUE NOT NULL,
  bio text,
  website text,
  company text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Subscriptions table (billing/plan info)
CREATE TABLE subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  plan text NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'canceled', 'past_due', 'trialing')),
  current_period_start timestamptz NOT NULL DEFAULT now(),
  current_period_end timestamptz NOT NULL DEFAULT now() + interval '30 days',
  cancel_at_period_end boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- Teams table (for multi-tenant features)
CREATE TABLE teams (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  owner_id uuid NOT NULL REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE teams ENABLE ROW LEVEL SECURITY;

-- Team members junction
CREATE TABLE team_members (
  team_id uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  joined_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;

-- RLS policies: users can read/update their own data
CREATE POLICY "Users can read own data" ON users FOR SELECT USING (true);
CREATE POLICY "Users can update own data" ON users FOR UPDATE USING (id = id);

CREATE POLICY "Profiles are publicly readable" ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can update own profile" ON profiles FOR UPDATE USING (id = id);
CREATE POLICY "Users can insert own profile" ON profiles FOR INSERT WITH CHECK (id = id);

CREATE POLICY "Users can read own subscriptions" ON subscriptions FOR SELECT USING (user_id = user_id);

CREATE POLICY "Team members can read team" ON teams FOR SELECT USING (true);
CREATE POLICY "Team owners can update team" ON teams FOR UPDATE USING (owner_id = owner_id);

CREATE POLICY "Members can read team membership" ON team_members FOR SELECT USING (true);

-- Function: handle new user signup (creates profile automatically)
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO profiles (id, username)
  VALUES (NEW.id, split_part(NEW.email, '@', 1));
  RETURN NEW;
END;
$$;

-- Trigger: auto-create profile on user insert
CREATE TRIGGER on_user_created
  AFTER INSERT ON users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user();

-- Function: update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

-- Triggers: auto-update timestamps
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_subscriptions_updated_at
  BEFORE UPDATE ON subscriptions
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_teams_updated_at
  BEFORE UPDATE ON teams
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- Indexes
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_teams_owner_id ON teams(owner_id);
CREATE INDEX idx_team_members_user_id ON team_members(user_id);

-- Seed data
INSERT INTO users (id, email, full_name) VALUES
  ('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 'sample-user-1@example.test', 'Alice Johnson'),
  ('b2c3d4e5-f6a7-8901-bcde-f12345678901', 'sample-user-2@example.test', 'Bob Martinez'),
  ('c3d4e5f6-a7b8-9012-cdef-123456789012', 'sample-user-3@example.test', 'Carol Chen'),
  ('d4e5f6a7-b8c9-0123-defa-234567890123', 'sample-user-4@example.test', 'Dave Wilson'),
  ('e5f6a7b8-c9d0-1234-efab-345678901234', 'sample-user-5@example.test', 'Eve Garcia');

INSERT INTO subscriptions (user_id, plan, status) VALUES
  ('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 'pro', 'active'),
  ('b2c3d4e5-f6a7-8901-bcde-f12345678901', 'pro', 'active'),
  ('c3d4e5f6-a7b8-9012-cdef-123456789012', 'enterprise', 'active'),
  ('d4e5f6a7-b8c9-0123-defa-234567890123', 'free', 'active'),
  ('e5f6a7b8-c9d0-1234-efab-345678901234', 'pro', 'trialing');

INSERT INTO teams (name, slug, owner_id) VALUES
  ('Startup Team', 'startup-team', 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'),
  ('BigCorp Engineering', 'bigcorp-eng', 'c3d4e5f6-a7b8-9012-cdef-123456789012');

INSERT INTO team_members (team_id, user_id, role)
SELECT t.id, u.id, CASE
  WHEN u.id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890' THEN 'owner'
  ELSE 'member'
END
FROM teams t, users u
WHERE t.slug = 'startup-team'
  AND u.email IN ('sample-user-1@example.test', 'sample-user-2@example.test', 'sample-user-5@example.test');

INSERT INTO team_members (team_id, user_id, role)
SELECT t.id, u.id, 'owner'
FROM teams t, users u
WHERE t.slug = 'bigcorp-eng' AND u.email = 'sample-user-3@example.test';

-- Record migrations
INSERT INTO supabase_migrations.schema_migrations (version, name, statements) VALUES
  ('20250101000000_init', 'create_saas_schema', 'CREATE TABLE users ...; CREATE TABLE profiles ...; CREATE TABLE subscriptions ...; CREATE TABLE teams ...; CREATE TABLE team_members ...;'),
  ('20250101000001_rls', 'enable_rls_policies', 'ALTER TABLE ... ENABLE ROW LEVEL SECURITY; CREATE POLICY ...;'),
  ('20250101000002_functions', 'create_functions_triggers', 'CREATE FUNCTION handle_new_user ...; CREATE TRIGGER ...;');
