-- LogistiX PostgreSQL Schema (Draft)
-- Based on conceptual design in system_architecture_draft.md

-- Enum Types (if needed, or use CHECK constraints)
CREATE TYPE user_role AS ENUM (
    'Merchant',
    'Courier',
    'Admin'
);

CREATE TYPE user_status AS ENUM (
    'Active',
    'Inactive',
    'PendingVerification',
    'Suspended'
);

CREATE TYPE courier_availability AS ENUM (
    'Available',
    'Busy',
    'Offline'
);

CREATE TYPE order_status AS ENUM (
    'Pending',
    'Assigned',
    'Accepted',
    'Rejected',
    'PickedUp',
    'InTransit',
    'Delivered',
    'FailedAttempt',
    'Cancelled'
);

CREATE TYPE payment_status AS ENUM (
    'Pending',
    'Completed',
    'Failed'
);

-- Users Table
CREATE TABLE Users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    role user_role NOT NULL,
    status user_status NOT NULL DEFAULT 'PendingVerification',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Merchants Table
CREATE TABLE Merchants (
    merchant_id UUID PRIMARY KEY REFERENCES Users(user_id) ON DELETE CASCADE,
    business_name VARCHAR(255) NOT NULL,
    business_address TEXT,
    -- billing_details JSONB, -- Consider storing billing info securely, maybe link to a separate service/table
    api_key VARCHAR(255) UNIQUE, -- Needs secure generation and storage
    webhook_url VARCHAR(512)
);

-- Couriers Table
CREATE TABLE Couriers (
    courier_id UUID PRIMARY KEY REFERENCES Users(user_id) ON DELETE CASCADE,
    vehicle_type VARCHAR(50),
    vehicle_registration VARCHAR(20),
    license_number VARCHAR(50),
    current_location GEOMETRY(Point, 4326), -- Requires PostGIS extension
    availability_status courier_availability DEFAULT 'Offline',
    rating NUMERIC(3, 2) DEFAULT 0.00 -- Average rating
);
-- Create spatial index for courier location
CREATE INDEX idx_couriers_location ON Couriers USING GIST (current_location);

-- Admins Table (can just use Users table with role='Admin', or add specific fields if needed)
-- CREATE TABLE Admins (
--     admin_id UUID PRIMARY KEY REFERENCES Users(user_id) ON DELETE CASCADE,
--     admin_level INTEGER DEFAULT 1
-- );

-- Addresses Table (Reusable for pickup/delivery)
-- Option 1: Embed in Orders (simpler for MVP, less normalization)
-- Option 2: Separate Table (better normalization, more complex joins)
-- Choosing Option 1 (embedding) for simplicity in this draft

-- Orders Table
CREATE TABLE Orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL REFERENCES Merchants(merchant_id),
    assigned_courier_id UUID REFERENCES Couriers(courier_id), -- Courier who accepted
    
    -- Pickup Address (Embedded)
    pickup_street VARCHAR(255) NOT NULL,
    pickup_city VARCHAR(100) NOT NULL,
    pickup_postal_code VARCHAR(20),
    pickup_country VARCHAR(50) DEFAULT 'Kenya',
    pickup_latitude NUMERIC(10, 7),
    pickup_longitude NUMERIC(10, 7),
    
    -- Delivery Address (Embedded)
    delivery_street VARCHAR(255) NOT NULL,
    delivery_city VARCHAR(100) NOT NULL,
    delivery_postal_code VARCHAR(20),
    delivery_country VARCHAR(50) DEFAULT 'Kenya',
    delivery_latitude NUMERIC(10, 7),
    delivery_longitude NUMERIC(10, 7),
    
    recipient_name VARCHAR(200) NOT NULL,
    recipient_phone VARCHAR(20) NOT NULL,
    
    -- Package Details (Embedded)
    package_description TEXT,
    package_weight_kg NUMERIC(8, 2),
    package_length_cm INTEGER,
    package_width_cm INTEGER,
    package_height_cm INTEGER,
    
    delivery_instructions TEXT,
    status order_status NOT NULL DEFAULT 'Pending',
    delivery_fee NUMERIC(10, 2),
    currency VARCHAR(3) DEFAULT 'KES',
    
    estimated_delivery_time TIMESTAMPTZ,
    pickup_time TIMESTAMPTZ,
    actual_delivery_time TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for Orders
CREATE INDEX idx_orders_merchant_id ON Orders(merchant_id);
CREATE INDEX idx_orders_assigned_courier_id ON Orders(assigned_courier_id);
CREATE INDEX idx_orders_status ON Orders(status);
CREATE INDEX idx_orders_created_at ON Orders(created_at);

-- OrderTracking Table (History)
CREATE TABLE OrderTracking (
    tracking_id BIGSERIAL PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES Orders(order_id) ON DELETE CASCADE,
    courier_id UUID REFERENCES Couriers(courier_id), -- Courier at the time of update
    status_update order_status NOT NULL,
    location GEOMETRY(Point, 4326), -- Location at the time of update (Requires PostGIS)
    notes TEXT, -- e.g., Reason for failed attempt
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for OrderTracking
CREATE INDEX idx_ordertracking_order_id ON OrderTracking(order_id);
CREATE INDEX idx_ordertracking_timestamp ON OrderTracking(timestamp);

-- Ratings Table
CREATE TABLE Ratings (
    rating_id BIGSERIAL PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES Orders(order_id),
    rated_by_user_id UUID NOT NULL REFERENCES Users(user_id),
    rated_user_id UUID NOT NULL REFERENCES Users(user_id), -- Typically the courier
    score INTEGER NOT NULL CHECK (score >= 1 AND score <= 5),
    comment TEXT,
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (order_id, rated_by_user_id) -- Ensure one rating per order per rater
);
-- Indexes for Ratings
CREATE INDEX idx_ratings_rated_user_id ON Ratings(rated_user_id);

-- Payments Table
CREATE TABLE Payments (
    payment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES Orders(order_id), -- Can be NULL for other payment types?
    user_id UUID NOT NULL REFERENCES Users(user_id), -- Who initiated/received payment
    amount NUMERIC(12, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'KES',
    payment_method VARCHAR(50), -- e.g., 'M-Pesa', 'Card', 'COD'
    transaction_id VARCHAR(255) UNIQUE, -- From payment gateway
    status payment_status NOT NULL DEFAULT 'Pending',
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for Payments
CREATE INDEX idx_payments_order_id ON Payments(order_id);
CREATE INDEX idx_payments_user_id ON Payments(user_id);

-- Notifications Table (Optional, could be handled externally)
-- CREATE TABLE Notifications (
--     notification_id BIGSERIAL PRIMARY KEY,
--     user_id UUID NOT NULL REFERENCES Users(user_id),
--     type VARCHAR(10) NOT NULL, -- 'SMS', 'Push', 'Email'
--     recipient VARCHAR(255) NOT NULL, -- Phone number, device token, email
--     message TEXT NOT NULL,
--     status VARCHAR(20) DEFAULT 'Pending', -- Pending, Sent, Failed
--     sent_at TIMESTAMPTZ,
--     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
-- );

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply the trigger to tables
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON Users
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON Orders
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

-- (Apply trigger to other tables like Merchants, Couriers as needed)

-- Enable PostGIS extension (if not already enabled)
-- CREATE EXTENSION IF NOT EXISTS postgis;
-- Enable uuid-ossp extension for gen_random_uuid()
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

