-- 組織テーブル
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- チームテーブル
CREATE TABLE teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ユーザーテーブル
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 組織メンバーシップ
CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(organization_id, user_id)
);

-- チームメンバーシップ
CREATE TABLE team_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES teams(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('leader', 'member')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(team_id, user_id)
);

-- ドキュメントテーブル
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  team_id UUID REFERENCES teams(id) ON DELETE CASCADE,
  creator_id UUID REFERENCES users(id) ON DELETE SET NULL,
  visibility TEXT NOT NULL CHECK (visibility IN ('public', 'organization', 'team', 'private')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- RLSを有効化
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

-- 全員が自分の所属組織を閲覧可能
CREATE POLICY "Users can view their organizations"
ON organizations FOR SELECT
USING (
  id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
  )
);

-- オーナーと管理者のみが更新可能
CREATE POLICY "Owners and admins can update organizations"
ON organizations FOR UPDATE
USING (
  id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

-- オーナーのみが削除可能
CREATE POLICY "Only owners can delete organizations"
ON organizations FOR DELETE
USING (
  id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role = 'owner'
  )
);

ALTER TABLE teams ENABLE ROW LEVEL SECURITY;

-- チームメンバーがチームを閲覧可能
CREATE POLICY "Team members can view their teams"
ON teams FOR SELECT
USING (
  id IN (
    SELECT team_id
    FROM team_members
    WHERE user_id = auth.uid()
  )
  OR
  -- 組織の管理者も閲覧可能
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

-- チームリーダーと組織管理者が更新可能
CREATE POLICY "Team leaders and org admins can update teams"
ON teams FOR UPDATE
USING (
  id IN (
    SELECT team_id
    FROM team_members
    WHERE user_id = auth.uid()
    AND role = 'leader'
  )
  OR
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 閲覧ポリシー
CREATE POLICY "Documents visibility policy"
ON documents FOR SELECT
USING (
  -- 公開ドキュメントは全員が閲覧可能
  visibility = 'public'
  OR
  -- 組織ドキュメントは組織メンバーが閲覧可能
  (
    visibility = 'organization'
    AND organization_id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = auth.uid()
    )
  )
  OR
  -- チームドキュメントはチームメンバーが閲覧可能
  (
    visibility = 'team'
    AND team_id IN (
      SELECT team_id
      FROM team_members
      WHERE user_id = auth.uid()
    )
  )
  OR
  -- プライベートドキュメントは作成者のみ閲覧可能
  (
    visibility = 'private'
    AND creator_id = auth.uid()
  )
);

-- 更新ポリシー
CREATE POLICY "Document owners and admins can update"
ON documents FOR UPDATE
USING (
  -- 作成者は更新可能
  creator_id = auth.uid()
  OR
  -- チームリーダーは更新可能
  team_id IN (
    SELECT team_id
    FROM team_members
    WHERE user_id = auth.uid()
    AND role = 'leader'
  )
  OR
  -- 組織管理者は更新可能
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

-- 削除ポリシー
CREATE POLICY "Document owners and org admins can delete"
ON documents FOR DELETE
USING (
  -- 作成者は削除可能
  creator_id = auth.uid()
  OR
  -- 組織管理者は削除可能
  organization_id IN (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

-- ビューを使ったデータマスキング
CREATE VIEW documents_with_masking AS
SELECT
  d.id,
  d.title,
  -- 閲覧権限がある場合のみコンテンツを表示
  CASE
    WHEN d.visibility = 'public' THEN d.content
    WHEN d.visibility = 'organization' AND EXISTS (
      SELECT 1 FROM organization_members
      WHERE organization_id = d.organization_id
      AND user_id = auth.uid()
    ) THEN d.content
    WHEN d.visibility = 'team' AND EXISTS (
      SELECT 1 FROM team_members
      WHERE team_id = d.team_id
      AND user_id = auth.uid()
    ) THEN d.content
    WHEN d.visibility = 'private' AND d.creator_id = auth.uid() THEN d.content
    ELSE NULL
  END AS content,
  d.visibility,
  d.created_at,
  d.updated_at
FROM documents d;

-- ビューに対するRLS
ALTER VIEW documents_with_masking SET (security_invoker = on);

// app/actions/documents.ts
'use server'

import { createClient } from '@/lib/supabase/server'

export async function getDocuments(teamId?: string) {
  const supabase = createClient()

  let query = supabase
    .from('documents')
    .select('*')
    .order('created_at', { ascending: false })

  if (teamId) {
    query = query.eq('team_id', teamId)
  }

  const { data, error } = await query

  if (error) {
    throw new Error(`Failed to fetch documents: ${error.message}`)
  }

  return data
}

export async function createDocument(
  title: string,
  content: string,
  visibility: 'public' | 'organization' | 'team' | 'private',
  organizationId: string,
  teamId?: string
) {
  const supabase = createClient()

  const { data: { user } } = await supabase.auth.getUser()

  if (!user) {
    throw new Error('Unauthorized')
  }

  const { data, error } = await supabase
    .from('documents')
    .insert([
      {
        title,
        content,
        visibility,
        organization_id: organizationId,
        team_id: teamId,
        creator_id: user.id,
      },
    ])
    .select()
    .single()

  if (error) {
    throw new Error(`Failed to create document: ${error.message}`)
  }

  return data
}

export async function updateDocument(
  id: string,
  updates: {
    title?: string
    content?: string
    visibility?: 'public' | 'organization' | 'team' | 'private'
  }
) {
  const supabase = createClient()

  const { data, error } = await supabase
    .from('documents')
    .update(updates)
    .eq('id', id)
    .select()
    .single()

  if (error) {
    throw new Error(`Failed to update document: ${error.message}`)
  }

  return data
}

'use client'

import { useEffect, useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function DocumentList({ teamId }: { teamId?: string }) {
  const [documents, setDocuments] = useState<any[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    async function fetchDocuments() {
      let query = supabase
        .from('documents')
        .select('*')
        .order('created_at', { ascending: false })

      if (teamId) {
        query = query.eq('team_id', teamId)
      }

      const { data, error } = await query

      if (error) {
        console.error('Error fetching documents:', error)
      } else {
        setDocuments(data || [])
      }

      setLoading(false)
    }

    fetchDocuments()

    // リアルタイムサブスクリプション
    const channel = supabase
      .channel('documents_changes')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'documents',
        },
        (payload) => {
          fetchDocuments()
        }
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [teamId, supabase])

  if (loading) {
    return <div>Loading...</div>
  }

  return (
    <div className="space-y-4">
      {documents.map((doc) => (
        <div key={doc.id} className="p-4 border rounded">
          <h3 className="font-bold">{doc.title}</h3>
          <p className="text-sm text-gray-600">{doc.visibility}</p>
        </div>
      ))}
    </div>
  )
}

-- テストユーザーとしてログイン
SELECT set_config('request.jwt.claims', '{"sub":"test-user-id"}', true);

-- ドキュメントの閲覧テスト
SELECT * FROM documents;

-- 権限のないドキュメントが見えないことを確認

-- テーブルに設定されているポリシーを確認
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'documents';

-- organization_members
CREATE INDEX idx_organization_members_user_id ON organization_members(user_id);
CREATE INDEX idx_organization_members_org_id ON organization_members(organization_id);

-- team_members
CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_team_members_team_id ON team_members(team_id);

-- documents
CREATE INDEX idx_documents_creator_id ON documents(creator_id);
CREATE INDEX idx_documents_organization_id ON documents(organization_id);
CREATE INDEX idx_documents_team_id ON documents(team_id);
CREATE INDEX idx_documents_visibility ON documents(visibility);

EXPLAIN ANALYZE
SELECT * FROM documents WHERE visibility = 'public';

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  action TEXT NOT NULL,
  table_name TEXT NOT NULL,
  record_id UUID,
  old_value JSONB,
  new_value JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- トリガーで自動記録
CREATE OR REPLACE FUNCTION audit_documents()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_logs (user_id, action, table_name, record_id, old_value, new_value)
  VALUES (
    auth.uid(),
    TG_OP,
    'documents',
    NEW.id,
    row_to_json(OLD),
    row_to_json(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER documents_audit
AFTER INSERT OR UPDATE OR DELETE ON documents
FOR EACH ROW EXECUTE FUNCTION audit_documents();