-- ============================================
-- GPS TRACKER - SUPABASE SCHEMA
-- Jalankan file ini di Supabase SQL Editor
-- ============================================

-- Tabel sessions: menyimpan setiap sesi perjalanan
CREATE TABLE IF NOT EXISTS sessions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,                        -- nama pengguna
  ip_address TEXT,                           -- IP address pengguna
  status TEXT DEFAULT 'idle'                 -- idle | traveling | finished
    CHECK (status IN ('idle', 'traveling', 'finished')),
  started_at TIMESTAMPTZ,
  finished_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabel waypoints: menyimpan titik-titik koordinat GPS per sesi
CREATE TABLE IF NOT EXISTS waypoints (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  accuracy DOUBLE PRECISION,               -- akurasi GPS dalam meter
  altitude DOUBLE PRECISION,
  speed DOUBLE PRECISION,                  -- kecepatan dalam m/s
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index untuk performa query waypoints per sesi
CREATE INDEX IF NOT EXISTS idx_waypoints_session_id ON waypoints(session_id);
CREATE INDEX IF NOT EXISTS idx_waypoints_recorded_at ON waypoints(recorded_at);
CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(status);

-- Enable Realtime untuk kedua tabel
ALTER PUBLICATION supabase_realtime ADD TABLE sessions;
ALTER PUBLICATION supabase_realtime ADD TABLE waypoints;

-- Row Level Security (opsional, aktifkan jika perlu auth)
-- ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE waypoints ENABLE ROW LEVEL SECURITY;

-- Policy untuk akses publik (tanpa auth)
-- CREATE POLICY "Allow all" ON sessions FOR ALL USING (true);
-- CREATE POLICY "Allow all" ON waypoints FOR ALL USING (true);