SET @email_verified_column_count = (
    SELECT COUNT(1)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'users'
      AND column_name = 'email_verified'
);

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS email_verified tinyint(1) NOT NULL DEFAULT 0 AFTER terms_accepted,
    ADD COLUMN IF NOT EXISTS email_verified_at varchar(255) NULL AFTER email_verified;

SET @activate_existing_users_sql = IF(@email_verified_column_count = 0, 'UPDATE users SET email_verified = 1 WHERE email_verified = 0', 'SELECT 1');
PREPARE activate_existing_users_stmt FROM @activate_existing_users_sql;
EXECUTE activate_existing_users_stmt;
DEALLOCATE PREPARE activate_existing_users_stmt;

CREATE TABLE IF NOT EXISTS user_email_verification_tokens (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    token_hash char(64) NOT NULL,
    created_at datetime NOT NULL,
    expires_at datetime NOT NULL,
    used_at datetime NULL,
    ip varchar(255) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_email_verification_token_hash (token_hash),
    KEY idx_email_verification_user_id (user_id),
    KEY idx_email_verification_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO template_mail (id, title, content)
VALUES (
    5,
    'Validez votre compte StudioDesk',
    'Bienvenue sur <strong>[title_site]</strong> !<br><br>Pour activer votre compte et accéder à votre espace, cliquez sur le lien ci-dessous :<br><br><a href="[validation_link]" target="_blank" style="display:inline-block;background:#251bd7;color:#ffffff;text-decoration:none;padding:12px 18px;border-radius:8px;font-weight:700;">Valider mon compte</a><br><br>Ce lien est personnel et à usage unique. Si vous n''êtes pas à l''origine de cette inscription, vous pouvez ignorer cet email.'
)
ON DUPLICATE KEY UPDATE
    title = VALUES(title),
    content = VALUES(content);
