Handballbooking/server/utils/initDatabase.js

import mariadb from 'mariadb';
// import dotenv from 'dotenv'; // Remove dotenv
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// dotenv.config(); // Remove dotenv config

// Create a pool using hardcoded database credentials (LESS SECURE)
const initialPool = mariadb.createPool({
  host: '172.10.1.4',
  user: 'handball',
  password: 'Gabi2104@',
  database: 'handball',
  connectionLimit: 5
});

// Ensure required directories exist
const ensureDirectories = () => {
  const uploadsDir = path.join(__dirname, '..', '..', 'public', 'uploads');
  const pdfDir = path.join(__dirname, '..', '..', 'public', 'pdfs');

  // Create uploads directory if it doesn't exist
  if (!fs.existsSync(uploadsDir)) {
    fs.mkdirSync(uploadsDir, { recursive: true });
    console.log('Created uploads directory');
  }

  // Create pdfs directory if it doesn't exist
  if (!fs.existsSync(pdfDir)) {
    fs.mkdirSync(pdfDir, { recursive: true });
    console.log('Created pdfs directory');
  }
};

// Ensure .env file exists with required variables (This check is no longer strictly necessary but can be kept as a reminder)
const ensureEnvFile = () => {
  const envPath = path.join(__dirname, '..' , '.env');
  const requiredEnvVars = [
    'DB_HOST',
    'DB_USER',
    'DB_PASSWORD',
    'DB_NAME',
    'PORT',
    'SMTP_HOST',
    'SMTP_PORT',
    'SMTP_USER',
    'SMTP_PASS'
  ];

  if (!fs.existsSync(envPath)) {
    console.warn('Warning: .env file not found. Using hardcoded credentials.'); // Change to warning
    // Do not throw error, just warn
  } else {
     const envContent = fs.readFileSync(envPath, 'utf8');
     const missingVars = requiredEnvVars.filter(varName => !envContent.includes(`${varName}=`)); // Check for key=value
     if (missingVars.length > 0) {
       console.warn('Warning: Missing required environment variables in .env:', missingVars.join(', ')); // Change to warning
     }
  }
};

// List of all required tables and their creation SQL
const tableDefinitions = {
  matches: `
    CREATE TABLE matches (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      date DATETIME NOT NULL,
      location VARCHAR(255) NOT NULL,
      totalSeats INT NOT NULL,
      availableSeats INT NOT NULL,
      price DECIMAL(10,2) DEFAULT 0,
      timeoutDate DATETIME NOT NULL,
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `,
  tickets: `
    CREATE TABLE tickets (
      id INT AUTO_INCREMENT PRIMARY KEY,
      matchId INT NOT NULL,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL,
      phone VARCHAR(20) NOT NULL,
      seats INT NOT NULL,
      status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending',
      pdfFile VARCHAR(255),
      deliveryMethod ENUM('download', 'email') DEFAULT 'download',
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      CONSTRAINT fk_ticket_match FOREIGN KEY (matchId) REFERENCES matches(id) ON DELETE CASCADE
    ) /* Added ON DELETE CASCADE */
  `,
  seats: `
    CREATE TABLE seats (
      id INT AUTO_INCREMENT PRIMARY KEY,
      matchId INT NOT NULL,
      seatNumber INT NOT NULL,
      status ENUM('available', 'reserved', 'booked') DEFAULT 'available',
      ticketId INT NULL,
      direction VARCHAR(50),
      extractedSeatNumber INT,
      uploadedPdfPath VARCHAR(255),
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      CONSTRAINT fk_seat_match FOREIGN KEY (matchId) REFERENCES matches(id) ON DELETE CASCADE, /* Added ON DELETE CASCADE */
      CONSTRAINT fk_seat_ticket FOREIGN KEY (ticketId) REFERENCES tickets(id) ON DELETE SET NULL, /* Added ON DELETE SET NULL */
      UNIQUE KEY unique_seat_match (matchId, seatNumber)
    )
  `,
  admin: `
    CREATE TABLE admin (
      id INT AUTO_INCREMENT PRIMARY KEY,
      passwordHash VARCHAR(255) NOT NULL,
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) /* No foreign keys */
  `,
  admin_settings: `
    CREATE TABLE admin_settings (
      id INT AUTO_INCREMENT PRIMARY KEY,
      setting_key VARCHAR(255) NOT NULL UNIQUE,
      setting_value TEXT,
      updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) /* No foreign keys */
  `
};

// Check if table exists
const tableExists = async (conn, tableName) => {
  try {
    const result = await conn.query(
      `SELECT COUNT(*) as count FROM information_schema.tables 
       WHERE table_schema = ? AND table_name = ?`, // Use env var for schema
      [process.env.DB_NAME, tableName]
    );
    return result[0].count > 0;
  } catch (error) {
    console.error(`Error checking if table ${tableName} exists:`, error);
    return false;
  }
};

// Create all tables
const createTables = async (conn) => {
  try {
    // Create tables in correct order to satisfy foreign keys
    const orderedTableNames = ['matches', 'tickets', 'seats', 'admin', 'admin_settings'];

    for (const tableName of orderedTableNames) {
      const createSQL = tableDefinitions[tableName];
      if (!createSQL) {
        console.error(`Error: Table definition for ${tableName} not found.`);
        continue;
      }

      try {
        console.log(`Creating ${tableName} table...`);
        await conn.query(createSQL);
        console.log(`${tableName} table created successfully`);
      } catch (error) {
        // If table already exists, that's fine - just log it
        if (error.code === 'ER_TABLE_EXISTS_ERROR') {
          console.log(`${tableName} table already exists, skipping creation`);
        } else {
          // For any other error, rethrow it
          throw error;
        }
      }
    }
  } catch (error) {
    console.error('Error creating tables:', error);
    throw error;
  }
};

// Export the initialPool (Keep this export)
export { initialPool };

async function initDatabase() {
  try {
    // Check required directories and files
    ensureDirectories();
    ensureEnvFile(); // Keep the check but it won't stop execution

    console.log('Attempting to connect to database server using hardcoded credentials...'); // Updated log
    let conn;
    try {
      // Connect directly to the database using hardcoded credentials
      conn = await initialPool.getConnection();
      console.log('Connected to database server.');

      // Check if the database exists (Keep this logic)
      const dbCheck = await conn.query(
        `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?`,
        ['handball'] // Use hardcoded DB name here
      );

      if (dbCheck.length === 0) {
        console.log(`Database 'handball' not found. Creating...`); // Updated log
        // Create the database
        await conn.query(`CREATE DATABASE handball`); // Use hardcoded DB name here
        console.log(`Database 'handball' created.`); // Updated log
      } else {
        console.log(`Database 'handball' already exists.`); // Updated log
      }

      // Now that the database exists, ensure the connection is using it
      // (Initial pool is already configured with hardcoded DB name)

      console.log('Attempting to create tables (if they don\'t exist)...');
      // Create tables if they don't exist
      await createTables(conn);

      console.log('Database initialization completed successfully');
    } catch (error) {
      console.error('Error during database initialization:', error);
      throw error;
    } finally {
      if (conn) conn.release();
      // The pool created with hardcoded DB name is needed for the main server
      console.log('Database initialization complete, pool kept open for server using hardcoded credentials.'); // Updated log
    }
  } catch (error) {
    console.error('Startup checks failed:', error);
    throw error;
  }
}

export default initDatabase;