schema.sql

CREATE TABLE IF NOT EXISTS companies (
  external_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  city TEXT NOT NULL,
  address TEXT NOT NULL,
  rating NUMERIC(2, 1),
  reviews_count INTEGER NOT NULL,
  site TEXT,
  phone TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT companies_rating_range CHECK (rating IS NULL OR rating BETWEEN 0 AND 5),
  CONSTRAINT companies_reviews_nonnegative CHECK (reviews_count >= 0)
);

CREATE TABLE IF NOT EXISTS import_rejects (
  id BIGSERIAL PRIMARY KEY,
  source_file TEXT NOT NULL,
  row_number INTEGER NOT NULL,
  reason TEXT NOT NULL,
  payload JSONB NOT NULL,
  rejected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT import_rejects_source_row_unique UNIQUE (source_file, row_number)
);

CREATE INDEX IF NOT EXISTS companies_city_idx ON companies (city);
CREATE INDEX IF NOT EXISTS companies_category_idx ON companies (category);
CREATE INDEX IF NOT EXISTS companies_name_lower_idx ON companies (lower(name));