-- MySQL Database Schema for Rover & Scout Question Bank System
CREATE DATABASE IF NOT EXISTS rover_test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rover_test_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default admin user (Password: admin123)
INSERT IGNORE INTO users (username, password_hash, full_name) VALUES (
    'admin',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'District Training Commissioner'
);

CREATE TABLE IF NOT EXISTS questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    question_hash CHAR(64) NOT NULL UNIQUE,
    answer TEXT NOT NULL,
    question_type VARCHAR(50) NOT NULL DEFAULT 'simple',
    question_part VARCHAR(100) NOT NULL DEFAULT 'General',
    sopan VARCHAR(50) NOT NULL DEFAULT 'Pravesh',
    wing VARCHAR(50) NOT NULL DEFAULT 'Rover',
    image_path VARCHAR(255) DEFAULT NULL,
    is_custom TINYINT(1) NOT NULL DEFAULT 0,
    auto_include TINYINT(1) NOT NULL DEFAULT 1,
    manual_include TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_wing (wing),
    INDEX idx_sopan (sopan),
    INDEX idx_part (question_part),
    INDEX idx_type (question_type)
) ENGINE=InnoDB;