create table contact (
    id          bigserial primary key,
    category    varchar(20)  not null,   -- 'APPLICANT' | 'MJ' | 'LAWYER'
    name        varchar(100) not null,
    affiliation varchar(200),            -- 소속 (신청기관 담당자는 기관명이 들어감)
    dept_name   varchar(200),
    title       varchar(100),
    phone       varchar(50),
    email       varchar(200),
    created_at  timestamptz  not null default now(),
    updated_at  timestamptz  not null default now()
);

alter table organization
    add column applicant_contact_id bigint references contact (id) on delete set null,
    add column mj_contact_id        bigint references contact (id) on delete set null,
    add column lawyer_contact_id    bigint references contact (id) on delete set null;

-- 백필 1/3: 신청기관 담당자. 기관 행마다 최대 1명이므로 기관 id로 바로 correlate 한다.
do $$
declare
    org_row record;
    new_contact_id bigint;
begin
    for org_row in
        select id, org_name, dept_name, manager_name, manager_title, manager_phone, manager_email
        from organization
        where manager_name is not null
    loop
        insert into contact (category, name, affiliation, dept_name, title, phone, email)
        values ('APPLICANT', org_row.manager_name, org_row.org_name, org_row.dept_name,
                org_row.manager_title, org_row.manager_phone, org_row.manager_email)
        returning id into new_contact_id;

        update organization set applicant_contact_id = new_contact_id where id = org_row.id;
    end loop;
end $$;

-- 백필 2/3: 문정원 담당자. 여러 기관이 같은 사람을 공유할 수 있어 distinct 튜플당 1행만 만든다.
do $$
declare
    mj_row record;
    new_contact_id bigint;
begin
    for mj_row in
        select distinct mj_manager_name, mj_dept_name, mj_manager_phone, mj_manager_email
        from organization
        where mj_manager_name is not null
    loop
        insert into contact (category, name, dept_name, phone, email)
        values ('MJ', mj_row.mj_manager_name, mj_row.mj_dept_name,
                mj_row.mj_manager_phone, mj_row.mj_manager_email)
        returning id into new_contact_id;

        update organization
        set mj_contact_id = new_contact_id
        where mj_manager_name = mj_row.mj_manager_name
          and mj_dept_name is not distinct from mj_row.mj_dept_name
          and mj_manager_phone is not distinct from mj_row.mj_manager_phone
          and mj_manager_email is not distinct from mj_row.mj_manager_email;
    end loop;
end $$;

-- 백필 3/3: 담당 변호사. 배정일은 사람이 아니라 기관↔변호사 관계에 속하므로 organization에 남긴다.
do $$
declare
    lawyer_row record;
    new_contact_id bigint;
begin
    for lawyer_row in
        select distinct lawyer_name, lawyer_phone, lawyer_email
        from organization
        where lawyer_name is not null
    loop
        insert into contact (category, name, phone, email)
        values ('LAWYER', lawyer_row.lawyer_name, lawyer_row.lawyer_phone, lawyer_row.lawyer_email)
        returning id into new_contact_id;

        update organization
        set lawyer_contact_id = new_contact_id
        where lawyer_name = lawyer_row.lawyer_name
          and lawyer_phone is not distinct from lawyer_row.lawyer_phone
          and lawyer_email is not distinct from lawyer_row.lawyer_email;
    end loop;
end $$;

alter table organization
    drop column dept_name,
    drop column manager_name,
    drop column manager_title,
    drop column manager_phone,
    drop column manager_email,
    drop column mj_dept_name,
    drop column mj_manager_name,
    drop column mj_manager_phone,
    drop column mj_manager_email,
    drop column lawyer_name,
    drop column lawyer_phone,
    drop column lawyer_email;