set check_function_bodies = off;

CREATE OR REPLACE FUNCTION autosend."getDailyAutoSendCoaches"(_id uuid, "_dailyAmount" integer)
 RETURNS TABLE(id uuid, name text, timezone text, "collegeId" uuid)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT autosend.weekly_coaches.coach, coaches.name, colleges.timezone, colleges.id
    FROM autosend.weekly_coaches
    JOIN autosend.profile ON profile."campaignId" = weekly_coaches.campaign
    JOIN autosend.coaches "autoSendCoaches" ON "autoSendCoaches".id = autosend.weekly_coaches.coach AND "autoSendCoaches".status = 'Pending'
    JOIN coaches ON coaches.id = "autoSendCoaches".coach AND coaches.email IS NOT NULL
    JOIN sports ON sports.id = coaches.sport 
    JOIN colleges ON colleges.id = sports.college
    WHERE "autoSendCoaches".campaign = _id 
    ORDER BY RANDOM()
    LIMIT ("_dailyAmount");
  END;
$function$
;

create or replace view "autosend"."profile" as  SELECT autosend.id,
    campaign.id AS "campaignId",
    "existingCampaign".id AS "existingCampaignId",
    COALESCE(campaign.status, 'Inactive'::autosend."CampaignStatus") AS status,
    autosend."user",
    autosend.profile,
    profile.sport AS "profileSport",
    autosend.template,
    ( SELECT autosend."hasAutoSendTemplate"(autosend.id) AS "hasAutoSendTemplate") AS "hasTemplate",
    campaign.preferences,
    campaign."currentWeek",
    campaign."weeklyLimit",
    campaign."updatedAt",
    autosend.timezone,
    autosend."createdAt",
    campaign."createdAt" AS "campaignCreatedAt"
   FROM (((autosend.autosend autosend
     JOIN profiles profile ON ((profile.id = autosend.profile)))
     LEFT JOIN LATERAL ( SELECT c.id,
            c.autosend,
            c.preferences,
            c."totalCoaches",
            c."weeksToComplete",
            c."currentWeek",
            c."weeklyLimit",
            c."weeklyEmailsSent",
            c."totalEmailsSent",
            c."createdAt",
            c."updatedAt",
            c.status
           FROM autosend.campaigns c
          WHERE (c.autosend = autosend.id)
          ORDER BY c."createdAt" DESC
         LIMIT 1) campaign ON (true))
     LEFT JOIN LATERAL ( SELECT c.id,
            c.autosend,
            c.preferences,
            c."totalCoaches",
            c."weeksToComplete",
            c."currentWeek",
            c."weeklyLimit",
            c."weeklyEmailsSent",
            c."totalEmailsSent",
            c."createdAt",
            c."updatedAt",
            c.status
           FROM autosend.campaigns c
          WHERE (c.autosend = autosend.id)
          ORDER BY c."createdAt" DESC
         OFFSET 1
         LIMIT 1) "existingCampaign" ON (true));



alter table "messaging"."messages" alter column status type "public"."MessageStatus" using status::text::"public"."MessageStatus";

alter table "messaging"."messages" alter column type type "public"."MessageType" using type::text::"public"."MessageType";


alter type "public"."MessageStatus" rename to "MessageStatus__old_version_to_be_dropped";

create type "public"."MessageStatus" as enum ('Pending', 'Delivered', 'Failed', 'Rescheduled', 'Retrying');

alter type "public"."MessageType" rename to "MessageType__old_version_to_be_dropped";

create type "public"."MessageType" as enum ('Manually Sent', 'Automatically Sent', 'Follow Up', 'System');

alter table "public"."messages" alter column type type "public"."MessageType" using type::text::"public"."MessageType";

drop type "public"."MessageStatus__old_version_to_be_dropped";

drop type "public"."MessageType__old_version_to_be_dropped";

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.create_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
  BEGIN
    INSERT INTO public.users (id, email, image, verified)
    VALUES (
      new.id, 
      new.email, 
      new.raw_user_meta_data ->> 'picture', 
      CASE
        WHEN new.raw_app_meta_data IS NOT NULL AND new.raw_app_meta_data ->> 'provider' = 'google' THEN TRUE
        ELSE FALSE
      END
    );
    RETURN new;
  END;
$function$
;