`migrations/001_init.sql`

115 lines
-- small-business-builder — initial schema
-- DB: small_business_directory  (standalone, NOT dw_unified)

BEGIN;

CREATE TABLE IF NOT EXISTS businesses (
  id BIGSERIAL PRIMARY KEY,
  slug TEXT UNIQUE,
  name TEXT NOT NULL,
  category TEXT, -- salon, barbershop, nail_salon, generic
  address TEXT,
  city TEXT,
  state TEXT,
  zip TEXT,
  latitude NUMERIC,
  longitude NUMERIC,
  phone TEXT,
  email TEXT,
  website TEXT,
  years_in_business INT,
  owner_name TEXT,
  hero_image_url TEXT,
  color_primary TEXT,
  color_accent TEXT,
  about_text TEXT,
  hours_json JSONB,
  tier TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free','plus','premium')),
  ranking_score NUMERIC,
  source_data_json JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_businesses_slug ON businesses(slug);
CREATE INDEX IF NOT EXISTS idx_businesses_category ON businesses(category);
CREATE INDEX IF NOT EXISTS idx_businesses_city_state ON businesses(city, state);
CREATE INDEX IF NOT EXISTS idx_businesses_tier ON businesses(tier);

CREATE TABLE IF NOT EXISTS business_socials (
  id BIGSERIAL PRIMARY KEY,
  business_id BIGINT REFERENCES businesses(id) ON DELETE CASCADE,
  platform TEXT, -- instagram, tiktok, twitter, linkedin, facebook, youtube
  handle TEXT,
  url TEXT,
  follower_count INT,
  last_post_at TIMESTAMPTZ,
  raw_json JSONB,
  scraped_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_socials_business ON business_socials(business_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_socials_business_platform
  ON business_socials(business_id, platform);

CREATE TABLE IF NOT EXISTS business_inputs (
  -- free-form structured input from owner: "anything clients should know" + arbitrary key-value
  id BIGSERIAL PRIMARY KEY,
  business_id BIGINT REFERENCES businesses(id) ON DELETE CASCADE,
  field_key TEXT NOT NULL,
  field_value TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_inputs_business ON business_inputs(business_id);

CREATE TABLE IF NOT EXISTS business_mockups (
  id BIGSERIAL PRIMARY KEY,
  business_id BIGINT REFERENCES businesses(id) ON DELETE CASCADE,
  template_id INT, -- 1..5
  preview_url TEXT,
  full_html TEXT,
  generated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_mockups_business ON business_mockups(business_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_mockups_business_template
  ON business_mockups(business_id, template_id);

CREATE TABLE IF NOT EXISTS tiers (
  id SERIAL PRIMARY KEY,
  code TEXT UNIQUE,
  name TEXT,
  monthly_usd NUMERIC,
  features_json JSONB
);

INSERT INTO tiers (code, name, monthly_usd, features_json) VALUES
  ('free', 'Free Listing', 0,
    '["directory listing", "name + address + hours + phone", "1 hero image"]'),
  ('plus', 'Plus', 19,
    '["full website (5 templates)", "custom domain via Cloudflare", "lead form", "Stripe payments", "no builder branding"]'),
  ('premium', 'Premium', 79,
    '["everything in Plus", "booking/calendar integration", "SEO autosubmission", "social-post auto-scheduler", "monthly performance email"]')
ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS leads (
  id BIGSERIAL PRIMARY KEY,
  business_id BIGINT REFERENCES businesses(id),
  client_email TEXT,
  client_phone TEXT,
  message TEXT,
  status TEXT DEFAULT 'new',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_leads_business ON leads(business_id);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);

-- updated_at trigger
CREATE OR REPLACE FUNCTION smb_set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_businesses_updated_at ON businesses;
CREATE TRIGGER trg_businesses_updated_at
  BEFORE UPDATE ON businesses
  FOR EACH ROW EXECUTE FUNCTION smb_set_updated_at();

COMMIT;