package migrations

import (
	"database/sql"
)

func init() {
	RegisterMigration(
		2,
		"Add ON DELETE CASCADE to foreign keys",
		migrateCascadeFKUp,
		migrateCascadeFKDown,
	)
}

func migrateCascadeFKUp(tx *sql.Tx) error {
	// Enable foreign keys for this transaction
	_, err := tx.Exec("PRAGMA foreign_keys = OFF")
	if err != nil {
		return err
	}

	// 1. Rebuild media_files with FK + CASCADE
	_, err = tx.Exec(`
		CREATE TABLE media_files_new (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			path TEXT NOT NULL,
			library_id INTEGER NOT NULL,
			FOREIGN KEY (library_id) REFERENCES libraries(id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO media_files_new SELECT id, path, library_id FROM media_files`)
	if err != nil {
		return err
	}
	_, err = tx.Exec("DROP TABLE media_files")
	if err != nil {
		return err
	}
	_, err = tx.Exec("ALTER TABLE media_files_new RENAME TO media_files")
	if err != nil {
		return err
	}

	// 2. Rebuild songs with FK + CASCADE on media_file_id
	_, err = tx.Exec(`
		CREATE TABLE songs_new (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			title TEXT NOT NULL,
			album_id INTEGER,
			artist_id INTEGER,
			duration INTEGER,
			media_file_id INTEGER UNIQUE,
			FOREIGN KEY (album_id) REFERENCES albums(id),
			FOREIGN KEY (artist_id) REFERENCES artists(id),
			FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO songs_new SELECT id, title, album_id, artist_id, duration, media_file_id FROM songs`)
	if err != nil {
		return err
	}
	_, err = tx.Exec("DROP TABLE songs")
	if err != nil {
		return err
	}
	_, err = tx.Exec("ALTER TABLE songs_new RENAME TO songs")
	if err != nil {
		return err
	}

	return nil
}

func migrateCascadeFKDown(tx *sql.Tx) error {
	_, err := tx.Exec("PRAGMA foreign_keys = OFF")
	if err != nil {
		return err
	}

	// Revert songs
	_, err = tx.Exec(`
		CREATE TABLE songs_old (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			title TEXT NOT NULL,
			album_id INTEGER,
			artist_id INTEGER,
			duration INTEGER,
			media_file_id INTEGER UNIQUE,
			FOREIGN KEY (album_id) REFERENCES albums(id),
			FOREIGN KEY (artist_id) REFERENCES artists(id),
			FOREIGN KEY (media_file_id) REFERENCES media_files(id)
		)
	`)
	if err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO songs_old SELECT id, title, album_id, artist_id, duration, media_file_id FROM songs`)
	if err != nil {
		return err
	}
	_, err = tx.Exec("DROP TABLE songs")
	if err != nil {
		return err
	}
	_, err = tx.Exec("ALTER TABLE songs_old RENAME TO songs")
	if err != nil {
		return err
	}

	// Revert media_files
	_, err = tx.Exec(`
		CREATE TABLE media_files_old (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			path TEXT NOT NULL,
			library_id INTEGER NOT NULL
		)
	`)
	if err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO media_files_old SELECT id, path, library_id FROM media_files`)
	if err != nil {
		return err
	}
	_, err = tx.Exec("DROP TABLE media_files")
	if err != nil {
		return err
	}
	_, err = tx.Exec("ALTER TABLE media_files_old RENAME TO media_files")
	if err != nil {
		return err
	}

	return nil
}