-- Add project management functions

-- Function to create a project with account validation
CREATE OR REPLACE FUNCTION create_project(
  name TEXT,
  description TEXT,
  account_id UUID
) RETURNS json
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  new_project_id uuid;
  project_data json;
BEGIN
  -- Insert project
  INSERT INTO public.projects (name, description, account_id)
  VALUES (create_project.name, create_project.description, create_project.account_id)
  RETURNING project_id INTO new_project_id;
  
  -- Get the full project data
  SELECT json_build_object(
    'project_id', p.project_id,
    'name', p.name,
    'description', p.description,
    'account_id', p.account_id,
    'sandbox', p.sandbox,
    'created_at', p.created_at,
    'updated_at', p.updated_at
  ) INTO project_data
  FROM public.projects p
  WHERE p.project_id = new_project_id;
  
  RETURN project_data;
END;
$$;

-- Function to update a project
CREATE OR REPLACE FUNCTION update_project(
  project_id UUID,
  name TEXT,
  description TEXT
) RETURNS json
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  updated_project_data json;
BEGIN
  -- Update the project
  UPDATE public.projects
  SET 
    name = COALESCE(update_project.name, name),
    description = COALESCE(update_project.description, description),
    updated_at = now()
  WHERE project_id = update_project.project_id;
  
  -- Get the updated project data
  SELECT json_build_object(
    'project_id', p.project_id,
    'name', p.name,
    'description', p.description,
    'account_id', p.account_id,
    'sandbox', p.sandbox,
    'created_at', p.created_at,
    'updated_at', p.updated_at
  ) INTO updated_project_data
  FROM public.projects p
  WHERE p.project_id = update_project.project_id;
  
  RETURN updated_project_data;
END;
$$;

-- Function to update a project's sandbox information
CREATE OR REPLACE FUNCTION update_project_sandbox(
  project_id UUID,
  sandbox_data jsonb
) RETURNS json
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  updated_project_data json;
BEGIN
  -- Update the project sandbox data
  UPDATE public.projects
  SET 
    sandbox = sandbox_data,
    updated_at = now()
  WHERE project_id = update_project_sandbox.project_id;
  
  -- Get the updated project data
  SELECT json_build_object(
    'project_id', p.project_id,
    'name', p.name,
    'description', p.description,
    'account_id', p.account_id,
    'sandbox', p.sandbox,
    'created_at', p.created_at,
    'updated_at', p.updated_at
  ) INTO updated_project_data
  FROM public.projects p
  WHERE p.project_id = update_project_sandbox.project_id;
  
  RETURN updated_project_data;
END;
$$;