-- v1.2.0 — SMS OTP Login (ملی‌پیامک)
-- Safe to re-run

CREATE TABLE IF NOT EXISTS otp_codes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    phone       VARCHAR(20)  NOT NULL,
    code        VARCHAR(10)  NOT NULL,
    expires_at  DATETIME     NOT NULL,
    used        TINYINT(1)   DEFAULT 0,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_otp_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
    ('sms_enabled',  '0'),
    ('sms_username', ''),
    ('sms_password', ''),
    ('sms_from',     ''),
    ('sms_otp_text', 'کد ورود به سامانه: {code}');

-- Add phone column to users table (safe: skips if already exists)
DROP PROCEDURE IF EXISTS _v120_AddPhoneToUsers;
DELIMITER ;;
CREATE PROCEDURE _v120_AddPhoneToUsers()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'phone'
  ) THEN
    ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL DEFAULT NULL AFTER email;
    ALTER TABLE users ADD INDEX idx_users_phone (phone);
  END IF;
END ;;
DELIMITER ;
CALL _v120_AddPhoneToUsers();
DROP PROCEDURE IF EXISTS _v120_AddPhoneToUsers;