Nova40/supabase/migrations/001_create_tables.sql

-- Nova40 Database Schema
-- Run this in your Supabase SQL editor

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- 1. Identities table
create table identities (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null,
  description text,
  start_date date not null,
  end_date date not null,
  created_at timestamptz default now()
);

alter table identities enable row level security;

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

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

-- 2. Habits table
create table habits (
  id uuid default uuid_generate_v4() primary key,
  identity_id uuid references identities(id) on delete cascade not null,
  title text not null,
  description text
);

alter table habits enable row level security;

create policy "Users can view habits for their identities"
  on habits for select using (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can create habits for their identities"
  on habits for insert with check (
    identity_id in (select id from identities where user_id = auth.uid())
  );

-- 3. Daily logs table
create table daily_logs (
  id uuid default uuid_generate_v4() primary key,
  identity_id uuid references identities(id) on delete cascade not null,
  date date not null,
  day_number int not null,
  identity_check text check (identity_check in ('yes', 'almost', 'no')),
  note text,
  unique (identity_id, date)
);

alter table daily_logs enable row level security;

create policy "Users can view their daily logs"
  on daily_logs for select using (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can insert daily logs"
  on daily_logs for insert with check (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can update their daily logs"
  on daily_logs for update using (
    identity_id in (select id from identities where user_id = auth.uid())
  );

-- 4. Habit logs table
create table habit_logs (
  id uuid default uuid_generate_v4() primary key,
  habit_id uuid references habits(id) on delete cascade not null,
  date date not null,
  completed boolean default false,
  unique (habit_id, date)
);

alter table habit_logs enable row level security;

create policy "Users can view their habit logs"
  on habit_logs for select using (
    habit_id in (
      select h.id from habits h
      join identities i on h.identity_id = i.id
      where i.user_id = auth.uid()
    )
  );

create policy "Users can insert habit logs"
  on habit_logs for insert with check (
    habit_id in (
      select h.id from habits h
      join identities i on h.identity_id = i.id
      where i.user_id = auth.uid()
    )
  );

create policy "Users can update their habit logs"
  on habit_logs for update using (
    habit_id in (
      select h.id from habits h
      join identities i on h.identity_id = i.id
      where i.user_id = auth.uid()
    )
  );

-- 5. Game sessions table
create table game_sessions (
  id uuid default uuid_generate_v4() primary key,
  identity_id uuid references identities(id) on delete cascade not null,
  game_type text not null,
  score int default 0,
  created_at timestamptz default now()
);

alter table game_sessions enable row level security;

create policy "Users can view their game sessions"
  on game_sessions for select using (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can insert game sessions"
  on game_sessions for insert with check (
    identity_id in (select id from identities where user_id = auth.uid())
  );

-- 6. Stats table
create table stats (
  id uuid default uuid_generate_v4() primary key,
  identity_id uuid references identities(id) on delete cascade not null unique,
  discipline_score int default 0,
  focus_score int default 0,
  consistency_score int default 0
);

alter table stats enable row level security;

create policy "Users can view their stats"
  on stats for select using (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can insert their stats"
  on stats for insert with check (
    identity_id in (select id from identities where user_id = auth.uid())
  );

create policy "Users can update their stats"
  on stats for update using (
    identity_id in (select id from identities where user_id = auth.uid())
  );