var db = module.exports;

var connection = require("./bookshelf");
var bookshelf = connection.bookshelf;
var knex = connection.knex;
var upload = require("../util/upload");

/* MODELS */

var User = (db.User = bookshelf.Model.extend({
  tableName: "users",
  hasTimestamps: false,
}));
var Thread = (db.Thread = bookshelf.Model.extend({
  tableName: "threads",
  hasTimestamps: false,
  checkPrivacy: function (user) {
    if (this.get("privacy") === 0) return true;
    let username = user.get("username");
    if (this.get("username") === username) return true;
    let allowed = (this.get("allowed") || "").split(" ");
    if (allowed.indexOf(username) !== -1) return true;
    return false;
  },
}));
var ThreadUser = (db.ThreadUser = bookshelf.Model.extend({
  tableName: "thread_users",
  hasTimestamps: false,
}));
var Comment = (db.Comment = bookshelf.Model.extend({
  tableName: "comments",
  hasTimestamps: false,
}));
var File = (db.File = bookshelf.Model.extend({
  tableName: "files",
  hasTimestamps: false,
}));
var Keyword = (db.Keyword = bookshelf.Model.extend({
  tableName: "keywords",
  hasTimestamps: false,
}));
var Mailbox = (db.Mailbox = bookshelf.Model.extend({
  tableName: "boxes",
  hasTimestamps: false,
}));
var Message = (db.Message = bookshelf.Model.extend({
  tableName: "messages",
  hasTimestamps: false,
}));

/* USERS */

db.createUser = function (data) {
  return new db.User(data).save();
};
db.getUsers = function () {
  return User.query(function (qb) {
    qb.orderBy("username", "asc");
  }).fetchAll({
    columns: [
      "id",
      "username",
      "realname",
      "firstseen",
      "lastseen",
      "location",
      "website",
      "avatar",
    ],
  });
};
db.getUser = function (id) {
  var model = new User({ id: id });
  return model.fetch();
};
db.getUsersById = function (ids) {
  return User.where("id", "in", ids).fetchAll();
};
db.getUsernamesById = function (ids) {
  return knex
    .column("id")
    .column("username")
    .select()
    .from("users")
    .where("id", "in", ids);
};
db.getUsernames = function () {
  return knex
    .column("username")
    .select()
    .from("users")
    .orderBy("username")
    .pluck("username");
};
db.checkUsernames = function (usernames) {
  return knex
    .column("username")
    .select()
    .distinct()
    .from("users")
    .where("username", "in", usernames);
};
db.getUserByUsername = function (username) {
  return new User({ username: username }).fetch();
};
db.getLastlog = function (limit) {
  return knex
    .column("id")
    .column("username")
    .column("lastseen")
    .select()
    .from("users")
    .orderBy("lastseen", "desc")
    .limit(limit || 10);
};

/** HOOTSTREAM */

db.getHootstreamFiles = ({ limit, offset, thread, keyword, username }) =>
  knex("files")
    .join("threads", "threads.id", "=", "files.thread")
    .select("files.*")
    .where((builder) => {
      builder.where("threads.privacy", false);
      if (keyword) {
        builder.where("threads.keyword", keyword);
      }
      if (thread) {
        builder.where("threads.id", thread);
      }
      if (username) {
        builder.where("files.username", username);
      }
    })
    .orderBy("files.id", "desc")
    .offset(offset)
    .limit(limit);
db.getHootstreamComments = ({ limit, offset, thread, keyword, username }) =>
  knex("comments")
    .join("threads", "threads.id", "=", "comments.thread")
    .select("comments.*")
    .where((builder) => {
      builder.where("threads.privacy", false);
      if (keyword) {
        builder.where("threads.keyword", keyword);
      }
      if (thread) {
        builder.where("threads.id", thread);
      }
      if (username) {
        builder.where("comments.username", username);
      }
    })
    .orderBy("comments.id", "desc")
    .offset(offset)
    .limit(limit);
db.getHootstreamThreads = ({ files, comments }) =>
  Thread.where((builder) =>
    builder.whereIn(
      "id",
      Array.from(
        new Set(
          [...comments, ...files]
            .map((item) => item?.thread)
            .filter((item) => !!item)
        )
      )
    )
  ).fetchAll();
db.getHootstreamAllCommentsForThread = (thread) =>
  Comment.where((builder) => builder.where("thread", thread.id)).fetchAll();
db.getHootstreamAllFilesForThread = (thread) =>
  File.where((builder) => builder.where("thread", thread.id)).fetchAll();

db.getHootstreamSomeFilesForThreads = (threads, count = 1) =>
  Promise.all(
    threads.map((thread) =>
      File.query((builder) =>
        builder.where("thread", thread.id).orderBy("date", "desc").limit(count)
      ).fetchAll()
    )
  ).then((results) =>
    results.reduce((list, result) => list.concat(Array.from(result)), [])
  );
db.getHootstreamSomeCommentsForThreads = (threads, count = 1) =>
  Promise.all(
    threads.map((thread) =>
      Comment.query((builder) =>
        builder.where("thread", thread.id).orderBy("date", "desc").limit(count)
      ).fetchAll()
    )
  ).then((results) =>
    results.reduce((list, result) => list.concat(Array.from(result)), [])
  );

/* THREADS */

db.getLatestThreads = function () {
  return Thread.query(function (qb) {
    qb.orderBy("lastmodified", "desc").limit(50);
  }).fetchAll();
};
db.getThreadsForKeyword = function (keyword) {
  return Thread.query(function (qb) {
    qb.where("keyword", "=", keyword).orderBy("id", "desc");
  }).fetchAll();
};
db.getThreadsForUser = function (username, limit, offset) {
  return Thread.query(function (qb) {
    qb.where("username", "=", username)
      .orderBy("id", "desc")
      .limit(limit || 40)
      .offset(offset || 0);
  }).fetchAll();
};
db.getTopThreadsForUser = function (username, limit, offset) {
  return Thread.query(function (qb) {
    qb.where("username", "=", username)
      .orderBy("viewed", "desc")
      .limit(limit || 40)
      .offset(offset || 0);
  }).fetchAll();
};
db.getThread = function (id) {
  return Thread.query("where", "id", "=", id).fetch();
};
db.getThreadsById = function (ids) {
  return Thread.where("id", "in", ids).fetchAll();
};
db.createThread = function (data) {
  console.log(data);
  return new db.Thread(data).save();
};
db.updateThread = function (data) {};
db.destroyThread = function (id) {};

/* THREAD USERS (user privacy) */
db.addUserToThread = function (user_id, thread_id) {
  return new ThreadUser({ user_id, thread_id }).save();
};
db.removeUserFromThread = function (user_id, thread_id) {
  return new ThreadUser({ user_id, thread_id }).destroy();
};
db.getThreadUserIds = function (thread_id) {
  return ThreadUser.column("user_id")
    .query("where", "thread_id", "=", thread_id)
    .fetch();
};
db.getThreadUsers = function (thread_id) {
  return db.getThreadUserIds(thread_id).then((thread_users) => {
    if (!thread_users.length) return [];
    var user_ids = thread_users.pluck("user_id");
    return db.getUsernamesById(user_ids).then((usernames) => {
      return usernames;
    });
  });
};
db.getUserThreadIds = function (user_id) {
  return ThreadUser.query("where", "user_id", "=", user_id).fetch();
};
db.getUserThreadCounts = function (ids) {
  return knex
    .column("username")
    .count("* as count")
    .select()
    .from("threads")
    .groupBy("username");
};
db.getUserCommentCounts = function (ids) {
  return knex
    .column("username")
    .count("* as count")
    .select()
    .from("comments")
    .groupBy("username");
};
db.getUserFileCounts = function (ids) {
  return knex
    .column("username")
    .sum("size as size")
    .count("* as count")
    .select()
    .from("files")
    .groupBy("username");
};

/* FILES */

db.getFileById = function (id) {
  return new File({ id: id }).fetch();
};
db.getFilesForThread = function (id) {
  return File.query("where", "thread", "=", id).fetchAll();
};
db.getFilesForUser = function (username, limit, offset) {
  return File.query(function (qb) {
    qb.where("username", "=", username)
      .orderBy("id", "desc")
      .limit(parseInt(limit) || 20)
      .offset(parseInt(offset) || 0);
  }).fetchAll();
};
db.getFileCounts = function (ids) {
  return knex
    .column("thread")
    .count("* as count")
    .select()
    .from("files")
    .where("thread", "in", ids)
    .groupBy("thread");
};
db.getFileSizes = function (ids) {
  return knex
    .column("thread")
    .sum("size as size")
    .count("* as count")
    .select()
    .from("files")
    .where("thread", "in", ids)
    .groupBy("thread");
};
db.getFilesById = function (ids) {
  return File.where("id", "in", ids).fetchAll();
};
db.createFile = function (data) {
  return new db.File(data).save();
};
db.destroyFile = function (id) {};
db.moveFile = function (file, thread_id) {
  var s3client = upload.client();
  var srcPath =
    process.env.S3_PATH +
    "/data/" +
    file.get("thread") +
    "/" +
    file.get("filename");
  var destPath =
    process.env.S3_PATH + "/data/" + thread_id + "/" + file.get("filename");
  console.log("moving", srcPath, "to", destPath);
  var copyPromise = new Promise((resolve, reject) => {
    s3client
      .copy(srcPath, destPath)
      .on("response", function (res) {
        console.log("copied file");
        s3client.deleteFile(srcPath, function (err, res) {
          if (err) {
            console.log("error deleting file");
            return reject(err);
          }
          file.set("thread", thread_id);
          file
            .save()
            .then(() => {
              resolve();
            })
            .catch((err) => {
              console.log("error saving file");
              reject(err);
            });
        });
      })
      .on("error", (err) => {
        console.error("/!\\ error connecting to s3");
        reject(err);
      })
      .end();
  });
  return copyPromise;
};
db.destroyFiles = function (files) {
  var s3client = upload.client();
  var rmPromises = files.map((file) => {
    return new Promise((resolve, reject) => {
      var thread_id = file.get("thread");
      var filename = file.get("filename");
      if (!thread_id || !filename) {
        return resolve();
      }
      var filePath =
        process.env.S3_PATH + "/data/" + thread_id + "/" + filename;
      console.log("delete", filePath);
      s3client.deleteFile(filePath, function (err, res) {
        // check `err`, then do `res.pipe(..)` or `res.resume()` or whatever.
        resolve();
      });
    });
  });
  var filePromises = files.map((file) => {
    return file.destroy();
  });
  return Promise.all(rmPromises.join(filePromises));
};

/* COMMENTS */

db.getCommentsForThread = function (id, limit, offset, order) {
  order = order || "asc";
  return Comment.query(function (qb) {
    qb.where("thread", "=", id).orderBy("id", order);
    if (limit) {
      qb.limit(limit);
    }
    if (offset) {
      qb.offset(offset);
    }
  })
    .fetchAll()
    .then(function (comments) {
      comments.forEach(function (comment) {
        comment.set("comment", comment.get("comment").toString());
      });
      return comments;
    });
};
db.getCommentById = function (id) {
  return new Comment({ id: id }).fetch();
};
db.getCommentsById = function (ids) {
  return Comment.where("id", "in", ids).fetchAll();
};
db.getCommentsForUser = function (username, limit) {
  return Comment.query(function (qb) {
    qb.where("username", "=", username)
      .orderBy("date", "desc")
      .limit(parseInt(limit) || 20);
  }).fetchAll();
};
db.getCommentCounts = function (ids) {
  return knex
    .column("thread")
    .count("* as count")
    .select()
    .from("comments")
    .where("thread", "in", ids)
    .groupBy("thread");
};
db.createComment = function (data) {
  return new db.Comment(data).save();
};
db.updateComment = function (data) {};
db.destroyComment = function (id) {};

/* KEYWORDS */

db.getKeywords = function (keywords) {
  if (keywords) {
    return Keyword.query("where", "keyword", "in", keywords).fetchAll();
  }
  return Keyword.fetchAll();
};
db.getKeyword = function (keyword) {
  return Keyword.query("where", "keyword", "=", keyword).fetch();
};
db.getThreadGroups = function (keyword) {
  return knex
    .column("keyword")
    .sum("viewed")
    .as("viewed")
    .count("*")
    .as("count")
    .select()
    .from("threads")
    .groupBy("keyword");
};
db.getLatestKeywordThreads = function (keyword) {
  var ids = knex("threads").max("id").groupBy("keyword");
  return knex
    .select("id", "keyword", "title", "lastmodified", "privacy")
    .from("threads")
    .where("id", "in", ids);
};
// .column('id')
// .column('title')
// .column('lastmodified')
// .column('privacy')

db.createKeyword = function (data) {
  return new db.Keyword(data).save();
};
db.updateKeyword = function (data) {};
db.destroyKeyword = function (id) {};

/* MAILBOXES */

db.getMailboxes = function (username) {
  return Mailbox.query("where", "owner", "=", username).fetchAll();
};
db.getMailbox = function (mbox) {
  return new Mailbox({ mbox: mbox }).fetch();
};
db.getMailboxCounts = function (boxes) {
  return knex
    .column("mbox")
    .count("* as count")
    .select()
    .from("messages")
    .where("mbox", "in", boxes)
    .groupBy("mbox");
};
db.createMailbox = function (data) {
  return new db.Mailbox(data).save();
};
db.bumpMailboxCount = function (mbox) {
  new db.Mailbox({ mbox: mbox }).fetch();
};
db.checkMail = function (username) {
  return knex
    .count("* as count")
    .select()
    .from("messages")
    .where("recipient", "=", username)
    .andWhere("unread", "=", 1);
};

/* MESSAGES */

db.getMessages = function (username, box, limit, offset) {
  var mbox = username + "." + box;
  return Message.query(function (qb) {
    qb.column(
      "id",
      "mbox",
      "unread",
      "sender",
      "recipient",
      "date",
      "subject",
      knex.raw("CHAR_LENGTH(body) as size")
    )
      .where("mbox", "=", mbox)
      .orderBy("id", "desc");
    if (limit) {
      qb.limit(limit);
    }
    if (offset) {
      qb.offset(offset);
    }
  }).fetchAll();
};
db.getMessage = function (id) {
  var model = new Message({ id: id });
  return model.fetch().then(function (message) {
    if (!message) return null;
    message.set("body", message.get("body").toString());
    return message;
  });
};
db.createMessage = function (data) {
  return new db.Message(data).save();
};
db.updateMessage = function (data) {};
db.destroyMessage = function (id) {};