init-db.sql

-- ============================================================
--  Zero-Trust API Gateway — Database Initialization Script
-- ============================================================
--  Runs automatically on first PostgreSQL container startup.
-- ============================================================

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'FREE',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Audit logs table
CREATE TABLE IF NOT EXISTS api_audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID,
    ip_address VARCHAR(45),
    method VARCHAR(10),
    endpoint VARCHAR(255),
    status_code INT,
    latency_ms BIGINT,
    timestamp TIMESTAMP DEFAULT NOW()
);

-- IP rules table
CREATE TABLE IF NOT EXISTS ip_rules (
    id BIGSERIAL PRIMARY KEY,
    ip_address VARCHAR(45) UNIQUE,
    rule_type VARCHAR(10),
    reason VARCHAR(255),
    created_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON api_audit_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_user_id ON api_audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_ip ON api_audit_logs(ip_address);
CREATE INDEX IF NOT EXISTS idx_ip_rules_address ON ip_rules(ip_address);

-- Insert default ADMIN user (password: Akhil9664)
-- BCrypt hash for "admin12345" with strength 12
INSERT INTO users (id, username, password, role, is_active)
VALUES (
    gen_random_uuid(),
    'Akhil',
    '$2a$12$LJ3m4yst4pTKhKgGOUmXxOxVPsYdOm.Ep3A.3s0WEvJwS.GmqGFMW',
    'ADMIN',
    true
) ON CONFLICT (username) DO NOTHING;

-- Insert sample FREE user (password: user12345)
INSERT INTO users (id, username, password, role, is_active)
VALUES (
    gen_random_uuid(),
    'testuser',
    '$2a$12$LJ3m4yst4pTKhKgGOUmXxOxVPsYdOm.Ep3A.3s0WEvJwS.GmqGFMW',
    'FREE',
    true
) ON CONFLICT (username) DO NOTHING;