`db/migrations/022_appointments.sql`

65 lines
-- 022_appointments.sql
-- Self-contained appointment-booking system.
-- All tables prefixed with appt_ to stay isolated from existing schema.

CREATE TABLE IF NOT EXISTS appt_appointments (
  id              BIGSERIAL PRIMARY KEY,
  name            TEXT NOT NULL,
  email           TEXT NOT NULL,
  phone           TEXT,
  business_id     BIGINT,                                   -- nullable; not FK'd so reversible
  owner_email     TEXT NOT NULL,                            -- which calendar owner this is for
  requested_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  slot_start      TIMESTAMPTZ NOT NULL,
  slot_end        TIMESTAMPTZ NOT NULL,
  status          TEXT NOT NULL DEFAULT 'confirmed'         -- confirmed | cancelled | pending
                  CHECK (status IN ('confirmed','cancelled','pending')),
  notes           TEXT,
  google_event_id TEXT,                                     -- nullable; set if pushed to Google Calendar
  cancel_token    TEXT NOT NULL DEFAULT md5(random()::text || clock_timestamp()::text),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS appt_appointments_owner_slot_idx
  ON appt_appointments (owner_email, slot_start);
CREATE INDEX IF NOT EXISTS appt_appointments_status_idx
  ON appt_appointments (status);

CREATE TABLE IF NOT EXISTS appt_availability (
  id                BIGSERIAL PRIMARY KEY,
  owner_email       TEXT NOT NULL,
  day_of_week       SMALLINT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6), -- 0 = Sunday, 6 = Saturday
  start_min         INT NOT NULL CHECK (start_min BETWEEN 0 AND 1439),     -- minutes from midnight, local owner tz (America/Los_Angeles)
  end_min           INT NOT NULL CHECK (end_min BETWEEN 1 AND 1440),
  slot_duration_min INT NOT NULL DEFAULT 30 CHECK (slot_duration_min BETWEEN 5 AND 240),
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (owner_email, day_of_week, start_min, end_min)
);

CREATE INDEX IF NOT EXISTS appt_availability_owner_idx
  ON appt_availability (owner_email);

CREATE TABLE IF NOT EXISTS appt_oauth_tokens (
  owner_email   TEXT PRIMARY KEY,
  access_token  TEXT NOT NULL,
  refresh_token TEXT,
  expires_at    TIMESTAMPTZ NOT NULL,
  scope         TEXT,
  token_type    TEXT DEFAULT 'Bearer',
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- OAuth state nonce — short-lived row to bind ?state to a pending start request.
CREATE TABLE IF NOT EXISTS appt_oauth_state (
  state        TEXT PRIMARY KEY,
  owner_email  TEXT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed the test owner: info@designerwallcoverings.com, 9 AM – 5 PM weekdays, 30-min slots.
-- 9 AM = 540 min, 5 PM = 1020 min. Mon=1 ... Fri=5.
INSERT INTO appt_availability (owner_email, day_of_week, start_min, end_min, slot_duration_min)
SELECT 'info@designerwallcoverings.com', d, 540, 1020, 30
FROM generate_series(1, 5) AS d
ON CONFLICT (owner_email, day_of_week, start_min, end_min) DO NOTHING;