-- 1. Create external_auditors table for external co-auditors
CREATE TABLE IF NOT EXISTS external_auditors (
    external_auditor_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    organization VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Alter coauditors: add id as PK (allows multiple co-auditors with same name), add FK columns
ALTER TABLE coauditors DROP PRIMARY KEY;
ALTER TABLE coauditors ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
ALTER TABLE coauditors
ADD COLUMN co_auditor_user_id INT NULL AFTER co_auditor_type,
ADD COLUMN external_auditor_id INT NULL AFTER co_auditor_user_id,
ADD CONSTRAINT fk_coauditors_user FOREIGN KEY (co_auditor_user_id) REFERENCES users(user_id) ON DELETE SET NULL,
ADD CONSTRAINT fk_coauditors_external FOREIGN KEY (external_auditor_id) REFERENCES external_auditors(external_auditor_id) ON DELETE SET NULL;

-- Existing rows keep co_auditor_name. New rows set co_auditor_user_id (internal) or external_auditor_id (external) and co_auditor_name from joined table.
