-- PhantomSignal MVP database schema
-- Focus: haunted location submissions owned by Scouts

CREATE TABLE locations (
  location_id TEXT PRIMARY KEY,
  gps_lat DECIMAL(10, 7) NOT NULL,
  gps_lng DECIMAL(10, 7) NOT NULL,
  scout_id TEXT NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  activity_rating TEXT NOT NULL CHECK (activity_rating IN ('1-5', '6-20', '21-49', '50+')),
  anomaly_type TEXT NOT NULL CHECK (anomaly_type IN ('A1', 'A2', 'A3', 'A4', 'A5', 'A6')),
  verification_status TEXT NOT NULL DEFAULT 'pending' CHECK (
    verification_status IN ('pending', 'under_review', 'verified', 'rejected', 'archived')
  ),
  tour_count INTEGER NOT NULL DEFAULT 0 CHECK (tour_count >= 0),
  total_earnings_paid NUMERIC(12, 2) NOT NULL DEFAULT 0.00 CHECK (total_earnings_paid >= 0)
);

CREATE INDEX idx_locations_scout_id ON locations (scout_id);
CREATE INDEX idx_locations_submitted_at ON locations (submitted_at DESC);
CREATE INDEX idx_locations_verification_status ON locations (verification_status);
CREATE INDEX idx_locations_anomaly_type ON locations (anomaly_type);

-- Recommended next-table additions for build phase:
-- scouts(id, handle, wallet_info, created_at)
-- location_media(id, location_id, media_type, storage_url, uploaded_at)
-- verification_logs(id, location_id, reviewer_id, old_status, new_status, changed_at, notes)
-- earnings_ledger(id, location_id, scout_id, amount, source_type, paid_at)
