schema.sql

-- ============================================================
-- PinPoint / LocationQA — Full Database Schema for Supabase
-- Run this in: Supabase Dashboard → SQL Editor → New Query
-- ============================================================

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ── USERS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  username      TEXT UNIQUE NOT NULL,
  email         TEXT UNIQUE NOT NULL,
  password      TEXT NOT NULL,
  avatar_url    TEXT,
  credits       INTEGER DEFAULT 0,
  reputation    INTEGER DEFAULT 0,
  total_answers INTEGER DEFAULT 0,
  upvotes_received INTEGER DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- ── QUESTIONS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS questions (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID REFERENCES users(id) ON DELETE CASCADE,
  title         TEXT NOT NULL,
  description   TEXT,
  latitude      DOUBLE PRECISION NOT NULL,
  longitude     DOUBLE PRECISION NOT NULL,
  radius_meters INTEGER DEFAULT 500,
  category      TEXT DEFAULT 'general',
  status        TEXT DEFAULT 'open',
  answer_count  INTEGER DEFAULT 0,
  upvote_count  INTEGER DEFAULT 0,
  downvote_count INTEGER DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- ── ANSWERS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS answers (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  question_id   UUID REFERENCES questions(id) ON DELETE CASCADE,
  user_id       UUID REFERENCES users(id) ON DELETE CASCADE,
  image_url     TEXT,
  image_base64  TEXT,
  text_answer   TEXT,
  status        TEXT DEFAULT 'pending',
  upvote_count  INTEGER DEFAULT 0,
  downvote_count INTEGER DEFAULT 0,
  helpful_score DOUBLE PRECISION DEFAULT 0.0,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- ── IMAGE ANALYSIS (ML pipeline results) ────────────────────
CREATE TABLE IF NOT EXISTS image_analysis (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  answer_id           UUID REFERENCES answers(id) ON DELETE CASCADE,
  image_quality_score DOUBLE PRECISION,
  is_motion_blurred   BOOLEAN DEFAULT false,
  nsfw_score          DOUBLE PRECISION,
  contains_faces      BOOLEAN DEFAULT false,
  face_count          INTEGER DEFAULT 0,
  objects_detected    JSONB DEFAULT '[]'::jsonb,
  scene_caption       TEXT,
  ocr_text            TEXT,
  created_at          TIMESTAMPTZ DEFAULT now()
);

-- ── ANSWER EMBEDDINGS (semantic similarity) ─────────────────
CREATE TABLE IF NOT EXISTS answer_embeddings (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  answer_id           UUID REFERENCES answers(id) ON DELETE CASCADE,
  question_id         UUID REFERENCES questions(id) ON DELETE CASCADE,
  semantic_similarity DOUBLE PRECISION,
  final_status        TEXT,
  created_at          TIMESTAMPTZ DEFAULT now()
);

-- ── IMAGE VERIFICATION (GPS / gyro / SSIM) ──────────────────
CREATE TABLE IF NOT EXISTS image_verification (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  answer_id         UUID REFERENCES answers(id) ON DELETE CASCADE,
  gps_latitude      DOUBLE PRECISION,
  gps_longitude     DOUBLE PRECISION,
  gps_accuracy      DOUBLE PRECISION,
  gyro_pitch        DOUBLE PRECISION,
  gyro_roll         DOUBLE PRECISION,
  gyro_yaw          DOUBLE PRECISION,
  gyro_valid        BOOLEAN DEFAULT false,
  ssim_score        DOUBLE PRECISION,
  ssim_valid        BOOLEAN DEFAULT false,
  verification_hash TEXT,
  timestamp         TIMESTAMPTZ DEFAULT now()
);

-- ── VOTES ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS votes (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID REFERENCES users(id) ON DELETE CASCADE,
  answer_id   UUID REFERENCES answers(id) ON DELETE CASCADE,
  vote_type   TEXT NOT NULL CHECK (vote_type IN ('upvote', 'downvote')),
  created_at  TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, answer_id)
);

-- ── NOTIFICATIONS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id           UUID REFERENCES users(id) ON DELETE CASCADE,
  question_id       UUID REFERENCES questions(id) ON DELETE SET NULL,
  notification_type TEXT,
  message           TEXT,
  is_read           BOOLEAN DEFAULT false,
  created_at        TIMESTAMPTZ DEFAULT now()
);
-- ── BLOCKCHAIN RECORDS (Solana verification) ────────────
CREATE TABLE IF NOT EXISTS blockchain_records (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  record_type     TEXT NOT NULL,          -- 'question' or 'answer'
  record_id       UUID NOT NULL,          -- question.id or answer.id
  tx_signature    TEXT NOT NULL,           -- Solana transaction signature
  explorer_url    TEXT,                    -- Link to Solana Explorer
  content_hash    TEXT NOT NULL,           -- SHA-256 hash of content
  network         TEXT DEFAULT 'devnet',   -- devnet / testnet / mainnet-beta
  wallet_address  TEXT,                    -- Signer public key
  created_at      TIMESTAMPTZ DEFAULT now()
);
-- ── INDEXES (performance) ───────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_questions_user     ON questions(user_id);
CREATE INDEX IF NOT EXISTS idx_questions_status   ON questions(status);
CREATE INDEX IF NOT EXISTS idx_questions_location ON questions(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_answers_question   ON answers(question_id);
CREATE INDEX IF NOT EXISTS idx_answers_user       ON answers(user_id);
CREATE INDEX IF NOT EXISTS idx_votes_answer       ON votes(answer_id);
CREATE INDEX IF NOT EXISTS idx_votes_user         ON votes(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_image_analysis_answer ON image_analysis(answer_id);
CREATE INDEX IF NOT EXISTS idx_answer_embeddings_answer ON answer_embeddings(answer_id);
CREATE INDEX IF NOT EXISTS idx_image_verification_answer ON image_verification(answer_id);
CREATE INDEX IF NOT EXISTS idx_blockchain_record_type ON blockchain_records(record_type, record_id);

-- ── Disable RLS (backend uses service_role key) ─────────────
-- If you want RLS, add policies instead of disabling.
ALTER TABLE users              ENABLE ROW LEVEL SECURITY;
ALTER TABLE questions          ENABLE ROW LEVEL SECURITY;
ALTER TABLE answers            ENABLE ROW LEVEL SECURITY;
ALTER TABLE image_analysis     ENABLE ROW LEVEL SECURITY;
ALTER TABLE answer_embeddings  ENABLE ROW LEVEL SECURITY;
ALTER TABLE image_verification ENABLE ROW LEVEL SECURITY;
ALTER TABLE votes              ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications      ENABLE ROW LEVEL SECURITY;
ALTER TABLE blockchain_records ENABLE ROW LEVEL SECURITY;

-- Service-role bypasses RLS automatically, so no policies needed
-- if you only access from the backend. If you want anon/frontend
-- access directly, add policies like:
-- CREATE POLICY "allow_all" ON users FOR ALL USING (true);

-- ── STORAGE BUCKET ──────────────────────────────────────────
-- The backend auto-creates the "answers" bucket on startup,
-- but you can also create it manually:
-- Go to Supabase Dashboard → Storage → New Bucket → "answers" (public)