calctext/supabase/migrations/001_create_schema.sql

-- CalcText database schema
-- Run via Docker init or manually

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

-- Roles for RLS
DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'anon') THEN
    CREATE ROLE anon NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticated') THEN
    CREATE ROLE authenticated NOLOGIN;
  END IF;
END $$;

GRANT USAGE ON SCHEMA public TO anon, authenticated;

-- ============================================================
-- User profiles (extends GoTrue auth.users)
-- ============================================================
CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY,
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY profiles_own ON profiles
  FOR ALL USING (id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid);

GRANT SELECT, INSERT, UPDATE ON profiles TO authenticated;

-- ============================================================
-- Folders
-- ============================================================
CREATE TABLE IF NOT EXISTS folders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  owner_id UUID NOT NULL,
  name TEXT NOT NULL DEFAULT 'New Folder',
  parent_id UUID REFERENCES folders(id) ON DELETE CASCADE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE folders ENABLE ROW LEVEL SECURITY;

CREATE POLICY folders_own ON folders
  FOR ALL USING (owner_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON folders TO authenticated;

-- ============================================================
-- Documents
-- ============================================================
CREATE TABLE IF NOT EXISTS documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  owner_id UUID NOT NULL,
  title TEXT NOT NULL DEFAULT 'Untitled',
  folder_id UUID REFERENCES folders(id) ON DELETE SET NULL,
  is_favorite BOOLEAN NOT NULL DEFAULT false,
  share_token TEXT UNIQUE,
  share_permission TEXT CHECK (share_permission IN ('view', 'edit')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_share_token ON documents(share_token) WHERE share_token IS NOT NULL;

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Owner can do anything
CREATE POLICY documents_owner ON documents
  FOR ALL USING (owner_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid);

-- Shared documents can be read via share token (anon access)
CREATE POLICY documents_shared_read ON documents
  FOR SELECT USING (share_token IS NOT NULL AND share_permission IS NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO authenticated;
GRANT SELECT ON documents TO anon;

-- ============================================================
-- Document collaborators (explicit user-to-user sharing)
-- ============================================================
CREATE TABLE IF NOT EXISTS document_collaborators (
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  permission TEXT NOT NULL CHECK (permission IN ('view', 'edit')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (document_id, user_id)
);

ALTER TABLE document_collaborators ENABLE ROW LEVEL SECURITY;

-- Users can see their own collaborations
CREATE POLICY collab_own ON document_collaborators
  FOR SELECT USING (user_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid);

-- Document owners can manage collaborators
CREATE POLICY collab_owner ON document_collaborators
  FOR ALL USING (
    document_id IN (
      SELECT id FROM documents
      WHERE owner_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid
    )
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON document_collaborators TO authenticated;

-- ============================================================
-- Device sessions (password renewal per device)
-- ============================================================
CREATE TABLE IF NOT EXISTS device_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  device_fingerprint TEXT NOT NULL,
  device_name TEXT,
  session_ttl_seconds INTEGER NOT NULL DEFAULT 86400,
  last_password_auth_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, device_fingerprint)
);

ALTER TABLE device_sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY device_sessions_own ON device_sessions
  FOR ALL USING (user_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON device_sessions TO authenticated;

-- ============================================================
-- Y.js document snapshots storage
-- ============================================================
CREATE TABLE IF NOT EXISTS ydoc_snapshots (
  document_id UUID PRIMARY KEY REFERENCES documents(id) ON DELETE CASCADE,
  state BYTEA NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE ydoc_snapshots ENABLE ROW LEVEL SECURITY;

-- Owner can read/write snapshots
CREATE POLICY ydoc_owner ON ydoc_snapshots
  FOR ALL USING (
    document_id IN (
      SELECT id FROM documents
      WHERE owner_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid
    )
  );

-- Collaborators can read/write snapshots
CREATE POLICY ydoc_collab ON ydoc_snapshots
  FOR ALL USING (
    document_id IN (
      SELECT document_id FROM document_collaborators
      WHERE user_id = current_setting('request.jwt.claims', true)::json->>'sub'::text::uuid
      AND permission = 'edit'
    )
  );

GRANT SELECT, INSERT, UPDATE ON ydoc_snapshots TO authenticated;