backend/migrations/004_employee_profiles.sqlSQL
CREATE TABLE IF NOT EXISTS employee_profiles (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
user_id CHAR(36) NULL,
employee_number VARCHAR(100) NOT NULL,
first_name VARCHAR(120) NOT NULL,
last_name VARCHAR(120) NOT NULL,
email VARCHAR(320) NOT NULL DEFAULT '',
phone VARCHAR(80) NOT NULL DEFAULT '',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
row_version INT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
deleted_at DATETIME NULL,
UNIQUE KEY employee_profiles_tenant_id (tenant_id, id),
UNIQUE KEY employee_profiles_tenant_number (tenant_id, employee_number),
UNIQUE KEY employee_profiles_tenant_user (tenant_id, user_id),
INDEX employee_profiles_tenant_name (tenant_id, last_name, first_name),
CONSTRAINT employee_profiles_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id),
CONSTRAINT employee_profiles_membership_fk FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_memberships(tenant_id, user_id)
);
-- Preserve pre-existing assignments without silently making their unknown employees usable.
-- Such legacy placeholders remain inactive until an administrator completes and activates them.
INSERT IGNORE INTO employee_profiles
(id, tenant_id, user_id, employee_number, first_name, last_name, email, phone, is_active, created_at, updated_at)
SELECT DISTINCT
assignment.employee_id,
assignment.tenant_id,
NULL,
CONCAT('legacy-', assignment.employee_id),
'Unbekannt',
'Altdaten',
'',
'',
FALSE,
UTC_TIMESTAMP(),
UTC_TIMESTAMP()
FROM job_assignments assignment
LEFT JOIN employee_profiles employee
ON employee.id = assignment.employee_id AND employee.tenant_id = assignment.tenant_id
WHERE employee.id IS NULL;
SET @employee_assignment_fk_sql = IF(
EXISTS(
SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND TABLE_NAME = 'job_assignments'
AND CONSTRAINT_NAME = 'assignments_employee_fk'
),
'SELECT 1',
'ALTER TABLE job_assignments ADD CONSTRAINT assignments_employee_fk FOREIGN KEY (tenant_id, employee_id) REFERENCES employee_profiles(tenant_id, id)'
);
PREPARE employee_assignment_fk_statement FROM @employee_assignment_fk_sql;
EXECUTE employee_assignment_fk_statement;
DEALLOCATE PREPARE employee_assignment_fk_statement;