Database Schema

CREATE TABLE bitacora_produccion (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  lote_producto TEXT NOT NULL,
  codigo_producto TEXT NOT NULL,
  sucursal TEXT NOT NULL,
  familia_producto TEXT,
  categoria_producto TEXT,
  fecha_fabricacion DATE NOT NULL,
  tamano_lote NUMERIC NOT NULL,
  ph NUMERIC,
  solidos_medicion_1 NUMERIC,
  temp_med1 NUMERIC,
  solidos_medicion_2 NUMERIC,
  temp_med2 NUMERIC,
  viscosidad_seg NUMERIC,
  temperatura NUMERIC,
  apariencia TEXT NOT NULL,
  color TEXT NOT NULL,
  aroma TEXT NOT NULL,
  contaminacion_microbiologica TEXT NOT NULL,
  observaciones TEXT,
  nombre_preparador TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE
);

-- Enable Row Level Security
ALTER TABLE bitacora_produccion ENABLE ROW LEVEL SECURITY;

-- Create index for faster queries
CREATE INDEX idx_bitacora_user_id ON bitacora_produccion(user_id);
CREATE INDEX idx_bitacora_fecha ON bitacora_produccion(fecha_fabricacion DESC);
CREATE INDEX idx_bitacora_producto ON bitacora_produccion(codigo_producto);
CREATE INDEX idx_bitacora_sucursal ON bitacora_produccion(sucursal);

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  nombre TEXT,
  area TEXT,
  puesto TEXT,
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Users can view their own records
CREATE POLICY "Users can view own records"
ON bitacora_produccion FOR SELECT
USING (auth.uid() = user_id);

-- Admins can view all records
CREATE POLICY "Admins can view all records"
ON bitacora_produccion FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
);

-- Users can insert their own records
CREATE POLICY "Users can insert own records"
ON bitacora_produccion FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can update their own records
CREATE POLICY "Users can update own records"
ON bitacora_produccion FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Admins can update all records
CREATE POLICY "Admins can update all records"
ON bitacora_produccion FOR UPDATE
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
)
WITH CHECK (true);

-- Only admins can delete records
CREATE POLICY "Admins can delete records"
ON bitacora_produccion FOR DELETE
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
);

-- Users can view their own profile
CREATE POLICY "Users can view own profile"
ON profiles FOR SELECT
USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles"
ON profiles FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM profiles p
    WHERE p.id = auth.uid()
    AND p.is_admin = true
  )
);

-- Users can update their own profile (except is_admin)
CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Only admins can change roles
CREATE POLICY "Admins can update all profiles"
ON profiles FOR UPDATE
USING (
  EXISTS (
    SELECT 1 FROM profiles p
    WHERE p.id = auth.uid()
    AND p.is_admin = true
  )
)
WITH CHECK (true);

NEXT_PUBLIC_SUPABASE_URL="https://your-project.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-anon-key"

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient(supabaseUrl, supabaseKey)