migrations/0001_baseline.sql

-- 001_baseline.sql

-- Merchants Table
CREATE TABLE IF NOT EXISTS merchants (
    merchant_id VARCHAR PRIMARY KEY,
    email VARCHAR NOT NULL UNIQUE,
    password_hash VARCHAR NOT NULL,
    brand_name VARCHAR NOT NULL,
    slug VARCHAR NOT NULL UNIQUE,
    social_url VARCHAR,
    upi_id VARCHAR,
    recovery_key VARCHAR,
    session_version BIGINT NOT NULL DEFAULT 1,
    delivery_rate_per_km DOUBLE PRECISION NOT NULL DEFAULT 10.0,
    delivery_base_fee DOUBLE PRECISION NOT NULL DEFAULT 20.0,
    base_pincode VARCHAR NOT NULL DEFAULT '560001',
    auto_settle_threshold DOUBLE PRECISION DEFAULT 0.5,
    business_address TEXT,
    logistics_config JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Product Links Table
CREATE TABLE IF NOT EXISTS product_links (
    link_id VARCHAR PRIMARY KEY,
    merchant_id VARCHAR NOT NULL,
    product_name VARCHAR NOT NULL,
    price_inr DOUBLE PRECISION NOT NULL,
    image_data TEXT,
    expected_weight DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    link_views BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    transaction_id VARCHAR PRIMARY KEY,
    merchant_id VARCHAR NOT NULL,
    link_id VARCHAR NOT NULL,
    buyer_phone VARCHAR NOT NULL,
    buyer_name VARCHAR NOT NULL DEFAULT '',
    buyer_email VARCHAR NOT NULL DEFAULT '',
    shipping_pincode VARCHAR,
    delivery_address TEXT,
    price_inr DOUBLE PRECISION NOT NULL,
    status VARCHAR NOT NULL,
    vpa VARCHAR NOT NULL DEFAULT '',
    outbound_weight DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    return_weight DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    proof_data TEXT,
    proof_received_at TIMESTAMP,
    settled_at TIMESTAMP,
    paid_at TIMESTAMP,
    payu_id VARCHAR NOT NULL DEFAULT '',
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    estimated_delivery_at TIMESTAMP,
    shipping_method VARCHAR,
    is_payment BOOLEAN NOT NULL DEFAULT FALSE,
    platform_fee_paid BOOLEAN NOT NULL DEFAULT FALSE,
    platform_fee DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    delivery_fee DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    distance_km DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    risk_score DOUBLE PRECISION DEFAULT 0.0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Feedback Table
CREATE TABLE IF NOT EXISTS feedback (
    id BIGSERIAL PRIMARY KEY,
    merchant_id VARCHAR,
    category VARCHAR,
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Risk Audit Logs Table
CREATE TABLE IF NOT EXISTS risk_audit_logs (
    id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR,
    merchant_id VARCHAR NOT NULL,
    event_type VARCHAR NOT NULL,
    risk_level VARCHAR NOT NULL,
    details TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Idempotency Keys Table
CREATE TABLE IF NOT EXISTS idempotency_keys (
    key VARCHAR NOT NULL,
    merchant_id VARCHAR,
    action_scope VARCHAR NOT NULL DEFAULT 'global',
    request_hash VARCHAR NOT NULL,
    response_data TEXT,
    status VARCHAR NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    PRIMARY KEY (key, merchant_id, action_scope)
);

-- Customers Table
CREATE TABLE IF NOT EXISTS customers (
    customer_id VARCHAR PRIMARY KEY,
    phone VARCHAR NOT NULL UNIQUE,
    name VARCHAR,
    email VARCHAR,
    password_hash VARCHAR NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Security Blocks Table
CREATE TABLE IF NOT EXISTS security_blocks (
    ip VARCHAR PRIMARY KEY,
    reason VARCHAR,
    block_level VARCHAR NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Login Attempts Table
CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR NOT NULL,
    ip_address VARCHAR NOT NULL,
    successful BOOLEAN NOT NULL DEFAULT FALSE,
    attempted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indices
CREATE INDEX IF NOT EXISTS idx_merchants_email ON merchants(email);
CREATE INDEX IF NOT EXISTS idx_merchants_slug ON merchants(slug);
CREATE INDEX IF NOT EXISTS idx_product_links_merchant ON product_links(merchant_id);
CREATE INDEX IF NOT EXISTS idx_orders_merchant_status ON orders(merchant_id, status);
CREATE INDEX IF NOT EXISTS idx_orders_link ON orders(link_id);
CREATE INDEX IF NOT EXISTS idx_orders_phone ON orders(buyer_phone);
CREATE INDEX IF NOT EXISTS idx_risk_merchant ON risk_audit_logs(merchant_id);
CREATE INDEX IF NOT EXISTS idx_login_attempts_email_time ON login_attempts(email, attempted_at);
CREATE INDEX IF NOT EXISTS idx_login_attempts_ip_time ON login_attempts(ip_address, attempted_at);
CREATE INDEX IF NOT EXISTS idx_idempotency_lookup ON idempotency_keys(key, merchant_id, action_scope);
CREATE INDEX IF NOT EXISTS idx_orders_pincode_prefix ON orders ((LEFT(shipping_pincode, 3)));
CREATE INDEX IF NOT EXISTS idx_orders_status_created ON orders(status, created_at);
CREATE INDEX IF NOT EXISTS idx_orders_merchant_created ON orders(merchant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_buyer_merchant ON orders(buyer_phone, merchant_id);
CREATE INDEX IF NOT EXISTS idx_security_blocks_expires ON security_blocks(ip, expires_at);
CREATE INDEX IF NOT EXISTS idx_customers_phone_unique ON customers(phone);