-- ============================================================
-- GYM BOOKING SYSTEM — DATABASE SCHEMA
-- Stack: PHP Native + MySQL
-- Charset: utf8mb4_unicode_ci
-- ============================================================

CREATE DATABASE IF NOT EXISTS gym_booking
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gym_booking;

-- ============================================================
-- 1. USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('member','admin') NOT NULL DEFAULT 'member',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. KELAS
-- ============================================================
CREATE TABLE IF NOT EXISTS kelas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama_kelas VARCHAR(100) NOT NULL,
  jenis_olahraga VARCHAR(50) NOT NULL,
  instruktur VARCHAR(100) NOT NULL,
  hari VARCHAR(20) NOT NULL,
  jam_mulai TIME NOT NULL,
  jam_selesai TIME NOT NULL,
  kuota INT NOT NULL,
  sisa_slot INT NOT NULL,
  harga INT NOT NULL,
  intensitas ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. KERANJANG
-- ============================================================
CREATE TABLE IF NOT EXISTS keranjang (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  kelas_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  UNIQUE KEY unique_cart_item (user_id, kelas_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. TRANSAKSI
-- ============================================================
CREATE TABLE IF NOT EXISTS transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  total_harga INT NOT NULL DEFAULT 0,
  status ENUM('pending','tervalidasi','ditolak') NOT NULL DEFAULT 'pending',
  bukti_bayar VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. DETAIL_TRANSAKSI
-- ============================================================
CREATE TABLE IF NOT EXISTS detail_transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  transaksi_id INT NOT NULL,
  kelas_id INT NOT NULL,
  harga_satuan INT NOT NULL,
  FOREIGN KEY (transaksi_id) REFERENCES transaksi(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. ABSENSI
-- ============================================================
CREATE TABLE IF NOT EXISTS absensi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  transaksi_id INT NOT NULL,
  user_id INT NOT NULL,
  kelas_id INT NOT NULL,
  status ENUM('hadir','tidak_hadir') NOT NULL DEFAULT 'hadir',
  FOREIGN KEY (transaksi_id) REFERENCES transaksi(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. RATING
-- ============================================================
CREATE TABLE IF NOT EXISTS rating (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  kelas_id INT NOT NULL,
  instruktur VARCHAR(100) NOT NULL,
  nilai INT NOT NULL CHECK (nilai BETWEEN 1 AND 5),
  komentar TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  UNIQUE KEY unique_rating (user_id, kelas_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DATA DUMMY
-- ============================================================

-- Admin (password: admin123)
INSERT INTO users (nama, email, password, role) VALUES
('Admin FITZONE', 'admin@gym.com', '$2y$10$04pOXDO/ts92cqSIaqrm2u3lA9IGeVONYg4yzLyOh42ahU0FOFhDy', 'admin');

-- Members (password: member123)
INSERT INTO users (nama, email, password, role) VALUES
('Andi Pratama', 'andi@mail.com', '$2y$10$JA3vB5epnbxjh//K20.5s.6UTXZmtrtXxW0S6pzpz.KtsyhDMlzAm', 'member'),
('Sari Dewi', 'sari@mail.com', '$2y$10$JA3vB5epnbxjh//K20.5s.6UTXZmtrtXxW0S6pzpz.KtsyhDMlzAm', 'member'),
('Budi Santoso', 'budi@mail.com', '$2y$10$JA3vB5epnbxjh//K20.5s.6UTXZmtrtXxW0S6pzpz.KtsyhDMlzAm', 'member');

-- 6 Kelas Aktif
INSERT INTO kelas (nama_kelas, jenis_olahraga, instruktur, hari, jam_mulai, jam_selesai, kuota, sisa_slot, harga, intensitas, status) VALUES
('Yoga Flow Pagi', 'Yoga', 'Instruktur Maya', 'Senin', '07:00:00', '08:30:00', 20, 15, 75000, 'low', 'aktif'),
('Pilates Core', 'Pilates', 'Instruktur Lina', 'Rabu', '09:00:00', '10:00:00', 15, 10, 85000, 'low', 'aktif'),
('Zumba Party', 'Zumba', 'Instruktur Dian', 'Selasa', '17:00:00', '18:00:00', 25, 18, 65000, 'medium', 'aktif'),
('Spinning Endurance', 'Spinning', 'Instruktur Riko', 'Kamis', '06:00:00', '07:00:00', 20, 8, 90000, 'medium', 'aktif'),
('Muay Thai Combat', 'Muay Thai', 'Instruktur Fajar', 'Jumat', '18:00:00', '19:30:00', 15, 5, 120000, 'high', 'aktif'),
('CrossFit Extreme', 'Gym', 'Instruktur Andi', 'Sabtu', '08:00:00', '09:30:00', 12, 3, 110000, 'high', 'aktif');

-- Transaksi dummy
INSERT INTO transaksi (user_id, total_harga, status, bukti_bayar, created_at) VALUES
(2, 140000, 'tervalidasi', 'bukti_andi_1.jpg', '2026-05-15 10:00:00'),
(3, 65000, 'tervalidasi', 'bukti_sari_1.jpg', '2026-05-16 14:00:00'),
(4, 230000, 'pending', 'bukti_budi_1.jpg', '2026-05-20 09:00:00'),
(2, 120000, 'pending', NULL, '2026-05-21 11:00:00');

-- Detail transaksi dummy
INSERT INTO detail_transaksi (transaksi_id, kelas_id, harga_satuan) VALUES
(1, 1, 75000),
(1, 3, 65000),
(2, 3, 65000),
(3, 5, 120000),
(3, 6, 110000),
(4, 5, 120000);

-- Absensi dummy (hanya untuk transaksi tervalidasi)
INSERT INTO absensi (transaksi_id, user_id, kelas_id, status) VALUES
(1, 2, 1, 'hadir'),
(1, 2, 3, 'hadir'),
(2, 3, 3, 'hadir');

-- Rating dummy
INSERT INTO rating (user_id, kelas_id, instruktur, nilai, komentar) VALUES
(2, 1, 'Instruktur Maya', 5, 'Sangat menenangkan dan profesional!'),
(2, 3, 'Instruktur Dian', 4, 'Seru banget, bikin semangat!'),
(3, 3, 'Instruktur Dian', 5, 'Best zumba class ever!');
