-- ============================================
-- DATABASE (OPTIONAL)
-- ============================================
-- CREATE DATABASE IF NOT EXISTS your_database;
-- USE your_database;

-- ============================================
-- TABLE: users
-- ============================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User ID',
    username VARCHAR(50) UNIQUE NOT NULL COMMENT 'Username for login',
    password VARCHAR(255) NOT NULL COMMENT 'Hashed password',
    role ENUM('admin', 'agent') DEFAULT 'agent' COMMENT 'User role',
    last_login TIMESTAMP NULL COMMENT 'Last login timestamp',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Account active status',
    two_factor_enabled BOOLEAN DEFAULT FALSE COMMENT '2FA enabled flag',
    two_factor_verified_at TIMESTAMP NULL COMMENT '2FA verification timestamp',
    license_key VARCHAR(255) NULL COMMENT 'License key bound to this user',
    license_bound_at TIMESTAMP NULL COMMENT 'When license was bound to this user',
    license_expire_date TIMESTAMP NULL COMMENT 'License expiration date',
    license_status VARCHAR(20) DEFAULT 'inactive' COMMENT 'License status',
    license_page_id VARCHAR(50) NULL COMMENT 'Facebook Page ID bound to license',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation time',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_active (is_active),
    INDEX idx_2fa (two_factor_enabled),
    INDEX idx_license_key (license_key),
    INDEX idx_license_expire (license_expire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User accounts (admin & agent)';

-- ============================================
-- INSERT ADMIN (DEFAULT)
-- Username: admin
-- Password: admin123
-- ============================================

INSERT INTO users (username, password, role, is_active, created_at)
VALUES (
    'admin',
    '$2y$10$wH0Z8X2XyZb6H9QkzZ5bI.8KX6M6d7lQy0s8Q5sQkG6R5V1bZxY9K', -- password: admin123
    'admin',
    1,
    NOW()
);

-- ============================================
-- DONE
-- ============================================