`scripts/db-init.sql`

42 lines
-- Prestige Car Wash — Postgres schema (optional backend; app runs on data/*.json otherwise).
-- Every table carries created_at (feeds the admin date+time chip) + source provenance.

CREATE TABLE IF NOT EXISTS pcw_services (
  id text PRIMARY KEY, name text NOT NULL, category text, price numeric, price_display text,
  duration_min int, blurb text, highlights jsonb DEFAULT '[]', media_still text, media_clip text,
  ig_photo text, featured boolean DEFAULT false, sort_order int DEFAULT 99,
  source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_competitors (
  id text PRIMARY KEY, name text NOT NULL, city text, address text, format text,
  rating numeric, reviews int, price_signal text, notes text, ad_platforms jsonb DEFAULT '[]',
  website text, source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_suggestions (
  id text PRIMARY KEY, title text NOT NULL, category text, impact int, effort int, rationale text,
  source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_holidays (
  id text PRIMARY KEY, name text NOT NULL, date date, campaign text, channels jsonb DEFAULT '[]',
  lead_days int, source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_directories (
  id text PRIMARY KEY, name text NOT NULL, type text, priority text, status text, url text, notes text,
  source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_ad_campaigns (
  id text PRIMARY KEY, platform text, type text, status text, budget_month numeric, targeting text,
  keywords jsonb DEFAULT '[]', headline text, body text, source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_best_times (
  id serial PRIMARY KEY, payload jsonb NOT NULL, source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_media (
  id serial PRIMARY KEY, service_id text REFERENCES pcw_services(id), kind text, path text,
  model text, cost_usd numeric, source text, created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pcw_leads (
  id serial PRIMARY KEY, name text, phone text, email text, vehicle text, service text,
  preferred text, message text, source text, created_at timestamptz DEFAULT now()
);