`migrations/002_website_analysis.sql`

40 lines
-- migration 002 — website-analysis feature
-- Adds storage for the Website Analysis flow (entry → 3-mockup picker → full rebuild).

BEGIN;

CREATE TABLE IF NOT EXISTS website_analyses (
  id BIGSERIAL PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  business_id BIGINT REFERENCES businesses(id) ON DELETE SET NULL,
  url TEXT NOT NULL,
  ig_handle TEXT,
  vertical TEXT,                            -- salon, restaurant, lawyer, etc.
  user_screenshot_path TEXT,                -- /uploads/wa/{slug}/orig.jpg (optional, deferred)
  status TEXT NOT NULL DEFAULT 'new'
         CHECK (status IN ('new','analyzing','ready','error')),
  selected_mockup_id TEXT,                  -- which of the 3 the user picked
  summary_json JSONB,                       -- {grade, top_3_issues, vertical}
  meta_json JSONB,                          -- raw audit blob, lazily populated
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_wa_slug   ON website_analyses(slug);
CREATE INDEX IF NOT EXISTS idx_wa_status ON website_analyses(status);
CREATE INDEX IF NOT EXISTS idx_wa_biz    ON website_analyses(business_id);

CREATE TABLE IF NOT EXISTS website_analysis_mockups (
  id BIGSERIAL PRIMARY KEY,
  analysis_id BIGINT REFERENCES website_analyses(id) ON DELETE CASCADE,
  mockup_id TEXT NOT NULL,                  -- e.g. '01-editorial'
  title TEXT NOT NULL,
  theme TEXT NOT NULL,                      -- editorial, neon, brutalist, minimalist
  preview_html TEXT,                        -- inlined single-file HTML
  generated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_wam_analysis ON website_analysis_mockups(analysis_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_wam_analysis_mockup
  ON website_analysis_mockups(analysis_id, mockup_id);

COMMIT;