#!/usr/bin/env python3
"""Membuat database/seed.sql berisi DATA SIMULASI (bukan data nyata). Jalankan: python3 database/generate_seed.py"""
import hashlib
import random
from datetime import datetime, timedelta
from pathlib import Path

random.seed(20261024)
NOW = datetime(2026, 10, 24, 9, 0, 0)
out = ["-- DATA SIMULASI untuk pengujian lokal. Jangan dipakai di produksi.",
       "USE cybersecurity_monitoring;", "SET time_zone = '+00:00';", ""]


def q(v):
    if v is None:
        return "NULL"
    if isinstance(v, datetime):  # waktu relatif agar data selalu "segar" saat diimpor
        return f"(UTC_TIMESTAMP() - INTERVAL {max(0, int((NOW - v).total_seconds()))} SECOND)"
    if isinstance(v, (int, float)):
        return str(v)
    return "'" + str(v).replace("\\", "\\\\").replace("'", "''") + "'"


def ins(table, cols, rows):
    for r in rows:
        out.append(f"INSERT INTO {table} ({','.join(cols)}) VALUES ({','.join(q(x) for x in r)});")


SENSORS = [("sim-linux-01", "web-server-01", "linux", "bash", "SIM-KEY-LINUX-01"),
           ("sim-windows-01", "app-server-02", "windows", "powershell", "SIM-KEY-WINDOWS-01"),
           ("sim-go-agent-01", "edge-gateway", "linux", "golang", "SIM-KEY-GO-01")]
ins("sensor_status", ["id", "sensor_code", "hostname", "os", "sensor_type", "api_key_hash", "last_seen_at", "status"],
    [(i + 1, c, h, o, t, hashlib.sha256(k.encode()).hexdigest(), NOW - timedelta(seconds=20 * (i + 1)), "online")
     for i, (c, h, o, t, k) in enumerate(SENSORS)])

IPS = ["203.0.113.9", "203.0.113.44", "198.51.100.7", "198.51.100.23", "192.0.2.50", "192.0.2.77", "10.0.0.15", "10.0.0.22"]
EVENT_TYPES = [
    ("brute_force", "high", "login gagal berulang dari {ip}", "Blokir/ratelimit IP dan aktifkan MFA."),
    ("sql_injection", "warning", "Pola SQL Injection mencurigakan pada /products.php", "Gunakan prepared statement; tinjau log DB."),
    ("sql_injection", "high", "Pola SQL Injection (UNION SELECT) pada /search.php", "Gunakan prepared statement; blokir IP sementara."),
    ("xss", "warning", "Pola XSS mencurigakan pada /comment.php", "Escape output dan terapkan CSP."),
    ("path_traversal", "high", "Percobaan akses ../../etc/passwd pada /download.php", "Validasi path terhadap whitelist."),
    ("port_scan", "high", "{ip} menyentuh 24 port berbeda dalam 60 detik (dugaan port scan)", "Tinjau aturan firewall."),
    ("traffic_spike", "warning", "Lonjakan trafik: 540 request/menit (4.8x rata-rata)", "Periksa IP teratas, aktifkan rate limiting."),
    ("unusual_admin", "warning", "Aktivitas administratif tidak biasa 'delete_user': di luar jam kerja", "Konfirmasi dengan admin."),
    ("system", "normal", "Log sistem: sesi SSH diterima", None),
    ("system", "warning", "Log sistem: Failed password for invalid user test", None),
]
events, attacks = [], []
for i in range(1, 61):
    t = NOW - timedelta(minutes=random.randint(5, 3 * 24 * 60))
    typ, risk, text, rec = random.choice(EVENT_TYPES)
    ip = random.choice(IPS)
    events.append((i, f"INC-SIM-{i:06X}", t, typ, SENSORS[i % 3][0], ip, risk, text.format(ip=ip), None, rec,
                   "new" if random.random() < .6 else random.choice(["investigating", "resolved", "false_positive"]), i % 3 + 1))
events += [
    (61, "INC-SIM-00003D", NOW - timedelta(minutes=12), "file_tampering", "sim-linux-01", None, "critical",
     "Berkas penting berubah: /etc/ssh/sshd_config", "/etc/ssh/sshd_config", "Bandingkan dengan backup; jika tidak sah isolasi sistem.", "new", 1),
    (62, "INC-SIM-00003E", NOW - timedelta(minutes=3), "sensor_failure", "engine", None, "high",
     "Sensor 'sim-go-agent-01' berhenti mengirim data", None, "Periksa layanan sensor dan jaringan.", "new", 3)]
events.sort(key=lambda e: e[2])
ins("security_events", ["id", "incident_code", "event_time", "event_type", "source", "ip_address", "risk_level", "summary",
                        "evidence", "recommendation", "handling_status", "sensor_id"], events)
ENDP = {"sql_injection": "/products.php", "xss": "/comment.php", "path_traversal": "/download.php"}
IND = {"sql_injection": "union select, tautologi OR/AND 1=1", "xss": "tag <script>", "path_traversal": "../ atau ..\\"}
aid = 1
for e in events:
    if e[3] in ENDP:
        attacks.append((aid, e[0], e[3], ENDP[e[3]], IND[e[3]], e[2], random.choice(["suspected", "suspected", "confirmed", "false_positive"])))
        aid += 1
ins("attack_attempts", ["id", "event_id", "attack_type", "endpoint", "indicator", "detected_at", "verification_status"], attacks)

logins = []
for _ in range(120):
    ok = random.random() < .6
    ip = random.choice(IPS)
    logins.append((random.choice(["admin", "analyst", "root", "test", "budi"]), ip, int(ok),
                   "Mozilla/5.0 (simulasi)", "dashboard", NOW - timedelta(minutes=random.randint(1, 3 * 24 * 60))))
for k in range(9):
    logins.append(("admin", "198.51.100.7", 0, "curl/8.0 (simulasi)", "dashboard", NOW - timedelta(minutes=40 - k)))
ins("login_logs", ["username", "ip_address", "success", "user_agent", "source", "created_at"], logins)

metrics = []
for sid in (1, 2, 3):
    for m in range(0, 360, 5):
        metrics.append((sid, round(random.uniform(10, 70) + (25 if m < 20 and sid == 1 else 0), 2), round(random.uniform(35, 75), 2),
                        round(random.uniform(40, 60), 2), '{"nginx": "running", "mysql": "running"}', NOW - timedelta(minutes=m)))
ins("system_metrics", ["sensor_id", "cpu_percent", "mem_percent", "disk_percent", "services_json", "collected_at"], metrics)

H = lambda s: hashlib.sha256(s.encode()).hexdigest()
fim = []
for path in ("/etc/passwd", "/etc/hosts", "/var/www/html/index.php"):
    fim.append((1, path, H(path), H(path), "ok", NOW - timedelta(hours=1)))
fim.append((1, "/etc/ssh/sshd_config", H("a"), H("b"), "modified", NOW - timedelta(minutes=12)))
fim.append((2, "C:\\Windows\\System32\\drivers\\etc\\hosts", H("h"), H("h"), "ok", NOW - timedelta(hours=1)))
ins("file_integrity_logs", ["sensor_id", "file_path", "baseline_hash", "current_hash", "status", "checked_at"], fim)

alerts = [(e[0], e[6] if e[6] != "normal" else "info", e[7], int(random.random() < .4), e[2]) for e in events if e[6] in ("high", "critical")]
ins("alerts", ["event_id", "priority", "message", "is_read", "created_at"], alerts)

inc = [(e[0], e[1], e[7][:120], e[3], e[6], "Terdeteksi oleh mesin deteksi (data simulasi).", "open" if n % 2 else "investigating", e[2])
       for n, e in enumerate([e for e in events if e[6] == "critical" or (e[6] == "high" and e[3] in ("brute_force", "path_traversal"))][:6])]
ins("incident_reports", ["event_id", "incident_code", "title", "category", "risk_level", "chronology", "status", "created_at"], inc)

Path(__file__).with_name("seed.sql").write_text("\n".join(out) + "\n", encoding="utf-8")
print(f"seed.sql: {len(events)} event, {len(attacks)} serangan, {len(logins)} login, {len(metrics)} metrik")