-- Optional session intelligence tables:
-- - ip_profiles: idempotent IP-level GeoIP cache
-- - session_ip_profiles: per-session projection linked to a cached IP profile

CREATE TABLE IF NOT EXISTS ip_profiles (
    id TEXT PRIMARY KEY,
    ip_address TEXT NOT NULL UNIQUE,
    provider TEXT NOT NULL,
    country_code TEXT,
    country_name TEXT,
    region_name TEXT,
    city TEXT,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    timezone TEXT,
    isp TEXT,
    organization TEXT,
    asn TEXT,
    is_vpn BOOLEAN,
    is_proxy BOOLEAN,
    is_tor BOOLEAN,
    is_hosting BOOLEAN,
    raw_payload JSONB NOT NULL DEFAULT '{}',
    lookup_expires_at TIMESTAMPTZ,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ip_profiles_lookup_expires_at ON ip_profiles (lookup_expires_at);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_country_code ON ip_profiles (country_code);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_last_seen_at ON ip_profiles (last_seen_at DESC);

CREATE TABLE IF NOT EXISTS session_ip_profiles (
    id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL UNIQUE REFERENCES sessions (id) ON DELETE CASCADE,
    session_token TEXT NOT NULL,
    user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    ip_address TEXT NOT NULL,
    ip_profile_id TEXT REFERENCES ip_profiles (id) ON DELETE SET NULL,
    country_code TEXT,
    country_name TEXT,
    is_vpn BOOLEAN,
    is_proxy BOOLEAN,
    is_tor BOOLEAN,
    is_hosting BOOLEAN,
    country_changed BOOLEAN NOT NULL DEFAULT FALSE,
    previous_country_code TEXT,
    provider TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_session_ip_profiles_session_token ON session_ip_profiles (session_token);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_user_created ON session_ip_profiles (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_code ON session_ip_profiles (country_code);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_ip_address ON session_ip_profiles (ip_address);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_changed ON session_ip_profiles (country_changed);
