CREATE DATABASE IF NOT EXISTS sistem_pendaftaran CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sistem_pendaftaran;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    activation_token_hash CHAR(64) NULL,
    activation_expires_at DATETIME NULL,
    email_verified_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    registration_locked TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_activation (activation_token_hash),
    INDEX idx_locked (registration_locked)
);

CREATE TABLE registrants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    public_id VARCHAR(16) NOT NULL UNIQUE,
    username VARCHAR(150) NOT NULL,
    namaTim VARCHAR(190) NOT NULL,
    email VARCHAR(190) NOT NULL,
    gambar VARCHAR(255) NULL,
    alamat VARCHAR(255) NULL,
    jenis_kelamin ENUM('Putra','Putri') NOT NULL,
    jenis_pertandingan JSON NOT NULL,
    tanggal_lahir DATE NOT NULL,
    kelompok_umur VARCHAR(50) NOT NULL,
    kelas_pertandingan JSON NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reg_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_reg_user (user_id)
);

CREATE TABLE admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE competition_classes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kelompok_umur VARCHAR(50) NOT NULL,
    jenis_kelamin ENUM('Putra','Putri') NOT NULL,
    jenis_pertandingan VARCHAR(50) NOT NULL,
    nama_kelas VARCHAR(150) NOT NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_competition_class (kelompok_umur, jenis_kelamin, jenis_pertandingan, nama_kelas),
    INDEX idx_class_filter (kelompok_umur, jenis_kelamin, jenis_pertandingan, aktif)
);

INSERT INTO competition_classes (kelompok_umur, jenis_kelamin, jenis_pertandingan, nama_kelas) VALUES
('PRA USIA DINI','Putra','OPEN KATA','Kata Perorangan'),
('PRA USIA DINI','Putra','OPEN KUMITE','Kumite - 25 Kg'),
('PRA USIA DINI','Putra','OPEN KUMITE','Kumite + 25 Kg'),
('PRA USIA DINI','Putri','OPEN KATA','Kata Perorangan'),
('PRA USIA DINI','Putri','OPEN KUMITE','Kumite - 20 Kg'),
('PRA USIA DINI','Putri','OPEN KUMITE','Kumite + 20 Kg');

-- Password contoh: GantiPasswordAdmin
INSERT INTO admins (username, password_hash)
VALUES ('admin', '$2y$12$pItDVYHDHjf9NvYh2o5K.ukxfaMhXdeTM.pIFPQ0E6One6b6Mv9di');