-- 0010_secure_intelligence.sql

CREATE TABLE IF NOT EXISTS pincode_stats (
    pincode VARCHAR PRIMARY KEY,
    total_orders BIGINT DEFAULT 0,
    total_disputes BIGINT DEFAULT 0,
    avg_delivery_time_hours DOUBLE PRECISION DEFAULT 0.0,
    volatility_score DOUBLE PRECISION DEFAULT 0.0, -- 0.0 to 1.0
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE merchants ADD COLUMN reliability_score DOUBLE PRECISION DEFAULT 1.0;
ALTER TABLE merchants ADD COLUMN network_rank INT;

ALTER TABLE orders ADD COLUMN pincode_volatility_at_checkout DOUBLE PRECISION DEFAULT 0.0;

CREATE INDEX IF NOT EXISTS idx_pincode_volatility ON pincode_stats(volatility_score);