SET NAMES utf8mb4;

ALTER TABLE ssm_job_sheets
    ADD COLUMN IF NOT EXISTS employee_id INT NULL AFTER position_id,
    ADD COLUMN IF NOT EXISTS department VARCHAR(190) NULL AFTER title,
    ADD COLUMN IF NOT EXISTS cor_code VARCHAR(100) NULL AFTER department,
    ADD COLUMN IF NOT EXISTS position_level VARCHAR(100) NULL AFTER cor_code,
    ADD COLUMN IF NOT EXISTS superior_position VARCHAR(190) NULL AFTER position_level,
    ADD COLUMN IF NOT EXISTS workplace VARCHAR(255) NULL AFTER superior_position,
    ADD COLUMN IF NOT EXISTS work_schedule VARCHAR(190) NULL AFTER workplace,

    ADD COLUMN IF NOT EXISTS work_relations LONGTEXT NULL AFTER requirements,
    ADD COLUMN IF NOT EXISTS document_management LONGTEXT NULL AFTER responsibilities,
    ADD COLUMN IF NOT EXISTS confidentiality LONGTEXT NULL AFTER document_management,
    ADD COLUMN IF NOT EXISTS authority_limits LONGTEXT NULL AFTER ssm_duties,
    ADD COLUMN IF NOT EXISTS performance_criteria LONGTEXT NULL AFTER authority_limits,
    ADD COLUMN IF NOT EXISTS working_conditions LONGTEXT NULL AFTER performance_criteria,
    ADD COLUMN IF NOT EXISTS employee_liability LONGTEXT NULL AFTER working_conditions,
    ADD COLUMN IF NOT EXISTS final_provisions LONGTEXT NULL AFTER employee_liability,

    ADD COLUMN IF NOT EXISTS employer_name VARCHAR(190) NULL AFTER effective_date,
    ADD COLUMN IF NOT EXISTS employer_position VARCHAR(190) NULL AFTER employer_name,
    ADD COLUMN IF NOT EXISTS employer_sign_date DATE NULL AFTER employer_position,
    ADD COLUMN IF NOT EXISTS employer_signature_path VARCHAR(500) NULL AFTER employer_sign_date,

    ADD COLUMN IF NOT EXISTS employee_name VARCHAR(190) NULL AFTER employer_signature_path,
    ADD COLUMN IF NOT EXISTS employee_sign_date DATE NULL AFTER employee_name,
    ADD COLUMN IF NOT EXISTS employee_signature_path VARCHAR(500) NULL AFTER employee_sign_date;

-- Adaugă relația doar dacă nu există deja.
SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ssm_job_sheets'
      AND CONSTRAINT_NAME = 'fk_ssm_job_sheets_employee'
);

SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE ssm_job_sheets
     ADD CONSTRAINT fk_ssm_job_sheets_employee
     FOREIGN KEY (employee_id)
     REFERENCES ssm_employees(id)
     ON DELETE SET NULL',
    'SELECT 1'
);

PREPARE statement FROM @fk_sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;