silo-server/migrations/sql/049_admin_history_import.sql

-- +goose Up
-- +goose StatementBegin
-- Add encrypted admin token to history import sources.
-- The token is encrypted at rest (AES-256-GCM) and never returned in API responses.
ALTER TABLE history_import_sources
    ADD COLUMN encrypted_admin_token text;

-- Persistent user mappings: (source_server_user) → (silo_user + profile).
-- Created by admins to enable recurring, admin-initiated history imports.
CREATE TABLE history_import_user_mappings (
    id                   integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    source_id            integer NOT NULL REFERENCES history_import_sources(id) ON DELETE RESTRICT,
    external_user_id     text    NOT NULL,
    external_user_name   text    NOT NULL DEFAULT '',
    silo_user_id    integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    silo_profile_id text    NOT NULL,
    last_imported_at     timestamptz,
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT history_import_user_mappings_unique
        UNIQUE (source_id, external_user_id),
    CONSTRAINT history_import_user_mappings_profile_fkey
        FOREIGN KEY (silo_user_id, silo_profile_id)
        REFERENCES user_profiles(user_id, id) ON DELETE CASCADE
);

CREATE INDEX idx_history_import_user_mappings_source
    ON history_import_user_mappings (source_id);

CREATE INDEX idx_history_import_user_mappings_user
    ON history_import_user_mappings (silo_user_id);

-- Link runs to mappings (nullable; null = self-service run, non-null = admin-initiated).
ALTER TABLE history_import_runs
    ADD COLUMN mapping_id integer REFERENCES history_import_user_mappings(id) ON DELETE SET NULL;

CREATE INDEX idx_history_import_runs_mapping
    ON history_import_runs (mapping_id)
    WHERE mapping_id IS NOT NULL;

-- Expand allowed connection_mode values to include admin_token.
ALTER TABLE history_import_runs
    DROP CONSTRAINT history_import_runs_connection_mode_check,
    ADD CONSTRAINT history_import_runs_connection_mode_check
        CHECK (connection_mode IN ('connect', 'predefined', 'custom', 'plex_oauth', 'admin_token'));
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
ALTER TABLE history_import_runs
    DROP CONSTRAINT history_import_runs_connection_mode_check,
    ADD CONSTRAINT history_import_runs_connection_mode_check
        CHECK (connection_mode IN ('connect', 'predefined', 'custom', 'plex_oauth'));

DROP INDEX IF EXISTS idx_history_import_runs_mapping;

ALTER TABLE history_import_runs
    DROP COLUMN IF EXISTS mapping_id;

DROP INDEX IF EXISTS idx_history_import_user_mappings_user;
DROP INDEX IF EXISTS idx_history_import_user_mappings_source;

DROP TABLE IF EXISTS history_import_user_mappings;

ALTER TABLE history_import_sources
    DROP COLUMN IF EXISTS encrypted_admin_token;
-- +goose StatementEnd