-- ============================================================================
-- EduAdvise CRM — Phase 6: Marketing, Tutorials, Client Mail
-- ============================================================================

create table public.promotional_materials (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  title text not null,
  category text,
  description text,
  file_path text,          -- path within the 'marketing' storage bucket
  link_url text,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);
create index promotional_materials_category_idx on public.promotional_materials (category);

create table public.promotional_tutorials (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  type text not null default 'video',   -- ppt | webinar | video
  url text,
  description text,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);

create table public.social_media_posts (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  platform text not null,                -- Facebook | Instagram | LinkedIn | Twitter | YouTube
  content text,
  link_url text,
  scheduled_at timestamptz,
  posted_at timestamptz,
  status text not null default 'draft',  -- draft | scheduled | posted
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);

create table public.crm_tutorials (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  topic text,
  video_url text,
  description text,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);

-- ---- Client mail -----------------------------------------------------------
create table public.client_mail_categories (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.client_mail_templates (
  id uuid primary key default gen_random_uuid(),
  category_id uuid references public.client_mail_categories(id) on delete set null,
  name text not null,
  subject text not null,
  body text not null,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.client_mail_sends (
  id uuid primary key default gen_random_uuid(),
  template_id uuid references public.client_mail_templates(id) on delete set null,
  subject text not null,
  body text not null,
  recipient_email text not null,
  recipient_name text,
  lead_id uuid references public.leads(id) on delete set null,
  status text not null default 'queued',  -- queued | sent | failed
  error text,
  sent_at timestamptz,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);
create index client_mail_sends_status_idx on public.client_mail_sends (status);

-- updated_at triggers
create trigger trg_client_mail_categories_updated before update on public.client_mail_categories
  for each row execute function public.set_updated_at();
create trigger trg_client_mail_templates_updated before update on public.client_mail_templates
  for each row execute function public.set_updated_at();