-- API Tokens Table
-- Stores API authentication tokens for mobile app and third-party integrations

CREATE TABLE IF NOT EXISTS api_tokens (
    token_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_used_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user (user_id),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='API authentication tokens for mobile app and third-party access';

-- Add api_key column to users table if it doesn't exist
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS api_key VARCHAR(64) NULL UNIQUE AFTER password_hash,
ADD INDEX IF NOT EXISTS idx_api_key (api_key);
