Web_BLS_RCUAction_Server/SQL_Script/db_manager.js

import fs from 'fs';
import path from 'path';
import pg from 'pg';
import { fileURLToPath } from 'url';

const { Client } = pg;

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

const parseNumber = (value, defaultValue) => {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : defaultValue;
};

const dbConfig = {
  host: process.env.DB_HOST || process.env.POSTGRES_HOST || 'localhost',
  port: parseNumber(process.env.DB_PORT || process.env.POSTGRES_PORT, 5432),
  user: process.env.DB_USER || process.env.POSTGRES_USER || 'postgres',
  password: process.env.DB_PASSWORD || process.env.POSTGRES_PASSWORD || '',
  database: process.env.DB_DATABASE || process.env.POSTGRES_DATABASE || 'bls_rcu_action',
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : undefined
};

const withClient = async (runner) => {
  const client = new Client(dbConfig);
  await client.connect();
  try {
    await runner(client);
  } finally {
    await client.end();
  }
};

const executeSqlFile = async (client, fileName) => {
  const filePath = path.join(scriptDir, fileName);
  const sql = fs.readFileSync(filePath, 'utf8');
  await client.query(sql);
};

export const ensureDatabase = async () => {
  const adminClient = new Client({
    ...dbConfig,
    database: process.env.DB_ADMIN_DATABASE || 'postgres'
  });
  await adminClient.connect();
  try {
    const targetDb = dbConfig.database;
    const check = await adminClient.query('SELECT 1 FROM pg_database WHERE datname = $1', [targetDb]);
    if (check.rowCount === 0) {
      await adminClient.query(`CREATE DATABASE "${targetDb}"`);
      console.log(`[SQL_Script] created database: ${targetDb}`);
    } else {
      console.log(`[SQL_Script] database exists: ${targetDb}`);
    }
  } finally {
    await adminClient.end();
  }
};

const toPartitionSuffix = (date) => {
  const yyyy = date.getFullYear();
  const mm = String(date.getMonth() + 1).padStart(2, '0');
  const dd = String(date.getDate()).padStart(2, '0');
  return `${yyyy}${mm}${dd}`;
};

const getDayRange = (date) => {
  const start = new Date(date);
  start.setHours(0, 0, 0, 0);
  const end = new Date(start);
  end.setDate(end.getDate() + 1);
  return { startMs: start.getTime(), endMs: end.getTime() };
};

export const ensureRcuPartitions = async (daysAhead = 30) => {
  const tpl = fs.readFileSync(path.join(scriptDir, 'partition_rcu_action.sql'), 'utf8');
  await withClient(async (client) => {
    const now = new Date();
    for (let i = 0; i < daysAhead; i++) {
      const d = new Date(now);
      d.setDate(now.getDate() + i);
      const suffix = toPartitionSuffix(d);
      const partitionName = `rcu_action.rcu_action_events_${suffix}`;
      const { startMs, endMs } = getDayRange(d);

      const sql = tpl
        .replaceAll('{partition_name}', partitionName)
        .replaceAll('{start_ms}', String(startMs))
        .replaceAll('{end_ms}', String(endMs));

      await client.query(sql);
    }
  });
  console.log(`[SQL_Script] ensured rcu_action partitions for ${daysAhead} days`);
};

export const ensureRoomStatusPartition = async (hotelId) => {
  if (!Number.isFinite(Number(hotelId))) {
    throw new Error('hotelId is required and must be a number');
  }
  const tpl = fs.readFileSync(path.join(scriptDir, 'partition_room_status.sql'), 'utf8');
  const sql = tpl.replaceAll('{hotel_id}', String(hotelId));

  await withClient(async (client) => {
    await client.query(sql);
  });

  console.log(`[SQL_Script] ensured room_status partition for hotel_id=${hotelId}`);
};

export const initAll = async () => {
  await ensureDatabase();
  await withClient(async (client) => {
    await executeSqlFile(client, 'init_rcu_action.sql');
    await executeSqlFile(client, 'init_room_status.sql');
  });
  console.log('[SQL_Script] initialized schemas and tables');
};

const run = async () => {
  const cmd = process.argv[2];

  if (!cmd) {
    throw new Error('missing command: init-all | init-rcu | init-room-status | partition-rcu [days] | partition-room-status <hotelId>');
  }

  switch (cmd) {
    case 'init-all':
      await initAll();
      break;
    case 'init-rcu':
      await withClient((client) => executeSqlFile(client, 'init_rcu_action.sql'));
      console.log('[SQL_Script] initialized rcu_action schema/table');
      break;
    case 'init-room-status':
      await withClient((client) => executeSqlFile(client, 'init_room_status.sql'));
      console.log('[SQL_Script] initialized room_status schema/table');
      break;
    case 'partition-rcu': {
      const days = parseNumber(process.argv[3], 30);
      await ensureRcuPartitions(days);
      break;
    }
    case 'partition-room-status': {
      const hotelId = process.argv[3];
      await ensureRoomStatusPartition(hotelId);
      break;
    }
    default:
      throw new Error(`unsupported command: ${cmd}`);
  }
};

run().catch((err) => {
  console.error('[SQL_Script] failed:', err?.message || err);
  process.exit(1);
});