'use strict';

const nameMap = {
  'Grace Hopper': 'Kinshasa Capital Delivery Office',
  'Alan Turing': 'Eastern Corridor Operations Unit',
  'Ada Lovelace': 'National Public Investment Office',
  'Marie Curie': 'Provincial Services Coordination Office',
};

const provinceNameMap = {
  'Grace Hopper': 'Kinshasa',
  'Alan Turing': 'North Kivu',
  'Ada Lovelace': 'Haut-Katanga',
  'Marie Curie': 'Kasaï Central',
};

const provinceCodeMap = {
  'Grace Hopper': 'KIN',
  'Alan Turing': 'NKV',
  'Ada Lovelace': 'HKT',
  'Marie Curie': 'KSC',
};

const departmentNameMap = {
  'Grace Hopper': 'Procurement Directorate',
  'Alan Turing': 'Finance Directorate',
  'Ada Lovelace': 'Project Delivery Unit',
  'Marie Curie': 'Internal Audit Office',
};

const departmentCodeMap = {
  'Grace Hopper': 'PD',
  'Alan Turing': 'FD',
  'Ada Lovelace': 'PDU',
  'Marie Curie': 'IAO',
};

const programmeNameMap = {
  'Grace Hopper': 'Road Connectivity Programme',
  'Alan Turing': 'Water Access Programme',
  'Ada Lovelace': 'School Rehabilitation Programme',
  'Marie Curie': 'Primary Health Strengthening Programme',
};

const programmeCodeMap = {
  'Grace Hopper': 'RCP',
  'Alan Turing': 'WAP',
  'Ada Lovelace': 'SRP',
  'Marie Curie': 'PHSP',
};

const projectNameMap = {
  'Grace Hopper': 'Bukavu Water Network Upgrade',
  'Alan Turing': 'Goma School Rehabilitation Phase 1',
  'Ada Lovelace': 'Kananga Rural Roads Package A',
  'Marie Curie': 'Lubumbashi Health Facilities Modernization',
};

const projectCodeMap = {
  'Grace Hopper': 'BWU',
  'Alan Turing': 'GSR',
  'Ada Lovelace': 'KRR',
  'Marie Curie': 'LHF',
};

const vendorNameMap = {
  'Grace Hopper': 'Congo Build Partners',
  'Alan Turing': 'Great Lakes Engineering',
  'Ada Lovelace': 'Equator Supply Services',
  'Marie Curie': 'Civic Works Consortium',
};

const contractTitleMap = {
  'Grace Hopper': 'Road works package',
  'Alan Turing': 'Medical equipment supply',
  'Ada Lovelace': 'School rehabilitation lot',
  'Marie Curie': 'Water network expansion',
};

const workflowNameMap = {
  'Grace Hopper': 'Capital Project Approval',
  'Alan Turing': 'Vendor Compliance Review',
  'Ada Lovelace': 'Budget Reallocation Approval',
  'Marie Curie': 'Payment Authorization Workflow',
};

const stepNameMap = {
  'Grace Hopper': 'Initial Review',
  'Alan Turing': 'Department Approval',
  'Ada Lovelace': 'Finance Clearance',
  'Marie Curie': 'Executive Sign-off',
};

const recordTypeMap = {
  'Grace Hopper': 'projects',
  'Alan Turing': 'contracts',
  'Ada Lovelace': 'payment_requests',
  'Marie Curie': 'requisitions',
};

const referencePrefixMap = {
  'Grace Hopper': 'REF-OPS',
  'Alan Turing': 'REF-CTL',
  'Ada Lovelace': 'REF-DEL',
  'Marie Curie': 'REF-CMP',
};

const notificationTitleMap = {
  'Grace Hopper': 'Contract action required',
  'Alan Turing': 'Project update available',
  'Ada Lovelace': 'Approval decision pending',
  'Marie Curie': 'Compliance evidence due',
};

const narrativeMap = {
  'Grace Hopper': 'Priority follow-up is required on this record.',
  'Alan Turing': 'Operational review is in progress for this item.',
  'Ada Lovelace': 'Programme delivery details are under active review.',
  'Marie Curie': 'Supporting evidence and control checks are pending.',
};

const alertTitleMap = {
  'Grace Hopper': 'Contract monitoring alert',
  'Alan Turing': 'Project delivery alert',
  'Ada Lovelace': 'Payment control alert',
  'Marie Curie': 'Compliance evidence alert',
};

const escapeValue = (queryInterface, value) => queryInterface.sequelize.escape(value);

const updateExactValues = async (queryInterface, transaction, table, column, valueMap) => {
  const entries = Object.entries(valueMap);

  if (!entries.length) {
    return;
  }

  const whenClauses = entries
    .map(([from, to]) => `WHEN ${escapeValue(queryInterface, from)} THEN ${escapeValue(queryInterface, to)}`)
    .join(' ');

  const fromList = entries.map(([from]) => escapeValue(queryInterface, from)).join(', ');

  await queryInterface.sequelize.query(
    `UPDATE "${table}"
     SET "${column}" = CASE "${column}" ${whenClauses} ELSE "${column}" END
     WHERE "${column}" IN (${fromList})`,
    { transaction },
  );
};

const updateWithIdSuffix = async (queryInterface, transaction, table, column, valueMap, separator = ' ') => {
  const entries = Object.entries(valueMap);

  if (!entries.length) {
    return;
  }

  const whenClauses = entries
    .map(([from, to]) => {
      const base = escapeValue(queryInterface, to);
      const sep = escapeValue(queryInterface, separator);
      return `WHEN ${escapeValue(queryInterface, from)} THEN CONCAT(${base}, ${sep}, RIGHT(REPLACE(id::text, '-', ''), 4))`;
    })
    .join(' ');

  const fromList = entries.map(([from]) => escapeValue(queryInterface, from)).join(', ');

  await queryInterface.sequelize.query(
    `UPDATE "${table}"
     SET "${column}" = CASE "${column}" ${whenClauses} ELSE "${column}" END
     WHERE "${column}" IN (${fromList})`,
    { transaction },
  );
};

module.exports = {
  async up(queryInterface) {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      await updateWithIdSuffix(queryInterface, transaction, 'organizations', 'name', nameMap);
      await updateExactValues(queryInterface, transaction, 'provinces', 'name', provinceNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'provinces', 'code', provinceCodeMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'departments', 'name', departmentNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'departments', 'code', departmentCodeMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'approval_workflows', 'name', workflowNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'approval_steps', 'name', stepNameMap);
      await updateExactValues(queryInterface, transaction, 'approvals', 'record_type', recordTypeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'notifications', 'title', notificationTitleMap);
      await updateExactValues(queryInterface, transaction, 'notifications', 'message', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'notifications', 'record_type', recordTypeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'fiscal_years', 'name', {
        'Grace Hopper': 'FY 2024/25',
        'Alan Turing': 'FY 2025/26',
        'Ada Lovelace': 'FY 2026/27',
        'Marie Curie': 'FY 2027/28',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'funding_sources', 'name', {
        'Grace Hopper': 'Treasury Capital Grant',
        'Alan Turing': 'Provincial Development Transfer',
        'Ada Lovelace': 'Infrastructure Recovery Fund',
        'Marie Curie': 'Service Delivery Support Fund',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'funding_sources', 'reference_code', referencePrefixMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'budget_programs', 'name', programmeNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'budget_programs', 'code', programmeCodeMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'budget_lines', 'name', {
        'Grace Hopper': 'Civil Works Envelope',
        'Alan Turing': 'Equipment and Materials',
        'Ada Lovelace': 'Professional Services',
        'Marie Curie': 'Monitoring and Evaluation',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'budget_lines', 'code', {
        'Grace Hopper': 'BL-CW',
        'Alan Turing': 'BL-EQ',
        'Ada Lovelace': 'BL-PS',
        'Marie Curie': 'BL-ME',
      }, '-');
      await updateExactValues(queryInterface, transaction, 'budget_lines', 'description', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'procurement_plans', 'name', {
        'Grace Hopper': 'Annual Infrastructure Sourcing Plan',
        'Alan Turing': 'Education Recovery Procurement Plan',
        'Ada Lovelace': 'Provincial Delivery Procurement Plan',
        'Marie Curie': 'Health Services Procurement Plan',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'requisitions', 'requisition_number', referencePrefixMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'requisitions', 'title', {
        'Grace Hopper': 'Road maintenance works request',
        'Alan Turing': 'Laboratory equipment request',
        'Ada Lovelace': 'School repair materials request',
        'Marie Curie': 'Water network extension request',
      });
      await updateExactValues(queryInterface, transaction, 'requisitions', 'scope_of_work', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'tenders', 'tender_number', referencePrefixMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'tenders', 'title', {
        'Grace Hopper': 'Open tender for civil works',
        'Alan Turing': 'Restricted tender for equipment supply',
        'Ada Lovelace': 'Framework tender for school rehabilitation',
        'Marie Curie': 'Competitive tender for water services expansion',
      });
      await updateExactValues(queryInterface, transaction, 'tenders', 'eligibility_criteria', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'tenders', 'submission_instructions', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'vendors', 'name', vendorNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'vendors', 'trade_name', vendorNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'vendors', 'contact_name', {
        'Grace Hopper': 'Patrick Ilunga',
        'Alan Turing': 'Aline Mbuyi',
        'Ada Lovelace': 'David Kasongo',
        'Marie Curie': 'Ruth Mukendi',
      });
      await updateExactValues(queryInterface, transaction, 'vendors', 'address', {
        'Grace Hopper': 'Kinshasa headquarters office',
        'Alan Turing': 'Goma regional office',
        'Ada Lovelace': 'Kananga delivery office',
        'Marie Curie': 'Lubumbashi programme office',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'vendor_compliance_documents', 'reference_number', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'vendor_compliance_documents', 'review_comment', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'bids', 'bid_reference', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'bids', 'notes', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'bid_evaluations', 'justification', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'awards', 'award_number', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'awards', 'award_memo', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'programs', 'name', programmeNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'programs', 'code', programmeCodeMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'projects', 'name', projectNameMap);
      await updateWithIdSuffix(queryInterface, transaction, 'projects', 'project_code', projectCodeMap, '-');
      await updateExactValues(queryInterface, transaction, 'projects', 'implementing_entity', {
        'Grace Hopper': 'Directorate of Public Works',
        'Alan Turing': 'Directorate of Basic Education',
        'Ada Lovelace': 'Provincial Infrastructure Unit',
        'Marie Curie': 'Directorate of Health Services',
      });
      await updateExactValues(queryInterface, transaction, 'projects', 'objectives', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'project_milestones', 'name', {
        'Grace Hopper': 'Mobilization complete',
        'Alan Turing': 'Site preparation complete',
        'Ada Lovelace': 'Materials delivered',
        'Marie Curie': 'Final inspection complete',
      });
      await updateExactValues(queryInterface, transaction, 'project_milestones', 'description', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'risks', 'title', {
        'Grace Hopper': 'Contract schedule slippage risk',
        'Alan Turing': 'Funding delay risk',
        'Ada Lovelace': 'Approval bottleneck risk',
        'Marie Curie': 'Evidence gap risk',
      });
      await updateExactValues(queryInterface, transaction, 'risks', 'description', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'risks', 'mitigation_plan', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'issues', 'title', {
        'Grace Hopper': 'Procurement handoff issue',
        'Alan Turing': 'Payment processing issue',
        'Ada Lovelace': 'Delivery coordination issue',
        'Marie Curie': 'Compliance follow-up issue',
      });
      await updateExactValues(queryInterface, transaction, 'issues', 'description', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'field_verifications', 'findings', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'field_verifications', 'actions_required', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'contracts', 'title', contractTitleMap);
      await updateWithIdSuffix(queryInterface, transaction, 'contracts', 'contract_number', {
        'Grace Hopper': 'CT-RD',
        'Alan Turing': 'CT-ME',
        'Ada Lovelace': 'CT-SR',
        'Marie Curie': 'CT-WN',
      }, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'grants', 'funding_window_name', {
        'Grace Hopper': 'Community Infrastructure Grant Window',
        'Alan Turing': 'Education Access Grant Window',
        'Ada Lovelace': 'Provincial Services Grant Window',
        'Marie Curie': 'Health Systems Grant Window',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'grants', 'call_reference', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'grants', 'eligibility_rules', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'grants', 'notes', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'beneficiaries', 'name', {
        'Grace Hopper': 'Bukavu Community Water Board',
        'Alan Turing': 'Goma Education Improvement Council',
        'Ada Lovelace': 'Kananga Roads Maintenance Group',
        'Marie Curie': 'Lubumbashi Health Access Network',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'beneficiaries', 'registration_number', referencePrefixMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'grant_applications', 'application_reference', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'grant_applications', 'proposal_summary', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'grant_evaluations', 'comments', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'grant_tranches', 'conditions', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'expense_categories', 'name', {
        'Grace Hopper': 'Civil works expense',
        'Alan Turing': 'Equipment purchase expense',
        'Ada Lovelace': 'Consultancy services expense',
        'Marie Curie': 'Monitoring activity expense',
      });
      await updateWithIdSuffix(queryInterface, transaction, 'expense_categories', 'code', {
        'Grace Hopper': 'EXP-CW',
        'Alan Turing': 'EXP-EQ',
        'Ada Lovelace': 'EXP-CS',
        'Marie Curie': 'EXP-ME',
      }, '-');
      await updateExactValues(queryInterface, transaction, 'expense_categories', 'description', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'invoices', 'invoice_number', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'invoices', 'notes', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'payment_requests', 'request_number', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'payment_requests', 'justification', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'payment_batches', 'batch_number', referencePrefixMap, '-');
      await updateWithIdSuffix(queryInterface, transaction, 'payments', 'payment_reference', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'payments', 'failure_reason', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'obligations', 'obligation_number', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'obligations', 'notes', narrativeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'ledger_entries', 'entry_reference', referencePrefixMap, '-');
      await updateExactValues(queryInterface, transaction, 'ledger_entries', 'description', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'ledger_entries', 'record_type', recordTypeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'documents', 'title', {
        'Grace Hopper': 'Signed contract dossier',
        'Alan Turing': 'Project monitoring report',
        'Ada Lovelace': 'Approval support memo',
        'Marie Curie': 'Compliance evidence file',
      });
      await updateExactValues(queryInterface, transaction, 'documents', 'description', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'documents', 'record_type', recordTypeMap);
      await updateWithIdSuffix(queryInterface, transaction, 'compliance_alerts', 'title', alertTitleMap);
      await updateExactValues(queryInterface, transaction, 'compliance_alerts', 'details', narrativeMap);
      await updateExactValues(queryInterface, transaction, 'compliance_alerts', 'record_type', recordTypeMap);

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      console.error('Error cleaning sample business data:', error);
      throw error;
    }
  },

  async down() {},
};
