40 lines
1.1 KiB
PL/PgSQL
40 lines
1.1 KiB
PL/PgSQL
-- 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();
|