import { Pool } from 'pg';
import { config } from '../config';
import fs from 'fs';
import path from 'path';

const pool = new Pool({
  user: config.db.user,
  host: config.db.host,
  database: config.db.database,
  password: config.db.password,
  port: config.db.port,
});

// 查询函数
export const query = async (text: string, params?: any[]) => {
  try {
    return await pool.query(text, params);
  } catch (error) {
    const err = error as Error;
    throw new Error(`Database query failed: ${err.message}`);
  }
};

// 初始化数据库
export const initializeDatabase = async () => {
  try {
    // 检查 design_usage 表是否存在
    const tableExists = await 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 表...');
      
      // 从文件读取 SQL 脚本
      const sqlPath = path.join(__dirname, 'init.sql');
      const sqlScript = fs.readFileSync(sqlPath, 'utf8');
      
      // 执行初始化脚本
      await query(sqlScript);
      console.log('🎉 Design_usage 表创建并初始化成功');
    }
    
    // 测试数据库连接
    const dbStatus = await testDbConnection();
    console.log('📡 数据库连接成功:', dbStatus);
    
    return true;
  } catch (error) {
    const err = error as Error;
    console.error('🔥 数据库初始化失败:', err.message);
    throw err;
  }
};

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

// 获取所有设计使用情况
export const getAllDesignUsage = async () => {
  try {
    const result = await 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) {
    const err = error as Error;
    throw new Error(`获取设计使用数据失败: ${err.message}`);
  }
};

// 记录设计使用情况
export const recordDesignUsage = async (design: {
  part1: string;
  part2: string;
  part3: string;
  part4: string;
}) => {
  try {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      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]
        );
      }
      
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  } catch (error) {
    const err = error as Error;
    throw new Error(`记录设计使用失败: ${err.message}`);
  }
};