SET check_function_bodies = off;
 
CREATE OR REPLACE FUNCTION autosend."clearDailyCoaches"(_id uuid DEFAULT NULL::uuid)
 RETURNS INTEGER
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'autosend'
AS $function$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM autosend.daily_coaches
    WHERE (_id IS NULL OR autosend.daily_coaches.autosend = _id);
 
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
 
    RETURN deleted_count;
END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getAutoSendCoachesFromPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
      FROM autosend.autosend
      JOIN user_profile profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id
      WHERE profiles.id = _id AND coaches.email IS NOT NULL;
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getAutoSendMatchedPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS jsonb
 LANGUAGE plpgsql
 SET search_path TO 'public'
AS $function$
  DECLARE
    result JSONB;
 
  BEGIN
    WITH "matchedCoaches" AS (
      SELECT colleges.id, colleges.name, colleges.city, colleges.state, coaches.name AS "coachName"
      FROM autosend.autosend
      JOIN user_profile profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id
      WHERE autosend.id = _id 
    ),
 
    "coachesCount" AS (
      SELECT COUNT(*) AS "totalCoaches" FROM "matchedCoaches"
    ),
 
    "limitedSchools" AS (
      SELECT DISTINCT id, name, city, state
      FROM "matchedCoaches"
      LIMIT 5
    )
 
 
    SELECT JSON_BUILD_OBJECT(
      'schools', (SELECT JSON_AGG(coach) FROM (SELECT * FROM "limitedSchools") coach),
      'totalSchools', (SELECT "totalCoaches" FROM "coachesCount")
    ) INTO result;
 
    RETURN result;
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getAutoSendOverview"("_autoSendId" uuid)
 RETURNS TABLE("allMessagesSent" bigint, "totalRepliesReceived" bigint, "replyRate" bigint)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT
      COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id),
      COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id),
      COALESCE((COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id) / NULLIF(COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id), 0)), 0)
    FROM autosend.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN conversations ON conversations.creator = 'Athlete' AND conversations.profile = profiles.id AND conversations."isAutomated" = TRUE
    JOIN messages ON messages.conversation = conversations.id
    WHERE autosend.id = "_autoSendId";
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getAutoSendTemplateCoach"(_id uuid)
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "autoSendProfile" AS (
        SELECT user_profile.id, sport
        FROM autosend.autosend
        JOIN user_profile ON user_profile.id = autosend.profile  
        WHERE autosend.id = _id
      ),
 
      "preferredCoach" AS (
        SELECT coach.id, coach.name, coach.title, coach."collegeId", coach."collegeName"
        FROM autosend.autosend
        JOIN user_profile profiles ON profiles.id = autosend.profile
        JOIN autosend."getAutoSendCoachesFromPreferences"(profiles.id, 
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'states')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'affiliations')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'divisions'))) coach ON TRUE
        WHERE autosend.id = _id
      ),
 
      "fallBackCoach" AS (
        SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
        FROM autosend.autosend
        JOIN profiles ON profiles.id = autosend.profile
        JOIN sports ON sports.sport = profiles.sport
        JOIN coaches ON coaches.sport = sports.id
        JOIN colleges ON colleges.id = sports.college
      )
 
      SELECT *
      FROM (
        SELECT *
        FROM "preferredCoach"
        ORDER BY random()
        LIMIT 1
      ) PC
 
      UNION ALL
 
      SELECT *
      FROM "fallBackCoach"
      WHERE NOT EXISTS (SELECT 1 FROM "preferredCoach")
      LIMIT 1;
 
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getAutoSendWeeklyCoaches"(_id uuid, _search text DEFAULT NULL::text, _offset INTEGER DEFAULT 0, "_isAllCoaches" BOOLEAN DEFAULT NULL::BOOLEAN, "_isToMessage" BOOLEAN DEFAULT NULL::BOOLEAN, "_isMessaged" BOOLEAN DEFAULT NULL::BOOLEAN)
 RETURNS TABLE(id uuid, name text, "collegeId" uuid, "collegeName" text, "collegeState" text, "collegeAffiliation" text, "collegeDivision" text, "collegeConference" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT weekly_coaches.id, coaches.name, colleges.id, colleges.name, colleges.state, colleges."athleticAffiliation", colleges."athleticDivision", colleges."athleticConference"
    FROM autosend.autosend
    JOIN autosend.weekly_coaches weekly_coaches ON weekly_coaches.autosend = autosend.id
    JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = weekly_coaches.coach
    JOIN coaches ON coaches.id = autosend_coaches.coach
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE 
      (autosend.id = _id) AND
      (_search IS NULL OR 
        coaches.name ILIKE '%' || _search || '%' OR
        colleges.name ILIKE '%' || _search || '%'
      ) AND
      ("_isToMessage" IS NULL OR weekly_coaches.status = 'Pending') AND
      ("_isMessaged" IS NULL OR weekly_coaches.status = 'Sent')
    OFFSET _offset
    LIMIT 7;  
  END;
$function$
;
 
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.coaches "autoSendCoaches" ON "autoSendCoaches".id = autosend.weekly_coaches.coach
    JOIN coaches ON coaches.id = "autoSendCoaches".coach
    JOIN sports ON sports.id = coaches.sport 
    JOIN colleges ON colleges.id = sports.college
    ORDER BY RANDOM()
    LIMIT ("_dailyAmount");
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getPendingMessage"(_id uuid)
 RETURNS TABLE(id uuid, name text, "userEmail" text, email text, "autoSendCoachId" uuid, "coachId" uuid, "coachName" text, "coachEmail" text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT 
      autosend.profile AS "profileId",
      users.name,
      users.email,
      profiles.email,
      autosend_coaches.id,
      coaches.id,
      coaches.name AS "coachName",
      coaches.email AS "coachEmail",
      colleges.id AS "collegeId",
      colleges.name AS "collegeName"
    FROM autosend.coaches autosend_coaches
    JOIN autosend.autosend ON autosend.id = autosend_coaches.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN users ON users.id = profiles.USER
    JOIN coaches ON coaches.id = autosend_coaches.coach
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE autosend_coaches.id = _id;
  END;
$function$
;
 
CREATE OR REPLACE FUNCTION autosend."getScheduledEmails"()
 RETURNS TABLE(id uuid, autosend uuid, "isLastScheduledMessage" BOOLEAN)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "coachTimezones" AS (
        SELECT autosend_coaches.id AS "coachId", colleges.timezone
        FROM autosend.coaches autosend_coaches
        JOIN coaches ON coaches.id = autosend_coaches.coach
        JOIN sports ON sports.id = coaches.sport
        JOIN colleges ON colleges.id = sports.college
      ),
      "allMessages" AS (
        SELECT autosend_coaches.id, autosend_coaches.autosend, autosend.daily_coaches.status, ROW_NUMBER() OVER (
                PARTITION BY autosend_coaches.autosend 
                ORDER BY daily_coaches."scheduledAt" DESC
            ) AS row_num, autosend.daily_coaches."scheduledAt", "coachTimezones".timezone
        FROM autosend.daily_coaches
        JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = daily_coaches.coach
        JOIN "coachTimezones" ON "coachTimezones"."coachId" = autosend_coaches.id 
      ),
      "filteredMessages" AS (
        SELECT "allMessages".id, "allMessages".autosend, "scheduledAt", (row_num = 1) AS "isLastScheduledMessage"
        FROM "allMessages"
        -- JOIN autosend ON autosend.id = "allMessages".autosend
        -- JOIN users ON 
        WHERE status = 'Pending' AND ("scheduledAt" AT TIME ZONE timezone) <= (now() AT TIME ZONE timezone)
      )
 
      SELECT "filteredMessages".id, "filteredMessages".autosend, "filteredMessages"."isLastScheduledMessage"
      FROM "filteredMessages"
      ORDER BY "isLastScheduledMessage", "scheduledAt" DESC;
  END;
$function$
;
 
CREATE OR REPLACE VIEW user_profile
WITH (security_invoker = ON) AS
SELECT
  profiles.id,
  users.id AS "userId",
  users.name,
  users.status,
  users.image,
  users.email AS "userEmail",
  profiles.email,
  profiles.image AS "profileImage",
  sport,
  data,
  "isArchived",
  users.gender,
  users."preferredGender"
FROM profiles
JOIN users ON users.id = profiles.USER
WHERE CURRENT_USER = 'postgres' OR CURRENT_USER = 'service_role' OR users.id = auth.UID();