-- ============================================================
-- Destination : smugmug_connector/sql/schema.sql
-- Version     : v0.1.0
-- What        : MySQL/MariaDB table definitions for SmugMug Connector.
--               Run once during setup, re-runnable safely (IF NOT EXISTS).
-- Dependencies: MySQL 5.7+ or MariaDB 10.3+
-- Known Issues: None
-- ============================================================

-- Settings key/value store (all module config stored here after install)
CREATE TABLE IF NOT EXISTS `sm_settings` (
    `setting_key`   VARCHAR(100)  NOT NULL,
    `setting_value` TEXT          DEFAULT NULL,
    `updated_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                  ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Temporary OAuth request tokens (cleared after successful access token exchange)
CREATE TABLE IF NOT EXISTS `sm_oauth_temp` (
    `id`                   INT          NOT NULL AUTO_INCREMENT,
    `request_token`        VARCHAR(255) NOT NULL,
    `request_token_secret` TEXT         NOT NULL,
    `created_at`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_request_token` (`request_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Settings keys written by the module (reference):
--
--   api_key               Plain      SmugMug consumer API key
--   api_secret            Encrypted  SmugMug consumer API secret
--   access_token          Encrypted  OAuth access token
--   access_token_secret   Encrypted  OAuth access token secret
--   nickname              Plain      Authenticated SmugMug user nickname
--   account_name          Plain      Authenticated SmugMug display name
--   connected_at          Plain      ISO-8601 datetime of last successful auth
--   callback_url          Plain      OAuth callback URL (overrides config constant)
--   admin_hash            Plain      bcrypt hash of admin password
-- ============================================================