leads4less/db/migrations/0001_local_core.sql

create extension if not exists pgcrypto;
create extension if not exists postgis;

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

create table if not exists public.users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  password_hash text not null,
  display_name text,
  avatar_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users (id) on delete cascade,
  token_hash text not null unique,
  expires_at timestamptz not null,
  last_seen_at timestamptz,
  user_agent text,
  ip_address inet,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.postal_areas (
  id uuid primary key default gen_random_uuid(),
  country_code text not null,
  postal_code text not null,
  display_name text,
  normalized_postal_code text not null,
  geom geometry(multipolygon, 4326) not null,
  centroid geography(point, 4326),
  search_radius_m integer,
  metadata_json jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint postal_areas_country_code_postal_code_key unique (country_code, normalized_postal_code)
);

create table if not exists public.deep_research_batches (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users (id) on delete cascade,
  pin_lat double precision not null,
  pin_lng double precision not null,
  pin_geom geography(point, 4326),
  base_postal_code text,
  country_code text,
  propagation integer not null default 0,
  business_type text not null,
  keywords text,
  status text not null check (status in ('pending', 'running', 'completed', 'failed', 'stopped')),
  total_postal_areas integer not null default 0,
  total_results integer not null default 0,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.search_jobs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users (id) on delete cascade,
  deep_research_batch_id uuid references public.deep_research_batches (id) on delete set null,
  postal_area_id uuid references public.postal_areas (id) on delete set null,
  name text not null,
  city text,
  address text,
  postal_code text,
  country_code text,
  radius_km numeric not null,
  business_type text not null,
  keywords text,
  status text not null check (status in ('pending', 'running', 'completed', 'failed', 'stopped')),
  total_results integer not null default 0,
  cancel_requested boolean not null default false,
  requested_lead_limit integer not null default 60,
  search_center_geom geography(point, 4326),
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.businesses (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users (id) on delete cascade,
  external_source_id text,
  source text not null,
  name text not null,
  address text,
  city text,
  state_province text,
  postal_code text,
  country text,
  phone text,
  website text,
  rating numeric,
  review_count integer,
  category text,
  hours_json jsonb,
  latitude double precision,
  longitude double precision,
  geom geography(point, 4326),
  general_info text,
  metadata_json jsonb,
  first_seen_at timestamptz,
  last_seen_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint businesses_user_source_external_source_key unique (user_id, source, external_source_id)
);

create table if not exists public.search_job_results (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users (id) on delete cascade,
  search_job_id uuid not null references public.search_jobs (id) on delete cascade,
  business_id uuid not null references public.businesses (id) on delete cascade,
  matched_keywords text[],
  rank integer,
  captured_at timestamptz not null default now(),
  constraint search_job_results_job_business_key unique (search_job_id, business_id)
);

create table if not exists public.postal_area_neighbors (
  postal_area_id uuid not null references public.postal_areas (id) on delete cascade,
  neighbor_postal_area_id uuid not null references public.postal_areas (id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (postal_area_id, neighbor_postal_area_id),
  check (postal_area_id <> neighbor_postal_area_id)
);

create index if not exists sessions_user_id_idx on public.sessions (user_id);
create index if not exists sessions_expires_at_idx on public.sessions (expires_at);
create index if not exists search_jobs_user_created_at_idx on public.search_jobs (user_id, created_at desc);
create index if not exists search_jobs_batch_idx on public.search_jobs (deep_research_batch_id);
create index if not exists businesses_user_created_at_idx on public.businesses (user_id, created_at desc);
create index if not exists search_job_results_user_job_idx on public.search_job_results (user_id, search_job_id);
create index if not exists deep_research_batches_user_created_at_idx on public.deep_research_batches (user_id, created_at desc);
create index if not exists postal_areas_geom_idx on public.postal_areas using gist (geom);
create index if not exists postal_areas_centroid_idx on public.postal_areas using gist (centroid);
create index if not exists businesses_geom_idx on public.businesses using gist (geom);

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

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

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

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

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

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