-- =====================================================================
-- IPTRACE — Database Schema
-- Untuk aplikasi dashboard IP tracking (lihat ip-tracker.html)
-- Dialek: SQLite (mudah diadaptasi ke PostgreSQL/MySQL, lihat catatan)
-- =====================================================================

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------
-- 1. users
-- Opsional: hanya diperlukan jika riwayat & watchlist ingin dipisah
-- per akun. Jika aplikasi tetap single-user/publik, tabel ini bisa
-- diabaikan dan kolom user_id di tabel lain dibuat nullable (sudah).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    email           TEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    display_name    TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- ---------------------------------------------------------------------
-- 2. ip_scans
-- Riwayat setiap pemindaian IP/domain yang dilakukan lewat dashboard.
-- Satu baris = satu hasil lookup (mirror dari respons ipwho.is).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ip_scans (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER REFERENCES users(id) ON DELETE SET NULL,

    -- apa yang diketik user (bisa IP atau domain) vs IP hasil resolve
    query_input     TEXT NOT NULL,
    ip_address      TEXT NOT NULL,
    is_success      BOOLEAN NOT NULL DEFAULT 1,
    error_message   TEXT,

    -- lokasi
    country         TEXT,
    country_code    TEXT,          -- ISO 3166-1 alpha-2, dipakai untuk emoji bendera
    region          TEXT,
    region_code     TEXT,
    city            TEXT,
    postal_code     TEXT,
    latitude        REAL,
    longitude       REAL,

    -- jaringan
    isp             TEXT,
    organization    TEXT,
    asn             TEXT,          -- contoh: "AS15169"

    -- lain-lain (ditampilkan di stat-grid dashboard)
    timezone_id     TEXT,
    utc_offset      TEXT,          -- contoh: "+07:00"
    currency_code   TEXT,
    currency_name   TEXT,
    calling_code    TEXT,

    raw_response    TEXT,          -- simpan JSON mentah dari API sebagai cadangan
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_ip_scans_ip         ON ip_scans (ip_address);
CREATE INDEX IF NOT EXISTS idx_ip_scans_created_at ON ip_scans (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ip_scans_user        ON ip_scans (user_id);


-- ---------------------------------------------------------------------
-- 3. watchlist
-- Daftar IP yang ingin dipantau/disimpan permanen oleh user
-- (berbeda dari riwayat scan yang bersifat log otomatis).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS watchlist (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER REFERENCES users(id) ON DELETE CASCADE,
    ip_address      TEXT NOT NULL,
    label           TEXT,           -- nama bebas, misal "Kantor Pusat"
    notes           TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, ip_address)
);


-- ---------------------------------------------------------------------
-- 4. ip_cache
-- Cache hasil lookup per-IP agar tidak memanggil API eksternal
-- berulang kali untuk IP yang sama dalam rentang waktu singkat.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ip_cache (
    ip_address      TEXT PRIMARY KEY,
    response_json   TEXT NOT NULL,
    fetched_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at      DATETIME NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_ip_cache_expires ON ip_cache (expires_at);


-- =====================================================================
-- Catatan portabilitas:
-- - PostgreSQL : ganti "INTEGER PRIMARY KEY AUTOINCREMENT" -> "SERIAL PRIMARY KEY",
--                "DATETIME" -> "TIMESTAMP", "BOOLEAN" tetap sama.
-- - MySQL      : ganti "INTEGER PRIMARY KEY AUTOINCREMENT" -> "INT AUTO_INCREMENT PRIMARY KEY",
--                "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" tetap didukung.
-- =====================================================================