-- ===================================================================== -- Multi Point 107 — skema database MySQL 5.7+ / MariaDB 10.3+ -- Charset utf8mb4 supaya nama tamu (中文, 日本語, 한국어) aman. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS payment_notifications; DROP TABLE IF EXISTS payments; DROP TABLE IF EXISTS bookings; DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS fleet_units; DROP TABLE IF EXISTS vehicle_rates; DROP TABLE IF EXISTS vehicles; DROP TABLE IF EXISTS pool_routes; DROP TABLE IF EXISTS pools; SET FOREIGN_KEY_CHECKS = 1; -- --------------------------------------------------------------------- -- Pool / kota layanan di koridor -- --------------------------------------------------------------------- CREATE TABLE pools ( code VARCHAR(8) NOT NULL, -- JKT, BDG, SMG, SOLO, DIY, MLG, SBY, BALI plate VARCHAR(4) NOT NULL, -- kode plat: B, D, H, AD, AB, N, L, DK name VARCHAR(60) NOT NULL, area VARCHAR(80) NOT NULL, address VARCHAR(200) NOT NULL, hours VARCHAR(40) NOT NULL, -- "24" atau "06.00 – 22.00" delivery_note VARCHAR(200) NULL, airports TEXT NULL, -- JSON array nama bandara/terminal timezone VARCHAR(40) NOT NULL DEFAULT 'Asia/Jakarta', lat DECIMAL(10,7) NULL, lng DECIMAL(10,7) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Estimasi jarak jalan darat antar pool (dipakai hitung biaya antar kota). -- Simpan sekali per pasangan dengan pool_a < pool_b (urutan abjad). CREATE TABLE pool_routes ( pool_a VARCHAR(8) NOT NULL, pool_b VARCHAR(8) NOT NULL, km INT UNSIGNED NOT NULL, PRIMARY KEY (pool_a, pool_b), CONSTRAINT fk_routes_a FOREIGN KEY (pool_a) REFERENCES pools(code) ON UPDATE CASCADE, CONSTRAINT fk_routes_b FOREIGN KEY (pool_b) REFERENCES pools(code) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Tipe kendaraan (model), bukan unit fisik -- --------------------------------------------------------------------- CREATE TABLE vehicles ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(30) NOT NULL, -- agya, avanza, ... (sama dengan id di prototype) name VARCHAR(80) NOT NULL, category VARCHAR(30) NOT NULL, -- City Car, MPV, MPV Premium, SUV, Van & Bus, Listrik seats TINYINT UNSIGNED NOT NULL, bags TINYINT UNSIGNED NOT NULL, transmission ENUM('Matic','Manual') NOT NULL, fuel ENUM('Bensin','Diesel','Listrik') NOT NULL, monthly_discount DECIMAL(4,3) NULL, -- NULL = pakai default config (0.20) photo_url VARCHAR(255) NULL, -- path lokal setelah tools/sync_photos.php, atau URL foto sendiri photo_credit VARCHAR(255) NULL, -- atribusi Wikimedia Commons (wajib CC BY-SA) wiki_titles VARCHAR(255) NULL, -- judul artikel Wikipedia dipisah "|" is_active TINYINT(1) NOT NULL DEFAULT 1, sort SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_vehicles_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Tarif harian lepas kunci per pool per segmen (wni / wna) CREATE TABLE vehicle_rates ( vehicle_id INT UNSIGNED NOT NULL, pool_code VARCHAR(8) NOT NULL, segment ENUM('wni','wna') NOT NULL, daily_rate INT UNSIGNED NOT NULL, PRIMARY KEY (vehicle_id, pool_code, segment), KEY idx_rates_pool (pool_code), CONSTRAINT fk_rates_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE, CONSTRAINT fk_rates_pool FOREIGN KEY (pool_code) REFERENCES pools(code) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Unit fisik (plat nomor) — dasar hitung ketersediaan CREATE TABLE fleet_units ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, vehicle_id INT UNSIGNED NOT NULL, pool_code VARCHAR(8) NOT NULL, plate_number VARCHAR(15) NOT NULL, color VARCHAR(30) NULL, year SMALLINT NULL, status ENUM('active','maintenance','retired') NOT NULL DEFAULT 'active', PRIMARY KEY (id), UNIQUE KEY uq_fleet_plate (plate_number), KEY idx_fleet_avail (vehicle_id, pool_code, status), CONSTRAINT fk_fleet_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id), CONSTRAINT fk_fleet_pool FOREIGN KEY (pool_code) REFERENCES pools(code) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Pelanggan & pemesanan -- --------------------------------------------------------------------- CREATE TABLE customers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(120) NOT NULL, email VARCHAR(120) NOT NULL, phone VARCHAR(25) NOT NULL, nationality CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 doc_type ENUM('ktp','passport') NOT NULL, doc_number VARCHAR(30) NOT NULL, license_type ENUM('sim_a','idp') NULL, lang VARCHAR(5) NOT NULL DEFAULT 'id', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_customers_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE bookings ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(24) NOT NULL, -- 107-BALI-7K2QXM customer_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, fleet_unit_id INT UNSIGNED NULL, -- diisi tim operasional saat serah terima pickup_pool VARCHAR(8) NOT NULL, return_pool VARCHAR(8) NOT NULL, start_at DATETIME NOT NULL, -- jam lokal pool ambil end_at DATETIME NOT NULL, -- jam lokal pool kembali days SMALLINT UNSIGNED NOT NULL, mode ENUM('sopir','lepas') NOT NULL, segment ENUM('wni','wna') NOT NULL, lang VARCHAR(5) NOT NULL DEFAULT 'id', handover_type ENUM('airport','pool','address') NOT NULL, handover_airport VARCHAR(120) NULL, flight_no VARCHAR(10) NULL, handover_address VARCHAR(200) NULL, notes VARCHAR(500) NULL, -- snapshot harga saat dipesan (tidak ikut berubah bila tarif diubah) daily_rate INT UNSIGNED NOT NULL, gross_rent INT UNSIGNED NOT NULL, discount INT UNSIGNED NOT NULL DEFAULT 0, driver_fee INT UNSIGNED NOT NULL DEFAULT 0, transfer_fee INT UNSIGNED NOT NULL DEFAULT 0, total INT UNSIGNED NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'IDR', status ENUM('pending_payment','paid','confirmed','ongoing','completed','cancelled','expired') NOT NULL DEFAULT 'pending_payment', hold_expires_at DATETIME NULL, -- unit ditahan sampai jam ini (UTC) paid_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_bookings_code (code), KEY idx_bookings_avail (vehicle_id, pickup_pool, status, start_at, end_at), KEY idx_bookings_customer (customer_id), KEY idx_bookings_hold (status, hold_expires_at), CONSTRAINT fk_bookings_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT fk_bookings_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id), CONSTRAINT fk_bookings_unit FOREIGN KEY (fleet_unit_id) REFERENCES fleet_units(id), CONSTRAINT fk_bookings_pickup FOREIGN KEY (pickup_pool) REFERENCES pools(code) ON UPDATE CASCADE, CONSTRAINT fk_bookings_return FOREIGN KEY (return_pool) REFERENCES pools(code) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Pembayaran (satu booking bisa punya beberapa percobaan bayar) -- --------------------------------------------------------------------- CREATE TABLE payments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(40) NOT NULL, -- order_id di gateway: 107-BALI-7K2QXM-P1A booking_id INT UNSIGNED NOT NULL, method ENUM('qris','va','card') NOT NULL, bank VARCHAR(20) NULL, -- bca, bni, bri, mandiri, permata gateway VARCHAR(20) NOT NULL, -- mock | midtrans gateway_ref VARCHAR(80) NULL, -- transaction_id dari gateway amount INT UNSIGNED NOT NULL, status ENUM('pending','paid','expired','failed','refunded') NOT NULL DEFAULT 'pending', qr_string TEXT NULL, va_number VARCHAR(40) NULL, biller_code VARCHAR(20) NULL, redirect_url VARCHAR(500) NULL, expires_at DATETIME NOT NULL, -- UTC paid_at DATETIME NULL, raw_response MEDIUMTEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_payments_code (code), KEY idx_payments_booking (booking_id, status), CONSTRAINT fk_payments_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Log mentah notifikasi webhook (audit & debug) CREATE TABLE payment_notifications ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, gateway VARCHAR(20) NOT NULL, payment_code VARCHAR(40) NULL, signature_ok TINYINT(1) NOT NULL DEFAULT 0, payload MEDIUMTEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_notif_code (payment_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;