init.sql

CREATE DATABASE IF NOT EXISTS fuel_db;
USE fuel_db;

-- 1. Create the Vehicles Table (The Mock BRTA DB)
CREATE TABLE vehicles (
    plate_number VARCHAR(20) PRIMARY KEY,
    make VARCHAR(50),
    color VARCHAR(30),
    vehicle_type VARCHAR(30)
);

-- 2. Create the Fuel Logs Table
CREATE TABLE fuel_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plate_number VARCHAR(20),
    fuel_date DATETIME,
    FOREIGN KEY (plate_number) REFERENCES vehicles(plate_number)
);

-- 3. Insert Demo Vehicles (BRTA Data)
INSERT INTO vehicles (plate_number, make, color, vehicle_type) VALUES
('DHA-KA-11-2233', 'Toyota Corolla', 'Silver', 'Sedan'),
('DHA-GA-44-5566', 'Honda Civic', 'Black', 'Sedan'),
('SYL-HA-99-8877', 'Yamaha FZ', 'Blue', 'Motorcycle'),
('DHA-GA-12-3456', 'Hyundai Tucson', 'White', 'SUV'),
('CTG-KHA-88-1122', 'Toyota Hiace', 'White', 'Microbus'),
('RAJ-GA-55-9988', 'Suzuki Swift', 'Red', 'Hatchback'),
('KHU-KA-33-4455', 'Nissan Sunny', 'Grey', 'Sedan'),
('BAR-HA-11-9999', 'Honda CBR', 'Red', 'Motorcycle'),
('DHA-CHA-77-6655', 'Toyota Prado', 'Black', 'SUV'),
('SYL-GA-22-3344', 'Mitsubishi Outlander', 'Silver', 'SUV');

-- 4. Insert Demo Fuel Logs (Simulating past fill-ups)
-- Using DATE_SUB to automatically create dates relative to right now
INSERT INTO fuel_logs (plate_number, fuel_date) VALUES
('DHA-KA-11-2233', DATE_SUB(NOW(), INTERVAL 4 DAY)),  -- Eligible (4 days ago)
('DHA-GA-44-5566', DATE_SUB(NOW(), INTERVAL 1 DAY)),  -- Ineligible (1 day ago)
('SYL-HA-99-8877', DATE_SUB(NOW(), INTERVAL 5 DAY)),  -- Eligible (5 days ago)
('DHA-GA-12-3456', DATE_SUB(NOW(), INTERVAL 2 DAY)),  -- Ineligible (2 days ago)
('CTG-KHA-88-1122', DATE_SUB(NOW(), INTERVAL 10 HOUR)), -- Ineligible (10 hours ago)
('RAJ-GA-55-9988', DATE_SUB(NOW(), INTERVAL 6 DAY)),  -- Eligible (6 days ago)
('KHU-KA-33-4455', DATE_SUB(NOW(), INTERVAL 12 DAY)), -- Eligible (12 days ago)
('DHA-CHA-77-6655', DATE_SUB(NOW(), INTERVAL 20 HOUR)); -- Ineligible (20 hours ago)
-- BAR-HA-11-9999 and SYL-GA-22-3344 have NO records, meaning they are fully eligible.