const mysql = require("mysql2/promise");

let connection = null;

async function init(sqlConnection) {
  sqlConnection.execute("CREATE DATABASE IF NOT EXISTS capstone_project");
  sqlConnection.query("use capstone_project");

  sqlConnection.execute(
    `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        password VARCHAR(255) NOT NULL,
        vt_member BOOLEAN DEFAULT FALSE,
        address VARCHAR(255),
        phone VARCHAR(255)
        )`
  );


  // New: Create items table
  //TODO: mark as sold
  //TODO: 优先处理图片
  sqlConnection.execute(
    `CREATE TABLE IF NOT EXISTS items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        price DECIMAL(10, 2) NOT NULL,
        category_id VARCHAR(255) NOT NULL,
        seller_id INT,
        sold BOOLEAN DEFAULT FALSE,
        FOREIGN KEY (seller_id) REFERENCES users(id)
        )`
  );

  sqlConnection.execute(
      `CREATE TABLE IF NOT EXISTS images (
       id INT AUTO_INCREMENT PRIMARY KEY,
       item_id INT NOT NULL,
       url VARCHAR(255),
       FOREIGN KEY (item_id) REFERENCES items(id)
        )`
  )

  sqlConnection.execute(
    `CREATE TABLE IF NOT EXISTS messages (
        message_id INT AUTO_INCREMENT PRIMARY KEY,
        sender_id INT NOT NULL,
        receiver_id INT NOT NULL,
        content TEXT NOT NULL,
        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (sender_id) REFERENCES users(id),
        FOREIGN KEY (receiver_id) REFERENCES users(id)
        )`
  );
  return sqlConnection;
}

let getConnection = async function () {
  if (!connection) {
    connection = await mysql.createConnection({
      host: "localhost",
      user: "capstone",
      password: "password",
    });
    await init(connection);
    return connection;
  } else {
    return connection;
  }
};


let insertTest = async function () {
  let db = await getConnection();
  await db.execute(`
        INSERT INTO users (name, email, password, vt_member, address, phone) VALUES
                                                                                ('Alice', 'alice@example.com', 'password1', FALSE, 'address1', '1234567890'),
                                                                                ('Bob', 'bob@vt.edu', 'password2', TRUE, 'address2', '9876543210'),
                                                                                ('Charlie', 'charlie@example.com', 'password3', FALSE, 'address3', '1122334455');
    `);
};
// insertTest().then(r => console.log(123))
//getConnection()

// exports as promise, need await before use
module.exports = { getConnection };
