begin;

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

drop schema if exists public cascade;
create schema public;

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

create extension if not exists pgcrypto;

create type public.user_role as enum ('user', 'platform_admin');
create type public.membership_role as enum ('owner', 'admin', 'member');
create type public.contact_status as enum ('subscribed', 'unsubscribed', 'bounced', 'complained');
create type public.import_status as enum ('processing', 'completed', 'failed');
create type public.campaign_status as enum ('draft', 'sending', 'sent', 'failed');
create type public.editor_mode as enum ('builder', 'html');
create type public.send_status as enum ('queued', 'sent', 'failed', 'bounced', 'complained', 'opened', 'clicked');

create table public.plan_definitions (
  code text primary key,
  name text not null,
  monthly_price numeric(10,2) not null default 0,
  contact_limit integer,
  monthly_email_limit integer,
  unlimited_emails boolean not null default false,
  description text,
  sort_order integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

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

create table public.user_profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text not null unique,
  full_name text not null,
  avatar_url text,
  role public.user_role not null default 'user',
  default_organization_id uuid references public.organizations(id) on delete set null,
  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,
  user_id uuid not null references public.user_profiles(id) on delete cascade,
  membership_role public.membership_role not null default 'member',
  created_at timestamptz not null default now(),
  unique (organization_id, user_id)
);

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,
  brand_name text not null,
  primary_color text not null default '#102a43',
  secondary_color text not null default '#f4efe8',
  accent_color text not null default '#e86d3d',
  logo_url text,
  avatar_url text,
  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,
  name text,
  country text,
  city 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,
  filename text not null,
  source_format text not null,
  status public.import_status not null default 'processing',
  total_rows integer not null default 0,
  imported_rows 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.editor_mode not null default 'builder',
  html_body text,
  text_body text,
  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,
  block_type text not null,
  block_order integer not null default 0,
  config jsonb not null default '{}'::jsonb,
  created_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,
  send_status public.send_status not null default 'queued',
  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(),
  campaign_send_id uuid not null references public.campaign_sends(id) on delete cascade,
  provider_name text not null,
  provider_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(),
  campaign_send_id uuid not null references public.campaign_sends(id) on delete cascade,
  event_type text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_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_code text not null references public.plan_definitions(code),
  active boolean not null default true,
  monthly_contact_limit integer,
  monthly_email_limit integer,
  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,
  usage_month date not null,
  contact_count integer not null default 0,
  emails_sent integer not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (organization_id, usage_month)
);

create table public.admin_content_pages (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  summary text,
  body jsonb not null default '{}'::jsonb,
  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 table public.password_reset_tokens (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.user_profiles(id) on delete cascade,
  token text not null unique,
  expires_at timestamptz not null,
  used_at timestamptz,
  created_at timestamptz not null default now()
);

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 trigger set_updated_at_plan_definitions before update on public.plan_definitions for each row execute function public.set_updated_at();
create trigger set_updated_at_organizations before update on public.organizations for each row execute function public.set_updated_at();
create trigger set_updated_at_user_profiles before update on public.user_profiles for each row execute function public.set_updated_at();
create trigger set_updated_at_brand_kits before update on public.brand_kits for each row execute function public.set_updated_at();
create trigger set_updated_at_contacts before update on public.contacts for each row execute function public.set_updated_at();
create trigger set_updated_at_contact_import_jobs before update on public.contact_import_jobs for each row execute function public.set_updated_at();
create trigger set_updated_at_campaigns before update on public.campaigns for each row execute function public.set_updated_at();
create trigger set_updated_at_campaign_sends before update on public.campaign_sends for each row execute function public.set_updated_at();
create trigger set_updated_at_organization_subscriptions before update on public.organization_subscriptions for each row execute function public.set_updated_at();
create trigger set_updated_at_monthly_usage_counters before update on public.monthly_usage_counters for each row execute function public.set_updated_at();
create trigger set_updated_at_admin_content_pages before update on public.admin_content_pages for each row execute function public.set_updated_at();
create trigger set_updated_at_admin_settings before update on public.admin_settings for each row execute function public.set_updated_at();

alter table public.user_profiles enable row level security;
alter table public.organizations enable row level security;
alter table public.organization_members enable row level security;
alter table public.brand_kits enable row level security;
alter table public.contacts enable row level security;
alter table public.contact_import_jobs enable row level security;
alter table public.campaigns enable row level security;
alter table public.campaign_blocks enable row level security;
alter table public.campaign_sends enable row level security;
alter table public.email_provider_messages enable row level security;
alter table public.email_events enable row level security;
alter table public.organization_subscriptions enable row level security;
alter table public.monthly_usage_counters enable row level security;
alter table public.admin_content_pages enable row level security;
alter table public.admin_settings enable row level security;
alter table public.password_reset_tokens enable row level security;

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values
  ('brand-assets', 'brand-assets', true, 5242880, array['image/png','image/jpeg','image/svg+xml','image/webp']),
  ('user-avatars', 'user-avatars', true, 5242880, array['image/png','image/jpeg','image/webp']);

insert into public.plan_definitions (code, name, monthly_price, contact_limit, monthly_email_limit, unlimited_emails, description, sort_order)
values
  ('free', 'Free', 0, 100, 1000, false, 'For testing the platform with a small list.', 1),
  ('pro_20', 'Growth', 20, 5000, 50000, false, 'For brands scaling their first serious campaigns.', 2),
  ('scale_100', 'Scale', 100, 100000, null, true, 'For teams that need very large audiences and unlimited monthly sends.', 3);

insert into public.admin_content_pages (slug, title, summary, body)
values
  ('faq', 'Frequently asked questions', 'Answers for product, plans, and sending.', '{"items":[{"question":"How do plans work?","answer":"Plans enforce contact and monthly email limits."}]}'::jsonb),
  ('privacy', 'Privacy policy', 'How Nextacom handles your data.', '{"sections":[{"title":"Overview","body":"Nextacom stores campaign and contact data to deliver your email workflows."}]}'::jsonb),
  ('landing', 'Landing content', 'Homepage marketing copy.', '{"hero":{"eyebrow":"Email orchestration","title":"Send precise campaigns with less operational drag.","subtitle":"Nextacom gives teams a cleaner way to manage lists, design campaigns, and track results."}}'::jsonb);

insert into public.admin_settings (key, value)
values
  ('site', '{"siteTitle":"Nextacom","tagline":"A focused email marketing platform.","supportEmail":"hello@nextacom.nextali.online"}'::jsonb),
  ('branding', '{"primaryColor":"#102a43","accentColor":"#e86d3d"}'::jsonb);

commit;