import { createConnection } from 'mysql2/promise';

async function createTable() {
  const connection = await createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'job_portal123'
  });

  const sql = `
    CREATE TABLE IF NOT EXISTS seeker_certifications (
      id INT AUTO_INCREMENT PRIMARY KEY,
      seeker_id INT NOT NULL,
      certification_id INT DEFAULT NULL,
      certification_name VARCHAR(255) NOT NULL,
      certification_provider VARCHAR(150) DEFAULT NULL,
      issue_date DATE DEFAULT NULL,
      expiry_date DATE DEFAULT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX (seeker_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `;

  try {
    await connection.query(sql);
    console.log('Table "seeker_certifications" created successfully!');
  } catch (error) {
    console.error('Error creating table:', error);
  } finally {
    await connection.end();
  }
}

createTable();
