'use strict';

require('dotenv').config();

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

async function columnExists(connection, tableName, columnName) {
  const [rows] = await connection.execute(
    `
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = ?
        AND TABLE_NAME = ?
        AND COLUMN_NAME = ?
      LIMIT 1
    `,
    [process.env.DB_NAME, tableName, columnName]
  );

  return rows.length > 0;
}

async function indexExists(connection, tableName, indexName) {
  const [rows] = await connection.execute(
    `
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = ?
        AND TABLE_NAME = ?
        AND INDEX_NAME = ?
      LIMIT 1
    `,
    [process.env.DB_NAME, tableName, indexName]
  );

  return rows.length > 0;
}

async function foreignKeyExists(connection, constraintName) {
  const [rows] = await connection.execute(
    `
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE CONSTRAINT_SCHEMA = ?
        AND CONSTRAINT_NAME = ?
        AND CONSTRAINT_TYPE = 'FOREIGN KEY'
      LIMIT 1
    `,
    [process.env.DB_NAME, constraintName]
  );

  return rows.length > 0;
}

async function run() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || '127.0.0.1',
    port: Number(process.env.DB_PORT || 3306),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME
  });

  try {
    if (!(await columnExists(connection, 'custodies', 'custody_name'))) {
      await connection.query(`
        ALTER TABLE custodies
        ADD COLUMN custody_name VARCHAR(180) NULL
        AFTER custody_number
      `);
    }

    if (!(await columnExists(connection, 'custodies', 'last_edited_by_user_id'))) {
      await connection.query(`
        ALTER TABLE custodies
        ADD COLUMN last_edited_by_user_id BIGINT UNSIGNED NULL
        AFTER created_by_category
      `);
    }

    if (!(await columnExists(connection, 'custodies', 'last_edited_by_name'))) {
      await connection.query(`
        ALTER TABLE custodies
        ADD COLUMN last_edited_by_name VARCHAR(80) NULL
        AFTER last_edited_by_user_id
      `);
    }

    if (!(await columnExists(connection, 'custodies', 'last_edited_at'))) {
      await connection.query(`
        ALTER TABLE custodies
        ADD COLUMN last_edited_at DATETIME NULL
        AFTER last_edited_by_name
      `);
    }

    await connection.query(`
      UPDATE custodies
      SET custody_name = CONCAT(
        'عهدة ',
        DATE_FORMAT(custody_date, '%Y-%m-%d')
      )
      WHERE custody_name IS NULL
         OR TRIM(custody_name) = ''
    `);

    await connection.query(`
      ALTER TABLE custodies
      MODIFY custody_name VARCHAR(180) NOT NULL
    `);

    if (!(await indexExists(connection, 'custodies', 'idx_custodies_name'))) {
      await connection.query(`
        ALTER TABLE custodies
        ADD KEY idx_custodies_name (custody_name)
      `);
    }

    if (
      !(await foreignKeyExists(
        connection,
        'fk_custodies_last_edited_user'
      ))
    ) {
      await connection.query(`
        ALTER TABLE custodies
        ADD CONSTRAINT fk_custodies_last_edited_user
        FOREIGN KEY (last_edited_by_user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
      `);
    }

    console.log('تم تحديث قاعدة البيانات المحلية بنجاح.');
  } finally {
    await connection.end();
  }
}

run().catch((error) => {
  console.error('فشل تحديث قاعدة البيانات:', error.message);
  process.exit(1);
});