FROM postgres:16-alpine
ENV POSTGRES_DB=verifid_kyc
ENV POSTGRES_USER=verifid_admin
COPY ./init-scripts/ /docker-entrypoint-initdb.d/

shared_buffers = '1GB'
effective_cache_size = '3GB'
work_mem = '64MB'
maintenance_work_mem = '256MB'
max_connections = 200
wal_level = 'replica'
max_wal_senders = 5

CREATE SCHEMA kyc;

CREATE TABLE kyc.verification_sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    confidence_score NUMERIC(5,4),
    reasons JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ,
    ip_address INET,
    device_fingerprint TEXT
);

CREATE TABLE kyc.audit_logs (
    log_id BIGSERIAL PRIMARY KEY,
    session_id UUID REFERENCES kyc.verification_sessions(session_id),
    module_name VARCHAR(50) NOT NULL,
    module_score NUMERIC(5,4),
    details JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE ROLE verifid_app LOGIN PASSWORD 'secure_password';
GRANT USAGE ON SCHEMA kyc TO verifid_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA kyc TO verifid_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA kyc TO verifid_app;

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    verifid_kyc     verifid_app     10.0.0.0/8              scram-sha-256
host    verifid_kyc     verifid_admin   10.0.0.0/8              scram-sha-256
host    all             all             0.0.0.0/0               reject

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: verifid_kyc
      POSTGRES_USER: verifid_admin
      POSTGRES_PASSWORD_FILE: /run/secrets/db_password
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U verifid_admin -d verifid_kyc"]
      interval: 10s
      timeout: 5s
      retries: 5