-- Social Media OAuth & Account Management

-- Platform app credentials (admin configures once per platform)
CREATE TABLE IF NOT EXISTS sn_social_apps (
    platform     VARCHAR(30)  NOT NULL PRIMARY KEY,
    app_id       VARCHAR(300) DEFAULT '',
    app_secret   TEXT,                          -- AES-encrypted
    extra        TEXT,                          -- JSON: additional config
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Connected accounts (one row per page/profile/account)
CREATE TABLE IF NOT EXISTS sn_social_accounts (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    platform         VARCHAR(30)  NOT NULL,
    account_type     ENUM('personal','page','organization','channel') DEFAULT 'personal',
    platform_id      VARCHAR(300) NOT NULL,
    username         VARCHAR(300) DEFAULT '',
    display_name     VARCHAR(400) DEFAULT '',
    avatar_url       VARCHAR(700) DEFAULT '',
    access_token     TEXT,                      -- AES-encrypted
    refresh_token    TEXT,                      -- AES-encrypted
    token_expires_at DATETIME     DEFAULT NULL,
    scopes           VARCHAR(800) DEFAULT '',
    profile_json     MEDIUMTEXT,               -- raw API response JSON
    followers_count  INT          DEFAULT 0,
    status           ENUM('active','expired','revoked','disconnected') DEFAULT 'active',
    is_active        TINYINT(1)   DEFAULT 1,   -- user toggled on/off
    connected_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    last_sync        DATETIME     DEFAULT NULL,
    UNIQUE KEY uq_platform_id (platform, platform_id(200)),
    INDEX idx_platform (platform),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Short-lived OAuth state tokens (CSRF protection during OAuth flow)
CREATE TABLE IF NOT EXISTS sn_social_oauth_states (
    state       VARCHAR(80)  NOT NULL PRIMARY KEY,
    platform    VARCHAR(30)  NOT NULL,
    data        TEXT,                           -- JSON: extra params
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Publishing log (richer than queue — permanent record)
CREATE TABLE IF NOT EXISTS sn_social_publish_log (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    account_id    INT          NOT NULL,
    platform      VARCHAR(30)  NOT NULL,
    post_ref      VARCHAR(200) DEFAULT '',
    content       TEXT,
    image_url     VARCHAR(700) DEFAULT '',
    remote_id     VARCHAR(300) DEFAULT '',
    status        ENUM('published','failed','scheduled') DEFAULT 'published',
    error         TEXT,
    published_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_account (account_id),
    INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO sn_social_apps (platform) VALUES
  ('facebook'),('instagram'),('linkedin'),('x'),('tiktok'),('threads'),('youtube'),('pinterest');