database.sql

CREATE DATABASE IF NOT EXISTS localhelp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE localhelp_db;
CREATE TABLE users (
 user_id INT PRIMARY KEY AUTO_INCREMENT, full_name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL, phone VARCHAR(30) NOT NULL, city VARCHAR(100) NOT NULL, area VARCHAR(100) NOT NULL,
 pincode VARCHAR(10) NOT NULL, role ENUM('CUSTOMER','PROVIDER','ADMIN') NOT NULL DEFAULT 'CUSTOMER', is_active BOOLEAN NOT NULL DEFAULT TRUE,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE service_categories (category_id INT PRIMARY KEY AUTO_INCREMENT, category_name VARCHAR(80) NOT NULL UNIQUE, is_active BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE provider_profiles (
 provider_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL UNIQUE, category_id INT NOT NULL, service_description TEXT NOT NULL,
 price DECIMAL(10,2) NOT NULL, price_unit VARCHAR(30) NOT NULL, experience_years INT NOT NULL DEFAULT 0,
 urgent_available BOOLEAN NOT NULL DEFAULT FALSE, availability_status ENUM('AVAILABLE','UNAVAILABLE') NOT NULL DEFAULT 'AVAILABLE',
 FOREIGN KEY(user_id) REFERENCES users(user_id), FOREIGN KEY(category_id) REFERENCES service_categories(category_id)
);
CREATE TABLE bookings (
 booking_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, provider_id INT NOT NULL, service_address VARCHAR(255) NOT NULL,
 service_date DATE NOT NULL, service_time TIME NOT NULL, task_description TEXT NOT NULL, urgency ENUM('ROUTINE','URGENT') NOT NULL DEFAULT 'ROUTINE',
 booking_status ENUM('PENDING','ACCEPTED','REJECTED','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES users(user_id), FOREIGN KEY(provider_id) REFERENCES provider_profiles(provider_id),
 INDEX(customer_id), INDEX(provider_id), INDEX(service_date, booking_status)
);
INSERT INTO service_categories(category_name) VALUES ('Plumbing'),('Electrical'),('Home Cleaning'),('Tutoring'),('Computer Repair')
 ON DUPLICATE KEY UPDATE category_name=VALUES(category_name);
-- Create an administrator by hashing a password with com.localhelp.util.PasswordUtil, then:
-- INSERT INTO users(full_name,email,password_hash,phone,city,area,pincode,role) VALUES (...,'ADMIN');