🏡 index : quine-global/quintodrome.git

package migrations

import (
	"context"
	"database/sql"

	"github.com/pressly/goose/v3"
)

func init() {
	goose.AddMigrationContext(upAddAlbumReplaygain, downAddAlbumReplaygain)
}

func upAddAlbumReplaygain(ctx context.Context, tx *sql.Tx) error {
	// Backfill the most-frequent value per album (matching MediaFiles.ToAlbum), staging RG-bearing rows
	// into an indexed temp table — a correlated subquery over a windowed CTE re-scans media_file per album.
	_, err := tx.ExecContext(ctx, `
ALTER TABLE album ADD COLUMN rg_album_gain real;
ALTER TABLE album ADD COLUMN rg_album_peak real;

CREATE TEMP TABLE _rg_backfill AS
	SELECT album_id, rg_album_gain, rg_album_peak FROM media_file
	WHERE rg_album_gain IS NOT NULL OR rg_album_peak IS NOT NULL;
CREATE INDEX _rg_backfill_album ON _rg_backfill(album_id);

UPDATE album SET
	rg_album_gain = (SELECT rg_album_gain FROM _rg_backfill WHERE _rg_backfill.album_id = album.id AND rg_album_gain IS NOT NULL
		GROUP BY rg_album_gain ORDER BY count(*) DESC, rg_album_gain LIMIT 1),
	rg_album_peak = (SELECT rg_album_peak FROM _rg_backfill WHERE _rg_backfill.album_id = album.id AND rg_album_peak IS NOT NULL
		GROUP BY rg_album_peak ORDER BY count(*) DESC, rg_album_peak LIMIT 1)
WHERE album.id IN (SELECT album_id FROM _rg_backfill);

DROP TABLE _rg_backfill;
	`)
	return err
}

func downAddAlbumReplaygain(ctx context.Context, tx *sql.Tx) error {
	// This code is executed when the migration is rolled back.
	return nil
}