class AddEntriesFts < ActiveRecord::Migration[8.1]
  def up
    execute <<~SQL
      CREATE VIRTUAL TABLE IF NOT EXISTS entries_fts USING fts5(
        fi,
        en,
        sv,
        no,
        ru,
        de,
        notes,
        content='entries',
        content_rowid='id'
      );
    SQL

    execute <<~SQL
      INSERT INTO entries_fts(entries_fts) VALUES('rebuild');
    SQL

    execute <<~SQL
      CREATE TRIGGER IF NOT EXISTS entries_fts_after_insert
      AFTER INSERT ON entries
      BEGIN
        INSERT INTO entries_fts(rowid, fi, en, sv, no, ru, de, notes)
        VALUES (new.id, new.fi, new.en, new.sv, new.no, new.ru, new.de, new.notes);
      END;
    SQL

    execute <<~SQL
      CREATE TRIGGER IF NOT EXISTS entries_fts_after_update
      AFTER UPDATE ON entries
      BEGIN
        INSERT INTO entries_fts(entries_fts, rowid, fi, en, sv, no, ru, de, notes)
        VALUES('delete', old.id, old.fi, old.en, old.sv, old.no, old.ru, old.de, old.notes);
        INSERT INTO entries_fts(rowid, fi, en, sv, no, ru, de, notes)
        VALUES (new.id, new.fi, new.en, new.sv, new.no, new.ru, new.de, new.notes);
      END;
    SQL

    execute <<~SQL
      CREATE TRIGGER IF NOT EXISTS entries_fts_after_delete
      AFTER DELETE ON entries
      BEGIN
        INSERT INTO entries_fts(entries_fts, rowid, fi, en, sv, no, ru, de, notes)
        VALUES('delete', old.id, old.fi, old.en, old.sv, old.no, old.ru, old.de, old.notes);
      END;
    SQL
  end

  def down
    execute "DROP TRIGGER IF EXISTS entries_fts_after_insert"
    execute "DROP TRIGGER IF EXISTS entries_fts_after_update"
    execute "DROP TRIGGER IF EXISTS entries_fts_after_delete"
    execute "DROP TABLE IF EXISTS entries_fts"
  end
end