Supabaseセットアップガイド

-- =============================================
-- JizaiLog GPS Database Schema
-- Run this in YOUR Supabase SQL Editor
-- =============================================

-- Sessions — one row per recorded walk
CREATE TABLE sessions (
    id            UUID PRIMARY KEY,
    user_id       UUID NOT NULL,
    status        TEXT NOT NULL DEFAULT 'stopped',
    started_at    TIMESTAMPTZ NOT NULL,
    ended_at      TIMESTAMPTZ,
    point_count   INTEGER DEFAULT 0,
    discard_count INTEGER DEFAULT 0,
    distance_m    REAL DEFAULT 0,
    duration_s    INTEGER DEFAULT 0,
    created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_sessions_user ON sessions(user_id);

-- Points — raw GPS coordinates per session
CREATE TABLE points (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    ts         BIGINT NOT NULL,
    lat        DOUBLE PRECISION NOT NULL,
    lng        DOUBLE PRECISION NOT NULL,
    alt        DOUBLE PRECISION,
    accuracy   REAL,
    speed      REAL,
    heading    REAL,
    UNIQUE(session_id, ts)
);

CREATE INDEX idx_points_session_ts ON points(session_id, ts);

-- Device debug logs
CREATE TABLE device_logs (
    id          BIGSERIAL PRIMARY KEY,
    user_id     UUID NOT NULL,
    device_id   TEXT NOT NULL,
    platform    TEXT NOT NULL,
    ts          BIGINT NOT NULL,
    tag         TEXT NOT NULL,
    message     TEXT NOT NULL,
    app_version TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_device_logs_user_ts ON device_logs(user_id, ts DESC);
CREATE INDEX idx_device_logs_device ON device_logs(device_id, ts DESC);

-- Session comments (chat)
CREATE TABLE session_comments (
    id          UUID PRIMARY KEY,
    session_id  UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL,
    content     TEXT,
    image_path  TEXT,
    created_at  BIGINT NOT NULL
);

CREATE INDEX idx_comments_session ON session_comments(session_id, created_at);

-- Comment read tracking
CREATE TABLE comment_reads (
    session_id   UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id      UUID NOT NULL,
    last_read_at BIGINT NOT NULL,
    PRIMARY KEY (session_id, user_id)
);

-- Enable Row Level Security on all tables
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE points ENABLE ROW LEVEL SECURITY;
ALTER TABLE device_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE session_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE comment_reads ENABLE ROW LEVEL SECURITY;

-- RLS policies — allow anon access (Worker uses anon key)
CREATE POLICY "anon_all_sessions" ON sessions
    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "anon_all_points" ON points
    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "anon_all_device_logs" ON device_logs
    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "anon_all_comments" ON session_comments
    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "anon_all_comment_reads" ON comment_reads
    FOR ALL TO anon USING (true) WITH CHECK (true);

-- Grant permissions to anon role
GRANT ALL ON sessions TO anon;
GRANT ALL ON points TO anon;
GRANT ALL ON device_logs TO anon;
GRANT ALL ON session_comments TO anon;
GRANT ALL ON comment_reads TO anon;
GRANT USAGE, SELECT ON SEQUENCE device_logs_id_seq TO anon;