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

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 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');
