schema.sql

-- Enable PostGIS (run once)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create technicians table
CREATE TABLE IF NOT EXISTS technicians (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    qualifications TEXT[] NOT NULL,
    location GEOGRAPHY(POINT) NOT NULL,
    availability VARCHAR(20) DEFAULT 'available',
    rating FLOAT DEFAULT 5.0,
    contact VARCHAR(50) NOT NULL
);

-- Create index for geospatial queries
CREATE INDEX IF NOT EXISTS technicians_location_idx ON technicians USING GIST (location);

-- Insert sample technician
INSERT INTO technicians (name, qualifications, location, availability, rating, contact)
VALUES (
    'John Doe',
    ARRAY['plumbing', 'electrical'],
    ST_SetSRID(ST_MakePoint(36.8219, -1.2921), 4326),
    'available',
    4.8,
    'whatsapp:+254123456789'
);

-- Create users table for session tracking
CREATE TABLE IF NOT EXISTS users (
    number VARCHAR(50) PRIMARY KEY,
    last_message TEXT,
    state VARCHAR(50),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create requests table for feedback collection
CREATE TABLE IF NOT EXISTS requests (
    id SERIAL PRIMARY KEY,
    user_number VARCHAR(50),
    technician_id INT,
    service_type VARCHAR(50),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (technician_id) REFERENCES technicians(id)
);

-- Check if technicians exist
SELECT * FROM technicians;

-- If empty, insert sample data:
INSERT INTO technicians (name, qualifications, location, contact)
VALUES (
    'John Doe',
    ARRAY['hvac', 'electrical'],
    ST_SetSRID(ST_MakePoint(36.8219, -1.2921), 4326),  -- Nairobi coords
    '+254712345678'
);

INSERT INTO technicians (name, qualifications, location, contact) VALUES
    ('AC Specialist', ARRAY['hvac'], ST_MakePoint(36.81, -1.29), '+254700000001'),
    ('Plumber Ltd', ARRAY['plumbing'], ST_MakePoint(36.82, -1.30), '+254700000002');