ALTER TABLE users
    ADD COLUMN client_id INT UNSIGNED NULL AFTER password_hash,
    ADD COLUMN plant_id INT UNSIGNED NULL AFTER client_id;

ALTER TABLE users
    ADD CONSTRAINT fk_users_client FOREIGN KEY (client_id) REFERENCES clients(id),
    ADD CONSTRAINT fk_users_plant FOREIGN KEY (plant_id) REFERENCES plants(id);

UPDATE users
SET client_id = 1, plant_id = 1
WHERE email = 'support@erp.local' AND client_id IS NULL;

UPDATE users
SET user_id = email
WHERE user_id IS NULL OR user_id = '';

ALTER TABLE tickets
    ADD COLUMN requester_email VARCHAR(150) NULL AFTER requester_name;

UPDATE tickets
SET requester_email = requester_user_id
WHERE requester_email IS NULL;

INSERT INTO users (name, user_id, email, password_hash, client_id, plant_id, role, is_verified, created_at, updated_at)
SELECT 'ERP Admin', 'admin@erp.local', 'admin@erp.local', '$2y$10$D1DRtutoJmF6QSLn.ORx1.KYVQFCfW64igpyg5qW0yzr/kRdqFIWy', 1, 1, 'admin', 1, NOW(), NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = 'admin@erp.local'
);
