-- WC360 Users Table Extension for Authentication System
-- Bu dosya mevcut users tablosunu auth sistemi için genişletir
-- Mevcut verileri korur, sadece yeni alanlar ekler

-- 1. Yeni alanları ekle (eğer mevcut değilse)
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS role ENUM('user', 'admin', 'moderator') DEFAULT 'user' AFTER phone,
ADD COLUMN IF NOT EXISTS status ENUM('active', 'suspended', 'pending') DEFAULT 'active' AFTER role,
ADD COLUMN IF NOT EXISTS is_verified BOOLEAN DEFAULT FALSE AFTER is_active,
ADD COLUMN IF NOT EXISTS avatar_url VARCHAR(255) NULL AFTER is_verified,
ADD COLUMN IF NOT EXISTS bio TEXT NULL AFTER avatar_url,
ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP NULL AFTER bio,
ADD COLUMN IF NOT EXISTS login_count INT DEFAULT 0 AFTER last_login_at,
ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMP NULL AFTER login_count;

-- 2. İndeksler ekle (performans için)
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active);

-- 3. User sessions tablosu oluştur
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    user_agent TEXT,
    ip_address VARCHAR(45),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sessions_user_id (user_id),
    INDEX idx_sessions_token_hash (token_hash),
    INDEX idx_sessions_expires_at (expires_at),
    INDEX idx_sessions_active (is_active)
);

-- 4. Mevcut users'ları güncelle (eğer varsa)
UPDATE users 
SET 
    role = 'user',
    status = 'active',
    is_verified = TRUE,
    login_count = 0
WHERE role IS NULL OR role = '';

-- 5. İlk admin user oluştur (eğer admin yoksa)
INSERT IGNORE INTO users (
    username, email, password_hash, full_name, 
    role, status, is_active, is_verified, created_at
) VALUES (
    'admin', 
    'admin@wc360.local', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password: 123456
    'WC360 Admin',
    'admin',
    'active',
    TRUE,
    TRUE,
    NOW()
);

-- 6. Test user oluştur (development için)
INSERT IGNORE INTO users (
    username, email, password_hash, full_name, 
    role, status, is_active, is_verified, created_at
) VALUES (
    'testuser', 
    'test@wc360.local', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password: 123456
    'Test User',
    'user',
    'active',
    TRUE,
    TRUE,
    NOW()
);

-- Sonuç mesajı
SELECT 'Users table successfully extended for authentication system!' as result; 