database/schema.sql

CREATE TABLE IF NOT EXISTS api_requests (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    endpoint VARCHAR(50) NOT NULL,
    status_code INTEGER NOT NULL,
    success BOOLEAN NOT NULL,
    error_message TEXT,
    latency_ms FLOAT,
    model_name VARCHAR(255),
    model_version VARCHAR(50),
    input_json JSONB,
    output_json JSONB
);

CREATE TABLE IF NOT EXISTS inference_steps (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    step_name VARCHAR(100) NOT NULL,
    success BOOLEAN NOT NULL,
    error_message TEXT,
    duration_ms FLOAT,
    details_json JSONB
);

CREATE TABLE IF NOT EXISTS api_predictions (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    area VARCHAR(255),
    item VARCHAR(255),
    recommended_crop VARCHAR(255),
    predicted_yield FLOAT,
    n_candidates INTEGER,
    prediction_type VARCHAR(50)
);


CREATE TABLE IF NOT EXISTS api_recommendation_details (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    rank INTEGER NOT NULL,
    item VARCHAR(255) NOT NULL,
    predicted_yield FLOAT NOT NULL
);

CREATE TABLE IF NOT EXISTS country_crop_mapping (
    id SERIAL PRIMARY KEY,
    area VARCHAR(255) NOT NULL,
    item VARCHAR(255) NOT NULL,
    source VARCHAR(50) DEFAULT 'train',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(area, item)
);

CREATE TABLE IF NOT EXISTS reference_distributions (
    id SERIAL PRIMARY KEY,
    feature_name VARCHAR(100) NOT NULL,
    feature_value TEXT NOT NULL,
    source VARCHAR(50) DEFAULT 'train',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_reference_distributions_feature
ON reference_distributions(feature_name);CREATE INDEX IF NOT EXISTS idx_country_crop_mapping_area
ON country_crop_mapping(area);

CREATE INDEX IF NOT EXISTS idx_country_crop_mapping_item
ON country_crop_mapping(item);

CREATE INDEX IF NOT EXISTS idx_api_requests_timestamp
ON api_requests(timestamp);

CREATE INDEX IF NOT EXISTS idx_api_requests_endpoint
ON api_requests(endpoint);

CREATE INDEX IF NOT EXISTS idx_api_predictions_area
ON api_predictions(area);

CREATE INDEX IF NOT EXISTS idx_api_predictions_recommended_crop
ON api_predictions(recommended_crop);

CREATE INDEX IF NOT EXISTS idx_recommendation_details_request_id
ON api_recommendation_details(request_id);

CREATE INDEX IF NOT EXISTS idx_recommendation_details_item
ON api_recommendation_details(item);