-- SQL to create core tables for SmartScan Probe Track
CREATE OR REPLACE FUNCTION public.create_core_tables()
RETURNS void
LANGUAGE sql
AS $$
  -- Users table
  CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    can_calibrate BOOLEAN NOT NULL DEFAULT false,
    can_review BOOLEAN NOT NULL DEFAULT false,
    signature_image TEXT
  );

  -- Probe Models table
  CREATE TABLE IF NOT EXISTS probe_models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_name TEXT UNIQUE NOT NULL,
    specifications JSONB
  );

  -- Probes table
  CREATE TABLE IF NOT EXISTS probes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id UUID REFERENCES probe_models(id),
    serial_number TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    retired_at TIMESTAMPTZ
  );

  -- Create indexes
  CREATE INDEX IF NOT EXISTS idx_probes_model_id ON probes(model_id);
  CREATE INDEX IF NOT EXISTS idx_probes_serial_number ON probes(serial_number);
$$;

-- Execute the function to create tables
SELECT public.create_core_tables();