const sql = require('C:/Users/13623/AppData/Roaming/npm/node_modules/@eamonboyle/mssql-mcp/node_modules/mssql');

const config = {
  server: process.env.DB_SERVER,
  database: process.env.DB_DATABASE,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: false,
    trustServerCertificate: String(process.env.DB_TRUST_CERT || 'true').toLowerCase() === 'true'
  }
};

const headerTables = ['in_stockin', 'in_stockin_finish', 'out_stockout', 'out_stockout_finish'];
const detailTables = ['in_stockin_detail', 'in_stockin_detail_finish', 'out_stockout_detail', 'out_stockout_detail_finish'];

const headerColumns = [
  ['document_n', 'NVARCHAR(80)', '单据编号'],
  ['document_dat', 'DATE', '单据日期'],
  ['project_nam', 'NVARCHAR(255)', '项目号/项目名称'],
  ['counterpart', 'NVARCHAR(255)', '供应商/客户'],
  ['receiving_or', 'NVARCHAR(255)', '收料组织'],
  ['purchasing_or', 'NVARCHAR(255)', '采购组织'],
  ['receiving_dep', 'NVARCHAR(255)', '收料部门'],
  ['purchasing_dep', 'NVARCHAR(255)', '采购部门'],
  ['handle', 'NVARCHAR(255)', '仓管员/发货人/发料人'],
  ['creato', 'NVARCHAR(255)', '采购员/制单人'],
  ['logistics_', 'NVARCHAR(255)', '物流公司'],
  ['logistics_n', 'NVARCHAR(255)', '物流单号'],
  ['warehous', 'NVARCHAR(255)', '库房'],
  ['print_coun', 'INT', '打印次数'],
  ['tota', 'DECIMAL(18,2)', '合计'],
  ['reviewe', 'NVARCHAR(255)', '审核人/审批人'],
  ['create_dat', 'DATETIME2', '创建日期'],
  ['review_dat', 'DATETIME2', '审核日期'],
  ['receive', 'NVARCHAR(255)', '收货人'],
  ['receiver_a', 'NVARCHAR(255)', '收货地址'],
  ['receiver_phon', 'NVARCHAR(50)', '收货人电话'],
  ['material_re', 'NVARCHAR(255)', '领料人']
];

const detailColumns = [
  ['line_n', 'INT', '序号'],
  ['item_cod', 'NVARCHAR(255)', '物料编码/产品代码'],
  ['item_nam', 'NVARCHAR(255)', '物料名称/产品名称'],
  ['spec_mode', 'NVARCHAR(255)', '规格型号'],
  ['uni', 'NVARCHAR(100)', '单位'],
  ['actual_qt', 'DECIMAL(18,2)', '实发数量'],
  ['warehous', 'NVARCHAR(255)', '仓库'],
  ['locatio', 'NVARCHAR(255)', '仓位'],
  ['inventory_', 'NVARCHAR(255)', '库存状态'],
  ['source_doc_n', 'NVARCHAR(255)', '源单单号'],
  ['remar', 'NVARCHAR(255)', '备注']
];

function commentSql(tableName, columnName, comment) {
  const safeComment = comment.replace(/'/g, "''");
  return `
IF COL_LENGTH('dbo.${tableName}', '${columnName}') IS NOT NULL
BEGIN
    IF EXISTS (
        SELECT 1
        FROM sys.extended_properties ep
        INNER JOIN sys.columns c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
        INNER JOIN sys.tables t ON t.object_id = c.object_id
        WHERE ep.name = 'MS_Description'
          AND t.name = '${tableName}'
          AND c.name = '${columnName}'
    )
        EXEC sys.sp_updateextendedproperty
            @name = N'MS_Description',
            @value = N'${safeComment}',
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE',  @level1name = N'${tableName}',
            @level2type = N'COLUMN', @level2name = N'${columnName}';
    ELSE
        EXEC sys.sp_addextendedproperty
            @name = N'MS_Description',
            @value = N'${safeComment}',
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE',  @level1name = N'${tableName}',
            @level2type = N'COLUMN', @level2name = N'${columnName}';
END`;
}

function addColumnSql(tableName, columnName, columnType, comment) {
  return `
IF COL_LENGTH('dbo.${tableName}', '${columnName}') IS NULL
    ALTER TABLE dbo.${tableName} ADD ${columnName} ${columnType} NULL;
${commentSql(tableName, columnName, comment)}`;
}

async function main() {
  if (!config.server || !config.database || !config.user || !config.password) {
    throw new Error('Missing DB_SERVER / DB_DATABASE / DB_USER / DB_PASSWORD env vars');
  }

  const statements = [];
  for (const table of headerTables) {
    for (const [name, type, comment] of headerColumns) {
      statements.push(addColumnSql(table, name, type, comment));
    }
  }
  for (const table of detailTables) {
    for (const [name, type, comment] of detailColumns) {
      statements.push(addColumnSql(table, name, type, comment));
    }
  }

  const pool = await sql.connect(config);
  try {
    await pool.request().batch(statements.join('\n'));
    console.log('Bill schema updated successfully.');
  } finally {
    await pool.close();
  }
}

main().catch((error) => {
  console.error(error);
  process.exit(1);
});