schema.sql

-- PostgreSQL Database Schema for Smart Parking Lot Management System

-- Drop tables if re-initializing (Optional)
-- DROP TABLE IF EXISTS parking_records;
-- DROP TABLE IF EXISTS parking_slots;

-- Table to store multi-level parking lot slots
CREATE TABLE IF NOT EXISTS parking_slots (
    slot_id SERIAL PRIMARY KEY,
    slot_number VARCHAR(10) UNIQUE NOT NULL,
    vehicle_type VARCHAR(10) NOT NULL,       -- BIKE, CAR, TRUCK
    status VARCHAR(10) DEFAULT 'EMPTY' NOT NULL
);

-- Table to log vehicle entry, exit, and calculated fee records
CREATE TABLE IF NOT EXISTS parking_records (
    record_id SERIAL PRIMARY KEY,
    vehicle_number VARCHAR(15) NOT NULL,
    vehicle_type VARCHAR(10) NOT NULL,
    slot_number VARCHAR(10) NOT NULL,
    entry_time TIMESTAMP NOT NULL,
    exit_time TIMESTAMP,
    fee DECIMAL(8,2)
);

-- Seed initial slots for Bike, Car, and Truck levels
INSERT INTO parking_slots (slot_number, vehicle_type, status) VALUES
('B-101', 'BIKE', 'EMPTY'),
('B-102', 'BIKE', 'EMPTY'),
('C-201', 'CAR', 'EMPTY'),
('C-202', 'CAR', 'EMPTY'),
('T-301', 'TRUCK', 'EMPTY'),
('T-302', 'TRUCK', 'EMPTY')
ON CONFLICT (slot_number) DO NOTHING;