create table projects (
  id bigserial primary key,
  slug varchar(64) not null unique,
  name varchar(200) not null,
  landing_title varchar(200) not null,
  landing_description text not null,
  report_title varchar(200) not null,
  logo_url text not null default '',
  enabled boolean not null default true,
  created_by bigint references users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table project_memberships (
  id bigserial primary key,
  project_id bigint not null references projects(id) on delete cascade,
  user_id bigint not null references users(id) on delete cascade,
  role varchar(40) not null,
  enabled boolean not null default true,
  created_at timestamptz not null default now(),
  unique(project_id, user_id)
);

create table project_assessments (
  id bigserial primary key,
  project_id bigint not null references projects(id) on delete cascade,
  assessment_id bigint not null references assessments(id) on delete cascade,
  active boolean not null default true,
  unique(project_id, assessment_id)
);

insert into projects(slug, name, landing_title, landing_description, report_title)
values (
  'davos',
  '达沃斯会议心理测评系统',
  '达沃斯心理测评',
  '围绕国际会议高密度议程与高强度互动场景，建立标准化心理测评流程，用于识别压力水平、适应状态与支持需求，为后续沟通协同和健康支持提供依据。',
  '达沃斯会议心理测评报告'
)
on conflict (slug) do nothing;

alter table profiles add column project_id bigint;
alter table invitation_codes add column project_id bigint;
alter table submissions add column project_id bigint;
alter table reports add column project_id bigint;
alter table assessment_progress add column project_id bigint;
alter table user_assessment_access add column project_id bigint;
alter table legal_documents add column project_id bigint;
alter table consent_logs add column project_id bigint;
alter table admin_audit_logs add column project_id bigint references projects(id);

update profiles set project_id = (select id from projects where slug = 'davos') where project_id is null;
update invitation_codes set project_id = (select id from projects where slug = 'davos') where project_id is null;
update submissions set project_id = (select id from projects where slug = 'davos') where project_id is null;
update reports set project_id = (select id from projects where slug = 'davos') where project_id is null;
update assessment_progress set project_id = (select id from projects where slug = 'davos') where project_id is null;
update user_assessment_access set project_id = (select id from projects where slug = 'davos') where project_id is null;
update legal_documents set project_id = (select id from projects where slug = 'davos') where project_id is null;
update consent_logs set project_id = (select id from projects where slug = 'davos') where project_id is null;
update admin_audit_logs set project_id = (select id from projects where slug = 'davos') where project_id is null;

alter table profiles alter column project_id set not null;
alter table invitation_codes alter column project_id set not null;
alter table submissions alter column project_id set not null;
alter table reports alter column project_id set not null;
alter table assessment_progress alter column project_id set not null;
alter table user_assessment_access alter column project_id set not null;
alter table legal_documents alter column project_id set not null;
alter table consent_logs alter column project_id set not null;

alter table profiles add constraint fk_profiles_project foreign key (project_id) references projects(id) on delete cascade;
alter table invitation_codes add constraint fk_invitation_codes_project foreign key (project_id) references projects(id) on delete cascade;
alter table submissions add constraint fk_submissions_project foreign key (project_id) references projects(id);
alter table reports add constraint fk_reports_project foreign key (project_id) references projects(id) on delete cascade;
alter table assessment_progress add constraint fk_assessment_progress_project foreign key (project_id) references projects(id) on delete cascade;
alter table user_assessment_access add constraint fk_user_assessment_access_project foreign key (project_id) references projects(id) on delete cascade;
alter table legal_documents add constraint fk_legal_documents_project foreign key (project_id) references projects(id) on delete cascade;
alter table consent_logs add constraint fk_consent_logs_project foreign key (project_id) references projects(id) on delete cascade;

do $$
begin
  alter table profiles drop constraint if exists profiles_user_id_key;
  alter table invitation_codes drop constraint if exists invitation_codes_code_key;
  alter table legal_documents drop constraint if exists legal_documents_doc_type_key;
  alter table assessment_progress drop constraint if exists assessment_progress_user_id_assessment_id_key;
  alter table user_assessment_access drop constraint if exists user_assessment_access_user_id_assessment_id_key;
exception when undefined_object then
  null;
end $$;

create unique index if not exists idx_profiles_project_user_unique on profiles(project_id, user_id);
create unique index if not exists idx_invitation_codes_project_lower_code_unique on invitation_codes(project_id, lower(code));
create unique index if not exists idx_legal_documents_project_type_unique on legal_documents(project_id, doc_type);
create unique index if not exists idx_assessment_progress_project_user_assessment_unique on assessment_progress(project_id, user_id, assessment_id);
create unique index if not exists idx_user_assessment_access_project_user_assessment_unique on user_assessment_access(project_id, user_id, assessment_id);

create index if not exists idx_project_memberships_user on project_memberships(user_id);
create index if not exists idx_project_memberships_project_role on project_memberships(project_id, role);
create index if not exists idx_project_assessments_project_active on project_assessments(project_id, active);
create index if not exists idx_submissions_project_time on submissions(project_id, submitted_at desc);
create index if not exists idx_submissions_project_user_assessment_time on submissions(project_id, user_id, assessment_id, submitted_at desc);
create index if not exists idx_reports_project_created_at on reports(project_id, created_at desc);
create index if not exists idx_audit_project_created_at on admin_audit_logs(project_id, created_at desc);
create index if not exists idx_consent_project_created_at on consent_logs(project_id, created_at desc);

insert into project_assessments(project_id, assessment_id, active)
select p.id, a.id, a.active
from projects p
cross join assessments a
where p.slug = 'davos'
on conflict(project_id, assessment_id) do nothing;

insert into project_memberships(project_id, user_id, role, enabled)
select p.id,
       u.id,
       case
         when u.role in ('SUPER_ADMIN', 'ADMIN', 'OPS_ADMIN', 'READONLY_ADMIN', 'PLATFORM_SUPER_ADMIN') then 'PROJECT_ADMIN'
         else 'PARTICIPANT'
       end,
       u.enabled
from projects p
cross join users u
where p.slug = 'davos'
on conflict(project_id, user_id) do nothing;

update users set role = 'PLATFORM_SUPER_ADMIN' where role = 'SUPER_ADMIN';
update users set role = 'USER' where role in ('ADMIN', 'OPS_ADMIN', 'READONLY_ADMIN');
