import DB from "./db.js";

export async function initDatabase() {
  try {
    console.log("Initializing database tables if they do not exist...");
    
    // Create Users table
    await DB.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        mobile VARCHAR(20) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        userType INT NOT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // Create Folders table
    await DB.execute(`
      CREATE TABLE IF NOT EXISTS Folders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userId INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        path VARCHAR(512) NOT NULL,
        parentFolderId INT DEFAULT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (parentFolderId) REFERENCES Folders(id) ON DELETE CASCADE
      )
    `);

    // Create Documents table
    await DB.execute(`
      CREATE TABLE IF NOT EXISTS Documents (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userId INT NOT NULL,
        folderId INT DEFAULT NULL,
        title VARCHAR(255) NOT NULL,
        path VARCHAR(512) NOT NULL,
        fileName VARCHAR(255) NOT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (folderId) REFERENCES Folders(id) ON DELETE CASCADE
      )
    `);

    // Seed test user
    const [rows]: any = await DB.execute("SELECT * FROM users WHERE mobile = '1111111111'");
    if (rows.length === 0) {
      console.log("Seeding test user...");
      await DB.execute(
        "INSERT INTO users (mobile, password, userType) VALUES ('1111111111', 'testcred@1', 2)"
      );
      console.log("Test user seeded successfully!");
    } else {
      console.log("Test user already exists.");
    }
  } catch (error) {
    console.error("Database initialization failed:", error);
  }
}