✏ EDIT: db.sql

-- SQL schema for rental booking application

-- Drop existing tables if they exist
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS booking_items;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS users;

-- Users table stores both customers and administrators.
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('CUSTOMER','ADMIN') NOT NULL DEFAULT 'CUSTOMER',
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  google_id VARCHAR(200) NULL,
  password_hash VARCHAR(255) NULL,
  mobile VARCHAR(20) NULL,
  address VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Items available for rent.
CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price_per_unit DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  available_qty INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_items_name (name)
);

-- Bookings record each transaction. Customers may be null for walk‑ins
-- handled by administrators.
CREATE TABLE bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,
  customer_name VARCHAR(100) NOT NULL,
  mobile VARCHAR(20) NOT NULL,
  address VARCHAR(255) NOT NULL,
  from_date DATE NULL,
  to_date DATE NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  damage_charge DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  booking_status ENUM('DRAFT','CONFIRMED','CANCELLED','COMPLETED') NOT NULL DEFAULT 'DRAFT',
  payment_status ENUM('UNPAID','PARTIAL','PAID') NOT NULL DEFAULT 'UNPAID',
  return_status ENUM('NOT_RETURNED','PARTIAL_RETURNED','RETURNED','ISSUE') NOT NULL DEFAULT 'NOT_RETURNED',
  created_by_admin_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_bookings_created_at (created_at),
  INDEX idx_bookings_payment_status (payment_status),
  FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by_admin_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Items associated with bookings. Snapshots capture price and name
-- at the time of booking to allow historical accuracy if items change later.
CREATE TABLE booking_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id INT NOT NULL,
  item_id INT NOT NULL,
  item_name_snapshot VARCHAR(150) NOT NULL,
  price_per_unit_snapshot DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  qty_booked INT NOT NULL DEFAULT 1,
  qty_returned INT NOT NULL DEFAULT 0,
  damage_qty INT NOT NULL DEFAULT 0,
  damage_comment TEXT,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

-- Payments linked to bookings. Supports multiple payment methods
-- per booking, and tracks payment status.
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  method ENUM('CASH','UPI','ONLINE','CARD') NOT NULL,
  txn_ref VARCHAR(100) NULL,
  status ENUM('SUCCESS','FAILED','PENDING') NOT NULL DEFAULT 'SUCCESS',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
  INDEX idx_payments_booking_id (booking_id)
);

-- --------------------------------------------------------------------
-- Seed data for development and testing
-- --------------------------------------------------------------------

-- Create an administrator user. The password hash corresponds to the
-- plaintext password 'admin123' and was generated using PHP's
-- password_hash() (bcrypt algorithm). Adjust the email and name as
-- needed. When deploying to production, generate a unique password
-- hash using password_hash() in PHP.
INSERT INTO users (role, name, email, password_hash, mobile, address, created_at)
VALUES
  ('ADMIN', 'Admin User', 'admin@example.com', '$2y$12$BS2a7OptmgQ0KiPPuvp97eYJ6ty/97OSdYM0n8qaex2smFKGe0D8u', '9999999999', 'Admin Address', NOW());

-- Create a sample customer
INSERT INTO users (role, name, email, password_hash, mobile, address, created_at)
VALUES
  ('CUSTOMER', 'John Doe', 'john@example.com', NULL, '1234567890', '123 Main Street', NOW());

-- Insert some items into the inventory
INSERT INTO items (name, description, price_per_unit, available_qty, is_active, created_at)
VALUES
  ('Tent', 'Large camping tent suitable for up to 4 people', 1000.00, 10, 1, NOW()),
  ('Table', 'Foldable table great for events and gatherings', 200.00, 20, 1, NOW()),
  ('Chair', 'Durable plastic chair', 50.00, 100, 1, NOW());

[✗] CANCEL