-- data/schemas/users.sql
CREATE TABLE public.users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  
  -- Member-specific fields
  member_tier TEXT,
  member_since TIMESTAMP WITH TIME ZONE DEFAULT now(),
  
  constraint username_length check (char_length(full_name) >= 3)
);

-- Row Level Security Policies
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public profiles are viewable by everyone."
  ON users FOR SELECT
  USING ( true );

CREATE POLICY "Users can insert their own profile."
  ON users FOR INSERT
  WITH CHECK ( auth.uid() = id );

CREATE POLICY "Users can update own profile."
  ON users FOR UPDATE
  USING ( auth.uid() = id );

// data/models/user.model.ts
export interface User {
  id: string;
  email: string;
  fullName: string | null;
  createdAt: string;
  updatedAt: string;
  memberTier: string | null;
  memberSince: string;
}

// Helper functions for type conversion
export function fromDbUser(dbUser: any): User {
  return {
    id: dbUser.id,
    email: dbUser.email,
    fullName: dbUser.full_name,
    createdAt: dbUser.created_at,
    updatedAt: dbUser.updated_at,
    memberTier: dbUser.member_tier,
    memberSince: dbUser.member_since,
  };
}

export function toDbUser(user: User): any {
  return {
    id: user.id,
    email: user.email,
    full_name: user.fullName,
    created_at: user.createdAt,
    updated_at: user.updatedAt,
    member_tier: user.memberTier,
    member_since: user.memberSince,
  };
}

import { getSupabaseClient } from '@repo/supabase-client';

async function getProducts() {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .eq('product_type', 'candle')
    .order('created_at', { ascending: false })
    .limit(10);
    
  if (error) {
    console.error('Error fetching products:', error);
    return [];
  }
  
  return data;
}

// Fetch data
const { data, error } = await supabase
  .from('products')
  .select('*')
  .eq('product_type', 'candle')
  .order('created_at', { ascending: false })
  .limit(10)

// Insert data
const { data, error } = await supabase
  .from('profiles')
  .insert([
    { username: 'mnky_lover', full_name: 'MOOD MNKY Fan' }
  ])
  .select()

// Update data
const { data, error } = await supabase
  .from('products')
  .update({ stock_quantity: 15 })
  .eq('id', '123e4567-e89b-12d3-a456-426614174000')
  .select()

// Delete data
const { error } = await supabase
  .from('content')
  .delete()
  .eq('id', '123e4567-e89b-12d3-a456-426614174000')

// Subscribe to changes
const subscription = supabase
  .channel('table-db-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'conversation_history',
      filter: `member_id=eq.${userId}`
    },
    (payload) => {
      console.log('Change received!', payload)
      // Update UI with new message
    }
  )
  .subscribe()

// Cleanup subscription
onUnmount(() => {
  supabase.removeChannel(subscription)
})

// Upload file
const { data, error } = await supabase.storage
  .from('product-images')
  .upload('public/product-123.jpg', imageFile, {
    cacheControl: '3600',
    upsert: false
  })

// Get public URL
const { data } = supabase.storage
  .from('product-images')
  .getPublicUrl('public/product-123.jpg')

// Download file
const { data, error } = await supabase.storage
  .from('product-images')
  .download('public/product-123.jpg')

import { S3Client, PutObjectCommand, GetObjectCommand } from '@aws-sdk/client-s3';

// Initialize S3 client with Supabase credentials
const s3Client = new S3Client({
  endpoint: process.env.SUPABASE_S3_URL,
  region: process.env.SUPABASE_S3_REGION,
  credentials: {
    accessKeyId: process.env.SUPABASE_S3_ACCESS_KEY_ID,
    secretAccessKey: process.env.SUPABASE_S3_SECRET_ACCESS_KEY,
  },
  forcePathStyle: true, // Required for Supabase Storage
});

// Upload a file
const putCommand = new PutObjectCommand({
  Bucket: 'product-images',
  Key: 'public/product-123.jpg',
  Body: fileBuffer,
  ContentType: 'image/jpeg',
});
await s3Client.send(putCommand);

// Download a file
const getCommand = new GetObjectCommand({
  Bucket: 'product-images',
  Key: 'public/product-123.jpg',
});
const response = await s3Client.send(getCommand);
const fileContent = await response.Body.transformToByteArray();

-- Example: Products table security
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;

-- Anyone can view active products
CREATE POLICY "Anyone can view active products"
  ON products FOR SELECT
  USING (is_active = true);

-- Only admins can insert/update products
CREATE POLICY "Only admins can insert products"
  ON products FOR INSERT
  USING (auth.jwt() -> 'app_metadata' ->> 'role' = 'admin');

CREATE POLICY "Only admins can update products"
  ON products FOR UPDATE
  USING (auth.jwt() -> 'app_metadata' ->> 'role' = 'admin');

// Client-side validation example
function validateProduct(product) {
  const errors = {};
  
  if (!product.name) errors.name = 'Product name is required';
  if (!product.price || product.price <= 0) errors.price = 'Valid price is required';
  if (!product.product_type) errors.product_type = 'Product type is required';
  
  return {
    isValid: Object.keys(errors).length === 0,
    errors
  };
}

// Server-side validation with Edge Functions
export async function validateProductServerSide(product) {
  // More complex validation logic
  // ...
  
  // Database constraints through schema design
  // ...
}