import assert from 'node:assert/strict'
import { readFileSync } from 'node:fs'
import { dirname, resolve } from 'node:path'
import { fileURLToPath } from 'node:url'

const currentDir = dirname(fileURLToPath(import.meta.url))
const warehouseRoot = resolve(currentDir, '..')
const sqlPath = resolve(warehouseRoot, 'src/main/resources/db/warehouse-public-master-data.sql')
const acceptedPath = resolve(warehouseRoot, 'src/main/resources/db/master-data/fba-public-addresses.accepted.csv')
const rejectedPath = resolve(warehouseRoot, 'src/main/resources/db/master-data/fba-public-addresses.rejected.csv')
const fixedWarehousePath = resolve(warehouseRoot, 'src/main/resources/db/master-data/platform-fixed-warehouses.csv')
const unifiedSqlPath = resolve(warehouseRoot, '..', 'deploy/sql/头程数据库统一增量-MySQL8.sql')

function parseCsv(text) {
  const rows = []
  let field = ''
  let row = []
  let quoted = false
  for (let index = 0; index < text.length; index += 1) {
    const char = text[index]
    if (quoted) {
      if (char === '"' && text[index + 1] === '"') {
        field += '"'
        index += 1
      } else if (char === '"') {
        quoted = false
      } else {
        field += char
      }
    } else if (char === '"') {
      quoted = true
    } else if (char === ',') {
      row.push(field)
      field = ''
    } else if (char === '\n') {
      row.push(field.replace(/\r$/, ''))
      rows.push(row)
      row = []
      field = ''
    } else {
      field += char
    }
  }
  if (field.length > 0 || row.length > 0) {
    row.push(field)
    rows.push(row)
  }
  const [headers, ...data] = rows.filter((item) => item.some((value) => value !== ''))
  return data.map((values) => Object.fromEntries(headers.map((header, index) => [header, values[index] ?? ''])))
}

const sql = readFileSync(sqlPath, 'utf8')
const unifiedSql = readFileSync(unifiedSqlPath, 'utf8')
const accepted = parseCsv(readFileSync(acceptedPath, 'utf8'))
const rejected = parseCsv(readFileSync(rejectedPath, 'utf8'))
const fixedWarehouses = parseCsv(readFileSync(fixedWarehousePath, 'utf8'))

assert.equal(accepted.length, 314)
assert.equal(rejected.length, 0)
assert.equal(fixedWarehouses.length, 4)

const codes = new Set()
const warehouseIds = new Set()
const addressIds = new Set()
for (const row of accepted) {
  assert.ok(row.warehouse_code.trim())
  assert.equal(row.warehouse_code, row.warehouse_code.trim().toUpperCase())
  if (row.source_internal_id) assert.match(row.source_internal_id, /^[A-Z0-9]{1,16}$/)
  assert.equal(row.country_code, 'US')
  assert.ok(row.timezone === '' || /^(America|Pacific)\//.test(row.timezone), `invalid region zone ${row.warehouse_code}`)
  assert.ok(!codes.has(row.warehouse_code), `duplicate code ${row.warehouse_code}`)
  assert.ok(!warehouseIds.has(row.warehouse_id_seed), `duplicate warehouse id ${row.warehouse_id_seed}`)
  assert.ok(!addressIds.has(row.address_id_seed), `duplicate address id ${row.address_id_seed}`)
  codes.add(row.warehouse_code)
  warehouseIds.add(row.warehouse_id_seed)
  addressIds.add(row.address_id_seed)
  const internalCode = row.source_internal_id ? `'${row.source_internal_id}'` : 'NULL'
  const tuplePrefix = `(${row.warehouse_id_seed},${row.address_id_seed},'${row.warehouse_code}','${row.warehouse_name}',${internalCode},`
  assert.ok(sql.includes(tuplePrefix), `standalone SQL missing ${row.warehouse_code}`)
  const currentSeed = `(${row.warehouse_id_seed},0,'${row.warehouse_code}',${internalCode},'${row.warehouse_name}','FBA','US','${row.timezone}'`
  assert.ok(unifiedSql.includes(currentSeed), `unified SQL missing current warehouse/timezone ${row.warehouse_code}`)
}

assert.ok(accepted.some((row) => row.warehouse_code === 'FEDEX' && row.city === ''))
assert.ok(accepted.some((row) => row.warehouse_code === '纽约仓一件代发' && row.source_internal_id === 'NJDF'))
assert.equal(accepted.filter((row) => row.source_internal_id === '83').length, 2)

assert.deepEqual(fixedWarehouses.map((row) => row.warehouse_code), [
  'CN-YIWU-01',
  'CN-DONGGUAN-01',
  'US-LAX-01',
  'US-NYC-01',
])
assert.deepEqual(fixedWarehouses.map((row) => row.timezone), [
  'Asia/Shanghai',
  'Asia/Shanghai',
  'America/Los_Angeles',
  'America/New_York',
])
assert.deepEqual(
  fixedWarehouses.filter((row) => row.warehouse_type === 'OVERSEAS').map((row) => ({
    warehouse_code: row.warehouse_code,
    state_province: row.state_province,
    city: row.city,
    postal_code: row.postal_code,
    address_line1: row.address_line1,
  })),
  [
    { warehouse_code: 'US-LAX-01', state_province: 'California', city: 'Rancho Cucamonga', postal_code: '91730', address_line1: '10767 7th Street' },
    { warehouse_code: 'US-NYC-01', state_province: 'New Jersey', city: 'Edison', postal_code: '08817', address_line1: 'Door 13-14, 75 Carter Dr' },
  ],
)

assert.match(sql, /CREATE TEMPORARY TABLE tmp_wh_fixed_platform_warehouse/)
assert.match(sql, /'CN-YIWU-01','义乌仓','DOMESTIC','CN','Asia\/Shanghai'/)
assert.match(sql, /'CN-DONGGUAN-01','东莞仓','DOMESTIC','CN','Asia\/Shanghai'/)
assert.match(sql, /'US-LAX-01','洛杉矶仓','OVERSEAS','US','America\/Los_Angeles'/)
assert.match(sql, /'US-NYC-01','纽约仓','OVERSEAS','US','America\/New_York'/)
assert.match(sql, /固定平台仓地址种子ID与既有数据冲突/)
assert.match(sql, /固定平台仓地址父子解析失败/)
assert.match(sql, /FROM tmp_wh_fixed_platform_warehouse source[\s\S]+ON DUPLICATE KEY UPDATE\s+tenant_id = VALUES\(tenant_id\);/)

assert.match(sql, /CREATE TEMPORARY TABLE tmp_wh_fba_public_master_data/)
assert.match(sql, /JOIN wh_warehouse warehouse\s+ON warehouse\.tenant_id = 0\s+AND warehouse\.warehouse_code = source\.warehouse_code/s)
assert.match(sql, /ON DUPLICATE KEY UPDATE\s+internal_code = VALUES\(internal_code\),\s+warehouse_type = VALUES\(warehouse_type\),\s+country_code = VALUES\(country_code\),\s+timezone = VALUES\(timezone\),\s+updated_at = CURRENT_TIMESTAMP\(3\);/s)
assert.match(sql, /internal_code VARCHAR\(16\) NULL/)
assert.match(sql, /source\.internal_code/)
assert.doesNotMatch(sql, /status\s*=\s*VALUES\(status\)/)
assert.doesNotMatch(sql, /contact_name\s*=\s*VALUES\(contact_name\)/)
assert.doesNotMatch(sql, /contact_phone\s*=\s*VALUES\(contact_phone\)/)
assert.doesNotMatch(sql, /contact_email\s*=\s*VALUES\(contact_email\)/)
assert.doesNotMatch(sql, /remark\s*=\s*VALUES\(remark\)/)
assert.match(sql, /四个固定平台作业仓必须启用收货能力/)
assert.match(sql, /INSERT INTO wh_service_capability[\s\S]+?'RECEIVING'/i)
assert.match(sql, /193000000000400003,0,193000000000200003,'RECEIVING','海外收货',1,'16:00:00',[\s\S]*?'洛杉矶仓海外接柜入仓收货能力'/)
assert.match(sql, /193000000000400004,0,193000000000200004,'RECEIVING','海外收货',1,'16:00:00',[\s\S]*?'纽约仓海外接柜入仓收货能力'/)
assert.match(sql, /warehouse_id IN \(193000000000200001,193000000000200002,193000000000200003,193000000000200004\)[\s\S]*?<> 4 THEN[\s\S]*?固定平台作业仓收货能力写入校验失败/)
const fbaBlock = sql.slice(sql.indexOf('CREATE TEMPORARY TABLE tmp_wh_fba_public_master_data'))
assert.doesNotMatch(fbaBlock, /INSERT INTO wh_service_capability/i)
assert.match(sql, /FBA公共主数据地址种子ID与既有数据冲突/)
assert.match(sql, /FBA公共地址不得创建平台作业能力/)