create extension if not exists pgcrypto;

do $$
begin
  if not exists (select 1 from pg_type where typname = 'app_role') then
    create type app_role as enum ('admin', 'affiliate');
  end if;

  if not exists (select 1 from pg_type where typname = 'import_status') then
    create type import_status as enum ('processing', 'completed', 'failed');
  end if;
end $$;

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text not null,
  role app_role not null default 'affiliate',
  created_at timestamptz not null default timezone('utc'::text, now())
);

create table if not exists public.countries (
  id bigserial primary key,
  code text not null unique check (char_length(code) between 2 and 8),
  name text not null,
  created_at timestamptz not null default timezone('utc'::text, now())
);

create table if not exists public.user_country_access (
  id bigserial primary key,
  user_id uuid not null references public.profiles(id) on delete cascade,
  country_id bigint not null references public.countries(id) on delete cascade,
  created_at timestamptz not null default timezone('utc'::text, now()),
  unique (user_id, country_id)
);

create table if not exists public.imports (
  id bigserial primary key,
  uploaded_by uuid not null references public.profiles(id) on delete restrict,
  file_name text not null,
  file_hash text not null,
  storage_path text,
  detected_country_code text,
  status import_status not null default 'processing',
  total_rows integer not null default 0 check (total_rows >= 0),
  inserted_rows integer not null default 0 check (inserted_rows >= 0),
  updated_rows integer not null default 0 check (updated_rows >= 0),
  failed_rows integer not null default 0 check (failed_rows >= 0),
  error_summary text,
  created_at timestamptz not null default timezone('utc'::text, now())
);

create table if not exists public.raw_email_events (
  id bigserial primary key,
  sent_email_name text not null unique,
  primary_country_code text not null,
  approved_document_name text not null,
  last_open timestamptz,
  opened integer not null default 0 check (opened >= 0),
  total_opens integer not null default 0 check (total_opens >= 0),
  last_click timestamptz,
  clicked integer not null default 0 check (clicked >= 0),
  total_clicks integer not null default 0 check (total_clicks >= 0),
  sent_date timestamptz,
  latest_import_id bigint references public.imports(id) on delete set null,
  created_at timestamptz not null default timezone('utc'::text, now()),
  updated_at timestamptz not null default timezone('utc'::text, now())
);

create table if not exists public.email_metadata (
  id bigserial primary key,
  approved_document_name text not null unique,
  friendly_title text,
  brand text,
  notes text,
  thumbnail_path text,
  campaign_group text,
  created_at timestamptz not null default timezone('utc'::text, now()),
  updated_at timestamptz not null default timezone('utc'::text, now())
);

create table if not exists public.email_event_metadata_overrides (
  id bigserial primary key,
  sent_email_name text not null unique references public.raw_email_events(sent_email_name) on delete cascade,
  friendly_title text,
  brand text,
  notes text,
  thumbnail_path text,
  campaign_group text,
  created_at timestamptz not null default timezone('utc'::text, now()),
  updated_at timestamptz not null default timezone('utc'::text, now())
);

create index if not exists idx_profiles_role on public.profiles(role);
create index if not exists idx_user_country_access_user on public.user_country_access(user_id);
create index if not exists idx_imports_uploaded_by_created_at on public.imports(uploaded_by, created_at desc);
create index if not exists idx_imports_created_at on public.imports(created_at desc);
create index if not exists idx_raw_email_events_country on public.raw_email_events(primary_country_code);
create index if not exists idx_raw_email_events_sent_date on public.raw_email_events(sent_date);
create index if not exists idx_raw_email_events_doc on public.raw_email_events(approved_document_name);
create index if not exists idx_raw_email_events_import on public.raw_email_events(latest_import_id);
create index if not exists idx_email_metadata_brand on public.email_metadata(brand);

create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = timezone('utc'::text, now());
  return new;
end;
$$;

drop trigger if exists raw_email_events_set_updated_at on public.raw_email_events;
create trigger raw_email_events_set_updated_at
before update on public.raw_email_events
for each row execute function public.set_updated_at();

drop trigger if exists email_metadata_set_updated_at on public.email_metadata;
create trigger email_metadata_set_updated_at
before update on public.email_metadata
for each row execute function public.set_updated_at();

drop trigger if exists email_event_metadata_overrides_set_updated_at on public.email_event_metadata_overrides;
create trigger email_event_metadata_overrides_set_updated_at
before update on public.email_event_metadata_overrides
for each row execute function public.set_updated_at();

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, coalesce(new.email, ''));
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.handle_new_user();

create or replace function public.is_admin(p_user uuid default auth.uid())
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.profiles p
    where p.id = p_user
      and p.role = 'admin'
  );
$$;

create or replace function public.has_country_access(
  p_country_code text,
  p_user uuid default auth.uid()
)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select
    public.is_admin(p_user) or exists (
      select 1
      from public.user_country_access uca
      join public.countries c on c.id = uca.country_id
      where uca.user_id = p_user
        and upper(c.code) = upper(p_country_code)
    );
$$;

alter table public.profiles enable row level security;
alter table public.countries enable row level security;
alter table public.user_country_access enable row level security;
alter table public.imports enable row level security;
alter table public.raw_email_events enable row level security;
alter table public.email_metadata enable row level security;
alter table public.email_event_metadata_overrides enable row level security;

drop policy if exists profiles_select_self_or_admin on public.profiles;
create policy profiles_select_self_or_admin
on public.profiles
for select
using (auth.uid() = id or public.is_admin());

drop policy if exists profiles_admin_update on public.profiles;
create policy profiles_admin_update
on public.profiles
for update
using (public.is_admin())
with check (public.is_admin());

drop policy if exists countries_select_authenticated on public.countries;
create policy countries_select_authenticated
on public.countries
for select
using (auth.role() = 'authenticated');

drop policy if exists countries_admin_write on public.countries;
create policy countries_admin_write
on public.countries
for all
using (public.is_admin())
with check (public.is_admin());

drop policy if exists user_country_access_select_self_or_admin on public.user_country_access;
create policy user_country_access_select_self_or_admin
on public.user_country_access
for select
using (public.is_admin() or auth.uid() = user_id);

drop policy if exists user_country_access_admin_write on public.user_country_access;
create policy user_country_access_admin_write
on public.user_country_access
for all
using (public.is_admin())
with check (public.is_admin());

drop policy if exists imports_select_self_or_admin on public.imports;
create policy imports_select_self_or_admin
on public.imports
for select
using (public.is_admin() or auth.uid() = uploaded_by);

drop policy if exists imports_insert_self_or_admin on public.imports;
create policy imports_insert_self_or_admin
on public.imports
for insert
with check (public.is_admin() or auth.uid() = uploaded_by);

drop policy if exists imports_update_admin_only on public.imports;
create policy imports_update_admin_only
on public.imports
for update
using (public.is_admin() or auth.uid() = uploaded_by)
with check (public.is_admin() or auth.uid() = uploaded_by);

drop policy if exists raw_email_events_select_country_scope on public.raw_email_events;
create policy raw_email_events_select_country_scope
on public.raw_email_events
for select
using (public.is_admin() or public.has_country_access(primary_country_code));

drop policy if exists raw_email_events_insert_country_scope on public.raw_email_events;
create policy raw_email_events_insert_country_scope
on public.raw_email_events
for insert
with check (public.is_admin() or public.has_country_access(primary_country_code));

drop policy if exists raw_email_events_update_country_scope on public.raw_email_events;
create policy raw_email_events_update_country_scope
on public.raw_email_events
for update
using (public.is_admin() or public.has_country_access(primary_country_code))
with check (public.is_admin() or public.has_country_access(primary_country_code));

drop policy if exists email_metadata_select_all_authenticated on public.email_metadata;
create policy email_metadata_select_all_authenticated
on public.email_metadata
for select
using (auth.role() = 'authenticated');

drop policy if exists email_metadata_admin_write on public.email_metadata;
create policy email_metadata_admin_write
on public.email_metadata
for all
using (public.is_admin())
with check (public.is_admin());

drop policy if exists email_event_metadata_overrides_select_country_scope on public.email_event_metadata_overrides;
create policy email_event_metadata_overrides_select_country_scope
on public.email_event_metadata_overrides
for select
using (
  public.is_admin()
  or exists (
    select 1
    from public.raw_email_events ree
    where ree.sent_email_name = email_event_metadata_overrides.sent_email_name
      and public.has_country_access(ree.primary_country_code)
  )
);

drop policy if exists email_event_metadata_overrides_admin_write on public.email_event_metadata_overrides;
create policy email_event_metadata_overrides_admin_write
on public.email_event_metadata_overrides
for all
using (public.is_admin())
with check (public.is_admin());

insert into storage.buckets (id, name, public)
values ('imports', 'imports', false)
on conflict (id) do nothing;

insert into storage.buckets (id, name, public)
values ('thumbnails', 'thumbnails', true)
on conflict (id) do nothing;

drop policy if exists imports_bucket_select on storage.objects;
create policy imports_bucket_select
on storage.objects
for select
to authenticated
using (
  bucket_id = 'imports'
  and (
    public.is_admin()
    or split_part(name, '/', 1) = auth.uid()::text
  )
);

drop policy if exists imports_bucket_insert on storage.objects;
create policy imports_bucket_insert
on storage.objects
for insert
to authenticated
with check (
  bucket_id = 'imports'
  and (
    public.is_admin()
    or split_part(name, '/', 1) = auth.uid()::text
  )
);

drop policy if exists thumbnails_select on storage.objects;
create policy thumbnails_select
on storage.objects
for select
to authenticated
using (bucket_id = 'thumbnails');

drop policy if exists thumbnails_admin_write on storage.objects;
create policy thumbnails_admin_write
on storage.objects
for all
to authenticated
using (bucket_id = 'thumbnails' and public.is_admin())
with check (bucket_id = 'thumbnails' and public.is_admin());

create or replace function public.merge_raw_email_events(
  p_import_id bigint,
  p_rows jsonb
)
returns table(inserted_rows integer, updated_rows integer, failed_rows integer)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row jsonb;
  v_user_id uuid := auth.uid();
  v_is_admin boolean := public.is_admin(v_user_id);
  v_sent_email_name text;
  v_existing_country text;
  v_target_country text;
begin
  inserted_rows := 0;
  updated_rows := 0;
  failed_rows := 0;

  for v_row in select * from jsonb_array_elements(p_rows)
  loop
    begin
      v_sent_email_name := trim(coalesce(v_row->>'sent_email_name', ''));

      if v_sent_email_name = '' then
        failed_rows := failed_rows + 1;
        continue;
      end if;

      select ree.primary_country_code
      into v_existing_country
      from public.raw_email_events ree
      where ree.sent_email_name = v_sent_email_name;

      v_target_country := upper(coalesce(nullif(v_existing_country, ''), nullif(v_row->>'primary_country_code', '')));

      if not v_is_admin and (v_target_country is null or not public.has_country_access(v_target_country, v_user_id)) then
        failed_rows := failed_rows + 1;
        continue;
      end if;

      if exists (select 1 from public.raw_email_events ree where ree.sent_email_name = v_sent_email_name) then
        updated_rows := updated_rows + 1;
      else
        inserted_rows := inserted_rows + 1;
      end if;

      insert into public.raw_email_events (
        sent_email_name,
        primary_country_code,
        approved_document_name,
        last_open,
        opened,
        total_opens,
        last_click,
        clicked,
        total_clicks,
        sent_date,
        latest_import_id
      )
      values (
        v_sent_email_name,
        upper(coalesce(v_row->>'primary_country_code', '')),
        coalesce(v_row->>'approved_document_name', ''),
        nullif(v_row->>'last_open', '')::timestamptz,
        greatest(coalesce((v_row->>'opened')::integer, 0), 0),
        greatest(coalesce((v_row->>'total_opens')::integer, 0), 0),
        nullif(v_row->>'last_click', '')::timestamptz,
        greatest(coalesce((v_row->>'clicked')::integer, 0), 0),
        greatest(coalesce((v_row->>'total_clicks')::integer, 0), 0),
        nullif(v_row->>'sent_date', '')::timestamptz,
        p_import_id
      )
      on conflict (sent_email_name)
      do update set
        primary_country_code = coalesce(nullif(raw_email_events.primary_country_code, ''), excluded.primary_country_code),
        approved_document_name = coalesce(nullif(raw_email_events.approved_document_name, ''), excluded.approved_document_name),
        last_open = case
          when raw_email_events.last_open is null then excluded.last_open
          when excluded.last_open is null then raw_email_events.last_open
          else greatest(raw_email_events.last_open, excluded.last_open)
        end,
        opened = greatest(coalesce(raw_email_events.opened, 0), coalesce(excluded.opened, 0)),
        total_opens = greatest(coalesce(raw_email_events.total_opens, 0), coalesce(excluded.total_opens, 0)),
        last_click = case
          when raw_email_events.last_click is null then excluded.last_click
          when excluded.last_click is null then raw_email_events.last_click
          else greatest(raw_email_events.last_click, excluded.last_click)
        end,
        clicked = greatest(coalesce(raw_email_events.clicked, 0), coalesce(excluded.clicked, 0)),
        total_clicks = greatest(coalesce(raw_email_events.total_clicks, 0), coalesce(excluded.total_clicks, 0)),
        sent_date = coalesce(raw_email_events.sent_date, excluded.sent_date),
        latest_import_id = p_import_id,
        updated_at = timezone('utc'::text, now());
    exception
      when others then
        failed_rows := failed_rows + 1;
    end;
  end loop;

  return query select inserted_rows, updated_rows, failed_rows;
end;
$$;

grant execute on function public.merge_raw_email_events(bigint, jsonb) to authenticated;

create or replace view public.email_events_enriched with (security_invoker = true) as
select
  ree.sent_email_name,
  ree.primary_country_code,
  ree.approved_document_name,
  coalesce(eemo.friendly_title, em.friendly_title, ree.approved_document_name) as friendly_title,
  coalesce(eemo.brand, em.brand, 'Unspecified') as brand,
  coalesce(eemo.notes, em.notes) as notes,
  coalesce(eemo.thumbnail_path, em.thumbnail_path) as thumbnail_path,
  coalesce(eemo.campaign_group, em.campaign_group) as campaign_group,
  ree.last_open,
  ree.opened,
  ree.total_opens,
  ree.last_click,
  ree.clicked,
  ree.total_clicks,
  ree.sent_date,
  ree.created_at,
  ree.updated_at
from public.raw_email_events ree
left join public.email_metadata em
  on em.approved_document_name = ree.approved_document_name
left join public.email_event_metadata_overrides eemo
  on eemo.sent_email_name = ree.sent_email_name;

create or replace view public.country_overall_metrics with (security_invoker = true) as
select
  ree.primary_country_code as country_code,
  count(*)::integer as total_emails_sent,
  sum(ree.opened)::integer as total_opened,
  sum(ree.total_opens)::integer as total_opens,
  sum(ree.clicked)::integer as total_clicked,
  sum(ree.total_clicks)::integer as total_clicks,
  case when count(*) > 0 then sum(ree.opened)::numeric / count(*)::numeric else 0 end as open_rate,
  case when count(*) > 0 then sum(ree.clicked)::numeric / count(*)::numeric else 0 end as ctr,
  case when sum(ree.opened) > 0 then sum(ree.clicked)::numeric / sum(ree.opened)::numeric else 0 end as ctor
from public.raw_email_events ree
group by ree.primary_country_code;

create or replace view public.monthly_country_metrics with (security_invoker = true) as
select
  ree.primary_country_code as country_code,
  date_trunc('month', coalesce(ree.sent_date, ree.created_at))::date as month_start,
  count(*)::integer as total_emails_sent,
  sum(ree.opened)::integer as total_opened,
  sum(ree.total_opens)::integer as total_opens,
  sum(ree.clicked)::integer as total_clicked,
  sum(ree.total_clicks)::integer as total_clicks,
  case when count(*) > 0 then sum(ree.opened)::numeric / count(*)::numeric else 0 end as open_rate,
  case when count(*) > 0 then sum(ree.clicked)::numeric / count(*)::numeric else 0 end as ctr,
  case when sum(ree.opened) > 0 then sum(ree.clicked)::numeric / sum(ree.opened)::numeric else 0 end as ctor
from public.raw_email_events ree
group by ree.primary_country_code, date_trunc('month', coalesce(ree.sent_date, ree.created_at))::date;

create or replace view public.document_country_metrics with (security_invoker = true) as
select
  eee.primary_country_code as country_code,
  eee.approved_document_name,
  eee.friendly_title,
  eee.brand,
  eee.thumbnail_path,
  eee.campaign_group,
  count(*)::integer as total_emails_sent,
  sum(eee.opened)::integer as total_opened,
  sum(eee.total_opens)::integer as total_opens,
  sum(eee.clicked)::integer as total_clicked,
  sum(eee.total_clicks)::integer as total_clicks,
  case when count(*) > 0 then sum(eee.opened)::numeric / count(*)::numeric else 0 end as open_rate,
  case when count(*) > 0 then sum(eee.clicked)::numeric / count(*)::numeric else 0 end as ctr,
  case when sum(eee.opened) > 0 then sum(eee.clicked)::numeric / sum(eee.opened)::numeric else 0 end as ctor,
  max(coalesce(eee.sent_date, eee.created_at)) as latest_sent_at
from public.email_events_enriched eee
group by
  eee.primary_country_code,
  eee.approved_document_name,
  eee.friendly_title,
  eee.brand,
  eee.thumbnail_path,
  eee.campaign_group;

create or replace view public.top_emails_by_country with (security_invoker = true) as
select *
from public.document_country_metrics dcm
where dcm.total_emails_sent >= 10
order by dcm.country_code, dcm.open_rate desc, dcm.ctr desc;

create or replace view public.worst_emails_by_country with (security_invoker = true) as
select *
from public.document_country_metrics dcm
where dcm.total_emails_sent >= 10
order by dcm.country_code, dcm.open_rate asc, dcm.ctr asc;

grant usage on schema public to authenticated;

grant select on
  public.profiles,
  public.countries,
  public.user_country_access,
  public.imports,
  public.raw_email_events,
  public.email_metadata,
  public.email_event_metadata_overrides,
  public.email_events_enriched,
  public.country_overall_metrics,
  public.monthly_country_metrics,
  public.document_country_metrics,
  public.top_emails_by_country,
  public.worst_emails_by_country
to authenticated;

grant insert, update, delete on
  public.profiles,
  public.user_country_access,
  public.imports,
  public.raw_email_events,
  public.email_metadata,
  public.email_event_metadata_overrides
to authenticated;