`schema.sql`

74 lines
-- Costa Rica multi-vertical directory schema
-- Ventura-Corridor pattern: tourism + rentals + local services

CREATE TABLE IF NOT EXISTS regions (
  id           SERIAL PRIMARY KEY,
  slug         TEXT UNIQUE NOT NULL,
  name         TEXT NOT NULL,
  province     TEXT NOT NULL,
  region_type  TEXT NOT NULL DEFAULT 'town',
  description  TEXT,
  lat          NUMERIC(9,6),
  lng          NUMERIC(9,6)
);

CREATE TABLE IF NOT EXISTS places (
  id            SERIAL PRIMARY KEY,
  slug          TEXT UNIQUE NOT NULL,
  name          TEXT NOT NULL,
  category      TEXT NOT NULL CHECK (category IN ('tourism','rentals','service')),
  vertical      TEXT NOT NULL,
  region_id     INTEGER REFERENCES regions(id),
  description   TEXT,
  address       TEXT,
  phone         TEXT,
  email         TEXT,
  website       TEXT,
  price_range   TEXT,
  rating        NUMERIC(2,1),
  image_url     TEXT,
  tags          TEXT[],
  lat           NUMERIC(9,6),
  lng           NUMERIC(9,6),
  cedula_juridica TEXT,
  source        TEXT,
  source_url    TEXT,
  verified      BOOLEAN DEFAULT FALSE,
  status        TEXT DEFAULT 'active',
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_places_vertical    ON places(vertical);
CREATE INDEX IF NOT EXISTS idx_places_category    ON places(category);
CREATE INDEX IF NOT EXISTS idx_places_region      ON places(region_id);
CREATE INDEX IF NOT EXISTS idx_places_name_lower  ON places(LOWER(name));
CREATE INDEX IF NOT EXISTS idx_places_status      ON places(status);

CREATE TABLE IF NOT EXISTS leads (
  id           SERIAL PRIMARY KEY,
  place_id     INTEGER REFERENCES places(id),
  name         TEXT,
  email        TEXT,
  phone        TEXT,
  message      TEXT,
  meta         JSONB,
  ip           TEXT,
  user_agent   TEXT,
  created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_leads_place ON leads(place_id);

CREATE TABLE IF NOT EXISTS ingest_runs (
  id            SERIAL PRIMARY KEY,
  source        TEXT NOT NULL,
  started_at    TIMESTAMPTZ DEFAULT NOW(),
  finished_at   TIMESTAMPTZ,
  rows_in       INTEGER,
  rows_added    INTEGER,
  rows_updated  INTEGER,
  status        TEXT,
  notes         TEXT
);