schema.sql

-- HelpConnect Database Schema (SQLite) - Version 5
-- This script creates the four core tables: profiles, availabilities, jobs, and reviews.

-- --------------------------------------------------------
-- Table 1: profiles (Stores both Clients and Helpers)
-- --------------------------------------------------------
DROP TABLE IF EXISTS profiles;
CREATE TABLE profiles (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL, 
    role TEXT NOT NULL, -- 'client' or 'helper'
    full_name TEXT NOT NULL,
    phone TEXT,
    city TEXT NOT NULL,
    state TEXT,
    description TEXT,
    skills TEXT, -- Comma-separated list of skills (for both clients and helpers)
    hourly_rate REAL,
    rating REAL DEFAULT 0.0,
    reviews_count INTEGER DEFAULT 0,
    member_since INTEGER,
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- Table 2: availabilities (Stores a Helper's weekly schedule)
-- --------------------------------------------------------
DROP TABLE IF EXISTS availabilities;
CREATE TABLE availabilities (
    id TEXT PRIMARY KEY,
    helper_id TEXT NOT NULL,
    days TEXT NOT NULL, -- Comma-separated days (e.g., "Mon,Tue,Wed")
    start_time TEXT NOT NULL, -- Format: HH:MM
    end_time TEXT NOT NULL,   -- Format: HH:MM
    note TEXT,
    FOREIGN KEY (helper_id) REFERENCES profiles(id)
);

-- --------------------------------------------------------
-- Table 3: jobs (Stores service bookings/requests)
-- --------------------------------------------------------
DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
    id TEXT PRIMARY KEY,
    client_id TEXT NOT NULL,
    helper_id TEXT NOT NULL,
    scheduled_date TEXT NOT NULL, -- Date of service
    scheduled_start TEXT NOT NULL,
    scheduled_end TEXT NOT NULL,
    agreed_hourly_rate REAL,
    total_amount REAL,
    status TEXT NOT NULL, -- 'requested', 'accepted', 'completed', 'cancelled', 'rejected'
    details TEXT,
    address TEXT,
    special_instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES profiles(id),
    FOREIGN KEY (helper_id) REFERENCES profiles(id)
);

-- --------------------------------------------------------
-- Table 4: reviews (Stores ratings and comments)
-- --------------------------------------------------------
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
    id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL,
    reviewer_id TEXT NOT NULL,
    reviewee_id TEXT NOT NULL, -- The profile being reviewed (usually the helper)
    rating INTEGER NOT NULL, -- 1 to 5 stars
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES jobs(id),
    FOREIGN KEY (reviewer_id) REFERENCES profiles(id),
    FOREIGN KEY (reviewee_id) REFERENCES profiles(id)
);

-- --------------------------------------------------------
-- Sample Data Insertion (Passwords are 'password' for testing)
-- --------------------------------------------------------

INSERT INTO profiles (id, email, password_hash, role, full_name, city, state, description, skills, hourly_rate, rating, reviews_count, member_since, phone) VALUES
('h1', 'priya@example.com', 'mock_hash', 'helper', 'Priya Sharma', 'Mumbai', 'Maharashtra', 'Experienced in household management with 5+ years of service. Specialized in cooking North Indian cuisine and thorough cleaning.', 'Cleaning,Cooking,Childcare', 200.00, 4.8, 24, 2020, '+91 98765 43210'),
('h2', 'seema@example.com', 'mock_hash', 'helper', 'Seema Patel', 'Mumbai', 'Maharashtra', 'Specialized in traditional and modern cooking. Excellent with South Indian dishes and baking.', 'Cooking,Cleaning', 250.00, 4.9, 31, 2019, '+91 98765 43211'),
('h3', 'anita@example.com', 'mock_hash', 'helper', 'Anita Das', 'Pune', 'Maharashtra', 'Good with baby care and cleaning. Certified in childcare with 3 years of experience.', 'Baby Care,Cleaning', 200.00, 4.7, 18, 2021, '+91 98765 43212'),
('c1', 'client1@example.com', 'mock_hash', 'client', 'Swati Verma', 'Mumbai', 'Maharashtra', 'Software Engineer looking for reliable help with household chores.', 'General', NULL, NULL, NULL, 2023, '+91 98765 43213'),
('c2', 'client2@example.com', 'mock_hash', 'client', 'Rohan Mehra', 'Pune', 'Maharashtra', 'Need help with house cleaning every weekend and occasional cooking.', 'General', NULL, NULL, NULL, 2024, '+91 98765 43214');

INSERT INTO availabilities (id, helper_id, days, start_time, end_time, note) VALUES
('a1', 'h1', 'Mon,Tue,Wed,Thu,Fri', '09:00', '17:00', 'Available on weekdays'),
('a2', 'h1', 'Sat', '10:00', '14:00', 'Weekend availability'),
('a3', 'h2', 'Mon,Tue,Wed,Thu,Fri', '10:00', '15:00', 'Morning to afternoon'),
('a4', 'h3', 'Mon,Wed,Fri,Sat', '08:00', '16:00', 'Flexible hours available');

INSERT INTO jobs (id, client_id, helper_id, scheduled_date, scheduled_start, scheduled_end, agreed_hourly_rate, total_amount, status, details, address, special_instructions) VALUES
('j1', 'c1', 'h1', '2025-10-28', '09:00', '12:00', 200.00, 600.00, 'completed', 'Standard house cleaning and kitchen maintenance.', 'A-101, Sunshine Apartments, Bandra West, Mumbai', 'Please bring your own cleaning supplies'),
('j2', 'c1', 'h2', '2025-11-05', '10:00', '13:00', 250.00, 750.00, 'accepted', 'Cooking for small family gathering - 4 people.', 'B-205, Green Valley, Andheri East, Mumbai', 'Vegetarian meals only please'),
('j3', 'c2', 'h3', '2025-11-10', '14:00', '17:00', 200.00, 600.00, 'requested', 'Baby sitting for 3-year old child.', 'C-304, Royal Homes, Kothrud, Pune', 'Child has peanut allergy');

INSERT INTO reviews (id, job_id, reviewer_id, reviewee_id, rating, comment) VALUES
('r1', 'j1', 'c1', 'h1', 5, 'Very polite and hardworking helper! Highly recommended. The house was spotless after her work.');

-- --------------------------------------------------------
-- Indexes (for performance)
-- --------------------------------------------------------
CREATE INDEX idx_profiles_city ON profiles (city);
CREATE INDEX idx_profiles_role ON profiles (role);
CREATE INDEX idx_profiles_email ON profiles (email);
CREATE INDEX idx_jobs_status ON jobs (status);
CREATE INDEX idx_jobs_helper_id ON jobs (helper_id);
CREATE INDEX idx_jobs_client_id ON jobs (client_id);