database.sql

-- =====================================
-- TIME BANKING DBMS PROJECT DATABASE
-- =====================================

-- Create Database
CREATE DATABASE IF NOT EXISTS time_banking;

-- Use Database
USE time_banking;

-- =====================================
-- USERS TABLE
-- =====================================
CREATE TABLE IF NOT EXISTS USERS (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    location VARCHAR(100),
    time_balance INT DEFAULT 5
);

-- =====================================
-- SERVICES TABLE
-- =====================================
CREATE TABLE IF NOT EXISTS SERVICES (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    service_name VARCHAR(100),
    description VARCHAR(255),
    category VARCHAR(50),

    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    ON DELETE CASCADE
);

-- =====================================
-- REQUESTS TABLE
-- =====================================
CREATE TABLE IF NOT EXISTS REQUESTS (
    request_id INT AUTO_INCREMENT PRIMARY KEY,
    requester_id INT,
    provider_id INT,
    service_id INT,
    hours_requested INT,
    status VARCHAR(50),

    FOREIGN KEY (requester_id) REFERENCES USERS(user_id),
    FOREIGN KEY (provider_id) REFERENCES USERS(user_id),
    FOREIGN KEY (service_id) REFERENCES SERVICES(service_id)
);

-- =====================================
-- SAMPLE DATA (FOR TESTING)
-- =====================================

-- Users
INSERT INTO USERS (name, email, password, location, time_balance) VALUES
('Rahul', 'rahul@gmail.com', '123', 'Pune', 5),
('Priya', 'priya@gmail.com', '123', 'Mumbai', 5);

-- Services
INSERT INTO SERVICES (user_id, service_name, description, category) VALUES
(1, 'Math Tutoring', 'Basic math help', 'Education'),
(2, 'Graphic Design', 'Logo design service', 'Design');

-- Requests (optional demo data)
INSERT INTO REQUESTS (requester_id, provider_id, service_id, hours_requested, status) VALUES
(1, 2, 2, 2, 'Pending');

-- =====================================
-- TEST QUERIES (OPTIONAL)
-- =====================================

-- View Data
SELECT * FROM USERS;
SELECT * FROM SERVICES;
SELECT * FROM REQUESTS;