-- ============================================================
-- StatusSaver Server Database
-- Single-device model: 1 install = 1 device = 1 WhatsApp number.
-- (Simpler than WASeen's devices_engine + wa_accounts split.)
-- Millis timestamps are BIGINT here so the *1000 writes store correctly.
-- ============================================================

CREATE DATABASE IF NOT EXISTS statussaver
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE statussaver;

-- ------------------------------------------------------------
-- 1. ss_devices
--    Row created on app open (device_id, ip, country).
--    wa_phone filled after WhatsApp login (wa_register.php).
-- ------------------------------------------------------------
CREATE TABLE ss_devices (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    device_id       VARCHAR(100) NOT NULL UNIQUE,        -- Android ID / install UUID
    device_name     VARCHAR(64)  DEFAULT NULL,           -- "StatusSaver-A3F9K7M2X1" (WA linked-device name)
    platform        ENUM('android','ios') NOT NULL DEFAULT 'android',
    app_version     VARCHAR(20)  DEFAULT '1.0.0',
    push_token      VARCHAR(255) DEFAULT NULL,           -- FCM token (notifications)

    -- captured on app open / each register call
    ip              VARCHAR(45)  DEFAULT '',             -- IPv4/IPv6
    ip_country      VARCHAR(2)   DEFAULT '',             -- from Cloudflare CF-IPCountry
    country_code    VARCHAR(2)   DEFAULT '',             -- app locale fallback

    -- WhatsApp link state (set after login)
    wa_phone        VARCHAR(20)  DEFAULT NULL,           -- NOT unique: reinstall can reuse
    wa_status       ENUM('none','connected','disconnected','logged_out') DEFAULT 'none',
    wa_linked_at    BIGINT       DEFAULT NULL,

    last_seen       BIGINT       DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_dev_waphone (wa_phone),
    INDEX idx_dev_lastseen (last_seen),
    INDEX idx_dev_country (ip_country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 2. ss_contacts
--    Synced from the Go engine after WhatsApp login.
--    Go sends lowercase keys: phone, full_name, push_name, business_name.
--    Keyed per device (one device = one WA, so device scope is enough).
-- ------------------------------------------------------------
CREATE TABLE ss_contacts (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id_fk    INT          NOT NULL,
    wa_phone        VARCHAR(20)  DEFAULT '',             -- owning WhatsApp (denormalised)
    phone           VARCHAR(20)  NOT NULL,               -- contact's number
    name            VARCHAR(150) DEFAULT '',
    push_name       VARCHAR(150) DEFAULT '',
    business_name   VARCHAR(150) DEFAULT '',
    created_at      BIGINT       DEFAULT NULL,
    updated_at      BIGINT       DEFAULT NULL,

    FOREIGN KEY (device_id_fk) REFERENCES ss_devices(id) ON DELETE CASCADE,
    UNIQUE KEY uk_dev_phone (device_id_fk, phone),
    INDEX idx_ct_dev (device_id_fk),
    INDEX idx_ct_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 3. ss_contact_counts  (quick display, like pool_contact_counts)
-- ------------------------------------------------------------
CREATE TABLE ss_contact_counts (
    device_id_fk    INT PRIMARY KEY,
    wa_phone        VARCHAR(20) DEFAULT '',
    contact_count   INT         DEFAULT 0,
    updated_at      BIGINT      DEFAULT NULL,
    FOREIGN KEY (device_id_fk) REFERENCES ss_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- NEXT (pairs with the Go engine work — status features):
--   ss_status_log    (posts: device, sender phone, media type, msg id, ts)
--   ss_status_views  (who viewed MY status: device, viewer phone, played/read, ts)
-- These are added when we wire the engine's status + receipt events.
-- ============================================================
