create extension if not exists "http" with schema "extensions";

create table "public"."favicons_excluded_domains" (
    "id" bigint generated by default as identity not null,
    "domain" text not null
);


alter table "public"."favicons_excluded_domains" enable row level security;

CREATE UNIQUE INDEX favicons_excluded_domains_pkey ON public.favicons_excluded_domains USING btree (id);

alter table "public"."favicons_excluded_domains" add constraint "favicons_excluded_domains_pkey" PRIMARY KEY using index "favicons_excluded_domains_pkey";

alter table "public"."tasks" alter column "due_date" drop not null;

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.get_avatar_for_email(email text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$declare email_hash text;
declare gravatar_url text;
declare gravatar_status int8;
declare email_domain text;
declare favicon_url text;
declare domain_status int8;

begin
    -- Try to fetch a gravatar image
    email_hash = encode(digest(email, 'sha256'), 'hex');
    gravatar_url = concat('https://www.gravatar.com/avatar/', email_hash, '?d=404');

    select status from http_get(gravatar_url) into gravatar_status;

    if gravatar_status = 200 then
        return gravatar_url;
    end if;

    -- Fallback to email's domain favicon if not excluded
    email_domain = split_part(email, '@', 2);
    return get_domain_favicon(email_domain);
exception
    when others then
        return 'ERROR';
end;$function$
;

CREATE OR REPLACE FUNCTION public.get_domain_favicon(domain_name text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$declare domain_status int8;

begin
    if exists (select from favicons_excluded_domains as fav where fav.domain = domain_name) then
        return null;
    end if;

    return concat(
        'https://favicon.show/',
        (regexp_matches(domain_name, '^(?:https?:\/\/)?(?:[^@\/\n]+@)?(?:www\.)?([^:\/?\n]+)', 'i'))[1]
    );
end;$function$
;

CREATE OR REPLACE FUNCTION public.handle_contact_saved()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$declare contact_avatar text;
declare emails_length int8;
declare item jsonb;

begin
    if new.avatar is not null then
        return new;
    end if;

    select coalesce(jsonb_array_length(new.email_jsonb), 0) into emails_length;

    if emails_length = 0 then
        return new;
    end if;

    for item in select jsonb_array_elements(new.email_jsonb)
    loop
        select public.get_avatar_for_email(item->>'email') into contact_avatar;
        if (contact_avatar is not null) then
            exit;
        end if;
    end loop;

    if contact_avatar is null then
        return new;
    end if;

    new.avatar = concat('{"src":"', contact_avatar, '"}');
    return new;
end;$function$
;

CREATE TRIGGER contact_saved BEFORE INSERT OR UPDATE ON public.contacts FOR EACH ROW EXECUTE FUNCTION public.handle_contact_saved();

grant delete on table "public"."favicons_excluded_domains" to "authenticated";

grant insert on table "public"."favicons_excluded_domains" to "authenticated";

grant references on table "public"."favicons_excluded_domains" to "authenticated";

grant select on table "public"."favicons_excluded_domains" to "authenticated";

grant trigger on table "public"."favicons_excluded_domains" to "authenticated";

grant truncate on table "public"."favicons_excluded_domains" to "authenticated";

grant update on table "public"."favicons_excluded_domains" to "authenticated";

grant delete on table "public"."favicons_excluded_domains" to "postgres";

grant insert on table "public"."favicons_excluded_domains" to "postgres";

grant references on table "public"."favicons_excluded_domains" to "postgres";

grant select on table "public"."favicons_excluded_domains" to "postgres";

grant trigger on table "public"."favicons_excluded_domains" to "postgres";

grant truncate on table "public"."favicons_excluded_domains" to "postgres";

grant update on table "public"."favicons_excluded_domains" to "postgres";

grant delete on table "public"."favicons_excluded_domains" to "service_role";

grant insert on table "public"."favicons_excluded_domains" to "service_role";

grant references on table "public"."favicons_excluded_domains" to "service_role";

grant select on table "public"."favicons_excluded_domains" to "service_role";

grant trigger on table "public"."favicons_excluded_domains" to "service_role";

grant truncate on table "public"."favicons_excluded_domains" to "service_role";

grant update on table "public"."favicons_excluded_domains" to "service_role";


create policy "Enable access for authenticated users only"
  on "public"."favicons_excluded_domains"
  as permissive
  for all
  to authenticated
using (true)
with check (true);

CREATE OR REPLACE FUNCTION public.handle_company_saved()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$declare company_logo text;

begin
    if new.logo is not null then
        return new;
    end if;

    company_logo = get_domain_favicon(new.website);
    if company_logo is null then
        return new;
    end if;

    new.logo = concat('{"src":"', company_logo, '","title":"Company favicon"}');
    return new;
end;$function$
;

CREATE TRIGGER company_saved BEFORE INSERT OR UPDATE ON public.companies FOR EACH ROW EXECUTE FUNCTION public.handle_company_saved();

CREATE OR REPLACE FUNCTION public.handle_contact_note_created_or_updated()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  update public.contacts set last_seen = new.date where contacts.id = new.contact_id and contacts.last_seen < new.date;
  return new;
end;
$function$
;

CREATE TRIGGER on_public_contact_notes_created_or_updated AFTER INSERT ON public.contact_notes FOR EACH ROW EXECUTE FUNCTION public.handle_contact_note_created_or_updated();

CREATE OR REPLACE FUNCTION get_user_id_by_email(email TEXT)
RETURNS TABLE (id uuid)
SECURITY definer
AS $$
BEGIN
  RETURN QUERY SELECT au.id FROM auth.users au WHERE au.email = $1;
END;
$$ LANGUAGE plpgsql;

revoke execute on function public.get_user_id_by_email from anon, authenticated, public;