init.sql

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- GEM #2: Stations with Auto-Search
CREATE TABLE stations (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    amenities TEXT,
    location_name TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', name || ' ' || coalesce(amenities, '') || ' ' || coalesce(location_name, ''))
    ) STORED
);

-- GEM #3: Reservations with Conflict Prevention
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    charger_id INT REFERENCES stations(id),
    user_email TEXT NOT NULL,
    booking_period tsrange NOT NULL,
    EXCLUDE USING gist (charger_id WITH =, booking_period WITH &&)
);

-- GEM #2: Auto-Billing Usage Logs
CREATE TABLE usage_logs (
    id SERIAL PRIMARY KEY,
    reservation_id INT REFERENCES reservations(id),
    kwh_used NUMERIC(10, 2),
    rate_per_kwh NUMERIC(10, 2) DEFAULT 0.25,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (kwh_used * rate_per_kwh) STORED
);

-- GEM #1: Reliable Hardware Queue
CREATE TABLE hardware_queue (
    id SERIAL PRIMARY KEY,
    charger_id INT,
    command TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT now()
);

-- Trigger to notify hardware on new reservation
CREATE OR REPLACE FUNCTION notify_hardware_command() RETURNS trigger AS $$
BEGIN
    INSERT INTO hardware_queue (charger_id, command) VALUES (NEW.charger_id, 'START_CHARGE');
    PERFORM pg_notify('hardware_events', NEW.charger_id::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_reservation_start
AFTER INSERT ON reservations
FOR EACH ROW EXECUTE FUNCTION notify_hardware_command();