import {
  Board,
  Identity,
  Post,
  PostStats,
  RecentPost,
  Thread,
  ThreadWithReplies,
} from './models';
import { sql } from './sql';

import db from '.';

const queryBoards = sql`
SELECT * FROM v_boards
`;

export const getBoards = (): Array<Board> => queryBoards.all();

const queryBoardStatsById = sql<[number]>`
SELECT COUNT(threads.id) AS threadCount
FROM boards
INNER JOIN posts AS threads ON threads.boardId = boards.id
AND threads.type = 0
AND threads.deleted = 0
WHERE boards.id = ?
  AND boards.deleted = 0
`;

export const getBoardStatsById = (id: number): Omit<PostStats, 'replyCount'> =>
  queryBoardStatsById.get(id);

const queryBoardByName = sql<[string]>`
SELECT *
FROM v_boards
WHERE name = ?
`;

export const getBoardByName = (name: string): Board =>
  queryBoardByName.get(name);

const queryThreadInfosByBoardId = sql<[number, number, number]>`
SELECT *
FROM v_threads
WHERE boardId = ?
ORDER BY lastReplyId DESC
LIMIT ?
OFFSET ?
`;

export const getThreadInfosByBoardId = (
  boardId: number,
  page: number,
  perPage: number,
): Array<Thread> =>
  queryThreadInfosByBoardId.all(boardId, perPage, page * perPage);

const queryAllRepliesByThreadId = sql<[number]>`
SELECT *
FROM v_posts
WHERE threadId = ?
  AND TYPE = 1
`;

const queryFewRepliesByThreadId = sql<[number, number]>`
SELECT *
FROM
  (SELECT *
   FROM v_posts
   WHERE threadId = ?
     AND TYPE = 1
   ORDER BY id DESC
   LIMIT ?)
ORDER BY id ASC
`;

export const getRepliesByThreadId = (
  threadId: number,
  count?: number,
): Array<Post> =>
  count
    ? queryFewRepliesByThreadId.all(threadId, count)
    : queryAllRepliesByThreadId.all(threadId);

export const getRepliesByThreadIds = db.transaction(
  (threadIds: Array<number>, count?: number) =>
    threadIds.reduce(
      (acc, id) => ({ ...acc, [id]: getRepliesByThreadId(id, count) }),
      {} as Record<number, Array<Post>>,
    ),
);

export const getThreadsByBoardId = (
  boardId: number,
  repliesPerThread: number,
  page: number,
  threadsPerPage: number,
): Array<ThreadWithReplies> => {
  const threadInfos = getThreadInfosByBoardId(boardId, page, threadsPerPage);
  const repliesByThreadId = getRepliesByThreadIds(
    threadInfos.map(({ id }) => id),
    repliesPerThread,
  );

  return threadInfos.map((post) => ({
    ...post,
    replies: repliesByThreadId[post.id],
  }));
};

const queryThreadInfoById = sql<[number]>`
SELECT *,
       (COUNT(*) - 1) AS replyCount
FROM v_posts
WHERE threadId = ?
GROUP BY threadId
`;

export const getPartialThreadById = (postId: number): Thread =>
  queryThreadInfoById.get(postId);

export const getThreadById = (
  threadId: number,
  replyCount?: number,
): ThreadWithReplies => {
  const post = getPartialThreadById(threadId);
  const replies = getRepliesByThreadId(threadId, replyCount);

  return {
    ...post,
    replies,
  };
};

const insertThread = sql<
  [number, string, string | null, string | null, string]
>`
INSERT INTO posts (boardId, comment, fingerprint, type, image, origin)
VALUES (?, ?, ?, 0, ?, ?)
`;

const updateThreadId = sql<[number, number]>`
UPDATE posts
SET threadId = ?
WHERE id = ?
`;

export const createThread = (
  boardId: number,
  comment: string,
  fingerprint: string | null = null,
  image: string | null = null,
  origin: string,
): ThreadWithReplies => {
  const threadId = insertThread.run(
    boardId,
    comment,
    fingerprint,
    image,
    origin,
  ).lastInsertRowid as number;

  updateThreadId.run(threadId, threadId);

  return getThreadById(threadId);
};

const insertPost = sql<
  [number, number, string, string | null, string | null, string]
>`
INSERT INTO posts (boardId, threadId, comment, fingerprint, type, image, origin)
VALUES (?, ?, ?, ?, 1, ?, ?)
`;

export const createReply = (
  threadId: number,
  comment: string,
  fingerprint: string | null = null,
  image: string | null = null,
  origin: string,
): number => {
  const thread = getThreadById(threadId);

  const postId = insertPost.run(
    thread.boardId,
    threadId,
    comment,
    fingerprint,
    image,
    origin,
  ).lastInsertRowid as number;

  return postId;
};

const querySiteInfo = sql`
SELECT
  (SELECT COUNT(id)
   FROM posts
   WHERE posts.type = 0 ) AS threadCount,

  (SELECT COUNT(id)
   FROM posts
   WHERE posts.type = 1 ) AS replyCount
`;

export const getSiteStats = (): PostStats => querySiteInfo.get();

const queryRecentPosts = sql<[number]>`
SELECT v_posts.*,
       threads.comment AS threadComment
FROM v_posts
LEFT JOIN posts AS threads ON v_posts.threadId = threads.id
WHERE (threads.muted IS NULL OR threads.muted = 0)
ORDER BY v_posts.id DESC
LIMIT ?
`;

export const getRecentPosts = (count: number): Array<RecentPost> =>
  queryRecentPosts.all(count);

const queryPost = sql<[number]>`
SELECT *
FROM v_posts
WHERE id = ?
`;

export const getPost = (id: number): Thread => queryPost.get(id);

const queryPostsForSubscription = sql<[number, number]>`
SELECT v_posts.*,
       threads.comment AS threadComment
FROM v_posts
LEFT JOIN posts AS threads ON v_posts.threadId = threads.id
WHERE v_posts.boardId = ?
  AND (threads.muted IS NULL OR threads.muted = 0)
ORDER BY id DESC
LIMIT ?
`;

export const getPostsForSubscription = db.transaction(
  (boardIds: Array<number>, count: number) =>
    boardIds
      .reduce(
        (acc, id) => acc.concat(queryPostsForSubscription.all(id, count)),
        [] as Array<RecentPost>,
      )
      .sort((a, b) => b.id - a.id)
      .slice(0, count),
);

const upsertNameForIdentity = sql<[string, string, string, string, string]>`
INSERT INTO identities (fingerprint, name, hash)
VALUES (?, ?, ?) ON CONFLICT (fingerprint) DO
UPDATE
SET name = ?, hash = ?
`;

export const setNameForIdentity = (
  fingerprint: string,
  name: string,
  hash: string,
): void => {
  const trim = fingerprint.replaceAll(':', '');
  upsertNameForIdentity.run(trim, name, hash, name, hash);
};

const queryIdentity = sql<[string]>`
SELECT *
FROM identities
WHERE fingerprint = ?
`;

export const getIdentity = (fingerprint: string): Identity => {
  const trim = fingerprint.replaceAll(':', '');
  const identity = queryIdentity.get(trim);

  if (identity !== undefined) {
    return identity;
  }

  return {
    fingerprint: trim,
    name: '',
  };
};

const queryBan = sql<[string]>`
SELECT COUNT(*) AS count
FROM bans
WHERE origin = ?
`;

export const hasBan = (origin: string): boolean =>
  queryBan.get(origin).count === 1;