-- AeThex Studio Initial Database Schema
-- Run this in Supabase SQL Editor to set up your database

-- Enable RLS (Row Level Security)
alter default privileges revoke execute on functions from public;

-- Create subscription tier enum
create type subscription_tier as enum ('free', 'studio', 'pro', 'enterprise');

-- ============================================
-- PROFILES TABLE
-- ============================================
create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique,
  avatar_url text,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null,
  subscription_tier subscription_tier default 'free' not null,
  translation_count integer default 0 not null
);

-- Enable RLS
alter table public.profiles enable row level security;

-- Policies
create policy "Users can view their own profile"
  on public.profiles for select
  using (auth.uid() = id);

create policy "Users can update their own profile"
  on public.profiles for update
  using (auth.uid() = id);

-- Auto-create profile on signup
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, username, avatar_url)
  values (
    new.id,
    new.raw_user_meta_data ->> 'username',
    new.raw_user_meta_data ->> 'avatar_url'
  );
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- ============================================
-- PROJECTS TABLE
-- ============================================
create table public.projects (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id) on delete cascade not null,
  name text not null,
  description text,
  platforms text[] default '{}' not null,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null,
  is_public boolean default false not null
);

-- Enable RLS
alter table public.projects enable row level security;

-- Policies
create policy "Users can view their own projects"
  on public.projects for select
  using (auth.uid() = user_id);

create policy "Users can view public projects"
  on public.projects for select
  using (is_public = true);

create policy "Users can insert their own projects"
  on public.projects for insert
  with check (auth.uid() = user_id);

create policy "Users can update their own projects"
  on public.projects for update
  using (auth.uid() = user_id);

create policy "Users can delete their own projects"
  on public.projects for delete
  using (auth.uid() = user_id);

-- Indexes
create index projects_user_id_idx on public.projects(user_id);
create index projects_is_public_idx on public.projects(is_public) where is_public = true;

-- ============================================
-- FILES TABLE
-- ============================================
create table public.files (
  id uuid default gen_random_uuid() primary key,
  project_id uuid references public.projects(id) on delete cascade not null,
  name text not null,
  path text not null,
  content text,
  language text not null,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null,
  
  unique(project_id, path)
);

-- Enable RLS
alter table public.files enable row level security;

-- Policies
create policy "Users can view files in their projects"
  on public.files for select
  using (
    exists (
      select 1 from public.projects
      where projects.id = files.project_id
      and projects.user_id = auth.uid()
    )
  );

create policy "Users can view files in public projects"
  on public.files for select
  using (
    exists (
      select 1 from public.projects
      where projects.id = files.project_id
      and projects.is_public = true
    )
  );

create policy "Users can insert files to their projects"
  on public.files for insert
  with check (
    exists (
      select 1 from public.projects
      where projects.id = files.project_id
      and projects.user_id = auth.uid()
    )
  );

create policy "Users can update files in their projects"
  on public.files for update
  using (
    exists (
      select 1 from public.projects
      where projects.id = files.project_id
      and projects.user_id = auth.uid()
    )
  );

create policy "Users can delete files from their projects"
  on public.files for delete
  using (
    exists (
      select 1 from public.projects
      where projects.id = files.project_id
      and projects.user_id = auth.uid()
    )
  );

-- Indexes
create index files_project_id_idx on public.files(project_id);

-- ============================================
-- TRANSLATIONS TABLE
-- ============================================
create table public.translations (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id) on delete cascade not null,
  source_platform text not null,
  target_platform text not null,
  source_code text not null,
  translated_code text not null,
  created_at timestamptz default now() not null
);

-- Enable RLS
alter table public.translations enable row level security;

-- Policies
create policy "Users can view their own translations"
  on public.translations for select
  using (auth.uid() = user_id);

create policy "Users can insert their own translations"
  on public.translations for insert
  with check (auth.uid() = user_id);

-- Indexes
create index translations_user_id_idx on public.translations(user_id);
create index translations_created_at_idx on public.translations(created_at desc);

-- ============================================
-- HELPER FUNCTIONS
-- ============================================

-- Increment translation count for user
create function public.increment_translation_count(user_uuid uuid)
returns void
language plpgsql
security definer
as $$
begin
  update public.profiles
  set translation_count = translation_count + 1,
      updated_at = now()
  where id = user_uuid;
end;
$$;

-- Updated at trigger function
create function public.handle_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- Apply updated_at triggers
create trigger set_profiles_updated_at
  before update on public.profiles
  for each row execute procedure public.handle_updated_at();

create trigger set_projects_updated_at
  before update on public.projects
  for each row execute procedure public.handle_updated_at();

create trigger set_files_updated_at
  before update on public.files
  for each row execute procedure public.handle_updated_at();