-- sql/seed.sql
CREATE DATABASE IF NOT EXISTS gym_lancarfit;
USE gym_lancarfit;

CREATE TABLE admin (
  id_admin INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  nama VARCHAR(100) DEFAULT 'Admin'
);

CREATE TABLE customer (
  id_customer INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  no_telp VARCHAR(20),
  tanggal_daftar DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE paket (
  id_paket INT AUTO_INCREMENT PRIMARY KEY,
  nama_paket VARCHAR(100) NOT NULL,
  deskripsi TEXT,
  harga DECIMAL(12,2) NOT NULL,
  durasi VARCHAR(50)
);

CREATE TABLE trainer (
  id_trainer INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  spesialis VARCHAR(100)
);

CREATE TABLE jadwal (
  id_jadwal INT AUTO_INCREMENT PRIMARY KEY,
  id_paket INT NOT NULL,
  id_trainer INT,
  tanggal DATE NOT NULL,
  waktu TIME NOT NULL,
  kapasitas INT DEFAULT 20,
  lokasi VARCHAR(100),
  FOREIGN KEY (id_paket) REFERENCES paket(id_paket) ON DELETE CASCADE,
  FOREIGN KEY (id_trainer) REFERENCES trainer(id_trainer) ON DELETE SET NULL
);

CREATE TABLE reservasi (
  id_reservasi INT AUTO_INCREMENT PRIMARY KEY,
  id_customer INT NOT NULL,
  id_jadwal INT NOT NULL,
  tanggal_reservasi DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',
  note TEXT,
  FOREIGN KEY (id_customer) REFERENCES customer(id_customer) ON DELETE CASCADE,
  FOREIGN KEY (id_jadwal) REFERENCES jadwal(id_jadwal) ON DELETE CASCADE
);

CREATE TABLE pembayaran (
  id_pembayaran INT AUTO_INCREMENT PRIMARY KEY,
  id_reservasi INT NOT NULL,
  metode VARCHAR(50) DEFAULT 'transfer',
  jumlah DECIMAL(12,2),
  bukti_file VARCHAR(255),
  tanggal_pembayaran DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('pending','verified','rejected') DEFAULT 'pending',
  FOREIGN KEY (id_reservasi) REFERENCES reservasi(id_reservasi) ON DELETE CASCADE
);