supabase_schema.sql

-- ============================================================
-- ACCOUNTABILITY TRACKER - FULL DATABASE SCHEMA
-- Run this in Supabase SQL Editor (all at once)
-- ============================================================

-- 1. USERS TABLE (app-level profiles, not Supabase Auth)
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  display_name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('owner', 'user', 'viewer')),
  avatar TEXT DEFAULT '👤',
  bio TEXT DEFAULT '',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. PLANS TABLE (each user can have their own plan)
CREATE TABLE IF NOT EXISTS plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  subtitle TEXT DEFAULT '',
  total_days INT NOT NULL DEFAULT 90,
  start_date DATE NOT NULL DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT TRUE,
  template TEXT DEFAULT NULL, -- 'gulf-data-90', 'uni-study', 'custom', null
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. PHASES TABLE
CREATE TABLE IF NOT EXISTS phases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  phase_number INT NOT NULL,
  name TEXT NOT NULL,
  color TEXT DEFAULT '#3b82f6',
  goal TEXT DEFAULT '',
  day_start INT NOT NULL,
  day_end INT NOT NULL,
  sort_order INT DEFAULT 0
);

-- 4. PLAN_DAYS TABLE (each day's content - fully customizable)
CREATE TABLE IF NOT EXISTS plan_days (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  day_number INT NOT NULL,
  phase_id UUID REFERENCES phases(id) ON DELETE SET NULL,
  focus TEXT NOT NULL DEFAULT '',
  hours NUMERIC(4,1) DEFAULT 2.5,
  no_ai_practice TEXT DEFAULT '',
  UNIQUE(plan_id, day_number)
);

-- 5. TASKS TABLE (tasks within a day)
CREATE TABLE IF NOT EXISTS tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  day_id UUID REFERENCES plan_days(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  sort_order INT DEFAULT 0,
  is_completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMPTZ
);

-- 6. DELIVERABLES TABLE
CREATE TABLE IF NOT EXISTS deliverables (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  day_id UUID REFERENCES plan_days(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  sort_order INT DEFAULT 0
);

-- 7. DAY_RESOURCES TABLE (links for each day)
CREATE TABLE IF NOT EXISTS day_resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  day_id UUID REFERENCES plan_days(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  url TEXT,
  sort_order INT DEFAULT 0
);

-- 8. USER_PROGRESS TABLE (day completion, notes)
CREATE TABLE IF NOT EXISTS user_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  day_number INT NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  notes TEXT DEFAULT '',
  completed_at TIMESTAMPTZ,
  UNIQUE(user_id, plan_id, day_number)
);

-- 9. METRICS TABLE (tracked numbers)
CREATE TABLE IF NOT EXISTS metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  metric_key TEXT NOT NULL,
  metric_value NUMERIC DEFAULT 0,
  target_value NUMERIC DEFAULT 0,
  label TEXT DEFAULT '',
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, plan_id, metric_key)
);

-- 10. CHECKINS TABLE
CREATE TABLE IF NOT EXISTS checkins (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES users(id) ON DELETE CASCADE,
  target_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  checkin_type TEXT NOT NULL CHECK (checkin_type IN ('partner', 'self')),
  day_number INT,
  message TEXT DEFAULT '',
  rating INT CHECK (rating >= 1 AND rating <= 5),
  productivity INT CHECK (productivity >= 1 AND productivity <= 10),
  no_ai_today BOOLEAN,
  win TEXT DEFAULT '',
  struggle TEXT DEFAULT '',
  priority TEXT DEFAULT '',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 11. CERTIFICATIONS TABLE
CREATE TABLE IF NOT EXISTS certifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  target_day INT,
  cost TEXT DEFAULT '',
  earned BOOLEAN DEFAULT FALSE,
  earned_at TIMESTAMPTZ
);

-- 12. PROJECTS TABLE
CREATE TABLE IF NOT EXISTS projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  phase_number INT,
  start_day INT,
  end_day INT,
  tech TEXT[] DEFAULT '{}',
  completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMPTZ
);

-- 13. MILESTONES TABLE
CREATE TABLE IF NOT EXISTS milestones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID REFERENCES plans(id) ON DELETE CASCADE,
  day_number INT NOT NULL,
  title TEXT NOT NULL,
  icon TEXT DEFAULT 'star'
);

-- ============ ROW LEVEL SECURITY ============
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE phases ENABLE ROW LEVEL SECURITY;
ALTER TABLE plan_days ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE deliverables ENABLE ROW LEVEL SECURITY;
ALTER TABLE day_resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE checkins ENABLE ROW LEVEL SECURITY;
ALTER TABLE certifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE milestones ENABLE ROW LEVEL SECURITY;

-- Allow anon full access (app handles auth via username/password in users table)
CREATE POLICY "anon_all" ON users FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON plans FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON phases FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON plan_days FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON tasks FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON deliverables FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON day_resources FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON user_progress FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON metrics FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON checkins FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON certifications FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON projects FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "anon_all" ON milestones FOR ALL USING (true) WITH CHECK (true);

-- ============ ENABLE REALTIME ============
ALTER PUBLICATION supabase_realtime ADD TABLE user_progress;
ALTER PUBLICATION supabase_realtime ADD TABLE checkins;
ALTER PUBLICATION supabase_realtime ADD TABLE tasks;
ALTER PUBLICATION supabase_realtime ADD TABLE metrics;

-- ============ SIMPLE PASSWORD HASH FUNCTION ============
-- Using pgcrypto for basic password hashing
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Helper function to hash passwords
CREATE OR REPLACE FUNCTION hash_password(pwd TEXT)
RETURNS TEXT AS $$
  SELECT crypt(pwd, gen_salt('bf'));
$$ LANGUAGE SQL;

-- Helper function to verify passwords
CREATE OR REPLACE FUNCTION verify_password(pwd TEXT, hashed TEXT)
RETURNS BOOLEAN AS $$
  SELECT hashed = crypt(pwd, hashed);
$$ LANGUAGE SQL;