CREATE VIEW emp.fix_salary AS
WITH
  sal AS (
    SELECT name, position, employed,
        abs(round(salary::float))::int AS salary -- parse to nearest int and remove minus
    FROM emp.employee_form
  )
SELECT name, position, employed,
    (CASE
      WHEN salary < 100000 THEN salary * 9 
      ELSE salary
     END) AS salary
FROM sal;


CREATE VIEW emp.fix_employed AS
WITH
  err_dates AS (
    SELECT name, position, employed, salary, regexp_split_to_array(employed, '\.') AS darr
    FROM emp.fix_salary
    WHERE employed ~ '\d\d\.\d\d\.\d\d\d\d'
  )
SELECT name, position, concat_ws('-', darr[3], darr[2], darr[1])::date AS employed, salary
FROM err_dates
UNION ALL
SELECT name, position, employed::date, salary
FROM emp.fix_salary
WHERE NOT employed ~ '\d\d\.\d\d\.\d\d\d\d';


CREATE VIEW emp.fix_position AS
WITH
  -- Following uses the trigrams-based similarity measure to find similarity
  -- between position in form an list of positions below
  sim AS ( 
    SELECT e.name, p.position, similarity(e.position, p.position) AS similarity
    FROM emp.fix_employed AS e, -- cross join
         (VALUES ('researcher'), ('lecturer'), ('professor')) AS p(position)
  )
SELECT e.name,
      (SELECT s.position
       FROM sim AS s
       WHERE s.name = e.name
       ORDER BY similarity DESC
       LIMIT 1) AS position, -- pick most similar position
      e.employed,
      e.salary
FROM emp.fix_employed AS e;


CREATE VIEW emp.fixed AS 
WITH
  dup AS ( -- Find duplicates to remove
    SELECT f.name, f.position, f.employed, f.salary
    FROM emp.fix_position AS f 
         JOIN emp.employee AS e USING (position)
    WHERE levenshtein(f.name, e.name) <= 2
  )
SELECT *
FROM emp.employee
UNION ALL (
  SELECT * FROM emp.fix_position
  EXCEPT 
  SELECT * FROM dup
);
