CREATE TABLE bitacora_produccion (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Batch Identification
  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,
  
  -- Quality Measurements
  ph NUMERIC,
  solidos_medicion_1 NUMERIC,
  solidos_medicion_2 NUMERIC,
  
  -- Organoleptic Properties
  apariencia TEXT,
  color TEXT,
  aroma TEXT,
  
  -- Metadata
  nombre_preparador TEXT,
  user_id UUID REFERENCES auth.users(id)
);

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

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

export const PRODUCT_STANDARDS: Record<string, { min?: number, max?: number }> = {
  "LIMLIM": { min: 1.4, max: 1.6 },
  "LIMVIO": { min: 1.3, max: 1.4 },
  "TRALIM": { min: 16.5, max: 17.5 },
  "COLGIN": { min: 9.0, max: 10.5 },
  // ... 100+ products with specification limits
}

export const PH_STANDARDS: Record<string, { min: number, max: number }> = {
  "TRALIM": { min: 7, max: 9 },
  "COLGIN": { min: 7, max: 9 },
  "JABPM": { min: 5, max: 6 },
  // ... pH ranges by product
}

export const APPEARANCE_STANDARDS: Record<string, string> = {
  "LIMLIM": "CRISTALINO",
  "LIMVIO": "CRISTALINO",
  "SUASUE": "OPACO",
  "SHAPAN": "APERLADO",
  // ... expected appearance by product
}

export const PARAMETER_APPLICABILITY: Record<string, { solidos: boolean, ph: boolean }> = {
  "LIMLIM": { solidos: true, ph: false },
  "TRALIM": { solidos: true, ph: true },
  "AROANII": { solidos: false, ph: false },
  // ... applicability matrix
}

export const SUCURSALES = [
  "ACTOPAN", "AMOZOC", "APIZACO", "ATITALAQUIA", "ATLACOMULCO",
  "CANCUN 1", "CANCUN 2", "CELAYA", "CHOLULA", "PACHUCA 1",
  // ... 40+ branches
]

export const SUCURSAL_ACRONYMS: Record<string, string> = {
  "AMOZOC": "AMO",
  "APIZACO": "APZ",
  "PACHUCA 1": "PAC1",
  // ... acronyms used in lot generation
}

export const PRODUCT_CATEGORIES = [
  { id: "aro-amb", name: "Aromatizante Ambiental", image: "..." },
  { id: "det-trastes", name: "Detergente liquido para trastes", image: "..." },
  { id: "det-ropa", name: "Detergente líquido para ropa", image: "..." },
  // ... 19 categories
]

export const CATEGORY_PRODUCTS: Record<string, string[]> = {
  "Aromatizante Ambiental": ["AROANII", "AROCAN", "AROCAR", ...],
  "Detergente liquido para trastes": ["TRABLU", "TRALIM", ...],
  // ... products grouped by category
}

export const PRODUCT_GROUPS = [
  {
    title: "Cuidado del Hogar",
    color: "text-orange-600",
    icon: "🏠",
    ids: ["aro-amb", "esp-hogar", "lim-mult", "det-trastes", ...]
  },
  {
    title: "Cuidado Personal",
    color: "text-emerald-600",
    icon: "💆",
    ids: ["crem-corp", "enj-cap", "jab-manos", "shampoo"]
  },
  // ... 6 product groups
]

const avgSolids = 
  (solidos_medicion_1 + solidos_medicion_2) / 2

type ConformityLevel = 'conforme' | 'semi-conforme' | 'no-conforme' | 'na'

interface AnalysisResult {
  isConform: boolean
  failedParams: string[]
  phStatus: ConformityLevel
  solidsStatus: ConformityLevel
  appearanceStatus: ConformityLevel
  overallStatus: ConformityLevel
}

// Specification limits (red lines)
const specMin = PRODUCT_STANDARDS[product].min
const specMax = PRODUCT_STANDARDS[product].max

// Tolerance limits (yellow lines) - 5% relative error
const warnMin = specMin * 0.95
const warnMax = specMax * 1.05

// Classification
if (avgSolids >= specMin && avgSolids <= specMax) {
  status = 'conforme'  // Between red lines
} else if (
  (avgSolids >= warnMin && avgSolids < specMin) ||
  (avgSolids > specMax && avgSolids <= warnMax)
) {
  status = 'semi-conforme'  // Between red and yellow lines
} else {
  status = 'no-conforme'  // Outside yellow lines
}

interface EnrichedRecord {
  id: number
  lote_producto: string
  codigo_producto: string
  sucursal: string
  familia_producto: string
  fecha_fabricacion: string
  nombre_preparador: string
  ph: number | null
  solidos_medicion_1: number | null
  solidos_medicion_2: number | null
  solidos_promedio: number | null  // Computed
  apariencia: string
  analysis: AnalysisResult  // Computed conformity
  tamano_lote: number
}

-- User's own records (most common query)
CREATE INDEX idx_bitacora_user_id ON bitacora_produccion(user_id);

-- Date-based filtering
CREATE INDEX idx_bitacora_fecha ON bitacora_produccion(fecha_fabricacion DESC);

-- Branch filtering
CREATE INDEX idx_bitacora_sucursal ON bitacora_produccion(sucursal);

-- Product filtering
CREATE INDEX idx_bitacora_producto ON bitacora_produccion(codigo_producto);

-- Composite index for common filters
CREATE INDEX idx_bitacora_composite 
  ON bitacora_produccion(sucursal, fecha_fabricacion DESC, user_id);