create table users (
  id bigserial primary key,
  email varchar(255) not null unique,
  password_hash varchar(255) not null,
  role varchar(24) not null,
  enabled boolean not null default true,
  created_at timestamptz not null default now()
);

create table profiles (
  id bigserial primary key,
  user_id bigint not null unique references users(id) on delete cascade,
  name varchar(120) not null,
  phone varchar(80) not null,
  organization varchar(180) not null,
  title varchar(160) not null,
  industry varchar(160) not null,
  city varchar(160) not null,
  attendee_type varchar(120) not null,
  updated_at timestamptz not null default now()
);

create table invitation_codes (
  id bigserial primary key,
  code varchar(80) not null unique,
  max_uses integer not null,
  used_count integer not null default 0,
  expires_at timestamptz,
  enabled boolean not null default true,
  created_at timestamptz not null default now()
);

create table assessments (
  id bigserial primary key,
  test_paper_id varchar(80) not null unique,
  title varchar(200) not null,
  description text not null,
  active boolean not null default true
);

create table questions (
  id bigserial primary key,
  assessment_id bigint not null references assessments(id) on delete cascade,
  code varchar(80) not null unique,
  dimension varchar(80) not null,
  prompt text not null,
  sort_order integer not null
);

create table submissions (
  id bigserial primary key,
  user_id bigint not null references users(id) on delete cascade,
  assessment_id bigint not null references assessments(id),
  raw_score integer not null,
  level varchar(80) not null,
  dimension_scores text not null,
  submitted_at timestamptz not null default now()
);

create table answers (
  id bigserial primary key,
  submission_id bigint not null references submissions(id) on delete cascade,
  question_id bigint not null references questions(id),
  answer_value integer not null,
  unique(submission_id, question_id)
);

create table reports (
  id bigserial primary key,
  submission_id bigint not null unique references submissions(id) on delete cascade,
  user_id bigint not null references users(id) on delete cascade,
  summary text not null,
  suggestions text not null,
  created_at timestamptz not null default now()
);

create table admin_audit_logs (
  id bigserial primary key,
  actor_user_id bigint references users(id),
  action varchar(120) not null,
  target_type varchar(120) not null,
  target_id varchar(120),
  created_at timestamptz not null default now()
);

create index idx_submissions_user_id on submissions(user_id);
create index idx_reports_user_id on reports(user_id);
create index idx_audit_created_at on admin_audit_logs(created_at desc);
