-- Database Sistem Pemesanan Tiket Wisata
-- DBMS: MySQL 8.x / MariaDB 10.4+

DROP DATABASE IF EXISTS wisata_tiket;
CREATE DATABASE wisata_tiket
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE wisata_tiket;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS refund_pembayaran;
DROP TABLE IF EXISTS ulasan;
DROP TABLE IF EXISTS pembayaran;
DROP TABLE IF EXISTS detail_pemesanan;
DROP TABLE IF EXISTS pemesanan;
DROP TABLE IF EXISTS jadwal_wisata;
DROP TABLE IF EXISTS tiket_wisata;
DROP TABLE IF EXISTS wisata;
DROP TABLE IF EXISTS kategori_wisata;
DROP TABLE IF EXISTS pelanggan;
DROP TABLE IF EXISTS pengguna;
DROP TABLE IF EXISTS role_pengguna;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE role_pengguna (
    id_role INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama_role VARCHAR(30) NOT NULL UNIQUE,
    deskripsi VARCHAR(150) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE pengguna (
    id_pengguna BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_role INT UNSIGNED NOT NULL,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    no_hp VARCHAR(20) NULL,
    status ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pengguna_role
        FOREIGN KEY (id_role) REFERENCES role_pengguna(id_role)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE pelanggan (
    id_pelanggan BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pengguna BIGINT UNSIGNED NOT NULL UNIQUE,
    nik VARCHAR(20) NULL UNIQUE,
    alamat TEXT NULL,
    tanggal_lahir DATE NULL,
    jenis_kelamin ENUM('L', 'P') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pelanggan_pengguna
        FOREIGN KEY (id_pengguna) REFERENCES pengguna(id_pengguna)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE kategori_wisata (
    id_kategori INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama_kategori VARCHAR(80) NOT NULL UNIQUE,
    deskripsi VARCHAR(200) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE wisata (
    id_wisata BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_kategori INT UNSIGNED NOT NULL,
    nama_wisata VARCHAR(120) NOT NULL,
    lokasi VARCHAR(160) NOT NULL,
    alamat TEXT NULL,
    deskripsi TEXT NULL,
    jam_buka TIME NOT NULL,
    jam_tutup TIME NOT NULL,
    status ENUM('aktif', 'nonaktif', 'renovasi') NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_wisata_kategori
        FOREIGN KEY (id_kategori) REFERENCES kategori_wisata(id_kategori)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_wisata_jam
        CHECK (jam_tutup > jam_buka)
) ENGINE=InnoDB;

CREATE TABLE tiket_wisata (
    id_tiket BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_wisata BIGINT UNSIGNED NOT NULL,
    nama_tiket VARCHAR(80) NOT NULL,
    tipe_hari ENUM('weekday', 'weekend', 'libur') NOT NULL DEFAULT 'weekday',
    kategori_pengunjung ENUM('dewasa', 'anak', 'pelajar', 'mancanegara') NOT NULL DEFAULT 'dewasa',
    harga DECIMAL(12,2) NOT NULL,
    status ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tiket_wisata
        FOREIGN KEY (id_wisata) REFERENCES wisata(id_wisata)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT uq_tiket_wisata
        UNIQUE (id_wisata, nama_tiket, tipe_hari, kategori_pengunjung),
    CONSTRAINT chk_tiket_harga
        CHECK (harga >= 0)
) ENGINE=InnoDB;

CREATE TABLE jadwal_wisata (
    id_jadwal BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_wisata BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    kuota INT UNSIGNED NOT NULL,
    kuota_tersedia INT UNSIGNED NOT NULL,
    status ENUM('tersedia', 'penuh', 'ditutup') NOT NULL DEFAULT 'tersedia',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_jadwal_wisata
        FOREIGN KEY (id_wisata) REFERENCES wisata(id_wisata)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT uq_jadwal_wisata
        UNIQUE (id_wisata, tanggal),
    CONSTRAINT chk_jadwal_kuota
        CHECK (kuota > 0 AND kuota_tersedia <= kuota)
) ENGINE=InnoDB;

CREATE TABLE pemesanan (
    id_pemesanan BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode_pemesanan VARCHAR(30) NULL UNIQUE,
    id_pelanggan BIGINT UNSIGNED NOT NULL,
    id_jadwal BIGINT UNSIGNED NOT NULL,
    tanggal_pesan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    batas_bayar DATETIME NOT NULL,
    jumlah_tiket INT UNSIGNED NOT NULL DEFAULT 0,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    diskon DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_bayar DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('menunggu_pembayaran', 'dibayar', 'dibatalkan', 'selesai') NOT NULL DEFAULT 'menunggu_pembayaran',
    catatan VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pemesanan_pelanggan
        FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_pemesanan_jadwal
        FOREIGN KEY (id_jadwal) REFERENCES jadwal_wisata(id_jadwal)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_pemesanan_total
        CHECK (
            jumlah_tiket >= 0
            AND subtotal >= 0
            AND diskon >= 0
            AND total_bayar >= 0
            AND total_bayar = subtotal - diskon
        )
) ENGINE=InnoDB;

CREATE TABLE detail_pemesanan (
    id_detail BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pemesanan BIGINT UNSIGNED NOT NULL,
    id_tiket BIGINT UNSIGNED NOT NULL,
    nama_pengunjung VARCHAR(100) NOT NULL,
    no_identitas VARCHAR(30) NULL,
    harga_satuan DECIMAL(12,2) NOT NULL,
    qty INT UNSIGNED NOT NULL DEFAULT 1,
    total_harga DECIMAL(12,2) NOT NULL,
    kode_qr VARCHAR(80) NOT NULL UNIQUE,
    status_checkin ENUM('belum', 'sudah') NOT NULL DEFAULT 'belum',
    waktu_checkin DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_detail_pemesanan
        FOREIGN KEY (id_pemesanan) REFERENCES pemesanan(id_pemesanan)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_detail_tiket
        FOREIGN KEY (id_tiket) REFERENCES tiket_wisata(id_tiket)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_detail_harga
        CHECK (harga_satuan >= 0 AND qty > 0 AND total_harga = harga_satuan * qty)
) ENGINE=InnoDB;

CREATE TABLE pembayaran (
    id_pembayaran BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pemesanan BIGINT UNSIGNED NOT NULL UNIQUE,
    kode_pembayaran VARCHAR(30) NOT NULL UNIQUE,
    metode ENUM('transfer_bank', 'ewallet', 'kartu_kredit', 'cash') NOT NULL,
    kanal VARCHAR(60) NULL,
    nominal DECIMAL(12,2) NOT NULL,
    tanggal_bayar DATETIME NULL,
    status ENUM('pending', 'berhasil', 'gagal', 'refund') NOT NULL DEFAULT 'pending',
    bukti_bayar VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pembayaran_pemesanan
        FOREIGN KEY (id_pemesanan) REFERENCES pemesanan(id_pemesanan)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_pembayaran_nominal
        CHECK (nominal >= 0)
) ENGINE=InnoDB;

CREATE TABLE refund_pembayaran (
    id_refund BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pembayaran BIGINT UNSIGNED NOT NULL,
    alasan VARCHAR(255) NOT NULL,
    nominal_refund DECIMAL(12,2) NOT NULL,
    tanggal_pengajuan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tanggal_proses DATETIME NULL,
    status ENUM('diajukan', 'diproses', 'disetujui', 'ditolak') NOT NULL DEFAULT 'diajukan',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_refund_pembayaran
        FOREIGN KEY (id_pembayaran) REFERENCES pembayaran(id_pembayaran)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_refund_nominal
        CHECK (nominal_refund > 0)
) ENGINE=InnoDB;

CREATE TABLE ulasan (
    id_ulasan BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pemesanan BIGINT UNSIGNED NOT NULL UNIQUE,
    id_wisata BIGINT UNSIGNED NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    komentar TEXT NULL,
    tanggal_ulasan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ulasan_pemesanan
        FOREIGN KEY (id_pemesanan) REFERENCES pemesanan(id_pemesanan)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_ulasan_wisata
        FOREIGN KEY (id_wisata) REFERENCES wisata(id_wisata)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_ulasan_rating
        CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;

CREATE INDEX idx_pengguna_role ON pengguna(id_role);
CREATE INDEX idx_wisata_kategori ON wisata(id_kategori);
CREATE INDEX idx_tiket_wisata ON tiket_wisata(id_wisata);
CREATE INDEX idx_jadwal_tanggal ON jadwal_wisata(tanggal);
CREATE INDEX idx_pemesanan_pelanggan ON pemesanan(id_pelanggan);
CREATE INDEX idx_pemesanan_jadwal ON pemesanan(id_jadwal);
CREATE INDEX idx_pemesanan_status ON pemesanan(status);
CREATE INDEX idx_pembayaran_status ON pembayaran(status);

DELIMITER //

CREATE TRIGGER trg_pemesanan_generate_kode
BEFORE INSERT ON pemesanan
FOR EACH ROW
BEGIN
    DECLARE tanggal_kunjungan DATE;
    DECLARE prefix_kode VARCHAR(18);
    DECLARE urutan_terakhir INT DEFAULT 0;

    IF NEW.kode_pemesanan IS NULL OR NEW.kode_pemesanan = '' THEN
        SELECT tanggal
        INTO tanggal_kunjungan
        FROM jadwal_wisata
        WHERE id_jadwal = NEW.id_jadwal
        LIMIT 1;

        SET prefix_kode = CONCAT(
            'PSN-',
            DATE_FORMAT(COALESCE(tanggal_kunjungan, DATE(NEW.tanggal_pesan), CURRENT_DATE), '%Y%m%d'),
            '-'
        );

        SELECT COALESCE(MAX(CAST(SUBSTRING(kode_pemesanan, CHAR_LENGTH(prefix_kode) + 1) AS UNSIGNED)), 0)
        INTO urutan_terakhir
        FROM pemesanan
        WHERE kode_pemesanan LIKE CONCAT(prefix_kode, '%');

        SET NEW.kode_pemesanan = CONCAT(prefix_kode, LPAD(urutan_terakhir + 1, 4, '0'));
    END IF;
END//

CREATE TRIGGER trg_detail_after_insert
AFTER INSERT ON detail_pemesanan
FOR EACH ROW
BEGIN
    UPDATE pemesanan
    SET
        jumlah_tiket = jumlah_tiket + NEW.qty,
        subtotal = subtotal + NEW.total_harga,
        total_bayar = subtotal - diskon
    WHERE id_pemesanan = NEW.id_pemesanan;
END//

CREATE TRIGGER trg_detail_after_delete
AFTER DELETE ON detail_pemesanan
FOR EACH ROW
BEGIN
    UPDATE pemesanan
    SET
        jumlah_tiket = jumlah_tiket - OLD.qty,
        subtotal = subtotal - OLD.total_harga,
        total_bayar = subtotal - diskon
    WHERE id_pemesanan = OLD.id_pemesanan;
END//

CREATE TRIGGER trg_pembayaran_berhasil
AFTER UPDATE ON pembayaran
FOR EACH ROW
BEGIN
    IF NEW.status = 'berhasil' AND OLD.status <> 'berhasil' THEN
        UPDATE pemesanan
        SET status = 'dibayar'
        WHERE id_pemesanan = NEW.id_pemesanan;
    END IF;
END//

CREATE TRIGGER trg_pemesanan_dibayar_kurangi_kuota
AFTER UPDATE ON pemesanan
FOR EACH ROW
BEGIN
    IF NEW.status = 'dibayar' AND OLD.status <> 'dibayar' THEN
        UPDATE jadwal_wisata
        SET
            kuota_tersedia = kuota_tersedia - NEW.jumlah_tiket,
            status = CASE
                WHEN kuota_tersedia = 0 THEN 'penuh'
                ELSE status
            END
        WHERE id_jadwal = NEW.id_jadwal
          AND kuota_tersedia >= NEW.jumlah_tiket;
    END IF;
END//

DELIMITER ;

INSERT INTO role_pengguna (nama_role, deskripsi) VALUES
('admin', 'Mengelola master data dan laporan'),
('petugas', 'Melakukan validasi tiket dan check-in'),
('pelanggan', 'Melakukan pemesanan tiket wisata');

INSERT INTO pengguna (id_role, nama, email, password, no_hp, status) VALUES
(1, 'Admin Wisata', 'admin@wisata.test', '$2y$10$contohhashpasswordadmin', '081111111111', 'aktif'),
(2, 'Petugas Loket', 'petugas@wisata.test', '$2y$10$contohhashpasswordpetugas', '082222222222', 'aktif'),
(3, 'Budi Santoso', 'budi@example.com', '$2y$10$contohhashpasswordbudi', '083333333333', 'aktif'),
(3, 'Siti Aminah', 'siti@example.com', '$2y$10$contohhashpasswordsiti', '084444444444', 'aktif');

INSERT INTO pelanggan (id_pengguna, nik, alamat, tanggal_lahir, jenis_kelamin) VALUES
(3, '3273010101900001', 'Jl. Melati No. 10, Bandung', '1990-01-01', 'L'),
(4, '3273010202950002', 'Jl. Mawar No. 5, Jakarta', '1995-02-02', 'P');

INSERT INTO kategori_wisata (nama_kategori, deskripsi) VALUES
('Alam', 'Wisata pegunungan, pantai, dan danau'),
('Budaya', 'Wisata sejarah, museum, dan cagar budaya'),
('Rekreasi', 'Taman hiburan dan wahana keluarga');

INSERT INTO wisata (id_kategori, nama_wisata, lokasi, alamat, deskripsi, jam_buka, jam_tutup, status) VALUES
(1, 'Kawah Putih', 'Bandung', 'Ciwidey, Kabupaten Bandung', 'Destinasi alam dengan danau kawah berwarna putih kehijauan.', '07:00:00', '17:00:00', 'aktif'),
(2, 'Candi Borobudur', 'Magelang', 'Borobudur, Magelang, Jawa Tengah', 'Candi Buddha bersejarah dan ikon wisata budaya Indonesia.', '06:30:00', '16:30:00', 'aktif'),
(3, 'Dunia Fantasi', 'Jakarta', 'Ancol, Jakarta Utara', 'Taman rekreasi dengan berbagai wahana permainan.', '10:00:00', '18:00:00', 'aktif');

INSERT INTO tiket_wisata (id_wisata, nama_tiket, tipe_hari, kategori_pengunjung, harga, status) VALUES
(1, 'Tiket Reguler Kawah Putih', 'weekday', 'dewasa', 35000.00, 'aktif'),
(1, 'Tiket Anak Kawah Putih', 'weekday', 'anak', 25000.00, 'aktif'),
(1, 'Tiket Reguler Kawah Putih', 'weekend', 'dewasa', 50000.00, 'aktif'),
(2, 'Tiket Reguler Borobudur', 'weekday', 'dewasa', 50000.00, 'aktif'),
(2, 'Tiket Pelajar Borobudur', 'weekday', 'pelajar', 25000.00, 'aktif'),
(2, 'Tiket Mancanegara Borobudur', 'weekday', 'mancanegara', 375000.00, 'aktif'),
(3, 'Tiket Reguler Dufan', 'weekday', 'dewasa', 225000.00, 'aktif'),
(3, 'Tiket Reguler Dufan', 'weekend', 'dewasa', 275000.00, 'aktif');

INSERT INTO jadwal_wisata (id_wisata, tanggal, kuota, kuota_tersedia, status) VALUES
(1, '2026-07-01', 200, 200, 'tersedia'),
(1, '2026-07-02', 200, 200, 'tersedia'),
(2, '2026-07-01', 300, 300, 'tersedia'),
(2, '2026-07-02', 300, 300, 'tersedia'),
(3, '2026-07-01', 500, 500, 'tersedia'),
(3, '2026-07-02', 500, 500, 'tersedia');

INSERT INTO pemesanan (
    id_pelanggan,
    id_jadwal,
    tanggal_pesan,
    batas_bayar,
    diskon,
    total_bayar,
    status,
    catatan
) VALUES
(1, 1, '2026-06-22 09:00:00', '2026-06-22 12:00:00', 0.00, 0.00, 'menunggu_pembayaran', 'Contoh pemesanan Kawah Putih');

INSERT INTO pemesanan (
    id_pelanggan,
    id_jadwal,
    tanggal_pesan,
    batas_bayar,
    diskon,
    total_bayar,
    status,
    catatan
) VALUES
(2, 3, '2026-06-22 10:00:00', '2026-06-22 13:00:00', 0.00, 0.00, 'menunggu_pembayaran', 'Contoh pemesanan Borobudur');

INSERT INTO detail_pemesanan (
    id_pemesanan,
    id_tiket,
    nama_pengunjung,
    no_identitas,
    harga_satuan,
    qty,
    total_harga,
    kode_qr,
    status_checkin
) VALUES
(1, 1, 'Budi Santoso', '3273010101900001', 35000.00, 1, 35000.00, 'QR-PSN0001-001', 'belum'),
(1, 2, 'Dina Santoso', NULL, 25000.00, 1, 25000.00, 'QR-PSN0001-002', 'belum'),
(2, 4, 'Siti Aminah', '3273010202950002', 50000.00, 1, 50000.00, 'QR-PSN0002-001', 'belum');

UPDATE pemesanan
SET diskon = 5000.00,
    total_bayar = subtotal - 5000.00
WHERE id_pemesanan = 2;

INSERT INTO pembayaran (
    id_pemesanan,
    kode_pembayaran,
    metode,
    kanal,
    nominal,
    tanggal_bayar,
    status
) VALUES
(1, 'BYR-20260701-0001', 'transfer_bank', 'BCA Virtual Account', 60000.00, '2026-06-22 09:30:00', 'pending'),
(2, 'BYR-20260701-0002', 'ewallet', 'GoPay', 45000.00, '2026-06-22 10:20:00', 'pending');

UPDATE pembayaran
SET status = 'berhasil'
WHERE id_pembayaran IN (1, 2);

INSERT INTO ulasan (id_pemesanan, id_wisata, rating, komentar, tanggal_ulasan) VALUES
(1, 1, 5, 'Tempatnya bersih dan proses masuk mudah.', '2026-07-01 18:30:00');

CREATE OR REPLACE VIEW vw_daftar_tiket_aktif AS
SELECT
    w.id_wisata,
    w.nama_wisata,
    k.nama_kategori,
    w.lokasi,
    t.id_tiket,
    t.nama_tiket,
    t.tipe_hari,
    t.kategori_pengunjung,
    t.harga
FROM tiket_wisata t
JOIN wisata w ON w.id_wisata = t.id_wisata
JOIN kategori_wisata k ON k.id_kategori = w.id_kategori
WHERE w.status = 'aktif'
  AND t.status = 'aktif';

CREATE OR REPLACE VIEW vw_riwayat_pemesanan AS
SELECT
    p.id_pemesanan,
    p.kode_pemesanan,
    pg.nama AS nama_pelanggan,
    w.nama_wisata,
    jw.tanggal AS tanggal_kunjungan,
    p.jumlah_tiket,
    p.subtotal,
    p.diskon,
    p.total_bayar,
    p.status AS status_pemesanan,
    pb.status AS status_pembayaran,
    pb.metode AS metode_pembayaran
FROM pemesanan p
JOIN pelanggan pl ON pl.id_pelanggan = p.id_pelanggan
JOIN pengguna pg ON pg.id_pengguna = pl.id_pengguna
JOIN jadwal_wisata jw ON jw.id_jadwal = p.id_jadwal
JOIN wisata w ON w.id_wisata = jw.id_wisata
LEFT JOIN pembayaran pb ON pb.id_pemesanan = p.id_pemesanan;

CREATE OR REPLACE VIEW vw_laporan_pendapatan_wisata AS
SELECT
    w.id_wisata,
    w.nama_wisata,
    COUNT(DISTINCT p.id_pemesanan) AS total_pemesanan,
    COALESCE(SUM(p.jumlah_tiket), 0) AS total_tiket_terjual,
    COALESCE(SUM(p.total_bayar), 0) AS total_pendapatan
FROM wisata w
LEFT JOIN jadwal_wisata jw ON jw.id_wisata = w.id_wisata
LEFT JOIN pemesanan p ON p.id_jadwal = jw.id_jadwal
    AND p.status IN ('dibayar', 'selesai')
GROUP BY w.id_wisata, w.nama_wisata;

-- Contoh query:
-- SELECT * FROM vw_daftar_tiket_aktif;
-- SELECT * FROM vw_riwayat_pemesanan;
-- SELECT * FROM vw_laporan_pendapatan_wisata;