-- Add payment categories with accents from payment with accents

INSERT INTO
  payment_category (name, category, created_at)
SELECT
  DISTINCT lower(payment.name), payment_category.category, datetime('now')
FROM
  payment
INNER JOIN
  payment_category
ON
  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
WHERE
  payment.name
IN
  (SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);

-- Remove unused payment categories

DELETE FROM
  payment_category
WHERE
  name NOT IN (SELECT DISTINCT lower(name) FROM payment);

-- Add category id to payment table

PRAGMA foreign_keys = 0;

ALTER TABLE payment ADD COLUMN "category" INTEGER NOT NULL REFERENCES "category" DEFAULT -1;

PRAGMA foreign_keys = 1;

UPDATE
  payment
SET
  category = (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name))
WHERE
  EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name));

DELETE FROM payment WHERE category = -1;

-- Remove

DROP TABLE payment_category;