schema.sql

create extension if not exists "pgcrypto";

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text,
  created_at timestamptz not null default now()
);

create table if not exists public.properties (
  id text not null,
  user_id uuid not null references public.profiles (id) on delete cascade,
  title text,
  address text,
  price integer,
  area integer,
  room_type text,
  decor text,
  layout text,
  pet text,
  parking text,
  notes text,
  starred boolean not null default false,
  lng double precision,
  lat double precision,
  commute_json jsonb not null default '{}'::jsonb,
  source_text text,
  status text not null default 'new',
  score integer,
  tags_json jsonb not null default '[]'::jsonb,
  client_updated_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (user_id, id)
);

create table if not exists public.destinations (
  id text not null,
  user_id uuid not null references public.profiles (id) on delete cascade,
  name text not null,
  address text not null,
  lng double precision,
  lat double precision,
  mode text not null default 'transit',
  active_mode text not null default 'transit',
  reverse_dir boolean not null default false,
  color text not null default '#0F766E',
  client_updated_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (user_id, id)
);

create index if not exists properties_user_id_idx on public.properties (user_id);
create index if not exists destinations_user_id_idx on public.destinations (user_id);

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

drop trigger if exists properties_set_updated_at on public.properties;
create trigger properties_set_updated_at
before update on public.properties
for each row execute function public.set_updated_at();

drop trigger if exists destinations_set_updated_at on public.destinations;
create trigger destinations_set_updated_at
before update on public.destinations
for each row execute function public.set_updated_at();

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email)
  on conflict (id) do update
  set email = excluded.email;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.handle_new_user();

alter table public.profiles enable row level security;
alter table public.properties enable row level security;
alter table public.destinations enable row level security;

drop policy if exists "profiles_select_own" on public.profiles;
create policy "profiles_select_own"
on public.profiles
for select
to authenticated
using (auth.uid() = id);

drop policy if exists "profiles_insert_own" on public.profiles;
create policy "profiles_insert_own"
on public.profiles
for insert
to authenticated
with check (auth.uid() = id);

drop policy if exists "profiles_update_own" on public.profiles;
create policy "profiles_update_own"
on public.profiles
for update
to authenticated
using (auth.uid() = id)
with check (auth.uid() = id);

drop policy if exists "properties_own_all" on public.properties;
create policy "properties_own_all"
on public.properties
for all
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

drop policy if exists "destinations_own_all" on public.destinations;
create policy "destinations_own_all"
on public.destinations
for all
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

-- Existing projects (Sprint 1): add source_text if missing
alter table public.properties add column if not exists source_text text;

-- Sprint 4–5: decision metadata + client clock
alter table public.properties add column if not exists status text default 'new';
alter table public.properties add column if not exists score integer;
alter table public.properties add column if not exists tags_json jsonb default '[]'::jsonb;
alter table public.properties add column if not exists client_updated_at timestamptz;

alter table public.destinations add column if not exists client_updated_at timestamptz;

-- Sprint 7+: cross-device delete tombstones
create table if not exists public.sync_tombstones (
  user_id uuid not null references public.profiles (id) on delete cascade,
  entity_type text not null check (entity_type in ('property', 'destination')),
  entity_id text not null,
  deleted_at timestamptz not null default now(),
  primary key (user_id, entity_type, entity_id)
);

create index if not exists sync_tombstones_user_id_idx on public.sync_tombstones (user_id);

alter table public.sync_tombstones enable row level security;

drop policy if exists "sync_tombstones_own_all" on public.sync_tombstones;
create policy "sync_tombstones_own_all"
on public.sync_tombstones
for all
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);