import { createHash } from 'crypto';

import { Migration } from '../migrate';

export const prepareFingerprint = (raw: string): string =>
  raw.replaceAll(':', '');

export const generateHash = (fingerprint: string): string => {
  const hash = createHash('sha256');

  hash.update(process.env.SALT!);
  hash.update(prepareFingerprint(fingerprint), 'hex');

  return hash
    .digest()
    .toString('base64')
    .slice(0)
    .replaceAll('+', '-')
    .replaceAll('/', '_')
    .slice(0, 8);
};

export const migration: Migration = {
  down: (db) => {
    db.exec(`
DROP TRIGGER UpdateIdentitiesTimestamp;


DROP TRIGGER UpdatePostsTimestamp;


DROP TRIGGER UpdateBoardsTimestamp;


DROP VIEW v_boards;


DROP VIEW v_threads;


DROP VIEW v_posts;


ALTER TABLE identities
DROP COLUMN hash;
`);
  },
  up: (db) => {
    db.exec(`
ALTER TABLE identities ADD hash VARCHAR(32);


CREATE VIEW v_posts AS
SELECT posts.*,
       ('/' || boards.name || '/threads/' || threads.id) AS path,
       (COALESCE(identities.name, '') || '!' || identities.hash) AS author
FROM posts
INNER JOIN posts AS threads ON posts.threadId = threads.id
AND threads.deleted = 0
INNER JOIN boards ON posts.boardId = boards.id
AND boards.deleted = 0
LEFT JOIN identities ON posts.fingerprint = identities.fingerprint;


CREATE VIEW v_threads AS
SELECT *,
       COUNT(id) - 1 AS replyCount,
       MAX(id) AS lastReplyId
FROM v_posts
GROUP BY threadId
HAVING MIN(id);


CREATE VIEW v_boards AS
SELECT boards.*,
       ('/' || boards.name ||'/') AS path
FROM boards
WHERE boards.deleted = 0
ORDER BY boards.name ASC;


CREATE TRIGGER UpdateBoardsTimestamp AFTER
UPDATE ON boards
FOR EACH ROW WHEN NEW.updatedAt < OLD.updatedAt BEGIN
UPDATE boards
SET updatedAt=DATETIME('now', 'localtime')
WHERE id=OLD.id; END;


CREATE TRIGGER UpdatePostsTimestamp AFTER
UPDATE ON posts
FOR EACH ROW WHEN NEW.updatedAt < OLD.updatedAt BEGIN
UPDATE posts
SET updatedAt=DATETIME('now', 'localtime')
WHERE id=OLD.id; END;


CREATE TRIGGER UpdateIdentitiesTimestamp AFTER
UPDATE ON identities
FOR EACH ROW WHEN NEW.updatedAt < OLD.updatedAt BEGIN
UPDATE identities
SET updatedAt=DATETIME('now', 'localtime')
WHERE fingerprint=OLD.fingerprint; END;
`);

    const queryAll = db.prepare(`SELECT * FROM identities`);
    const identities = queryAll.all();

    const update = db.prepare<[string, string]>(`
UPDATE identities SET hash = ? WHERE fingerprint = ?
`);

    identities.forEach(({ fingerprint }) => {
      const hash = generateHash(fingerprint);
      update.run(hash, fingerprint);
    });
  },
  version: 1632523877,
};