-- DDL Utama Aplikasi Turnamen (Update 2026-04-10)

-- 1. Tabel Pengguna & Sesi
CREATE TABLE `users` (
  `id_user` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `nama_lengkap` VARCHAR(255) NOT NULL,
  `level` ENUM('admin', 'user') NOT NULL DEFAULT 'user',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ci_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  `data` blob NOT NULL,
  KEY `ci_sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Tabel Inti Turnamen
CREATE TABLE `turnamen` (
  `id_turnamen` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_user_pengelola` INT(11) UNSIGNED NULL,
  `nama_turnamen` VARCHAR(255) NOT NULL,
  `kode_turnamen` VARCHAR(10) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `cabor` VARCHAR(100) NOT NULL,
  `tanggal_mulai` DATE,
  `tanggal_selesai` DATE,
  `lokasi` VARCHAR(255),
  `logo` VARCHAR(255),
  `poster` VARCHAR(255),
  `banner` VARCHAR(255),
  `sistem_kompetisi` ENUM('penuh', 'setengah_kompetisi', 'gugur') NOT NULL,
  `maksimal_peserta` INT(11),
  `status` ENUM('draft', 'pendaftaran', 'berjalan', 'selesai') DEFAULT 'draft',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_turnamen`),
  FOREIGN KEY (`id_user_pengelola`) REFERENCES `users`(`id_user`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `kategori_turnamen` (
  `id_kategori` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_turnamen` INT(11) UNSIGNED NOT NULL,
  `nama_kategori` VARCHAR(100) NOT NULL,
  `slug_kategori` VARCHAR(100) NOT NULL,
  `maksimal_peserta` INT(11) DEFAULT 32,
  `status_kategori` ENUM('pendaftaran', 'berjalan', 'selesai') DEFAULT 'pendaftaran',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_kategori`),
  FOREIGN KEY (`id_turnamen`) REFERENCES `turnamen`(`id_turnamen`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pool_turnamen` (
    `id_pool` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_turnamen` INT(11) UNSIGNED NOT NULL,
    `id_kategori` INT(11) UNSIGNED NULL,
    `nama_pool` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_turnamen`) REFERENCES `turnamen`(`id_turnamen`) ON DELETE CASCADE,
    FOREIGN KEY (`id_kategori`) REFERENCES `kategori_turnamen`(`id_kategori`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `peserta` (
  `id_peserta` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_turnamen` INT(11) UNSIGNED NOT NULL,
  `id_kategori` INT(11) UNSIGNED NULL,
  `id_pool` INT(11) UNSIGNED NULL,
  `nama_tim` VARCHAR(255) NOT NULL,
  `kode_tim` VARCHAR(10) NOT NULL,
  `nama_manajer` VARCHAR(255),
  `kontak` VARCHAR(100),
  `status_verifikasi` ENUM('pending', 'terverifikasi') DEFAULT 'pending',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_peserta`),
  FOREIGN KEY (`id_turnamen`) REFERENCES `turnamen`(`id_turnamen`) ON DELETE CASCADE,
  FOREIGN KEY (`id_kategori`) REFERENCES `kategori_turnamen`(`id_kategori`) ON DELETE SET NULL,
  FOREIGN KEY (`id_pool`) REFERENCES `pool_turnamen`(`id_pool`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pertandingan` (
  `id_pertandingan` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_turnamen` INT(11) UNSIGNED NOT NULL,
  `fase` VARCHAR(100) NOT NULL,
  `peserta_1` INT(11) UNSIGNED NULL,
  `peserta_2` INT(11) UNSIGNED NULL,
  `skor_1` INT(11) DEFAULT 0,
  `skor_2` INT(11) DEFAULT 0,
  `waktu_pelaksanaan` DATETIME NULL,
  `status_pertandingan` ENUM('belum_dimulai', 'berjalan', 'selesai') DEFAULT 'belum_dimulai',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_pertandingan`),
  FOREIGN KEY (`id_turnamen`) REFERENCES `turnamen`(`id_turnamen`) ON DELETE CASCADE,
  FOREIGN KEY (`peserta_1`) REFERENCES `peserta`(`id_peserta`) ON DELETE SET NULL,
  FOREIGN KEY (`peserta_2`) REFERENCES `peserta`(`id_peserta`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Tabel Monitoring & Logs
CREATE TABLE `sys_performance_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `method` varchar(10) NOT NULL,
  `execution_time` float NOT NULL,
  `memory_usage` varchar(20) NOT NULL,
  `query_count` int(11) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_db_table_snapshots` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `table_name` VARCHAR(255) NOT NULL,
    `row_count` BIGINT NOT NULL,
    `size_mb` FLOAT NOT NULL,
    `snapshot_month` INT NOT NULL,
    `snapshot_year` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_auth_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nip` varchar(100) DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text NOT NULL,
  `activity` enum('LOGIN_SUCCESS','LOGIN_FAILED','LOGOUT','SESSION_EXPIRED') NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sys_activity_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nip` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `action` enum('INSERT','UPDATE','DELETE','EXPORT','PRINT') NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `data_before` longtext DEFAULT NULL,
  `data_after` longtext DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;