-- IMPORT VIA CPANEL: buat database terlebih dahulu di MySQL Databases,
-- lalu pilih database tersebut di phpMyAdmin sebelum mengimpor file ini.
CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(30) NULL,
 address TEXT NULL,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','user') NOT NULL DEFAULT 'user',
 status ENUM('pending','approved','blocked') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE enrollments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE,
 status ENUM('pending','accepted','rejected') NOT NULL DEFAULT 'pending',
 fee INT UNSIGNED NOT NULL DEFAULT 1500000,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_enroll_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 enrollment_id BIGINT UNSIGNED NOT NULL,
 order_id VARCHAR(80) NOT NULL UNIQUE,
 method ENUM('manual','pakasir') NOT NULL DEFAULT 'manual',
 amount INT UNSIGNED NOT NULL,
 status ENUM('pending','paid','rejected','expired') NOT NULL DEFAULT 'pending',
 provider_txn_id VARCHAR(120) NULL,
 payment_url TEXT NULL,
 verified_by BIGINT UNSIGNED NULL,
 verified_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_pay_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_pay_enrollment FOREIGN KEY(enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
 CONSTRAINT fk_pay_admin FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_pay_status(status)
) ENGINE=InnoDB;
CREATE TABLE activities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(180) NOT NULL,
 description TEXT NOT NULL,
 event_date DATE NULL,
 photo_path VARCHAR(255) NULL,
 status ENUM('pending','published','rejected') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_activity_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX idx_activity_status(status)
) ENGINE=InnoDB;
CREATE TABLE testimonials (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 message TEXT NOT NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_testimonial_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX idx_testimonial_status(status)
) ENGINE=InnoDB;
CREATE TABLE settings (
 setting_key VARCHAR(80) PRIMARY KEY,
 setting_value TEXT NOT NULL
) ENGINE=InnoDB;
INSERT INTO settings(setting_key,setting_value) VALUES('quota','10');