8cb45cbe03
- Commented out all Supabase services to facilitate testing with Supabase CLI. - Updated README to include Supabase directory in project structure. - Adjusted documentation for migration paths in Supabase Docker Compose guide. - Enhanced docker-compose-reset.sh to explicitly remove Supabase volumes and wait for migrations to complete.
601 lines
14 KiB
SQL
Executable File
601 lines
14 KiB
SQL
Executable File
-- Seed Data for USDA Vision Pecan Experiments System
|
|
-- This file populates the database with initial data
|
|
|
|
-- =============================================
|
|
-- 1. INSERT ROLES
|
|
-- =============================================
|
|
|
|
INSERT INTO public.roles (name, description) VALUES
|
|
('admin', 'System administrator with full access to all features'),
|
|
('conductor', 'Experiment conductor who can manage experiments and view all data'),
|
|
('analyst', 'Data analyst who can view and analyze experiment results'),
|
|
('data recorder', 'Data entry specialist who can record experiment measurements');
|
|
|
|
-- =============================================
|
|
-- 2. CREATE ADMIN USER
|
|
-- =============================================
|
|
|
|
-- Create admin user in auth.users
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
's.alireza.v@gmail.com',
|
|
crypt('admin123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
-- Create user profile
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Alireza', 'Vaezi', 'active'
|
|
FROM auth.users
|
|
WHERE email = 's.alireza.v@gmail.com'
|
|
;
|
|
|
|
-- Assign admin role
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
up.id
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 's.alireza.v@gmail.com'
|
|
AND r.name = 'admin'
|
|
;
|
|
|
|
-- =============================================
|
|
-- 3. CREATE ADDITIONAL USERS
|
|
-- =============================================
|
|
|
|
-- Create Claire Floyd (Conductor & Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'Ashlyn.Floyd@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Claire', 'Floyd', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'Ashlyn.Floyd@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'Ashlyn.Floyd@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
;
|
|
|
|
-- Create Bruna Dos-Santos (Conductor & Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'bkvsantos@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Bruna', 'Dos-Santos', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'bkvsantos@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'bkvsantos@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
;
|
|
|
|
-- Create Beni Rodriguez (Conductor & Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'Beni.Rodriguez@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Beni', 'Rodriguez', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'Beni.Rodriguez@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'Beni.Rodriguez@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
;
|
|
|
|
-- Create Brendan Surio (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'Brendan.Surio@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Brendan', 'Surio', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'Brendan.Surio@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'Brendan.Surio@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create William Mcconnell (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'William.McConnell@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'William', 'Mcconnell', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'William.McConnell@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'William.McConnell@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create Camille Deguzman (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'cpd08598@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Camille', 'Deguzman', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'cpd08598@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'cpd08598@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create Justin Hetzler (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'Justin.Hetzler@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Justin', 'Hetzler', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'Justin.Hetzler@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'Justin.Hetzler@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create Joshua Wilson (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'jdw58940@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Joshua', 'Wilson', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'jdw58940@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'jdw58940@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create Sydney Orlofsky (Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'Sydney.Orlofsky@uga.edu',
|
|
crypt('password123', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, first_name, last_name, status)
|
|
SELECT id, email, 'Sydney', 'Orlofsky', 'active'
|
|
FROM auth.users
|
|
WHERE email = 'Sydney.Orlofsky@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'Sydney.Orlofsky@uga.edu'
|
|
AND r.name IN ('conductor', 'data recorder')
|
|
|
|
;
|
|
|
|
-- Create engr-ugaif user (Conductor, Analyst & Data Recorder)
|
|
INSERT INTO auth.users (
|
|
instance_id,
|
|
id,
|
|
aud,
|
|
role,
|
|
email,
|
|
encrypted_password,
|
|
email_confirmed_at,
|
|
created_at,
|
|
updated_at,
|
|
confirmation_token,
|
|
email_change,
|
|
email_change_token_new,
|
|
recovery_token
|
|
) VALUES (
|
|
'00000000-0000-0000-0000-000000000000',
|
|
uuid_generate_v4(),
|
|
'authenticated',
|
|
'authenticated',
|
|
'engr-ugaif@uga.edu',
|
|
crypt('1048lab&2021', gen_salt('bf')),
|
|
NOW(),
|
|
NOW(),
|
|
NOW(),
|
|
'',
|
|
'',
|
|
'',
|
|
''
|
|
);
|
|
|
|
INSERT INTO public.user_profiles (id, email, status)
|
|
SELECT id, email, 'active'
|
|
FROM auth.users
|
|
WHERE email = 'engr-ugaif@uga.edu'
|
|
;
|
|
|
|
INSERT INTO public.user_roles (user_id, role_id, assigned_by)
|
|
SELECT
|
|
up.id,
|
|
r.id,
|
|
(SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')
|
|
FROM public.user_profiles up
|
|
CROSS JOIN public.roles r
|
|
WHERE up.email = 'engr-ugaif@uga.edu'
|
|
AND r.name IN ('conductor', 'analyst', 'data recorder')
|
|
;
|
|
|
|
-- =============================================
|
|
-- 4. CREATE MACHINE TYPES
|
|
-- =============================================
|
|
|
|
-- Insert default machine types
|
|
INSERT INTO public.machine_types (name, description, created_by) VALUES
|
|
('JC Cracker', 'JC Cracker machine with plate contact frequency and throughput parameters', (SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com')),
|
|
('Meyer Cracker', 'Meyer Cracker machine with motor speed and jig displacement parameters', (SELECT id FROM public.user_profiles WHERE email = 's.alireza.v@gmail.com'))
|
|
ON CONFLICT (name) DO NOTHING;
|
|
|
|
-- =============================================
|
|
-- 5. CREATE EXPERIMENT PHASES
|
|
-- =============================================
|
|
|
|
-- Create "Phase 2 of JC Experiments" phase
|
|
INSERT INTO public.experiment_phases (name, description, has_soaking, has_airdrying, has_cracking, has_shelling, cracking_machine_type_id, created_by)
|
|
SELECT
|
|
'Phase 2 of JC Experiments',
|
|
'Second phase of JC Cracker experiments for pecan processing optimization',
|
|
true,
|
|
true,
|
|
true,
|
|
false,
|
|
(SELECT id FROM public.machine_types WHERE name = 'JC Cracker'),
|
|
up.id
|
|
FROM public.user_profiles up
|
|
WHERE up.email = 's.alireza.v@gmail.com'
|
|
;
|
|
|
|
-- Create "Post Workshop Meyer Experiments" phase
|
|
INSERT INTO public.experiment_phases (name, description, has_soaking, has_airdrying, has_cracking, has_shelling, cracking_machine_type_id, created_by)
|
|
SELECT
|
|
'Post Workshop Meyer Experiments',
|
|
'Post workshop Meyer Cracker experiments for pecan processing optimization',
|
|
true,
|
|
true,
|
|
true,
|
|
false,
|
|
(SELECT id FROM public.machine_types WHERE name = 'Meyer Cracker'),
|
|
up.id
|
|
FROM public.user_profiles up
|
|
WHERE up.email = 's.alireza.v@gmail.com'
|
|
;
|