-- theme_settings.sql
-- Per-app, per-group theme selection. Ties into the existing multi-group
-- auth schema (user_groups) so a theme choice can be scoped to a group,
-- a single user, or an app-wide default (group_id/user_id both NULL).
--
-- Precedence when resolving a theme for a request (see sys.theme.php):
--   1. user_id match for this app
--   2. group_id match for this app
--   3. app-wide default row (group_id IS NULL AND user_id IS NULL)
--   4. hardcoded fallback in sys.theme.php ('cosmo')

CREATE TABLE IF NOT EXISTS app_theme_settings (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_name    VARCHAR(64)  NOT NULL,
    group_id    INT UNSIGNED NULL,
    user_id     INT UNSIGNED NULL,
    theme_name  VARCHAR(64)  NOT NULL,
    mode        ENUM('light','dark','auto') NOT NULL DEFAULT 'light',
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_app_scope (app_name, group_id, user_id),
    KEY idx_app (app_name),

    CONSTRAINT fk_theme_group FOREIGN KEY (group_id)
        REFERENCES groups(id) ON DELETE CASCADE,
    CONSTRAINT fk_theme_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;