-- Create OTP verification table
CREATE TABLE IF NOT EXISTS `otp_verification` (
  `num` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `otp` varchar(6) NOT NULL,
  `expiry` datetime NOT NULL,
  `used` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`num`),
  KEY `email` (`email`),
  KEY `expiry` (`expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Update members table
ALTER TABLE members 
ADD COLUMN IF NOT EXISTS email varchar(255) AFTER username,
ADD COLUMN IF NOT EXISTS password varchar(255) AFTER email,
ADD COLUMN IF NOT EXISTS first_name varchar(255) AFTER password,
ADD COLUMN IF NOT EXISTS last_name varchar(255) AFTER first_name,
ADD COLUMN IF NOT EXISTS profile_picture varchar(255) AFTER last_name; 