-- SeedCrack Dashboard Database Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.2+

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: "password")
INSERT INTO users (username, password_hash, email) VALUES 
('mitch', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com');

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    mc_version VARCHAR(50) DEFAULT '1.18+',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Observations table
CREATE TABLE IF NOT EXISTS observations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    structure_type ENUM(
        'igloo',
        'desert_pyramid',
        'jungle_temple',
        'swamp_hut',
        'shipwreck',
        'pillager_outpost',
        'ocean_monument'
    ) NOT NULL,
    x_block INT NOT NULL,
    z_block INT NOT NULL,
    x_chunk INT NOT NULL,
    z_chunk INT NOT NULL,
    is_confirmed TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_id (project_id),
    INDEX idx_structure_type (structure_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Crack runs table
CREATE TABLE IF NOT EXISTS crack_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    status ENUM('queued', 'running', 'done', 'error') DEFAULT 'queued',
    progress INT DEFAULT 0,
    log LONGTEXT,
    result_seed BIGINT NULL,
    result_candidates LONGTEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_id (project_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SQLite Compatible Version (for reference)
-- If using SQLite, use these statements instead:

/*
CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    mc_version TEXT DEFAULT '1.18+',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS observations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    structure_type TEXT NOT NULL CHECK(structure_type IN (
        'igloo', 'desert_pyramid', 'jungle_temple', 'swamp_hut',
        'shipwreck', 'pillager_outpost', 'ocean_monument'
    )),
    x_block INTEGER NOT NULL,
    z_block INTEGER NOT NULL,
    x_chunk INTEGER NOT NULL,
    z_chunk INTEGER NOT NULL,
    is_confirmed INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS crack_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    status TEXT DEFAULT 'queued' CHECK(status IN ('queued', 'running', 'done', 'error')),
    progress INTEGER DEFAULT 0,
    log TEXT,
    result_seed INTEGER NULL,
    result_candidates TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
*/
