[CRON SCHEDULER: */15 * * * *]
                 │
                 ▼ (Tahap 1)
   [B3 Scraper di Phone Farm Swarm] ──► Ekstraksi Linimasa Trending X/Twitter (Wilayah ID)
                 │                      (Keyword, Hashtag, Tweet Volume, Current Rank)
                 ▼ (Tahap 2)
      [Taxonomy & Pillar Matcher]   ──► Pencocokan ke taxonomy_nodes & taxonomy_keywords
                 │                      (Filter Noise Hiburan vs Isu Strategis / 7 Pilar)
                 ▼ (Tahap 3)
   [Normalisasi & Upsert PostgreSQL]
        ├──────────────────────────────────────────┐
        ▼                                          ▼
   [social_trending_topics]                   [social_trending_snapshots]
   (Master topik aktif, status, pilar)         (Time-series interval 15m, rank, laju volume)
        │
        ▼ (Tahap 4: Target Crawl Mission)
   [Phone Farm Swarm (ADB + Native APK)]
        ├─────────────────┬─────────────────┬─────────────────┐
        ▼                 ▼                 ▼                 ▼
   [X (Twitter)]    [Facebook (FB)]  [Instagram (IG)]   [TikTok (Mobile)]
   (Thread/Replies) (Public Groups)  (Reels & Caption)  (Video Transcripts)
        │
        └─────────────────┬─────────────────┘
                          │ (Tahap 5: Normalisasi & Direct Multi-Sink)
                          ▼
            ┌───────────────────────────┐
            │   PostgreSQL & Elastic    │
            └─────────────┬─────────────┘
                          ├──────────────────────────────────────┐
                          ▼                                      ▼
             [Tabel / Indeks: postings]              [Tabel: post_author]
             • id_post, platform, permalink          • author_id, username, platform
             • content_text, media_urls              • display_name, follower_count
             • metrics: likes, reposts, comments     • actor_profiling (KOL/Aktivis/Buzzer)
             • sentiment_score & timestamp           • verified_status, account_created

Contoh Output Ekstraksi X Trending:
[
  { "rank": 1, "topic": "#KenaikanPajak", "tweet_volume": 68400, "context": "Economy" },
  { "rank": 2, "topic": "KawalPutusanMK", "tweet_volume": 142000, "context": "Politics" },
  { "rank": 3, "topic": "TimnasDay", "tweet_volume": 52100, "context": "Sports" }
]

CREATE TABLE social_trending_topics (
    id SERIAL PRIMARY KEY,
    topic_name VARCHAR(255) NOT NULL UNIQUE,       -- Contoh: "kawal putusan mk"
    hashtag VARCHAR(255),                          -- Contoh: "#KawalPutusanMK"
    taxonomy_node_id INTEGER REFERENCES taxonomy_nodes(id),
    pillar_code VARCHAR(32),                       -- Contoh: "PILAR_01_HUKUM_HANKAM"
    status VARCHAR(32) DEFAULT 'active',          -- 'active', 'cooling_down', 'archived'
    highest_rank INTEGER,                          -- Posisi puncak tertinggi yang pernah dicapai
    first_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    total_snapshots_count INTEGER DEFAULT 1
);

CREATE TABLE social_trending_snapshots (
    id BIGSERIAL PRIMARY KEY,
    topic_id INTEGER NOT NULL REFERENCES social_trending_topics(id) ON DELETE CASCADE,
    snapshot_at TIMESTAMP WITH TIME ZONE NOT NULL, -- Timestamp rekaman cron (per 15 menit)
    rank_position INTEGER NOT NULL,                -- Peringkat tren saat snapshot (1 - 50)
    tweet_volume INTEGER,                          -- Estimasi volume cuitan pada menit tersebut
    velocity_rate NUMERIC(8, 2),                   -- Selisih lonjakan volume terhadap snapshot t-1
    sentiment_ratio_positive NUMERIC(5, 2),        -- Estimasi rasio sentimen awal
    sentiment_ratio_negative NUMERIC(5, 2)
);

CREATE INDEX idx_snapshots_topic_time ON social_trending_snapshots (topic_id, snapshot_at DESC);

┌────────────────────────────────────────────────────────────────────────┐
│                      KEUNGGULAN PHONE FARM SWARM                       │
├────────────────────────────────────────────────────────────────────────┤
│ • Native Mobile Execution: Menjalankan APK resmi di smartphone Android │
│   fisik sehingga memiliki sidik jari perangkat (fingerprint) otentik.  │
│ • Bypass Anti-Bot & WAF: Menghindari blokir Cloudflare / Akamai / CAPTCHA│
│   karena bukan headless browser atau emulasi Chromium.                 │
│ • Residential Cellular IP: Menggunakan koneksi kartu seluler 4G LTE    │
│   dengan rotasi IP dinamis operator lokal Indonesia.                  │
└────────────────────────────────────────────────────────────────────────┘

CREATE TABLE postings (
    id VARCHAR(128) PRIMARY KEY,                  -- ID unik postingan dari platform asal
    platform VARCHAR(32) NOT NULL,                -- 'twitter', 'facebook', 'instagram', 'tiktok'
    author_id VARCHAR(128) NOT NULL,              -- Relasi ke tabel post_author
    content_text TEXT NOT NULL,                   -- Isi caption atau teks postingan
    permalink_url TEXT NOT NULL,                  -- URL tautan menuju konten asli
    published_at TIMESTAMP WITH TIME ZONE NOT NULL,
    crawled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Metrik Interaksi & Engagement
    likes_count INTEGER DEFAULT 0,
    shares_count INTEGER DEFAULT 0,               -- Retweet / Share / Repost
    comments_count INTEGER DEFAULT 0,
    views_count BIGINT DEFAULT 0,
    
    -- Klasifikasi Isu & Analisis
    primary_topic_id INTEGER REFERENCES social_trending_topics(id),
    sentiment_label VARCHAR(16),                  -- 'positive', 'neutral', 'negative'
    sentiment_score NUMERIC(5, 4),                -- -1.0000 s.d. +1.0000
    potential_reach BIGINT                        -- Followers + (Shares * 250) + Views
);

CREATE TABLE post_author (
    id VARCHAR(128) PRIMARY KEY,                  -- author_user_id (unik per platform)
    platform VARCHAR(32) NOT NULL,
    username VARCHAR(128) NOT NULL,
    display_name VARCHAR(255),
    profile_url TEXT,
    avatar_url TEXT,
    follower_count INTEGER DEFAULT 0,
    following_count INTEGER DEFAULT 0,
    verified BOOLEAN DEFAULT FALSE,
    actor_category VARCHAR(32) DEFAULT 'citizen', -- 'kol', 'activist', 'official', 'media', 'buzzer_suspect'
    last_active_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);