-- Secrets & API-Key Manager — run once.
--
-- Stores expirable API keys/tokens (encrypted) with a health status per key.
-- secret_value format: base64(iv) ":" base64(ciphertext)  (AES-256-CBC,
-- key = SHA-256(SECRETS_ENC_KEY)). Seeded rows start empty/unknown; paste the
-- actual values via the admin panel (which encrypts on save). Until then the
-- Python pipeline falls back to the matching .env variable.

CREATE TABLE IF NOT EXISTS app_secret (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    secret_key     VARCHAR(64)  NOT NULL UNIQUE,          -- code used in .env / os.getenv
    label          VARCHAR(150) NOT NULL,                 -- human-friendly name
    provider       VARCHAR(30)  NOT NULL,                 -- facebook | groq | ...
    secret_value   TEXT         NULL,                     -- encrypted; NULL = not set yet
    status         ENUM('active','expired','error','unknown') NOT NULL DEFAULT 'unknown',
    last_error     TEXT         NULL,
    last_error_at  DATETIME     NULL,
    last_ok_at     DATETIME     NULL,
    expires_at     DATETIME     NULL,                     -- optional, for proactive display
    last_update_by INT          NULL,
    last_update_on DATETIME     NULL
);

INSERT IGNORE INTO app_secret (secret_key, label, provider, status) VALUES
('PAGE_ACCESS_TOKEN',   'Facebook / Instagram Page Access Token', 'facebook',  'unknown'),
('GROQ_API_KEY',        'Groq API Key (LLM enrichment)',          'groq',      'unknown'),
('WORLD_NEWS_API_KEY',  'World News API Key',                     'worldnews', 'unknown');
