cloth-design/end-b/src/database.js

const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
const { db } = require('./config');

// 全局连接池
let pool;

// 创建连接池
function createPool() {
  if (!pool) {
    pool = new Pool(db);
    
    // 错误处理
    pool.on('error', (err) => {
      console.error('Unexpected database error', err);
    });
  }
  return pool;
}

// 查询函数
exports.query = async (text, params) => {
  const pool = createPool();
  try {
    return await pool.query(text, params);
  } catch (error) {
    console.error('Database query failed:', error);
    throw error;
  }
};

// 事务执行函数
exports.executeInTransaction = async (callback) => {
  const pool = createPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await callback(client);
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 测试数据库连接
exports.testDbConnection = async () => {
  try {
    const result = await exports.query('SELECT NOW()');
    return { time: result.rows[0].now };
  } catch (error) {
    throw new Error(`Database connection failed: ${error.message}`);
  }
};

// 完整的数据库初始化
exports.initializeDatabase = async () => {
  try {
    // 第一步：检查数据库是否存在
    try {
      // 尝试连接到目标数据库
      await exports.testDbConnection();
      console.log('✅ 数据库连接成功');
    } catch (dbError) {
      // 如果数据库不存在，创建它
      if (dbError.code === '3D000') {
        console.log('🆕 数据库不存在，尝试创建...');
        
        // 连接到默认的postgres数据库
        const adminPool = new Pool({
          ...db,
          database: 'postgres'
        });
        
        // 创建新数据库
        await adminPool.query(`CREATE DATABASE ${db.database}`);
        console.log(`🎉 数据库 ${db.database} 创建成功`);
        
        // 关闭管理员连接
        await adminPool.end();
      } else {
        throw dbError;
      }
    }
    
    // 第二步：检查表是否存在
    const tableExists = await exports.query(
      `SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'design_usage'
      )`
    );
    
    // 如果表不存在则创建
    if (!tableExists.rows[0].exists) {
      console.log('🆕 创建 design_usage 表...');
      
      // 创建表结构
      await exports.query(`
        CREATE TABLE design_usage (
          id SERIAL PRIMARY KEY,
          part_id INTEGER NOT NULL CHECK (part_id BETWEEN 1 AND 4),
          color_code CHAR(2) NOT NULL CHECK (color_code ~ '^[0-9A-F]{2}$'),
          usage_count INTEGER NOT NULL DEFAULT 0,
          UNIQUE (part_id, color_code)
        )
      `);
      
      console.log('🎉 design_usage 表创建成功');
      
      // 插入初始数据
      console.log('📥 插入初始数据...');
      await exports.query(`
        INSERT INTO design_usage (part_id, color_code, usage_count) VALUES
        (1, '00', 1850), (1, '01', 1620), (1, '02', 1540), (1, '03', 1420),
        (2, '04', 1360), (2, '05', 1280), (2, '06', 1200), (2, '07', 1150),
        (3, '08', 1100), (3, '09', 1050), (3, '0A', 1000), (3, '0B', 950),
        (4, '0C', 900), (4, '0D', 850), (4, '0E', 800), (4, '0F', 750)
      `);
      console.log('🎉 初始数据插入完成');
    }
    
    // 第三步：测试数据库连接
    const dbStatus = await exports.testDbConnection();
    console.log('📡 数据库连接成功:', dbStatus);
    
    return true;
  } catch (error) {
    console.error('🔥 数据库初始化失败:');
    console.error(error);
    throw error;
  }
};

// 获取所有设计使用情况
exports.getAllDesignUsage = async () => {
  try {
    const result = await exports.query(`
      SELECT part_id, color_code, usage_count
      FROM design_usage
      ORDER BY part_id, usage_count DESC
    `);
    
    return result.rows.map(row => ({
      partId: row.part_id,
      colorCode: row.color_code,
      usageCount: row.usage_count
    }));
  } catch (error) {
    throw new Error(`获取设计使用数据失败: ${error.message}`);
  }
};

// 记录设计使用情况
exports.recordDesignUsage = async (design) => {
  await exports.executeInTransaction(async (client) => {
    const colors = [
      design.part1, design.part2, 
      design.part3, design.part4
    ];
    
    for (let partId = 1; partId <= 4; partId++) {
      const colorCode = colors[partId - 1];
      await client.query(
        `INSERT INTO design_usage (part_id, color_code, usage_count)
         VALUES ($1, $2, 1)
         ON CONFLICT (part_id, color_code)
         DO UPDATE SET usage_count = design_usage.usage_count + 1`,
        [partId, colorCode]
      );
    }
  });
};