-- Aurelia Medical Privé demonstration schema. Synthetic clinic data only.

create table if not exists tenants (
  id text primary key,
  slug text not null unique,
  name text not null,
  city text not null,
  country text not null,
  address text not null,
  phone text not null,
  email text not null,
  timezone text not null default 'Europe/Paris',
  plan text not null default 'prive',
  status text not null default 'active',
  is_demo boolean not null default true,
  theme jsonb not null default '{}'::jsonb,
  settings jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists branches (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  name text not null,
  address text not null,
  is_primary boolean not null default true
);

create table if not exists memberships (
  user_id text not null,
  tenant_id text not null,
  role text not null,
  created_at timestamptz not null default now(),
  primary key (user_id, tenant_id)
);

create table if not exists patients (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  user_id text,
  full_name text not null,
  email text,
  phone text,
  locale text not null default 'fa',
  channel text not null default 'email',
  notify_email boolean not null default true,
  notify_sms boolean not null default false,
  notes text not null default '',
  status text not null default 'active',
  is_demo boolean not null default true,
  deleted_at timestamptz,
  created_at timestamptz not null default now()
);
create index if not exists patients_tenant_idx on patients (tenant_id);
create index if not exists patients_user_idx on patients (tenant_id, user_id);

create table if not exists staff (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  user_id text,
  name text not null,
  portrait text,
  is_doctor boolean not null default false,
  active boolean not null default true,
  languages text not null default 'fa,en',
  sort_order int not null default 0
);

create table if not exists staff_translations (
  staff_id text not null references staff(id) on delete cascade,
  locale text not null,
  title text not null,
  bio text not null,
  interests text not null default '',
  career text not null default '',
  primary key (staff_id, locale)
);

create table if not exists services (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  slug text not null,
  duration_min int not null,
  price_cents int not null,
  image text not null,
  active boolean not null default true,
  bookable boolean not null default true,
  sort_order int not null default 0,
  unique (tenant_id, slug)
);

create table if not exists service_translations (
  service_id text not null references services(id) on delete cascade,
  locale text not null,
  name text not null,
  description text not null,
  preparation text not null,
  faqs jsonb not null default '[]'::jsonb,
  primary key (service_id, locale)
);

create table if not exists staff_services (
  staff_id text not null references staff(id) on delete cascade,
  service_id text not null references services(id) on delete cascade,
  primary key (staff_id, service_id)
);

create table if not exists staff_schedules (
  id text primary key,
  staff_id text not null references staff(id) on delete cascade,
  weekday int not null check (weekday between 0 and 6),
  start_min int not null,
  end_min int not null
);

create table if not exists staff_time_off (
  id text primary key,
  staff_id text not null references staff(id) on delete cascade,
  day date not null,
  label text not null default ''
);

create table if not exists holidays (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  day date not null,
  label text not null,
  unique (tenant_id, day)
);

create table if not exists appointments (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  patient_id text not null references patients(id),
  staff_id text not null references staff(id),
  service_id text not null references services(id),
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  status text not null check (status in ('pending','confirmed','cancelled','completed','no_show')),
  reference text not null unique,
  notes text not null default '',
  internal_notes text not null default '',
  source text not null default 'web',
  created_at timestamptz not null default now()
);
create index if not exists appt_staff_time_idx on appointments (staff_id, starts_at);
create index if not exists appt_tenant_idx on appointments (tenant_id, starts_at);
create unique index if not exists appt_active_slot_uq
  on appointments (staff_id, starts_at)
  where status in ('pending','confirmed','completed');

create table if not exists faqs (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  sort_order int not null default 0,
  active boolean not null default true
);

create table if not exists faq_translations (
  faq_id text not null references faqs(id) on delete cascade,
  locale text not null,
  question text not null,
  answer text not null,
  primary key (faq_id, locale)
);

create table if not exists content_documents (
  tenant_id text not null references tenants(id) on delete cascade,
  key text not null,
  published jsonb not null,
  draft jsonb,
  previous jsonb,
  primary key (tenant_id, key)
);

create table if not exists media_assets (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  kind text not null,
  category text not null,
  url text not null,
  sort_order int not null default 0
);

create table if not exists media_translations (
  media_id text not null references media_assets(id) on delete cascade,
  locale text not null,
  caption text not null,
  primary key (media_id, locale)
);

create table if not exists tour_rooms (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  image text not null,
  sort_order int not null default 0
);

create table if not exists tour_translations (
  room_id text not null references tour_rooms(id) on delete cascade,
  locale text not null,
  name text not null,
  description text not null,
  primary key (room_id, locale)
);

create table if not exists tour_links (
  from_room text not null references tour_rooms(id) on delete cascade,
  to_room text not null references tour_rooms(id) on delete cascade,
  primary key (from_room, to_room)
);

create table if not exists contact_requests (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  name text not null,
  email text not null,
  phone text not null default '',
  message text not null,
  locale text not null,
  preference text not null default 'email',
  status text not null default 'new',
  created_at timestamptz not null default now()
);

create table if not exists conversations (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  user_id text,
  locale text not null,
  created_at timestamptz not null default now()
);

create table if not exists conversation_messages (
  id text primary key,
  conversation_id text not null references conversations(id) on delete cascade,
  role text not null,
  content text not null,
  mode text not null default 'records',
  created_at timestamptz not null default now()
);

create table if not exists knowledge_items (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  kind text not null,
  active boolean not null default true,
  sort_order int not null default 0
);

create table if not exists knowledge_translations (
  item_id text not null references knowledge_items(id) on delete cascade,
  locale text not null,
  title text not null,
  body text not null,
  primary key (item_id, locale)
);

create table if not exists ai_settings (
  tenant_id text primary key references tenants(id) on delete cascade,
  assistant_name text not null,
  voice_note text not null,
  forbidden text not null,
  escalation text not null,
  allowed text not null
);

create table if not exists notifications (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  patient_id text,
  kind text not null,
  locale text not null,
  title text not null,
  body text not null,
  channel text not null,
  status text not null default 'preview',
  created_at timestamptz not null default now()
);

create table if not exists message_templates (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  kind text not null,
  locale text not null,
  title text not null,
  body text not null,
  unique (tenant_id, kind, locale)
);

create table if not exists audit_logs (
  id text primary key,
  tenant_id text,
  user_id text,
  action text not null,
  entity text not null default '',
  entity_id text not null default '',
  detail text not null default '',
  created_at timestamptz not null default now()
);

create table if not exists subscriptions (
  tenant_id text primary key references tenants(id) on delete cascade,
  plan text not null,
  status text not null,
  amount_cents int not null,
  currency text not null default 'EUR',
  renews_on date not null
);

create table if not exists feature_flags (
  tenant_id text not null references tenants(id) on delete cascade,
  key text not null,
  enabled boolean not null,
  primary key (tenant_id, key)
);

create table if not exists usage_events (
  id bigserial primary key,
  tenant_id text not null,
  user_id text,
  kind text not null,
  locale text,
  created_at timestamptz not null default now()
);

create table if not exists invoices (
  id text primary key,
  tenant_id text not null references tenants(id) on delete cascade,
  label text not null,
  amount_cents int not null,
  status text not null,
  issued_on date not null
);