`db/schema.sql`

299 lines
-- NationalPaperHangers.com schema
-- Standalone PG database `national_paper_hangers` (NOT in dw_unified)

BEGIN;

-- =======================================================================
-- INSTALLERS
-- =======================================================================

CREATE TABLE IF NOT EXISTS installers (
  id              SERIAL PRIMARY KEY,
  slug            TEXT UNIQUE NOT NULL,
  email           TEXT UNIQUE NOT NULL,
  password_hash   TEXT NOT NULL,
  business_name   TEXT NOT NULL,
  contact_name    TEXT,
  phone           TEXT,
  bio             TEXT,
  headline        TEXT,
  city            TEXT,
  state           TEXT,
  zip             TEXT,
  country         TEXT DEFAULT 'US',
  service_radius_miles INTEGER DEFAULT 50,
  travel_available BOOLEAN DEFAULT false,
  team_size       INTEGER,
  founded_year    INTEGER,
  website         TEXT,
  -- specialties / segments
  market_segments TEXT[] DEFAULT '{}',           -- e.g. {luxury_residential, hospitality, retail, museum}
  materials       TEXT[] DEFAULT '{}',           -- e.g. {grasscloth, silk, hand_painted, mural, vinyl}
  brands_handled  TEXT[] DEFAULT '{}',           -- e.g. {Maya Romanoff, Fromental, de Gournay}
  accreditations  TEXT[] DEFAULT '{}',           -- WIA, manufacturer certs, etc.
  -- trust / verification
  verified        BOOLEAN DEFAULT false,
  verified_at     TIMESTAMPTZ,
  verified_by     TEXT,
  insurance_on_file BOOLEAN DEFAULT false,
  insurance_expires DATE,
  license_number  TEXT,
  license_state   TEXT,
  -- subscription
  tier            TEXT DEFAULT 'basic',          -- basic | pro | signature | enterprise
  subscription_status TEXT DEFAULT 'inactive',   -- inactive | active | past_due | canceled
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  current_period_end TIMESTAMPTZ,
  -- meta
  response_time_hours INTEGER DEFAULT 24,
  status          TEXT DEFAULT 'pending',        -- pending | active | suspended | archived
  profile_complete BOOLEAN DEFAULT false,
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now(),
  last_login_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_installers_slug ON installers(slug);
CREATE INDEX IF NOT EXISTS idx_installers_email ON installers(email);
CREATE INDEX IF NOT EXISTS idx_installers_status ON installers(status);
CREATE INDEX IF NOT EXISTS idx_installers_tier ON installers(tier);
CREATE INDEX IF NOT EXISTS idx_installers_zip ON installers(zip);
CREATE INDEX IF NOT EXISTS idx_installers_state ON installers(state);
CREATE INDEX IF NOT EXISTS idx_installers_segments ON installers USING GIN(market_segments);
CREATE INDEX IF NOT EXISTS idx_installers_materials ON installers USING GIN(materials);

-- =======================================================================
-- PORTFOLIO (project case studies per installer)
-- =======================================================================

CREATE TABLE IF NOT EXISTS installer_portfolio (
  id              SERIAL PRIMARY KEY,
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  title           TEXT NOT NULL,
  description     TEXT,
  image_url       TEXT NOT NULL,
  caption         TEXT,
  market_segment  TEXT,
  material        TEXT,
  brand           TEXT,
  city            TEXT,
  state           TEXT,
  year            INTEGER,
  display_order   INTEGER DEFAULT 0,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_portfolio_installer ON installer_portfolio(installer_id);

-- =======================================================================
-- AVAILABILITY (recurring weekly schedule)
-- =======================================================================

CREATE TABLE IF NOT EXISTS installer_availability (
  id              SERIAL PRIMARY KEY,
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  day_of_week     SMALLINT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6), -- 0=Sun ... 6=Sat
  start_time      TIME NOT NULL,
  end_time        TIME NOT NULL,
  timezone        TEXT DEFAULT 'America/Los_Angeles',
  active          BOOLEAN DEFAULT true,
  created_at      TIMESTAMPTZ DEFAULT now(),
  CHECK (end_time > start_time)
);

CREATE INDEX IF NOT EXISTS idx_availability_installer ON installer_availability(installer_id);
CREATE INDEX IF NOT EXISTS idx_availability_day ON installer_availability(installer_id, day_of_week);

-- =======================================================================
-- TIME OFF (blocked dates / vacations / one-off blocks)
-- =======================================================================

CREATE TABLE IF NOT EXISTS installer_time_off (
  id              SERIAL PRIMARY KEY,
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  start_at        TIMESTAMPTZ NOT NULL,
  end_at          TIMESTAMPTZ NOT NULL,
  reason          TEXT,
  all_day         BOOLEAN DEFAULT false,
  created_at      TIMESTAMPTZ DEFAULT now(),
  CHECK (end_at > start_at)
);

CREATE INDEX IF NOT EXISTS idx_time_off_installer ON installer_time_off(installer_id);
CREATE INDEX IF NOT EXISTS idx_time_off_range ON installer_time_off(installer_id, start_at, end_at);

-- =======================================================================
-- BOOKINGS (consumer-scheduled installs / consultations)
-- =======================================================================

CREATE TABLE IF NOT EXISTS bookings (
  id              SERIAL PRIMARY KEY,
  uuid            UUID NOT NULL DEFAULT gen_random_uuid(),
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE RESTRICT,
  -- consumer identity
  customer_name   TEXT NOT NULL,
  customer_email  TEXT NOT NULL,
  customer_phone  TEXT,
  -- project basics
  project_type    TEXT,                          -- consultation | install | site_visit | quote
  market_segment  TEXT,                          -- luxury_residential | hospitality | retail | etc.
  material        TEXT,
  brand           TEXT,
  surfaces        TEXT,                          -- free text: walls, ceiling, etc.
  rooms           TEXT,
  square_feet     INTEGER,
  budget_band     TEXT,                          -- under_5k | 5k_15k | 15k_50k | 50k_plus
  -- location
  address_line1   TEXT,
  address_line2   TEXT,
  city            TEXT,
  state           TEXT,
  zip             TEXT,
  -- schedule
  scheduled_start TIMESTAMPTZ NOT NULL,
  scheduled_end   TIMESTAMPTZ NOT NULL,
  timezone        TEXT DEFAULT 'America/Los_Angeles',
  -- status
  status          TEXT NOT NULL DEFAULT 'pending', -- pending | confirmed | declined | completed | canceled | no_show
  installer_notes TEXT,
  customer_notes  TEXT,
  cancel_reason   TEXT,
  -- payment (deposits — phase 4)
  deposit_amount_cents INTEGER,
  deposit_status  TEXT,                          -- none | requires_payment | paid | refunded
  stripe_payment_intent_id TEXT,
  -- meta
  source          TEXT DEFAULT 'web',            -- web | concierge | partner
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now(),
  confirmed_at    TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,
  canceled_at     TIMESTAMPTZ,
  CHECK (scheduled_end > scheduled_start)
);

CREATE INDEX IF NOT EXISTS idx_bookings_installer ON bookings(installer_id);
CREATE INDEX IF NOT EXISTS idx_bookings_status ON bookings(status);
CREATE INDEX IF NOT EXISTS idx_bookings_scheduled ON bookings(installer_id, scheduled_start, scheduled_end);
CREATE INDEX IF NOT EXISTS idx_bookings_customer_email ON bookings(customer_email);
CREATE INDEX IF NOT EXISTS idx_bookings_uuid ON bookings(uuid);

-- =======================================================================
-- REVIEWS (post-completion, gated on completed booking)
-- =======================================================================

CREATE TABLE IF NOT EXISTS installer_reviews (
  id              SERIAL PRIMARY KEY,
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  booking_id      INTEGER REFERENCES bookings(id) ON DELETE SET NULL,
  customer_name   TEXT,
  customer_email  TEXT,
  rating          SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  title           TEXT,
  body            TEXT,
  verified        BOOLEAN DEFAULT false,        -- true if booking_id is non-null AND completed
  published       BOOLEAN DEFAULT false,        -- moderation gate
  moderation_note TEXT,
  created_at      TIMESTAMPTZ DEFAULT now(),
  published_at    TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_reviews_installer ON installer_reviews(installer_id);
CREATE INDEX IF NOT EXISTS idx_reviews_published ON installer_reviews(installer_id, published);

-- =======================================================================
-- CONSUMER LEADS (pre-booking briefs from concierge intake)
-- =======================================================================

CREATE TABLE IF NOT EXISTS consumer_leads (
  id              SERIAL PRIMARY KEY,
  uuid            UUID NOT NULL DEFAULT gen_random_uuid(),
  customer_name   TEXT NOT NULL,
  customer_email  TEXT NOT NULL,
  customer_phone  TEXT,
  customer_role   TEXT,                          -- homeowner | designer | architect | hospitality | other
  company         TEXT,
  project_name    TEXT,
  market_segment  TEXT,
  material        TEXT,
  brand           TEXT,
  surfaces        TEXT,
  rooms           TEXT,
  square_feet     INTEGER,
  budget_band     TEXT,
  timeline        TEXT,
  city            TEXT,
  state           TEXT,
  zip             TEXT,
  product_sourced BOOLEAN,
  notes           TEXT,
  status          TEXT DEFAULT 'new',           -- new | shortlisted | matched | closed
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_leads_status ON consumer_leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_zip ON consumer_leads(zip);

-- =======================================================================
-- LEAD ↔ INSTALLER OFFERS (when a lead is routed to N installers)
-- =======================================================================

CREATE TABLE IF NOT EXISTS lead_offers (
  id              SERIAL PRIMARY KEY,
  lead_id         INTEGER NOT NULL REFERENCES consumer_leads(id) ON DELETE CASCADE,
  installer_id    INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  status          TEXT DEFAULT 'pending',       -- pending | accepted | declined | expired
  expires_at      TIMESTAMPTZ,
  responded_at    TIMESTAMPTZ,
  installer_response TEXT,
  created_at      TIMESTAMPTZ DEFAULT now(),
  UNIQUE (lead_id, installer_id)
);

CREATE INDEX IF NOT EXISTS idx_offers_installer ON lead_offers(installer_id, status);

-- =======================================================================
-- SUBSCRIPTION EVENTS (audit trail from Stripe webhooks)
-- =======================================================================

CREATE TABLE IF NOT EXISTS subscription_events (
  id              SERIAL PRIMARY KEY,
  installer_id    INTEGER REFERENCES installers(id) ON DELETE SET NULL,
  stripe_event_id TEXT UNIQUE,
  event_type      TEXT NOT NULL,
  payload         JSONB,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sub_events_installer ON subscription_events(installer_id);

-- =======================================================================
-- SESSIONS (express-session via connect-pg-simple)
-- =======================================================================

CREATE TABLE IF NOT EXISTS session (
  sid    VARCHAR NOT NULL COLLATE "default" PRIMARY KEY,
  sess   JSON NOT NULL,
  expire TIMESTAMP(6) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_session_expire ON session(expire);

-- =======================================================================
-- updated_at trigger
-- =======================================================================

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_installers_updated ON installers;
CREATE TRIGGER trg_installers_updated BEFORE UPDATE ON installers
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

DROP TRIGGER IF EXISTS trg_bookings_updated ON bookings;
CREATE TRIGGER trg_bookings_updated BEFORE UPDATE ON bookings
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

COMMIT;