usda-vision/supabase/migrations/00014_experiments_with_reps_and_phases_view.sql

-- View: Experiments with All Repetitions and Phase Parameters
-- This view provides a comprehensive view of experiments with all their repetitions
-- and all phase execution parameters (soaking, airdrying, cracking, shelling)

CREATE OR REPLACE VIEW public.experiments_with_all_reps_and_phases AS
SELECT 
    -- Experiment fields
    e.id as experiment_id,
    e.experiment_number,
    e.reps_required,
    e.weight_per_repetition_lbs,
    e.results_status,
    e.completion_status,
    e.phase_id,
    e.created_at as experiment_created_at,
    e.updated_at as experiment_updated_at,
    e.created_by as experiment_created_by,
    
    -- Phase information
    ep.name as phase_name,
    ep.description as phase_description,
    ep.has_soaking,
    ep.has_airdrying,
    ep.has_cracking,
    ep.has_shelling,
    ep.cracking_machine_type_id as phase_cracking_machine_type_id,
    
    -- Repetition fields
    er.id as repetition_id,
    er.repetition_number,
    er.status as repetition_status,
    er.scheduled_date,
    er.created_at as repetition_created_at,
    er.updated_at as repetition_updated_at,
    er.created_by as repetition_created_by,
    
    -- Soaking phase execution
    soaking_e.id as soaking_execution_id,
    soaking_e.scheduled_start_time as soaking_scheduled_start,
    soaking_e.actual_start_time as soaking_actual_start,
    soaking_e.soaking_duration_minutes,
    soaking_e.scheduled_end_time as soaking_scheduled_end,
    soaking_e.actual_end_time as soaking_actual_end,
    soaking_e.status as soaking_status,
    
    -- Airdrying phase execution
    airdrying_e.id as airdrying_execution_id,
    airdrying_e.scheduled_start_time as airdrying_scheduled_start,
    airdrying_e.actual_start_time as airdrying_actual_start,
    airdrying_e.duration_minutes as airdrying_duration_minutes,
    airdrying_e.scheduled_end_time as airdrying_scheduled_end,
    airdrying_e.actual_end_time as airdrying_actual_end,
    airdrying_e.status as airdrying_status,
    
    -- Cracking phase execution
    cracking_e.id as cracking_execution_id,
    cracking_e.scheduled_start_time as cracking_scheduled_start,
    cracking_e.actual_start_time as cracking_actual_start,
    cracking_e.scheduled_end_time as cracking_scheduled_end,
    cracking_e.actual_end_time as cracking_actual_end,
    cracking_e.machine_type_id as cracking_machine_type_id,
    cracking_e.status as cracking_status,
    mt.name as machine_type_name,
    
    -- Shelling phase execution
    shelling_e.id as shelling_execution_id,
    shelling_e.scheduled_start_time as shelling_scheduled_start,
    shelling_e.actual_start_time as shelling_actual_start,
    shelling_e.scheduled_end_time as shelling_scheduled_end,
    shelling_e.actual_end_time as shelling_actual_end,
    shelling_e.status as shelling_status
    
FROM public.experiments e
LEFT JOIN public.experiment_phases ep ON e.phase_id = ep.id
LEFT JOIN public.experiment_repetitions er ON er.experiment_id = e.id
LEFT JOIN public.experiment_phase_executions soaking_e 
    ON soaking_e.repetition_id = er.id AND soaking_e.phase_type = 'soaking'
LEFT JOIN public.experiment_phase_executions airdrying_e 
    ON airdrying_e.repetition_id = er.id AND airdrying_e.phase_type = 'airdrying'
LEFT JOIN public.experiment_phase_executions cracking_e 
    ON cracking_e.repetition_id = er.id AND cracking_e.phase_type = 'cracking'
LEFT JOIN public.experiment_phase_executions shelling_e 
    ON shelling_e.repetition_id = er.id AND shelling_e.phase_type = 'shelling'
LEFT JOIN public.machine_types mt ON cracking_e.machine_type_id = mt.id
ORDER BY e.experiment_number, er.repetition_number;

-- Grant permissions
GRANT SELECT ON public.experiments_with_all_reps_and_phases TO authenticated;

-- Function: Get experiment with all repetitions and phase parameters
-- This function returns a JSON structure with experiment and all its repetitions
CREATE OR REPLACE FUNCTION public.get_experiment_with_reps_and_phases(p_experiment_id UUID)
RETURNS TABLE (
    experiment_id UUID,
    experiment_number INTEGER,
    phase_name TEXT,
    repetitions JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        e.id,
        e.experiment_number,
        ep.name,
        COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'repetition_id', er.id,
                    'repetition_number', er.repetition_number,
                    'status', er.status,
                    'scheduled_date', er.scheduled_date,
                    'soaking', jsonb_build_object(
                        'scheduled_start', soaking_e.scheduled_start_time,
                        'actual_start', soaking_e.actual_start_time,
                        'duration_minutes', soaking_e.soaking_duration_minutes,
                        'scheduled_end', soaking_e.scheduled_end_time,
                        'actual_end', soaking_e.actual_end_time,
                        'status', soaking_e.status
                    ),
                    'airdrying', jsonb_build_object(
                        'scheduled_start', airdrying_e.scheduled_start_time,
                        'actual_start', airdrying_e.actual_start_time,
                        'duration_minutes', airdrying_e.duration_minutes,
                        'scheduled_end', airdrying_e.scheduled_end_time,
                        'actual_end', airdrying_e.actual_end_time,
                        'status', airdrying_e.status
                    ),
                    'cracking', jsonb_build_object(
                        'scheduled_start', cracking_e.scheduled_start_time,
                        'actual_start', cracking_e.actual_start_time,
                        'scheduled_end', cracking_e.scheduled_end_time,
                        'actual_end', cracking_e.actual_end_time,
                        'machine_type_id', cracking_e.machine_type_id,
                        'machine_type_name', mt.name,
                        'status', cracking_e.status
                    ),
                    'shelling', jsonb_build_object(
                        'scheduled_start', shelling_e.scheduled_start_time,
                        'actual_start', shelling_e.actual_start_time,
                        'scheduled_end', shelling_e.scheduled_end_time,
                        'actual_end', shelling_e.actual_end_time,
                        'status', shelling_e.status
                    )
                )
                ORDER BY er.repetition_number
            ),
            '[]'::jsonb
        ) as repetitions
    FROM public.experiments e
    LEFT JOIN public.experiment_phases ep ON e.phase_id = ep.id
    LEFT JOIN public.experiment_repetitions er ON er.experiment_id = e.id
    LEFT JOIN public.experiment_phase_executions soaking_e 
        ON soaking_e.repetition_id = er.id AND soaking_e.phase_type = 'soaking'
    LEFT JOIN public.experiment_phase_executions airdrying_e 
        ON airdrying_e.repetition_id = er.id AND airdrying_e.phase_type = 'airdrying'
    LEFT JOIN public.experiment_phase_executions cracking_e 
        ON cracking_e.repetition_id = er.id AND cracking_e.phase_type = 'cracking'
    LEFT JOIN public.experiment_phase_executions shelling_e 
        ON shelling_e.repetition_id = er.id AND shelling_e.phase_type = 'shelling'
    LEFT JOIN public.machine_types mt ON cracking_e.machine_type_id = mt.id
    WHERE e.id = p_experiment_id
    GROUP BY e.id, e.experiment_number, ep.name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION public.get_experiment_with_reps_and_phases(UUID) TO authenticated;

-- Update the existing experiments_with_phases view to use unified table
CREATE OR REPLACE VIEW public.experiments_with_phases AS
SELECT 
    e.id,
    e.experiment_number,
    e.reps_required,
    e.weight_per_repetition_lbs,
    e.results_status,
    e.completion_status,
    e.phase_id,
    e.created_at,
    e.updated_at,
    e.created_by,
    ep.name as phase_name,
    ep.description as phase_description,
    ep.has_soaking,
    ep.has_airdrying,
    ep.has_cracking,
    ep.has_shelling,
    er.id as first_repetition_id,
    er.repetition_number as first_repetition_number,
    soaking_e.id as soaking_id,
    soaking_e.scheduled_start_time as soaking_scheduled_start,
    soaking_e.actual_start_time as soaking_actual_start,
    soaking_e.soaking_duration_minutes,
    soaking_e.scheduled_end_time as soaking_scheduled_end,
    soaking_e.actual_end_time as soaking_actual_end,
    airdrying_e.id as airdrying_id,
    airdrying_e.scheduled_start_time as airdrying_scheduled_start,
    airdrying_e.actual_start_time as airdrying_actual_start,
    airdrying_e.duration_minutes as airdrying_duration,
    airdrying_e.scheduled_end_time as airdrying_scheduled_end,
    airdrying_e.actual_end_time as airdrying_actual_end,
    cracking_e.id as cracking_id,
    cracking_e.scheduled_start_time as cracking_scheduled_start,
    cracking_e.actual_start_time as cracking_actual_start,
    cracking_e.actual_end_time as cracking_actual_end,
    mt.name as machine_type_name,
    shelling_e.id as shelling_id,
    shelling_e.scheduled_start_time as shelling_scheduled_start,
    shelling_e.actual_start_time as shelling_actual_start,
    shelling_e.actual_end_time as shelling_actual_end
FROM public.experiments e
LEFT JOIN public.experiment_phases ep ON e.phase_id = ep.id
LEFT JOIN LATERAL (
    SELECT id, repetition_number 
    FROM public.experiment_repetitions 
    WHERE experiment_id = e.id 
    ORDER BY repetition_number 
    LIMIT 1
) er ON true
LEFT JOIN public.experiment_phase_executions soaking_e 
    ON soaking_e.repetition_id = er.id AND soaking_e.phase_type = 'soaking'
LEFT JOIN public.experiment_phase_executions airdrying_e 
    ON airdrying_e.repetition_id = er.id AND airdrying_e.phase_type = 'airdrying'
LEFT JOIN public.experiment_phase_executions cracking_e 
    ON cracking_e.repetition_id = er.id AND cracking_e.phase_type = 'cracking'
LEFT JOIN public.experiment_phase_executions shelling_e 
    ON shelling_e.repetition_id = er.id AND shelling_e.phase_type = 'shelling'
LEFT JOIN public.machine_types mt ON cracking_e.machine_type_id = mt.id;

-- Update repetitions_with_phases view to use unified table
CREATE OR REPLACE VIEW public.repetitions_with_phases AS
SELECT 
    er.id,
    er.experiment_id,
    er.repetition_number,
    er.status,
    er.created_at,
    er.updated_at,
    er.created_by,
    e.experiment_number,
    e.phase_id,
    e.weight_per_repetition_lbs,
    ep.name as phase_name,
    ep.has_soaking,
    ep.has_airdrying,
    ep.has_cracking,
    ep.has_shelling,
    soaking_e.scheduled_start_time as soaking_scheduled_start,
    soaking_e.actual_start_time as soaking_actual_start,
    soaking_e.soaking_duration_minutes,
    soaking_e.scheduled_end_time as soaking_scheduled_end,
    soaking_e.actual_end_time as soaking_actual_end,
    airdrying_e.scheduled_start_time as airdrying_scheduled_start,
    airdrying_e.actual_start_time as airdrying_actual_start,
    airdrying_e.duration_minutes as airdrying_duration,
    airdrying_e.scheduled_end_time as airdrying_scheduled_end,
    airdrying_e.actual_end_time as airdrying_actual_end,
    cracking_e.scheduled_start_time as cracking_scheduled_start,
    cracking_e.actual_start_time as cracking_actual_start,
    cracking_e.actual_end_time as cracking_actual_end,
    mt.name as machine_type_name,
    shelling_e.scheduled_start_time as shelling_scheduled_start,
    shelling_e.actual_start_time as shelling_actual_start,
    shelling_e.actual_end_time as shelling_actual_end
FROM public.experiment_repetitions er
JOIN public.experiments e ON er.experiment_id = e.id
LEFT JOIN public.experiment_phases ep ON e.phase_id = ep.id
LEFT JOIN public.experiment_phase_executions soaking_e 
    ON er.id = soaking_e.repetition_id AND soaking_e.phase_type = 'soaking'
LEFT JOIN public.experiment_phase_executions airdrying_e 
    ON er.id = airdrying_e.repetition_id AND airdrying_e.phase_type = 'airdrying'
LEFT JOIN public.experiment_phase_executions cracking_e 
    ON er.id = cracking_e.repetition_id AND cracking_e.phase_type = 'cracking'
LEFT JOIN public.experiment_phase_executions shelling_e 
    ON er.id = shelling_e.repetition_id AND shelling_e.phase_type = 'shelling'
LEFT JOIN public.machine_types mt ON cracking_e.machine_type_id = mt.id;