-- Run this ONLY when upgrading a database created by the v1 package.
-- Take a backup first. New installations should import schema.sql instead.

ALTER TABLE users
  MODIFY role ENUM('super_admin','company_admin','assessment_manager','examiner','recruiter','candidate') NOT NULL;

-- Super administrators are platform-wide and must not be bound to one tenant.
UPDATE users SET company_id = NULL WHERE role = 'super_admin';

ALTER TABLE candidates
  ADD UNIQUE KEY uq_candidate_tenant_id (company_id, id);

ALTER TABLE papers
  DROP INDEX code,
  ADD UNIQUE KEY uq_paper_company_code (company_id, code),
  ADD UNIQUE KEY uq_paper_tenant_id (company_id, id);

ALTER TABLE users
  ADD KEY idx_users_company (company_id),
  ADD CONSTRAINT fk_users_candidate_tenant
    FOREIGN KEY (company_id, candidate_id) REFERENCES candidates(company_id, id);

ALTER TABLE assignments
  ADD CONSTRAINT fk_assign_candidate_tenant
    FOREIGN KEY (company_id, candidate_id) REFERENCES candidates(company_id, id),
  ADD CONSTRAINT fk_assign_paper_tenant
    FOREIGN KEY (company_id, paper_id) REFERENCES papers(company_id, id);