-- SQL Schema untuk RB Garage Database (Full Feature)

CREATE DATABASE IF NOT EXISTS rb_garage_db;
USE rb_garage_db;

-- Tabel Users (Admin & Customer)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    role ENUM('admin', 'customer') DEFAULT 'customer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Layanan / Services
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(12, 2) NOT NULL,
    category VARCHAR(50) DEFAULT 'Servis Rutin',
    icon_name VARCHAR(50) DEFAULT 'build_circle',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Booking Servis
CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_name VARCHAR(100) NOT NULL,
    vehicle_name VARCHAR(100) NOT NULL,
    vehicle_year INT DEFAULT NULL,
    vehicle_number VARCHAR(20) NOT NULL DEFAULT '-',
    booking_date VARCHAR(50) NOT NULL,
    image_url VARCHAR(255),
    notes TEXT,
    total_price DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    status ENUM('Menunggu', 'Proses', 'Sedang Diservis', 'Selesai', 'Servis Selesai', 'Batal', 'Dibatalkan') DEFAULT 'Menunggu',
    payment_status ENUM('DP', 'Lunas') DEFAULT 'DP',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabel Pembayaran / Payment
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NOT NULL,
    external_id VARCHAR(100) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL,
    payment_method VARCHAR(50) DEFAULT 'Xendit/QRIS',
    payment_url TEXT,
    status ENUM('PENDING', 'PAID', 'EXPIRED', 'FAILED') DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
);

-- Tabel Token Notifikasi FCM
CREATE TABLE IF NOT EXISTS user_fcm_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    fcm_token TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabel Riwayat Notifikasi
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Seed Data Awal (Admin & Dummy Customer)
INSERT INTO users (name, email, password, phone, role) 
VALUES 
('Administrator', 'admin@rbgarage.com', '$2a$10$e8w.xLhK2c7I7.k8pG3j8uW0mQ1XQ.q9g3c6I1e7k8pG3j8uW0mQ', '081234567890', 'admin'),
('Aditya Fajri', 'user@gmail.com', '$2a$10$e8w.xLhK2c7I7.k8pG3j8uW0mQ1XQ.q9g3c6I1e7k8pG3j8uW0mQ', '081234567890', 'customer')
ON DUPLICATE KEY UPDATE email=email;

-- Seed Data Layanan
INSERT INTO services (title, description, price, category)
VALUES
('Servis Rutin', 'Ganti oli, pembersihan karburator/injeksi, cek busi & rantai.', 150000.00, 'Servis Rutin'),
('Ganti Oli', 'Penggantian oli mesin & oli gardan matic berkualitas.', 75000.00, 'Oli & Pelumas'),
('Servis Rem', 'Pembersihan kanvas rem, ganti minyak rem & kuras rem hidrolik.', 50000.00, 'Perbaikan'),
('Tune Up Injeksi', 'Pembersihan injektor dengan alat khusus & riset ECU.', 120000.00, 'Performa')
ON DUPLICATE KEY UPDATE title=title;