-- Initial schema for the Next Estimate Agency platform (Wave 1).
--
-- Creates the six core tables per the roadmap:
--   customers, users, estimates, service_areas, product_images, update_requests
--
-- RLS is enabled on every table but no policies are defined yet — policies
-- land in the next migration alongside the dual-write estimator code, so
-- this schema-only migration is reversible if something looks wrong.
--
-- Apply by pasting the contents of this file into the Supabase SQL Editor
-- (Project → SQL Editor → New query → paste → Run). Once Supabase CLI is
-- installed in a later wave, migrations will run automatically via
-- `supabase db push`.

-- ============================================================
-- Extensions
-- ============================================================

-- PostGIS for service_areas.polygon (geography) and point-in-polygon
-- checks at estimator submission time.
create extension if not exists "postgis";

-- pgcrypto provides gen_random_uuid() for UUID primary keys.
-- Supabase usually has this pre-installed; create-if-not-exists is safe.
create extension if not exists "pgcrypto";

-- ============================================================
-- customers: one row per business using the estimator.
-- ============================================================
create table public.customers (
  id                  uuid primary key default gen_random_uuid(),
  slug                text unique not null,
  business_name       text not null,
  owner_user_id       uuid references auth.users(id) on delete set null,
  status              text not null default 'trial'
                        check (status in ('trial','active','paused','churned')),
  plan                text not null default 'pro'
                        check (plan in ('freemium','pro')),
  stripe_customer_id  text,
  ghl_webhook_url     text,
  branding_json       jsonb not null default '{}'::jsonb,
  pricing_json        jsonb not null default '{}'::jsonb,
  created_at          timestamptz not null default now()
);

comment on column public.customers.slug is
  'URL-safe identifier. Immutable once set (enforced in application code). '
  'Used in /e/<slug> routes and baked into ad campaigns — never rename.';

-- ============================================================
-- users: joins auth.users to a customer with a role.
-- auth.users is Supabase's built-in auth table; we reference it by id.
-- ============================================================
create table public.users (
  id           uuid primary key references auth.users(id) on delete cascade,
  customer_id  uuid not null references public.customers(id) on delete cascade,
  role         text not null default 'owner'
                 check (role in ('owner','staff','admin')),
  email        text not null,
  created_at   timestamptz not null default now()
);

create index users_customer_id_idx on public.users(customer_id);

-- ============================================================
-- estimates: homeowner lead submissions from the estimator page.
-- Line-item breakdown lives in addons_json to avoid premature normalization.
-- ============================================================
create table public.estimates (
  id              uuid primary key default gen_random_uuid(),
  customer_id     uuid not null references public.customers(id) on delete cascade,
  homeowner_name  text,
  email           text,
  phone           text,
  address         text,
  lat             double precision,
  lng             double precision,
  in_service_area boolean,
  fence_type      text,
  linear_feet     numeric,
  gates_json      jsonb not null default '[]'::jsonb,
  addons_json     jsonb not null default '{}'::jsonb,
  subtotal        numeric,
  total           numeric,
  source          text not null default 'direct'
                    check (source in ('direct','embed','freemium')),
  referrer_url    text,
  created_at      timestamptz not null default now()
);

-- Dashboard queries sort by customer + recency, so this covers both filters.
create index estimates_customer_id_created_at_idx
  on public.estimates(customer_id, created_at desc);

-- ============================================================
-- service_areas: one or more polygons per customer. is_primary flags the
-- default polygon used for in/out-of-area checks.
-- ============================================================
create table public.service_areas (
  id          uuid primary key default gen_random_uuid(),
  customer_id uuid not null references public.customers(id) on delete cascade,
  polygon     geography(polygon, 4326) not null,
  name        text,
  is_primary  boolean not null default false,
  created_at  timestamptz not null default now()
);

create index service_areas_customer_id_idx on public.service_areas(customer_id);
-- GiST index on the polygon column makes point-in-polygon lookups fast.
create index service_areas_polygon_gix on public.service_areas using gist (polygon);

-- ============================================================
-- product_images: per-customer photos attached to a fence product.
-- storage_path references a file in Supabase Storage (bucket configured
-- separately in the dashboard).
-- ============================================================
create table public.product_images (
  id           uuid primary key default gen_random_uuid(),
  customer_id  uuid not null references public.customers(id) on delete cascade,
  product_key  text not null,
  storage_path text not null,
  alt_text     text,
  sort_order   integer not null default 0,
  created_at   timestamptz not null default now()
);

create index product_images_customer_id_product_key_idx
  on public.product_images(customer_id, product_key, sort_order);

-- ============================================================
-- update_requests: customer-submitted change requests via the self-service
-- form. Jake processes the queue manually in the admin dashboard (Wave 4).
-- ============================================================
create table public.update_requests (
  id                    uuid primary key default gen_random_uuid(),
  customer_id           uuid not null references public.customers(id) on delete cascade,
  submitted_by_user_id  uuid references public.users(id) on delete set null,
  payload_json          jsonb not null,
  status                text not null default 'open'
                          check (status in ('open','in_progress','done')),
  created_at            timestamptz not null default now(),
  resolved_at           timestamptz
);

create index update_requests_status_idx on public.update_requests(status);

-- ============================================================
-- Row-Level Security: enable on every table. Policies follow in a later
-- migration. Until policies exist, only the service_role key (server-side)
-- can read or write these tables — anon/authenticated keys will be denied.
-- That's intentional; it matches the "dual-write is non-breaking" gate: if
-- anything goes wrong with the Supabase write, we want it to fail closed
-- without breaking the existing GHL webhook fire.
-- ============================================================
alter table public.customers       enable row level security;
alter table public.users           enable row level security;
alter table public.estimates       enable row level security;
alter table public.service_areas   enable row level security;
alter table public.product_images  enable row level security;
alter table public.update_requests enable row level security;
