database_setup.sql

-- Vehicle Parking System Database Setup
-- Run this script in MySQL to create the database and table

-- Create database
CREATE DATABASE IF NOT EXISTS parking_system;

-- Use the database
USE parking_system;

-- Create users table for login
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a default admin user (password: admin123)
-- In a real app, passwords should be hashed!
INSERT IGNORE INTO users (username, password, full_name) 
VALUES ('admin', 'admin123', 'Administrator');

-- Create parked_vehicles table
CREATE TABLE IF NOT EXISTS parked_vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_number VARCHAR(20) NOT NULL,
    owner_name VARCHAR(100),
    phone VARCHAR(15),
    vehicle_type VARCHAR(20) NOT NULL,
    entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    exit_time TIMESTAMP NULL,
    parking_fee DECIMAL(10, 2) DEFAULT 0.00,
    status ENUM('PARKED', 'EXITED') DEFAULT 'PARKED',
    INDEX idx_vehicle_number (vehicle_number),
    INDEX idx_status (status),
    INDEX idx_entry_time (entry_time)
);

-- Insert sample data (optional)
-- INSERT INTO parked_vehicles (vehicle_number, owner_name, phone, vehicle_type, status) 
-- VALUES ('ABC-1234', 'John Doe', '0771234567', 'Car', 'PARKED');

-- Display table structure
DESCRIBE users;
DESCRIBE parked_vehicles;

-- Display current data
SELECT * FROM users;
SELECT * FROM parked_vehicles;