Fitaru Supabase Initial Migration

File ini adalah SQL migration awal untuk schema database Fitaru.

-- Fitaru MVP initial database schema
-- Target: Supabase PostgreSQL

create extension if not exists "pgcrypto";

-- Enums
do $$ begin
  create type account_status as enum ('active', 'suspended', 'deleted');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type admin_status as enum ('active', 'inactive');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type admin_role as enum ('super_admin', 'content_admin', 'support_admin');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type auth_provider as enum ('google', 'apple', 'email', 'phone');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type gender_type as enum ('male', 'female', 'other');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type user_goal as enum ('lose_weight', 'maintain_weight', 'gain_muscle', 'healthier');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type diet_style as enum ('relaxed', 'calorie_deficit', 'high_protein', 'low_sugar', 'low_fried', 'fasting', 'custom');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type activity_level as enum ('sedentary', 'light', 'active', 'very_active');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type meal_time as enum ('breakfast', 'lunch', 'dinner', 'snack', 'drink');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type portion_size as enum ('small', 'medium', 'large');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type intensity_level as enum ('light', 'medium', 'heavy');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type record_status as enum ('active', 'inactive');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type article_status as enum ('draft', 'review', 'published', 'archived');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type notification_status as enum ('draft', 'scheduled', 'sent', 'cancelled');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type notification_segment as enum ('all', 'active', 'inactive_7_days', 'lose_weight', 'no_meal_today');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type delivery_status as enum ('pending', 'sent', 'failed', 'opened');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type feedback_type as enum ('bug', 'suggestion', 'content_request', 'other');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type feedback_status as enum ('open', 'reviewed', 'resolved', 'archived');
exception when duplicate_object then null;
end $$;

do $$ begin
  create type audit_action as enum ('create', 'update', 'delete', 'publish', 'suspend');
exception when duplicate_object then null;
end $$;

-- Utility
create or replace function set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- Users
create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  email text unique,
  phone text unique,
  auth_provider auth_provider not null default 'email',
  status account_status not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint users_contact_required check (email is not null or phone is not null)
);

create trigger users_set_updated_at
before update on users
for each row execute function set_updated_at();

create table if not exists user_profiles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references users(id) on delete cascade,
  display_name text not null,
  gender gender_type,
  birth_year int,
  height_cm numeric(5, 2),
  current_weight_kg numeric(5, 2),
  target_weight_kg numeric(5, 2),
  goal user_goal not null default 'healthier',
  diet_style diet_style not null default 'relaxed',
  activity_level activity_level not null default 'light',
  timezone text not null default 'Asia/Jakarta',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint user_profiles_birth_year_check check (birth_year is null or birth_year between 1900 and 2100),
  constraint user_profiles_height_check check (height_cm is null or height_cm > 0),
  constraint user_profiles_weight_check check (current_weight_kg is null or current_weight_kg > 0),
  constraint user_profiles_target_weight_check check (target_weight_kg is null or target_weight_kg > 0)
);

create trigger user_profiles_set_updated_at
before update on user_profiles
for each row execute function set_updated_at();

create table if not exists daily_targets (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references users(id) on delete cascade,
  water_glasses_target int not null default 8,
  exercise_weekly_target int not null default 3,
  calorie_target int,
  weigh_in_days text[] not null default array['monday', 'thursday'],
  is_calorie_tracking_enabled boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint daily_targets_water_check check (water_glasses_target between 1 and 30),
  constraint daily_targets_exercise_check check (exercise_weekly_target between 0 and 14),
  constraint daily_targets_calorie_check check (calorie_target is null or calorie_target > 0)
);

create trigger daily_targets_set_updated_at
before update on daily_targets
for each row execute function set_updated_at();

-- Admin CMS
create table if not exists admin_users (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null unique,
  role admin_role not null default 'support_admin',
  status admin_status not null default 'active',
  last_login_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger admin_users_set_updated_at
before update on admin_users
for each row execute function set_updated_at();

-- Reference data
create table if not exists food_items (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  category text not null default 'other',
  default_portion portion_size not null default 'medium',
  calories_per_portion int,
  protein_g numeric(6, 2),
  carbs_g numeric(6, 2),
  fat_g numeric(6, 2),
  notes text,
  status record_status not null default 'active',
  created_by uuid references admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint food_items_calories_check check (calories_per_portion is null or calories_per_portion >= 0),
  constraint food_items_macro_check check (
    (protein_g is null or protein_g >= 0)
    and (carbs_g is null or carbs_g >= 0)
    and (fat_g is null or fat_g >= 0)
  )
);

create trigger food_items_set_updated_at
before update on food_items
for each row execute function set_updated_at();

create table if not exists exercise_items (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  category text not null default 'other',
  default_intensity intensity_level not null default 'medium',
  default_duration_minutes int not null default 30,
  calories_per_30_minutes int,
  notes text,
  status record_status not null default 'active',
  created_by uuid references admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint exercise_items_duration_check check (default_duration_minutes > 0),
  constraint exercise_items_calories_check check (calories_per_30_minutes is null or calories_per_30_minutes >= 0)
);

create trigger exercise_items_set_updated_at
before update on exercise_items
for each row execute function set_updated_at();

-- Activity logs
create table if not exists meal_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  food_item_id uuid references food_items(id) on delete set null,
  meal_time meal_time not null,
  food_name text not null,
  portion_size portion_size not null default 'medium',
  estimated_calories int,
  photo_url text,
  note text,
  logged_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint meal_logs_calories_check check (estimated_calories is null or estimated_calories >= 0)
);

create trigger meal_logs_set_updated_at
before update on meal_logs
for each row execute function set_updated_at();

create table if not exists exercise_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  exercise_item_id uuid references exercise_items(id) on delete set null,
  exercise_name text not null,
  duration_minutes int not null,
  intensity intensity_level not null default 'medium',
  estimated_calories_burned int,
  note text,
  logged_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint exercise_logs_duration_check check (duration_minutes > 0),
  constraint exercise_logs_calories_check check (estimated_calories_burned is null or estimated_calories_burned >= 0)
);

create trigger exercise_logs_set_updated_at
before update on exercise_logs
for each row execute function set_updated_at();

create table if not exists weight_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  weight_kg numeric(5, 2) not null,
  progress_photo_url text,
  note text,
  logged_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  constraint weight_logs_weight_check check (weight_kg > 0)
);

create table if not exists water_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  glasses int not null default 1,
  logged_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  constraint water_logs_glasses_check check (glasses > 0)
);

-- Content CMS
create table if not exists content_categories (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  description text,
  status record_status not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger content_categories_set_updated_at
before update on content_categories
for each row execute function set_updated_at();

create table if not exists articles (
  id uuid primary key default gen_random_uuid(),
  category_id uuid references content_categories(id) on delete set null,
  title text not null,
  slug text not null unique,
  summary text not null,
  content text not null,
  thumbnail_url text,
  status article_status not null default 'draft',
  author_id uuid references admin_users(id) on delete set null,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger articles_set_updated_at
before update on articles
for each row execute function set_updated_at();

-- Notifications
create table if not exists notifications (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  message text not null,
  target_segment notification_segment not null default 'all',
  scheduled_at timestamptz,
  sent_at timestamptz,
  status notification_status not null default 'draft',
  created_by uuid references admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger notifications_set_updated_at
before update on notifications
for each row execute function set_updated_at();

create table if not exists notification_deliveries (
  id uuid primary key default gen_random_uuid(),
  notification_id uuid not null references notifications(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  delivery_status delivery_status not null default 'pending',
  sent_at timestamptz,
  opened_at timestamptz,
  created_at timestamptz not null default now(),
  unique (notification_id, user_id)
);

-- Feedback
create table if not exists feedback (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references users(id) on delete set null,
  type feedback_type not null default 'other',
  subject text not null,
  message text not null,
  status feedback_status not null default 'open',
  admin_note text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger feedback_set_updated_at
before update on feedback
for each row execute function set_updated_at();

-- Audit logs
create table if not exists audit_logs (
  id uuid primary key default gen_random_uuid(),
  admin_user_id uuid references admin_users(id) on delete set null,
  action audit_action not null,
  resource_type text not null,
  resource_id uuid,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

-- Indexes
create index if not exists idx_users_status_created_at on users(status, created_at);
create index if not exists idx_user_profiles_goal on user_profiles(goal);

create index if not exists idx_meal_logs_user_logged_at on meal_logs(user_id, logged_at desc);
create index if not exists idx_meal_logs_food_name on meal_logs(food_name);
create index if not exists idx_exercise_logs_user_logged_at on exercise_logs(user_id, logged_at desc);
create index if not exists idx_weight_logs_user_logged_at on weight_logs(user_id, logged_at desc);
create index if not exists idx_water_logs_user_logged_at on water_logs(user_id, logged_at desc);

create index if not exists idx_food_items_name on food_items(name);
create index if not exists idx_food_items_status_category on food_items(status, category);
create index if not exists idx_exercise_items_name on exercise_items(name);
create index if not exists idx_exercise_items_status_category on exercise_items(status, category);

create index if not exists idx_content_categories_status on content_categories(status);
create index if not exists idx_articles_status_published_at on articles(status, published_at desc);
create index if not exists idx_articles_slug on articles(slug);

create index if not exists idx_notifications_status_scheduled_at on notifications(status, scheduled_at);
create index if not exists idx_notification_deliveries_user_status on notification_deliveries(user_id, delivery_status);

create index if not exists idx_feedback_status_created_at on feedback(status, created_at desc);
create index if not exists idx_audit_logs_admin_created_at on audit_logs(admin_user_id, created_at desc);
create index if not exists idx_audit_logs_resource on audit_logs(resource_type, resource_id);

-- Seed content categories
insert into content_categories (name, slug, description)
values
  ('Makan santai', 'makan-santai', 'Tips makan lebih sadar tanpa diet ekstrem.'),
  ('Olahraga pemula', 'olahraga-pemula', 'Panduan gerak ringan untuk pemula.'),
  ('Kurangi gula', 'kurangi-gula', 'Tips mengurangi gula secara realistis.'),
  ('Meal prep', 'meal-prep', 'Ide persiapan makanan sederhana.'),
  ('Tidur dan recovery', 'tidur-recovery', 'Tips tidur, recovery, dan ritme tubuh.')
on conflict (slug) do nothing;