parking.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Parking staff/owners who manage check-in/check-out
CREATE TABLE parking_staff (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE,
    phone VARCHAR(20),
    password_hash TEXT NOT NULL,
    role VARCHAR(50) DEFAULT 'operator', -- operator, manager, admin
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Vehicle types with different pricing
CREATE TABLE vehicle_types (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- e.g. Motorcycle, Car, SUV, Truck, Bus
    price_per_hour DECIMAL(10,2) NOT NULL,
    price_per_day DECIMAL(10,2) NOT NULL,
    description TEXT
);

-- Parking sessions (check-in to check-out)
CREATE TABLE parking_sessions (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES users(id),
    vehicle_type_id BIGINT REFERENCES vehicle_types(id),
    license_plate VARCHAR(50) NOT NULL,
    check_in_photo_url TEXT NOT NULL, -- Photo taken by parking owner at check-in
    check_in_time TIMESTAMP NOT NULL DEFAULT NOW(),
    check_out_time TIMESTAMP,
    check_out_photo_url TEXT, -- Optional photo at check-out
    price_per_hour DECIMAL(10,2) NOT NULL,
    price_per_day DECIMAL(10,2) NOT NULL,
    total_hours DECIMAL(10,2),
    total_days INT,
    total_price DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'checked_in', -- checked_in, checked_out, cancelled
    checked_in_by BIGINT REFERENCES parking_staff(id), -- Staff who checked in
    checked_out_by BIGINT REFERENCES parking_staff(id), -- Staff who checked out
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- QR codes for check-in/check-out verification
CREATE TABLE qr_codes (
    id BIGSERIAL PRIMARY KEY,
    parking_session_id BIGINT REFERENCES parking_sessions(id) ON DELETE CASCADE,
    qr_code VARCHAR(255) UNIQUE NOT NULL, -- Unique QR code string
    qr_type VARCHAR(20) NOT NULL, -- 'check_in' or 'check_out'
    is_used BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Payments for parking sessions
CREATE TABLE payments (
    id BIGSERIAL PRIMARY KEY,
    parking_session_id BIGINT REFERENCES parking_sessions(id),
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(50), -- cash, ABA, Wing, Visa, Mastercard, PayPal
    status VARCHAR(50) DEFAULT 'pending', -- pending, completed, refunded, failed
    transaction_id VARCHAR(200),
    paid_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Parking slots/spots (optional - for tracking which spot a vehicle is parked in)
CREATE TABLE parking_slots (
    id BIGSERIAL PRIMARY KEY,
    slot_number VARCHAR(50) UNIQUE NOT NULL,
    vehicle_type_id BIGINT REFERENCES vehicle_types(id), -- Which vehicle types can use this slot
    is_occupied BOOLEAN DEFAULT FALSE,
    status VARCHAR(50) DEFAULT 'available' -- available, occupied, maintenance
);

-- Link parking sessions to slots (optional)
CREATE TABLE parking_session_slots (
    id BIGSERIAL PRIMARY KEY,
    parking_session_id BIGINT REFERENCES parking_sessions(id) ON DELETE CASCADE,
    parking_slot_id BIGINT REFERENCES parking_slots(id),
    assigned_at TIMESTAMP DEFAULT NOW()
);


-- 🚗 Core Features of the Parking System

-- Users (customers) register & verify

-- Parking staff manage check-in/check-out operations

-- Different vehicle types have different pricing (per hour and per day)

-- Check-in Flow:
--   1. Customer arrives at parking
--   2. Parking owner takes photo of vehicle
--   3. Customer pays for parking
--   4. System generates QR code and sends to customer
--   5. Vehicle is checked in

-- Check-out Flow:
--   1. Customer shows QR code to parking owner
--   2. Parking owner scans QR code to verify
--   3. System calculates total time and price
--   4. If additional payment needed, customer pays
--   5. Vehicle is checked out

-- Explanation:

-- users: Customer accounts (vehicle owners)

-- parking_staff: Parking operators/managers who handle check-in/check-out

-- vehicle_types: Different vehicle categories with different pricing (Motorcycle, Car, SUV, etc.)

-- parking_sessions: Records of each parking session from check-in to check-out

-- qr_codes: QR codes generated for check-in verification and check-out scanning

-- payments: Payment records for parking fees

-- parking_slots: Optional - Physical parking spots/spaces

-- parking_session_slots: Optional - Links sessions to specific parking slots