PostgREST

-- === PostgreSQL 스키마 정의 ===
-- 테이블 생성
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    user_id INT,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Row Level Security 활성화
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews ENABLE ROW LEVEL SECURITY;

-- 읽기 정책: 모든 사용자가 읽기 가능
CREATE POLICY "Products are viewable by everyone"
    ON products FOR SELECT
    USING (true);

-- 쓰기 정책: 인증된 사용자만 리뷰 작성 가능
CREATE POLICY "Users can insert their own reviews"
    ON reviews FOR INSERT
    WITH CHECK (auth.uid() = user_id);

-- API를 위한 뷰 생성
CREATE VIEW product_with_rating AS
SELECT
    p.*,
    COALESCE(AVG(r.rating), 0) AS avg_rating,
    COUNT(r.id) AS review_count
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id;

// === JavaScript/TypeScript 클라이언트 사용 예시 ===

// 기본 CRUD 작업
const API_URL = 'https://your-project.supabase.co/rest/v1';
const API_KEY = 'your-anon-key';

const headers = {
    'apikey': API_KEY,
    'Authorization': `Bearer ${API_KEY}`,
    'Content-Type': 'application/json',
    'Prefer': 'return=representation' // INSERT/UPDATE 후 결과 반환
};

// GET - 전체 상품 조회
const getAllProducts = async () => {
    const response = await fetch(`${API_URL}/products`, { headers });
    return response.json();
};

// GET - 필터링, 정렬, 페이지네이션
const getFilteredProducts = async () => {
    const params = new URLSearchParams({
        'select': 'id,name,price,category', // 컬럼 선택
        'category': 'eq.electronics',        // 필터: category = 'electronics'
        'price': 'lte.1000',                 // 필터: price <= 1000
        'order': 'price.asc',                // 정렬: 가격 오름차순
        'limit': '10',                       // 페이지 크기
        'offset': '0'                        // 시작 위치
    });

    const response = await fetch(`${API_URL}/products?${params}`, { headers });
    return response.json();
};

// GET - 관계 데이터 임베딩 (JOIN)
const getProductsWithReviews = async () => {
    // 상품과 함께 리뷰도 가져오기
    const response = await fetch(
        `${API_URL}/products?select=*,reviews(*)`,
        { headers }
    );
    return response.json();
};

// GET - 뷰 조회 (평균 평점 포함)
const getProductsWithRating = async () => {
    const response = await fetch(
        `${API_URL}/product_with_rating?order=avg_rating.desc`,
        { headers }
    );
    return response.json();
};

// POST - 상품 추가
const createProduct = async (product: {
    name: string;
    price: number;
    category: string;
}) => {
    const response = await fetch(`${API_URL}/products`, {
        method: 'POST',
        headers,
        body: JSON.stringify(product)
    });
    return response.json();
};

// PATCH - 상품 수정
const updateProduct = async (id: number, updates: Partial<Product>) => {
    const response = await fetch(`${API_URL}/products?id=eq.${id}`, {
        method: 'PATCH',
        headers,
        body: JSON.stringify(updates)
    });
    return response.json();
};

// DELETE - 상품 삭제
const deleteProduct = async (id: number) => {
    await fetch(`${API_URL}/products?id=eq.${id}`, {
        method: 'DELETE',
        headers
    });
};

// === Supabase JavaScript 클라이언트 (더 간편한 방식) ===
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
    'https://your-project.supabase.co',
    'your-anon-key'
);

// 동일한 작업을 더 간편하게
const { data, error } = await supabase
    .from('products')
    .select('*, reviews(*)')
    .eq('category', 'electronics')
    .lte('price', 1000)
    .order('price', { ascending: true })
    .range(0, 9);

// 실시간 구독
const subscription = supabase
    .channel('products-changes')
    .on('postgres_changes',
        { event: '*', schema: 'public', table: 'products' },
        (payload) => {
            console.log('변경 감지:', payload);
        }
    )
    .subscribe();