const connection = require('../sql');
class DashboardService {
  // 获取总用户量
  async getTotalUsers() {
    const statement = `SELECT  count(id) as total FROM users`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取今天注册用户量
  async getTotalUsersByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM users WHERE created_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取今天登录用户量
  async getLoginUsersByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM users WHERE updated_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取项目总数量
  async getTotalProjects() {
    const statement = `SELECT  count(id) as total FROM projects`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 今天创建项目总数
  async getTotalCreatedByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM projects WHERE created_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 今天更新项目总数
  async getTotalUpdatesByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM projects WHERE updated_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取页面总数量
  async getTotalPages() {
    const statement = `SELECT  count(id) as total FROM pages`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取页面今日创建数量
  async getCreatedPagesByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM pages WHERE created_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取页面今日更新数量
  async getUpdatedPagesByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM pages WHERE updated_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取组件库总数量
  async getTotalLibs() {
    const statement = `SELECT  count(id) as total FROM lib`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取组件今日创建数量
  async getCreatedLibsByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM lib WHERE created_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取组件今日更新数量
  async getUpdatedLibsByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  count(id) as total FROM lib WHERE updated_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }

  // 获取页面发布总次数
  async getPagesPublish() {
    const statement = `SELECT  count(id) as total FROM pages_publish`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取组件发布总次数
  async getLibsPublish() {
    const statement = `SELECT  count(id) as total FROM lib_publish`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  //   获取本月注册用户量
  async getUsersByWeek() {
    const statement = `
    SELECT
        DATE_FORMAT(created_at, '%Y-%m-%d') AS date,
        COUNT(*) AS value
    FROM
        users
    WHERE
        created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
        AND created_at < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')
    GROUP BY
        DATE_FORMAT(created_at, '%Y-%m-%d')
    ORDER BY
        date;
    `;
    const [result] = await connection.execute(statement, []);
    return result;
  }
  // 获取用户总条数
  async getUserCount(userId) {
    let statement = `SELECT count(id) total FROM users`;
    if (userId) {
      statement += ` WHERE id = ${userId}`;
    }
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取用户列表
  async getUserList(pageNum, pageSize, userId) {
    const offset = (+pageNum - 1) * pageSize + '';
    const limit = pageSize;
    const statement = `SELECT id,nick_name nickName,user_name userName,avatar,created_at createdAt, updated_at updatedAt FROM users where ${
      userId ? 'id = ' + userId : '1=1'
    } LIMIT ${offset},${limit};`;
    const [result] = await connection.execute(statement, []);
    return result;
  }
  // 获取充值总额
  async getTotalRecharge() {
    const statement = `SELECT  sum(amount) as total FROM pay_records`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
  // 获取今日充值总额
  async getTotalRechargeByToday() {
    const date = new Date().toLocaleDateString();
    const statement = `SELECT  sum(amount) as total FROM pay_records WHERE created_at > '${date} 00:00:00'`;
    const [result] = await connection.execute(statement, []);
    return result[0];
  }
}
module.exports = new DashboardService();