create extension if not exists pgcrypto;

delete from storage.objects where bucket_id in ('brand-assets', 'user-avatars');
delete from storage.buckets where id in ('brand-assets', 'user-avatars');

drop schema if exists public cascade;
create schema public;

grant usage on schema public to postgres, anon, authenticated, service_role;
grant all on schema public to postgres, service_role;
grant usage on schema public to anon, authenticated;

delete from auth.refresh_tokens;
delete from auth.sessions;
delete from auth.identities;
delete from auth.one_time_tokens;
delete from auth.mfa_amr_claims;
delete from auth.mfa_factors;
delete from auth.users;

create type public.app_role as enum ('user', 'platform_admin');
create type public.organization_role as enum ('owner', 'admin', 'member');
create type public.subscription_status as enum ('active', 'past_due', 'canceled');
create type public.contact_status as enum ('subscribed', 'unsubscribed', 'bounced', 'complained');
create type public.contact_import_status as enum ('processing', 'completed', 'failed');
create type public.campaign_editor_mode as enum ('builder', 'html');
create type public.campaign_status as enum ('draft', 'queued', 'sending', 'sent', 'failed');
create type public.campaign_send_status as enum ('queued', 'sent', 'failed', 'bounced', 'complained', 'opened', 'clicked');

create or replace function public.set_updated_at()
returns trigger
language plpgsql
set search_path = public
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.user_profiles (
  id uuid primary key default gen_random_uuid(),
  auth_user_id uuid not null unique references auth.users (id) on delete cascade,
  email text not null unique,
  full_name text not null,
  avatar_path text,
  role public.app_role not null default 'user',
  default_organization_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.organization_members (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  auth_user_id uuid not null references auth.users (id) on delete cascade,
  role public.organization_role not null default 'member',
  created_at timestamptz not null default now(),
  unique (organization_id, auth_user_id)
);

alter table public.user_profiles
  add constraint user_profiles_default_organization_id_fkey
  foreign key (default_organization_id) references public.organizations (id) on delete set null;

create table public.plan_definitions (
  id uuid primary key default gen_random_uuid(),
  code text not null unique,
  name text not null,
  price_cents integer not null default 0,
  contact_limit integer,
  monthly_email_limit integer,
  description text,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.organization_subscriptions (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  plan_definition_id uuid not null references public.plan_definitions (id) on delete restrict,
  status public.subscription_status not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.monthly_usage_counters (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  period_start date not null,
  contacts_count integer not null default 0,
  emails_sent_count integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (organization_id, period_start)
);

create table public.brand_kits (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null unique references public.organizations (id) on delete cascade,
  logo_path text,
  primary_color text not null default '#0f766e',
  secondary_color text not null default '#0f172a',
  accent_color text not null default '#14b8a6',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.contacts (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  email text not null,
  full_name text,
  tags jsonb not null default '[]'::jsonb,
  status public.contact_status not null default 'subscribed',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (organization_id, email)
);

create table public.contact_import_jobs (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  file_name text not null,
  status public.contact_import_status not null default 'processing',
  imported_count integer not null default 0,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.campaigns (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  name text not null,
  subject text not null,
  from_name text not null,
  from_email text not null,
  editor_mode public.campaign_editor_mode not null default 'builder',
  html_body text not null default '',
  text_body text not null default '',
  status public.campaign_status not null default 'draft',
  sent_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.campaign_blocks (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns (id) on delete cascade,
  position integer not null,
  block_type text not null,
  content jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.campaign_sends (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  campaign_id uuid not null references public.campaigns (id) on delete cascade,
  contact_id uuid references public.contacts (id) on delete set null,
  provider text not null,
  external_message_id text,
  status public.campaign_send_status not null default 'queued',
  error_message text,
  sent_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.email_provider_messages (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid references public.organizations (id) on delete cascade,
  campaign_id uuid references public.campaigns (id) on delete cascade,
  campaign_send_id uuid references public.campaign_sends (id) on delete cascade,
  provider text not null,
  external_message_id text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table public.email_events (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid references public.organizations (id) on delete cascade,
  campaign_id uuid references public.campaigns (id) on delete cascade,
  campaign_send_id uuid references public.campaign_sends (id) on delete cascade,
  provider text not null,
  event_type text not null,
  external_message_id text,
  recipient text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table public.admin_content_pages (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  body jsonb not null default '{}'::jsonb,
  published boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.admin_settings (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  value jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index contacts_organization_idx on public.contacts (organization_id);
create index campaigns_organization_idx on public.campaigns (organization_id);
create index campaign_sends_organization_idx on public.campaign_sends (organization_id);
create index campaign_sends_campaign_idx on public.campaign_sends (campaign_id);
create index organization_members_user_idx on public.organization_members (auth_user_id);
create index email_provider_messages_external_idx on public.email_provider_messages (provider, external_message_id);

create trigger organizations_set_updated_at before update on public.organizations for each row execute function public.set_updated_at();
create trigger user_profiles_set_updated_at before update on public.user_profiles for each row execute function public.set_updated_at();
create trigger plan_definitions_set_updated_at before update on public.plan_definitions for each row execute function public.set_updated_at();
create trigger organization_subscriptions_set_updated_at before update on public.organization_subscriptions for each row execute function public.set_updated_at();
create trigger monthly_usage_counters_set_updated_at before update on public.monthly_usage_counters for each row execute function public.set_updated_at();
create trigger brand_kits_set_updated_at before update on public.brand_kits for each row execute function public.set_updated_at();
create trigger contacts_set_updated_at before update on public.contacts for each row execute function public.set_updated_at();
create trigger contact_import_jobs_set_updated_at before update on public.contact_import_jobs for each row execute function public.set_updated_at();
create trigger campaigns_set_updated_at before update on public.campaigns for each row execute function public.set_updated_at();
create trigger campaign_blocks_set_updated_at before update on public.campaign_blocks for each row execute function public.set_updated_at();
create trigger campaign_sends_set_updated_at before update on public.campaign_sends for each row execute function public.set_updated_at();
create trigger admin_content_pages_set_updated_at before update on public.admin_content_pages for each row execute function public.set_updated_at();
create trigger admin_settings_set_updated_at before update on public.admin_settings for each row execute function public.set_updated_at();

insert into storage.buckets (id, name, public)
values
  ('brand-assets', 'brand-assets', false),
  ('user-avatars', 'user-avatars', false);

insert into public.plan_definitions (code, name, price_cents, contact_limit, monthly_email_limit, description)
values
  ('free', 'Free', 0, 100, 1000, 'Starter plan for small lists.'),
  ('pro_20', 'Growth', 2000, 5000, 50000, 'For growing senders with higher monthly volume.'),
  ('scale_100', 'Scale', 10000, 100000, null, 'For large contact lists with unlimited monthly email volume.');

insert into public.admin_content_pages (slug, title, body)
values
  ('pricing', 'Pricing', '{"hero":"Choose the plan that fits your list size.","plans":["free","pro_20","scale_100"]}'::jsonb),
  ('faq', 'Frequently Asked Questions', '{"items":[{"question":"What is Nextacom?","answer":"A modern email marketing platform for brands and teams."}]}'::jsonb),
  ('privacy', 'Privacy Policy', '{"sections":[{"title":"Privacy","body":"Your data is stored securely and scoped to your organization."}]}'::jsonb);

insert into public.admin_settings (key, value)
values
  ('platform', '{"siteName":"Nextacom","supportEmail":"hello@nextacom.nextali.online"}'::jsonb),
  ('branding', '{"primaryColor":"#0f766e","secondaryColor":"#0f172a","accentColor":"#14b8a6"}'::jsonb);