campaign-command-center/scripts/import-xlsx.js

const XLSX = require('xlsx');
const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const XLSX_PATH = path.join(__dirname, '..', 'AgentMinder Launch - Campaign Tracker.xlsx');
const DB_DIR = path.join(__dirname, '..', 'data');
const DB_PATH = path.join(DB_DIR, 'campaign.db');

if (!fs.existsSync(DB_DIR)) fs.mkdirSync(DB_DIR, { recursive: true });

// Delete old database to start fresh
if (fs.existsSync(DB_PATH)) {
  fs.unlinkSync(DB_PATH);
  console.log('Deleted existing database');
}

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');

// Create schema
db.exec(`
  CREATE TABLE IF NOT EXISTS accounts (
    Account_Name TEXT PRIMARY KEY,
    District_Name TEXT NOT NULL DEFAULT '',
    Tier TEXT NOT NULL DEFAULT '',
    Priority TEXT NOT NULL DEFAULT '',
    AgentMinder_Status TEXT NOT NULL DEFAULT 'Not Touched',
    Current_ARR_USD REAL,
    Touch_Count INTEGER DEFAULT 0,
    Date_First_Touched TEXT,
    Date_Last_Touched TEXT,
    MAP_In_Place_YN TEXT,
    Company_URL TEXT,
    Area_Sales_Leader TEXT,
    DM TEXT,
    AD TEXT,
    Logo_URL TEXT,
    Next_Renewal_Date TEXT,
    Next_Renewal_EAR REAL,
    Anchor_Contract_Date TEXT,
    Anchor_Contract_EAR REAL
  );

  CREATE TABLE IF NOT EXISTS pipeline (
    Opportunity_ID TEXT PRIMARY KEY,
    Account_Name TEXT NOT NULL,
    District_Name TEXT NOT NULL DEFAULT '',
    Stage TEXT NOT NULL DEFAULT '01-Qualified',
    Forecast_Category TEXT NOT NULL DEFAULT 'Pipeline',
    Amount_USD REAL NOT NULL DEFAULT 0,
    Closed_Amount_USD REAL,
    Probability_Pct INTEGER NOT NULL DEFAULT 0,
    Created_Date TEXT NOT NULL,
    Expected_Close_Date TEXT NOT NULL DEFAULT '',
    Closed_Date TEXT,
    Champion_Name TEXT,
    Economic_Buyer TEXT,
    Next_Step TEXT,
    Next_Step_Date TEXT,
    Primary_Objection TEXT,
    Competitor TEXT,
    Source_Play TEXT,
    Product TEXT NOT NULL DEFAULT 'AgentMinder',
    Deal_Type TEXT,
    Status TEXT DEFAULT 'Open',
    Stage_Entered_Date TEXT,
    Tier TEXT,
    MAP_In_Place_YN TEXT
  );

  CREATE TABLE IF NOT EXISTS activities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    Activity_ID TEXT,
    Activity_Date TEXT NOT NULL,
    Activity_Type TEXT NOT NULL,
    Account_Name TEXT NOT NULL,
    District_Name TEXT NOT NULL DEFAULT '',
    Contact_Name TEXT,
    Notes TEXT,
    Play TEXT,
    Channel TEXT,
    Persona TEXT,
    Outcome TEXT,
    Logged_By TEXT
  );

  CREATE TABLE IF NOT EXISTS implementations (
    Implementation_ID TEXT PRIMARY KEY,
    Opportunity_ID TEXT,
    Account_Name TEXT NOT NULL,
    District_Name TEXT,
    Tier TEXT,
    Closed_Date TEXT,
    Closed_Amount_USD REAL,
    Kickoff_Date TEXT,
    Go_Live_Target TEXT,
    Go_Live_Actual TEXT,
    Deployment_Status TEXT NOT NULL DEFAULT 'Not Started',
    Health_RYG TEXT,
    Milestones_Complete INTEGER DEFAULT 0,
    Milestones_Total INTEGER DEFAULT 5,
    Onboarding_Owner TEXT,
    Risk_Notes TEXT
  );

  CREATE TABLE IF NOT EXISTS metric_targets (
    Target_ID TEXT PRIMARY KEY,
    Period_Type TEXT NOT NULL,
    Period_Label TEXT NOT NULL DEFAULT '',
    District_Name TEXT NOT NULL DEFAULT '',
    Metric TEXT NOT NULL,
    Target_Value REAL NOT NULL DEFAULT 0,
    Notes TEXT
  );

  CREATE INDEX IF NOT EXISTS idx_pipeline_account ON pipeline(Account_Name);
  CREATE INDEX IF NOT EXISTS idx_pipeline_district ON pipeline(District_Name);
  CREATE INDEX IF NOT EXISTS idx_activities_account ON activities(Account_Name);
  CREATE INDEX IF NOT EXISTS idx_activities_date ON activities(Activity_Date);
  CREATE INDEX IF NOT EXISTS idx_impl_account ON implementations(Account_Name);
`);

console.log('Reading Excel file...');
const wb = XLSX.readFile(XLSX_PATH);

// Helper: convert Excel serial date to yyyy-MM-dd
function excelDate(val) {
  if (!val && val !== 0) return null;
  if (typeof val === 'string') {
    if (/^\d{4}-\d{2}-\d{2}/.test(val)) return val.substring(0, 10);
    return val || null;
  }
  if (typeof val === 'number') {
    const d = new Date((val - 25569) * 86400000);
    const y = d.getUTCFullYear();
    const m = String(d.getUTCMonth() + 1).padStart(2, '0');
    const dd = String(d.getUTCDate()).padStart(2, '0');
    return `${y}-${m}-${dd}`;
  }
  if (val instanceof Date) {
    return val.toISOString().substring(0, 10);
  }
  return null;
}

function str(v) { return (v === undefined || v === null || v === '') ? null : String(v).trim(); }
function num(v) { if (v === undefined || v === null || v === '') return null; const n = Number(v); return isNaN(n) ? null : n; }
function int(v) { const n = num(v); return n === null ? null : Math.round(n); }

// ===== 1. Import Accounts =====
console.log('\n--- Importing Accounts ---');
const accts = XLSX.utils.sheet_to_json(wb.Sheets['Accounts']);
const acctInsert = db.prepare(`
  INSERT INTO accounts (Account_Name, District_Name, Tier, Priority, AgentMinder_Status, Current_ARR_USD, Touch_Count, Date_First_Touched, Date_Last_Touched, Company_URL, Area_Sales_Leader, DM, AD, Logo_URL, Next_Renewal_Date, Next_Renewal_EAR, Anchor_Contract_Date, Anchor_Contract_EAR)
  VALUES (@Account_Name, @District_Name, @Tier, @Priority, @AgentMinder_Status, @Current_ARR_USD, @Touch_Count, @Date_First_Touched, @Date_Last_Touched, @Company_URL, @Area_Sales_Leader, @DM, @AD, @Logo_URL, @Next_Renewal_Date, @Next_Renewal_EAR, @Anchor_Contract_Date, @Anchor_Contract_EAR)
  ON CONFLICT(Account_Name) DO UPDATE SET
    District_Name=excluded.District_Name, Tier=excluded.Tier, Priority=excluded.Priority,
    AgentMinder_Status=excluded.AgentMinder_Status, Current_ARR_USD=excluded.Current_ARR_USD,
    Touch_Count=excluded.Touch_Count, Date_First_Touched=excluded.Date_First_Touched,
    Date_Last_Touched=excluded.Date_Last_Touched, Company_URL=excluded.Company_URL,
    Area_Sales_Leader=excluded.Area_Sales_Leader, DM=excluded.DM, AD=excluded.AD,
    Logo_URL=excluded.Logo_URL, Next_Renewal_Date=excluded.Next_Renewal_Date,
    Next_Renewal_EAR=excluded.Next_Renewal_EAR, Anchor_Contract_Date=excluded.Anchor_Contract_Date,
    Anchor_Contract_EAR=excluded.Anchor_Contract_EAR
`);

// Build account name -> district lookup for later
const acctDistrictMap = {};
const acctTierMap = {};

let acctCount = 0;
const acctTx = db.transaction(() => {
  for (const r of accts) {
    const name = str(r['Account']);
    if (!name) continue;

    const district = str(r['District']) || '';
    const tier = str(r['Tier']) || '';

    acctDistrictMap[name] = district;
    acctDistrictMap[name.toUpperCase()] = district;
    acctTierMap[name] = tier;
    acctTierMap[name.toUpperCase()] = tier;

    acctInsert.run({
      Account_Name: name,
      District_Name: district,
      Tier: tier,
      Priority: str(r['Priority']) || '',
      AgentMinder_Status: str(r['AgentMinder_Status']) || 'Not Touched',
      Current_ARR_USD: num(r['Current_ARR_USD']),
      Touch_Count: int(r['Touch_Count']) || 0,
      Date_First_Touched: excelDate(r['Date_First_Touched']),
      Date_Last_Touched: excelDate(r['Date_Last_Touched']),
      Company_URL: str(r['Company URL']),
      Area_Sales_Leader: str(r['Area Sales Leader']),
      DM: str(r['DM']),
      AD: str(r['AD']),
      Logo_URL: str(r['Logo URL']),
      Next_Renewal_Date: excelDate(r['Next Renewal Date']),
      Next_Renewal_EAR: num(r['Next Renewal EAR']),
      Anchor_Contract_Date: excelDate(r['Anchor Contract Date']),
      Anchor_Contract_EAR: num(r['Anchor Contract EAR']),
    });
    acctCount++;
  }
});
acctTx();
console.log(`Imported ${acctCount} accounts`);

// Also load SE TIER for fallback district/tier lookup
const seTier = XLSX.utils.sheet_to_json(wb.Sheets['SE TIER']);
for (const r of seTier) {
  const name = str(r['Parent Account Name']);
  const district = str(r['FY26 District']);
  const tier = str(r['Tier']);
  if (name && district) {
    if (!acctDistrictMap[name]) acctDistrictMap[name] = district;
    if (!acctDistrictMap[name.toUpperCase()]) acctDistrictMap[name.toUpperCase()] = district;
  }
  if (name && tier) {
    if (!acctTierMap[name]) acctTierMap[name] = tier;
    if (!acctTierMap[name.toUpperCase()]) acctTierMap[name.toUpperCase()] = tier;
  }
}

// Helper to resolve district for an account name
function resolveDistrict(accountName, rawDistrict) {
  if (rawDistrict && rawDistrict !== 'Unmatched' && rawDistrict !== '') return rawDistrict;
  return acctDistrictMap[accountName] || acctDistrictMap[accountName?.toUpperCase()] || '';
}

function resolveTier(accountName, rawTier) {
  if (rawTier && rawTier !== 'Unmatched' && rawTier !== '') return rawTier;
  return acctTierMap[accountName] || acctTierMap[accountName?.toUpperCase()] || '';
}

// ===== 2. Import Pipeline =====
console.log('\n--- Importing Pipeline ---');
const pipe = XLSX.utils.sheet_to_json(wb.Sheets['Pipeline']);
const pipeInsert = db.prepare(`
  INSERT INTO pipeline (Opportunity_ID, Account_Name, District_Name, Stage, Forecast_Category, Amount_USD, Closed_Amount_USD, Probability_Pct, Created_Date, Expected_Close_Date, Closed_Date, Champion_Name, Economic_Buyer, Next_Step, Next_Step_Date, Primary_Objection, Competitor, Source_Play, Product, Deal_Type, Status, Stage_Entered_Date, Tier, MAP_In_Place_YN)
  VALUES (@Opportunity_ID, @Account_Name, @District_Name, @Stage, @Forecast_Category, @Amount_USD, @Closed_Amount_USD, @Probability_Pct, @Created_Date, @Expected_Close_Date, @Closed_Date, @Champion_Name, @Economic_Buyer, @Next_Step, @Next_Step_Date, @Primary_Objection, @Competitor, @Source_Play, @Product, @Deal_Type, @Status, @Stage_Entered_Date, @Tier, @MAP_In_Place_YN)
  ON CONFLICT(Opportunity_ID) DO UPDATE SET
    Account_Name=excluded.Account_Name, District_Name=excluded.District_Name, Stage=excluded.Stage,
    Forecast_Category=excluded.Forecast_Category, Amount_USD=excluded.Amount_USD,
    Closed_Amount_USD=excluded.Closed_Amount_USD, Probability_Pct=excluded.Probability_Pct,
    Created_Date=excluded.Created_Date, Expected_Close_Date=excluded.Expected_Close_Date,
    Closed_Date=excluded.Closed_Date, Champion_Name=excluded.Champion_Name,
    Economic_Buyer=excluded.Economic_Buyer, Next_Step=excluded.Next_Step,
    Next_Step_Date=excluded.Next_Step_Date, Primary_Objection=excluded.Primary_Objection,
    Competitor=excluded.Competitor, Source_Play=excluded.Source_Play, Product=excluded.Product,
    Deal_Type=excluded.Deal_Type, Status=excluded.Status, Stage_Entered_Date=excluded.Stage_Entered_Date,
    Tier=excluded.Tier, MAP_In_Place_YN=excluded.MAP_In_Place_YN
`);

let pipeCount = 0;
const pipeTx = db.transaction(() => {
  for (const r of pipe) {
    const oppId = str(r['Opportunity_ID']);
    if (!oppId) continue;
    const acctName = str(r['Account_Name']) || '';

    pipeInsert.run({
      Opportunity_ID: oppId,
      Account_Name: acctName,
      District_Name: resolveDistrict(acctName, str(r['District_Name'])),
      Stage: str(r['Stage']) || '01-Qualified',
      Forecast_Category: str(r['Forecast_Category']) || 'Pipeline',
      Amount_USD: num(r['Amount_USD']) || 0,
      Closed_Amount_USD: num(r['Closed_Amount_USD']),
      Probability_Pct: int(r['Probability_Pct']) || 0,
      Created_Date: excelDate(r['Created_Date']) || '',
      Expected_Close_Date: excelDate(r['Expected_Close_Date']) || '',
      Closed_Date: excelDate(r['Closed_Date']),
      Champion_Name: str(r['Champion_Name']),
      Economic_Buyer: str(r['Economic_Buyer']),
      Next_Step: str(r['Next_Step']),
      Next_Step_Date: excelDate(r['Next_Step_Date']),
      Primary_Objection: str(r['Primary_Objection']),
      Competitor: str(r['Competitor']),
      Source_Play: str(r['Source_Play']),
      Product: str(r['Product']) || 'AgentMinder',
      Deal_Type: str(r['Deal_Type']),
      Status: str(r['Status']) || 'Open',
      Stage_Entered_Date: excelDate(r['Stage_Entered_Date']),
      Tier: resolveTier(acctName, str(r['Tier'])),
      MAP_In_Place_YN: str(r['MAP_In_Place_YN']),
    });
    pipeCount++;
  }
});
pipeTx();
console.log(`Imported ${pipeCount} pipeline deals`);

// ===== 3. Import Activities =====
console.log('\n--- Importing Activities ---');
const acts = XLSX.utils.sheet_to_json(wb.Sheets['Activity_Log']);
const actInsert = db.prepare(`
  INSERT INTO activities (Activity_ID, Activity_Date, Activity_Type, Account_Name, District_Name, Contact_Name, Notes, Play, Channel, Persona, Outcome, Logged_By)
  VALUES (@Activity_ID, @Activity_Date, @Activity_Type, @Account_Name, @District_Name, @Contact_Name, @Notes, @Play, @Channel, @Persona, @Outcome, @Logged_By)
`);

let actCount = 0;
const actTx = db.transaction(() => {
  for (const r of acts) {
    const acctName = str(r['Account_Name']);
    const date = excelDate(r['Activity_Date']);
    if (!acctName || !date) continue;

    actInsert.run({
      Activity_ID: str(r['Activity_ID']),
      Activity_Date: date,
      Activity_Type: str(r['Activity_Type']) || '',
      Account_Name: acctName,
      District_Name: resolveDistrict(acctName, str(r['District_Name'])),
      Contact_Name: str(r['Contact_Name']),
      Notes: str(r['Notes']),
      Play: str(r['Play']),
      Channel: str(r['Channel']),
      Persona: str(r['Persona']),
      Outcome: str(r['Outcome']),
      Logged_By: str(r['Logged_By']),
    });
    actCount++;
  }
});
actTx();
console.log(`Imported ${actCount} activities`);

// ===== 4. Import Implementation =====
console.log('\n--- Importing Implementation ---');
const impl = XLSX.utils.sheet_to_json(wb.Sheets['Implementation']);
const implInsert = db.prepare(`
  INSERT INTO implementations (Implementation_ID, Opportunity_ID, Account_Name, District_Name, Tier, Closed_Date, Closed_Amount_USD, Kickoff_Date, Go_Live_Target, Go_Live_Actual, Deployment_Status, Health_RYG, Milestones_Complete, Milestones_Total, Onboarding_Owner, Risk_Notes)
  VALUES (@Implementation_ID, @Opportunity_ID, @Account_Name, @District_Name, @Tier, @Closed_Date, @Closed_Amount_USD, @Kickoff_Date, @Go_Live_Target, @Go_Live_Actual, @Deployment_Status, @Health_RYG, @Milestones_Complete, @Milestones_Total, @Onboarding_Owner, @Risk_Notes)
  ON CONFLICT(Implementation_ID) DO UPDATE SET
    Opportunity_ID=excluded.Opportunity_ID, Account_Name=excluded.Account_Name,
    District_Name=excluded.District_Name, Tier=excluded.Tier,
    Closed_Date=excluded.Closed_Date, Closed_Amount_USD=excluded.Closed_Amount_USD,
    Kickoff_Date=excluded.Kickoff_Date, Go_Live_Target=excluded.Go_Live_Target,
    Go_Live_Actual=excluded.Go_Live_Actual, Deployment_Status=excluded.Deployment_Status,
    Health_RYG=excluded.Health_RYG, Milestones_Complete=excluded.Milestones_Complete,
    Milestones_Total=excluded.Milestones_Total, Onboarding_Owner=excluded.Onboarding_Owner,
    Risk_Notes=excluded.Risk_Notes
`);

let implCount = 0;
const implTx = db.transaction(() => {
  for (const r of impl) {
    const id = str(r['Implementation_ID']);
    const acctName = str(r['Account_Name']);
    if (!id || !acctName) continue;

    implInsert.run({
      Implementation_ID: id,
      Opportunity_ID: str(r['Opportunity_ID']),
      Account_Name: acctName,
      District_Name: resolveDistrict(acctName, str(r['District_Name'])),
      Tier: resolveTier(acctName, str(r['Tier'])),
      Closed_Date: excelDate(r['Closed_Date']),
      Closed_Amount_USD: num(r['Closed_Amount_USD']),
      Kickoff_Date: excelDate(r['Kickoff_Date']),
      Go_Live_Target: excelDate(r['Go_Live_Target']),
      Go_Live_Actual: excelDate(r['Go_Live_Actual']),
      Deployment_Status: str(r['Deployment_Status']) || 'Not Started',
      Health_RYG: str(r['Health_RYG']),
      Milestones_Complete: int(r['Milestones_Complete']) || 0,
      Milestones_Total: int(r['Milestones_Total']) || 5,
      Onboarding_Owner: str(r['Onboarding_Owner']),
      Risk_Notes: str(r['Risk_Notes']),
    });
    implCount++;
  }
});
implTx();
console.log(`Imported ${implCount} implementations`);

// ===== 5. Import Metric Targets =====
console.log('\n--- Importing Metric Targets ---');
const targets = XLSX.utils.sheet_to_json(wb.Sheets['Targets']);
const tgtInsert = db.prepare(`
  INSERT INTO metric_targets (Target_ID, Period_Type, Period_Label, District_Name, Metric, Target_Value, Notes)
  VALUES (@Target_ID, @Period_Type, @Period_Label, @District_Name, @Metric, @Target_Value, @Notes)
  ON CONFLICT(Target_ID) DO UPDATE SET
    Period_Type=excluded.Period_Type, Period_Label=excluded.Period_Label,
    District_Name=excluded.District_Name, Metric=excluded.Metric,
    Target_Value=excluded.Target_Value, Notes=excluded.Notes
`);

// District name normalization for Targets tab
const districtNorm = {
  'Valley': 'SE-MISS-VALLEY',
  'PeachTree': 'SE-PEACHTREE',
  'Peachtree': 'SE-PEACHTREE',
  'Sunshine': 'SE-SUNSHINE',
  'MidAtl': 'SE-MID-ATL',
  'Mid-Atl': 'SE-MID-ATL',
};

let tgtCount = 0;
const tgtTx = db.transaction(() => {
  for (const r of targets) {
    const id = str(r['Target_ID']);
    if (!id) continue;
    const rawDistrict = str(r['District_Name']) || '';
    const district = districtNorm[rawDistrict] || rawDistrict;

    tgtInsert.run({
      Target_ID: id,
      Period_Type: str(r['Period_Type']) || '',
      Period_Label: str(r['Period_Label']) || '',
      District_Name: district,
      Metric: str(r['Metric']) || '',
      Target_Value: num(r['Target_Value']) || 0,
      Notes: str(r['Notes']),
    });
    tgtCount++;
  }
});
tgtTx();
console.log(`Imported ${tgtCount} metric targets`);

// ===== 6. Recalculate touch data from activities =====
console.log('\n--- Recalculating touch data ---');
const touchData = db.prepare(`
  SELECT Account_Name, MIN(Activity_Date) as first_touch, MAX(Activity_Date) as last_touch, COUNT(*) as touch_count
  FROM activities
  GROUP BY Account_Name
`).all();

const updateTouch = db.prepare(`
  UPDATE accounts SET
    Date_First_Touched = @first_touch,
    Date_Last_Touched = @last_touch,
    Touch_Count = @touch_count
  WHERE Account_Name = @Account_Name
`);

const touchTx = db.transaction(() => {
  for (const t of touchData) {
    updateTouch.run(t);
  }
});
touchTx();
console.log(`Updated touch data for ${touchData.length} accounts`);

// ===== 7. Update AgentMinder_Status based on activity/pipeline =====
console.log('\n--- Updating AgentMinder status ---');
const statusResult = db.prepare(`
  UPDATE accounts SET AgentMinder_Status = '10% - Prospect'
  WHERE Account_Name IN (SELECT DISTINCT Account_Name FROM activities)
    AND (AgentMinder_Status = 'Not Touched' OR AgentMinder_Status IS NULL)
`).run();
console.log(`Updated ${statusResult.changes} accounts to 10% - Prospect based on activities`);

const statusResult2 = db.prepare(`
  UPDATE accounts SET AgentMinder_Status = '20% - Research'
  WHERE Account_Name IN (SELECT DISTINCT Account_Name FROM pipeline WHERE Status = 'Open')
    AND (AgentMinder_Status = 'Not Touched' OR AgentMinder_Status = '10% - Prospect')
`).run();
console.log(`Updated ${statusResult2.changes} accounts to 20% - Research based on open pipeline`);

// ===== Summary =====
console.log('\n===== IMPORT COMPLETE =====');
const stats = {
  accounts: db.prepare('SELECT COUNT(*) as c FROM accounts').get().c,
  pipeline: db.prepare('SELECT COUNT(*) as c FROM pipeline').get().c,
  activities: db.prepare('SELECT COUNT(*) as c FROM activities').get().c,
  implementations: db.prepare('SELECT COUNT(*) as c FROM implementations').get().c,
  metricTargets: db.prepare('SELECT COUNT(*) as c FROM metric_targets').get().c,
};
console.log('Database stats:', stats);

// Show some data quality info
const unmatchedPipe = db.prepare("SELECT COUNT(*) as c FROM pipeline WHERE District_Name = '' OR District_Name IS NULL").get().c;
console.log(`\nPipeline deals with unresolved district: ${unmatchedPipe}`);

const touchedAccts = db.prepare("SELECT COUNT(*) as c FROM accounts WHERE Touch_Count > 0").get().c;
console.log(`Accounts with touch data: ${touchedAccts}`);

const districtBreakdown = db.prepare("SELECT District_Name, COUNT(*) as c FROM accounts WHERE District_Name != '' GROUP BY District_Name ORDER BY c DESC").all();
console.log('\nAccounts by district:');
districtBreakdown.forEach(r => console.log(`  ${r.District_Name}: ${r.c}`));

db.close();
console.log('\nDone! Database saved to:', DB_PATH);