Database schema

users ──┬── energy_markers ──┬── marker_ratings
        │                    ├── marker_photos
        │                    ├── comments
        │                    └── visits
        ├── followers (self-join)
        ├── saved_locations ──── (M:M to markers via saved_list_items)
        ├── journal_entries
        └── notifications

-- Enable extensions
create extension if not exists postgis;
create extension if not exists pg_trgm;

-- USERS
create table public.users (
  id uuid primary key default gen_random_uuid(),
  auth_user_id uuid unique not null references auth.users(id) on delete cascade,
  username text unique not null check (length(username) between 3 and 30),
  display_name text not null,
  avatar_url text,
  bio text check (length(bio) <= 280),
  energy_preferences text[] default '{}',
  xp int not null default 0,
  level int not null default 1,
  is_premium boolean not null default false,
  created_at timestamptz not null default now()
);

-- ENERGY_MARKERS
create type energy_category as enum (
  'healing','love','creativity','grounding','spiritual',
  'adventure','abundance','focus','joy','transformation'
);

create table public.energy_markers (
  id uuid primary key default gen_random_uuid(),
  creator_id uuid not null references public.users(id) on delete cascade,
  title text not null check (length(title) between 1 and 80),
  description text check (length(description) <= 500),
  category energy_category not null,
  intensity smallint not null check (intensity between 1 and 10),
  mood text not null,
  location geography(Point, 4326) not null,
  -- Denormalized aggregates (kept in sync via triggers / cron)
  energy_score numeric(4,2) not null default 0,
  ratings_count int not null default 0,
  visits_count int not null default 0,
  photos_count int not null default 0,
  created_at timestamptz not null default now()
);

create index energy_markers_location_idx on public.energy_markers using gist(location);
create index energy_markers_category_idx on public.energy_markers(category);
create index energy_markers_score_idx on public.energy_markers(energy_score desc);
create index energy_markers_title_trgm on public.energy_markers using gin (title gin_trgm_ops);

-- MARKER_RATINGS (6 sub-scores)
create table public.marker_ratings (
  id uuid primary key default gen_random_uuid(),
  marker_id uuid not null references public.energy_markers(id) on delete cascade,
  user_id uuid not null references public.users(id) on delete cascade,
  calmness smallint not null check (calmness between 1 and 10),
  inspiration smallint not null check (inspiration between 1 and 10),
  creativity smallint not null check (creativity between 1 and 10),
  emotional_impact smallint not null check (emotional_impact between 1 and 10),
  focus smallint not null check (focus between 1 and 10),
  overall smallint not null check (overall between 1 and 10),
  created_at timestamptz not null default now(),
  unique (marker_id, user_id)
);

-- MARKER_PHOTOS
create table public.marker_photos (
  id uuid primary key default gen_random_uuid(),
  marker_id uuid not null references public.energy_markers(id) on delete cascade,
  uploader_id uuid not null references public.users(id) on delete set null,
  storage_path text not null,
  width int, height int,
  created_at timestamptz not null default now()
);

-- COMMENTS
create table public.comments (
  id uuid primary key default gen_random_uuid(),
  marker_id uuid not null references public.energy_markers(id) on delete cascade,
  user_id uuid not null references public.users(id) on delete cascade,
  body text not null check (length(body) between 1 and 500),
  created_at timestamptz not null default now()
);

-- LIKES
create table public.marker_likes (
  marker_id uuid references public.energy_markers(id) on delete cascade,
  user_id uuid references public.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (marker_id, user_id)
);

-- FOLLOWERS (self-join)
create table public.followers (
  follower_id uuid references public.users(id) on delete cascade,
  followee_id uuid references public.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (follower_id, followee_id),
  check (follower_id <> followee_id)
);

-- SAVED LISTS + ITEMS
create table public.saved_lists (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users(id) on delete cascade,
  name text not null,
  is_default boolean not null default false,
  created_at timestamptz not null default now()
);

create table public.saved_list_items (
  list_id uuid references public.saved_lists(id) on delete cascade,
  marker_id uuid references public.energy_markers(id) on delete cascade,
  saved_at timestamptz not null default now(),
  primary key (list_id, marker_id)
);

-- VISITS (GPS-verified check-ins)
create table public.visits (
  id uuid primary key default gen_random_uuid(),
  marker_id uuid not null references public.energy_markers(id) on delete cascade,
  user_id uuid not null references public.users(id) on delete cascade,
  visited_at timestamptz not null default now(),
  gps_accuracy_m numeric,
  unique (marker_id, user_id, visited_at)
);

-- JOURNAL_ENTRIES (private by default)
create table public.journal_entries (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users(id) on delete cascade,
  marker_id uuid references public.energy_markers(id) on delete set null,
  title text,
  body text,
  mood text,
  photo_paths text[] default '{}',
  is_private boolean not null default true,
  entry_at timestamptz not null default now()
);

-- NOTIFICATIONS
create type notification_kind as enum ('follow','visit','like','comment','badge','system');

create table public.notifications (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users(id) on delete cascade,
  kind notification_kind not null,
  payload jsonb not null default '{}',
  is_read boolean not null default false,
  created_at timestamptz not null default now()
);
create index notifications_user_unread_idx
  on public.notifications(user_id, is_read, created_at desc);

-- BADGES
create table public.badges (
  id text primary key,         -- e.g. 'first_marker', 'globe_trotter'
  name text not null,
  description text not null,
  icon_url text
);
create table public.user_badges (
  user_id uuid references public.users(id) on delete cascade,
  badge_id text references public.badges(id),
  earned_at timestamptz not null default now(),
  primary key (user_id, badge_id)
);

-- ============================================================
-- GRANTS (required for PostgREST access)
-- ============================================================
grant select, insert, update, delete on public.users to authenticated;
grant select on public.users to anon;
grant all on public.users to service_role;

grant select, insert, update, delete on public.energy_markers to authenticated;
grant select on public.energy_markers to anon;
grant all on public.energy_markers to service_role;

grant select, insert, update, delete on
  public.marker_ratings, public.marker_photos, public.comments,
  public.marker_likes, public.followers, public.saved_lists,
  public.saved_list_items, public.visits, public.journal_entries,
  public.notifications, public.user_badges
  to authenticated;
grant select on
  public.marker_ratings, public.marker_photos, public.comments,
  public.marker_likes, public.followers, public.badges
  to anon;
grant all on
  public.marker_ratings, public.marker_photos, public.comments,
  public.marker_likes, public.followers, public.saved_lists,
  public.saved_list_items, public.visits, public.journal_entries,
  public.notifications, public.badges, public.user_badges
  to service_role;

-- ============================================================
-- ROW LEVEL SECURITY
-- ============================================================
alter table public.users enable row level security;
create policy "users readable to all"
  on public.users for select using (true);
create policy "users update self"
  on public.users for update using (auth.uid() = auth_user_id);

alter table public.energy_markers enable row level security;
create policy "markers readable to all"
  on public.energy_markers for select using (true);
create policy "markers insert by owner"
  on public.energy_markers for insert
  with check (creator_id in (select id from public.users where auth_user_id = auth.uid()));
create policy "markers update by owner"
  on public.energy_markers for update
  using (creator_id in (select id from public.users where auth_user_id = auth.uid()));

alter table public.journal_entries enable row level security;
create policy "journal owner-only"
  on public.journal_entries for all
  using (user_id in (select id from public.users where auth_user_id = auth.uid()));

energy_score = round(
    0.25 * avg(overall) +
    0.15 * avg(calmness) +
    0.15 * avg(inspiration) +
    0.15 * avg(creativity) +
    0.15 * avg(emotional_impact) +
    0.15 * avg(focus)
  , 2)

• Recomputed via pg_cron every 15 minutes per marker with new ratings.
• Cold-start: markers with < 3 ratings flagged as 'New' instead of scored.
• Weights tunable per cohort once we have data — stored in app_config table.

• Unique constraint on (marker_id, user_id) in ratings — one rating per user per place.
• Edge function validates GPS within 100m before insert on energy_markers and visits.
• Velocity check: max 10 marker creations / 24h per user.
• Photo moderation via on-device NSFW classifier + server fallback (AWS Rekognition Phase 2).