seed.mjs

import Database from 'better-sqlite3';
import { mkdirSync } from 'fs';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_DIR = join(__dirname, 'public');
const DB_PATH = join(DB_DIR, 'cc_mart.db');

mkdirSync(DB_DIR, { recursive: true });
const db = new Database(DB_PATH);

// ── ENABLE WAL FOR PERFORMANCE ──────────────────────────────
db.pragma('journal_mode = WAL');

// ════════════════════════════════════════════════════════════
//  SCHEMA — Star Schema Data Mart
// ════════════════════════════════════════════════════════════
db.exec(`
  -- DIMENSION: Geography
  CREATE TABLE IF NOT EXISTS dim_geography (
    geo_key          INTEGER PRIMARY KEY AUTOINCREMENT,
    country          TEXT NOT NULL,
    region           TEXT NOT NULL,
    state_province   TEXT NOT NULL,
    city             TEXT,
    site_name        TEXT,
    timezone         TEXT,
    language         TEXT
  );

  -- DIMENSION: Agent
  CREATE TABLE IF NOT EXISTS dim_agent (
    agent_key        INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id         TEXT NOT NULL UNIQUE,
    agent_name       TEXT NOT NULL,
    team             TEXT,
    supervisor       TEXT,
    tenure_band      TEXT,  -- 'New(<6mo)', 'Developing(6-18mo)', 'Experienced(18mo+)'
    skill_level      TEXT,  -- 'L1','L2','L3'
    geo_key          INTEGER REFERENCES dim_geography(geo_key)
  );

  -- DIMENSION: Channel
  CREATE TABLE IF NOT EXISTS dim_channel (
    channel_key      INTEGER PRIMARY KEY AUTOINCREMENT,
    channel_name     TEXT NOT NULL,  -- 'Voice','Chat','Email','SMS','Social'
    channel_type     TEXT            -- 'Inbound','Outbound','Async'
  );

  -- DIMENSION: Date
  CREATE TABLE IF NOT EXISTS dim_date (
    date_key         INTEGER PRIMARY KEY,  -- YYYYMMDD
    full_date        TEXT NOT NULL,
    year             INTEGER,
    quarter          INTEGER,
    month            INTEGER,
    month_name       TEXT,
    week             INTEGER,
    day_of_week      INTEGER,
    day_name         TEXT,
    is_weekend       INTEGER
  );

  -- DIMENSION: Queue / Skill Group
  CREATE TABLE IF NOT EXISTS dim_queue (
    queue_key        INTEGER PRIMARY KEY AUTOINCREMENT,
    queue_name       TEXT NOT NULL,
    skill_group      TEXT,
    priority_tier    INTEGER
  );

  -- FACT: Interactions (grain = one contact)
  CREATE TABLE IF NOT EXISTS fact_interactions (
    interaction_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    date_key         INTEGER REFERENCES dim_date(date_key),
    geo_key          INTEGER REFERENCES dim_geography(geo_key),
    agent_key        INTEGER REFERENCES dim_agent(agent_key),
    channel_key      INTEGER REFERENCES dim_channel(channel_key),
    queue_key        INTEGER REFERENCES dim_queue(queue_key),

    -- Time metrics (seconds)
    talk_time_sec    INTEGER,
    hold_time_sec    INTEGER,
    acw_time_sec     INTEGER,
    wait_time_sec    INTEGER,
    aht_sec          INTEGER,  -- talk + hold + acw

    -- Resolution metrics
    fcr_flag         INTEGER,  -- 1 = resolved on first contact
    transferred_flag INTEGER,
    escalated_flag   INTEGER,
    abandoned_flag   INTEGER,
    repeat_contact   INTEGER,  -- contacted again within 7 days

    -- Quality & satisfaction
    csat_score       REAL,     -- 1-5
    nps_score        INTEGER,  -- 0-10
    ces_score        REAL,     -- 1-7 (Customer Effort Score)
    qa_score         REAL,     -- 0-100
    sentiment_score  REAL,     -- -1 to 1

    -- Operational
    cost_usd         REAL,
    sla_met_flag     INTEGER,  -- answered within target window
    occupancy_pct    REAL,     -- agent occupancy during interval
    adherence_pct    REAL
  );

  -- MART VIEW: KPI Summary by Geography + Date
  CREATE VIEW IF NOT EXISTS v_kpi_by_geo_date AS
  SELECT
    d.full_date,
    d.year,
    d.month,
    d.month_name,
    d.quarter,
    g.country,
    g.region,
    g.state_province,
    g.site_name,
    ch.channel_name,
    COUNT(*)                                      AS total_contacts,
    ROUND(AVG(f.aht_sec),1)                       AS avg_aht_sec,
    ROUND(AVG(f.wait_time_sec),1)                 AS avg_wait_sec,
    ROUND(AVG(f.hold_time_sec),1)                 AS avg_hold_sec,
    ROUND(AVG(f.acw_time_sec),1)                  AS avg_acw_sec,
    ROUND(100.0*SUM(f.fcr_flag)/COUNT(*),1)       AS fcr_pct,
    ROUND(100.0*SUM(f.sla_met_flag)/COUNT(*),1)   AS sla_pct,
    ROUND(100.0*SUM(f.abandoned_flag)/COUNT(*),1) AS abandonment_pct,
    ROUND(100.0*SUM(f.transferred_flag)/COUNT(*),1) AS transfer_pct,
    ROUND(100.0*SUM(f.escalated_flag)/COUNT(*),1) AS escalation_pct,
    ROUND(AVG(f.csat_score),2)                    AS avg_csat,
    ROUND(AVG(f.nps_score),1)                     AS avg_nps,
    ROUND(AVG(f.ces_score),2)                     AS avg_ces,
    ROUND(AVG(f.qa_score),1)                      AS avg_qa,
    ROUND(AVG(f.sentiment_score),3)               AS avg_sentiment,
    ROUND(AVG(f.occupancy_pct),1)                 AS avg_occupancy,
    ROUND(AVG(f.adherence_pct),1)                 AS avg_adherence,
    ROUND(SUM(f.cost_usd),2)                      AS total_cost_usd,
    ROUND(AVG(f.cost_usd),2)                      AS cost_per_contact
  FROM fact_interactions f
  JOIN dim_date     d  ON f.date_key    = d.date_key
  JOIN dim_geography g ON f.geo_key     = g.geo_key
  JOIN dim_channel  ch ON f.channel_key = ch.channel_key
  GROUP BY d.full_date, g.country, g.region, g.state_province, g.site_name, ch.channel_name;
`);

// ════════════════════════════════════════════════════════════
//  REFERENCE DATA
// ════════════════════════════════════════════════════════════
const GEOGRAPHIES = [
  // North America
  { country: 'United States', region: 'North America', state: 'Arizona',        city: 'Phoenix',      site: 'PHX-01', tz: 'America/Phoenix',    lang: 'English' },
  { country: 'United States', region: 'North America', state: 'Texas',          city: 'Austin',       site: 'AUS-01', tz: 'America/Chicago',    lang: 'English' },
  { country: 'United States', region: 'North America', state: 'Georgia',        city: 'Atlanta',      site: 'ATL-01', tz: 'America/New_York',   lang: 'English' },
  { country: 'United States', region: 'North America', state: 'California',     city: 'Los Angeles',  site: 'LAX-01', tz: 'America/Los_Angeles',lang: 'English' },
  { country: 'Canada',        region: 'North America', state: 'Ontario',        city: 'Toronto',      site: 'YYZ-01', tz: 'America/Toronto',    lang: 'English' },
  { country: 'Mexico',        region: 'North America', state: 'Jalisco',        city: 'Guadalajara',  site: 'GDL-01', tz: 'America/Mexico_City',lang: 'Spanish' },
  // EMEA
  { country: 'United Kingdom',region: 'EMEA',          state: 'England',        city: 'London',       site: 'LHR-01', tz: 'Europe/London',      lang: 'English' },
  { country: 'Germany',       region: 'EMEA',          state: 'Bavaria',        city: 'Munich',       site: 'MUC-01', tz: 'Europe/Berlin',      lang: 'German'  },
  { country: 'France',        region: 'EMEA',          state: 'Île-de-France',  city: 'Paris',        site: 'CDG-01', tz: 'Europe/Paris',       lang: 'French'  },
  { country: 'South Africa',  region: 'EMEA',          state: 'Gauteng',        city: 'Johannesburg', site: 'JNB-01', tz: 'Africa/Johannesburg',lang: 'English' },
  // APAC
  { country: 'Philippines',   region: 'APAC',          state: 'Metro Manila',   city: 'Manila',       site: 'MNL-01', tz: 'Asia/Manila',        lang: 'English' },
  { country: 'India',         region: 'APAC',          state: 'Karnataka',      city: 'Bangalore',    site: 'BLR-01', tz: 'Asia/Kolkata',       lang: 'English' },
  { country: 'Australia',     region: 'APAC',          state: 'New South Wales',city: 'Sydney',       site: 'SYD-01', tz: 'Australia/Sydney',   lang: 'English' },
  { country: 'Japan',         region: 'APAC',          state: 'Tokyo',          city: 'Tokyo',        site: 'TYO-01', tz: 'Asia/Tokyo',         lang: 'Japanese'},
  // LATAM
  { country: 'Brazil',        region: 'LATAM',         state: 'São Paulo',      city: 'São Paulo',    site: 'GRU-01', tz: 'America/Sao_Paulo',  lang: 'Portuguese'},
  { country: 'Colombia',      region: 'LATAM',         state: 'Cundinamarca',   city: 'Bogotá',       site: 'BOG-01', tz: 'America/Bogota',     lang: 'Spanish' },
];

const CHANNELS = [
  { name: 'Voice',  type: 'Inbound'  },
  { name: 'Chat',   type: 'Inbound'  },
  { name: 'Email',  type: 'Async'    },
  { name: 'SMS',    type: 'Async'    },
  { name: 'Social', type: 'Async'    },
  { name: 'Voice',  type: 'Outbound' },
];

const QUEUES = [
  { name: 'General Support',    group: 'Tier 1', priority: 1 },
  { name: 'Technical Support',  group: 'Tier 2', priority: 2 },
  { name: 'Billing & Payments', group: 'Tier 1', priority: 1 },
  { name: 'Escalations',        group: 'Tier 3', priority: 3 },
  { name: 'Sales',              group: 'Sales',  priority: 1 },
  { name: 'Retention',          group: 'Sales',  priority: 2 },
];

const TENURE_BANDS = ['New(<6mo)', 'Developing(6-18mo)', 'Experienced(18mo+)'];
const SKILL_LEVELS = ['L1', 'L2', 'L3'];
const SUPERVISORS  = ['Maria Chen', 'James Okafor', 'Priya Nair', 'Carlos Reyes', 'Sophie Laurent'];

// ════════════════════════════════════════════════════════════
//  INSERT DIMENSIONS
// ════════════════════════════════════════════════════════════
const insertGeo = db.prepare(`
  INSERT INTO dim_geography (country, region, state_province, city, site_name, timezone, language)
  VALUES (@country, @region, @state, @city, @site, @tz, @lang)
`);
for (const g of GEOGRAPHIES) db.prepare(`INSERT INTO dim_geography (country,region,state_province,city,site_name,timezone,language) VALUES (?,?,?,?,?,?,?)`).run(g.country,g.region,g.state,g.city,g.site,g.tz,g.lang);

const insertChannel = db.prepare(`INSERT INTO dim_channel (channel_name, channel_type) VALUES (?,?)`);
for (const c of CHANNELS) insertChannel.run(c.name, c.type);

const insertQueue = db.prepare(`INSERT INTO dim_queue (queue_name, skill_group, priority_tier) VALUES (?,?,?)`);
for (const q of QUEUES) insertQueue.run(q.name, q.group, q.priority);

// Date dimension — 2 years
const insertDate = db.prepare(`
  INSERT OR IGNORE INTO dim_date (date_key,full_date,year,quarter,month,month_name,week,day_of_week,day_name,is_weekend)
  VALUES (?,?,?,?,?,?,?,?,?,?)
`);
const MONTH_NAMES = ['January','February','March','April','May','June','July','August','September','October','November','December'];
const DAY_NAMES   = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
const start = new Date('2024-01-01');
const end   = new Date('2025-12-31');
for (let d = new Date(start); d <= end; d.setDate(d.getDate()+1)) {
  const key = parseInt(d.toISOString().slice(0,10).replace(/-/g,''));
  insertDate.run(
    key, d.toISOString().slice(0,10),
    d.getFullYear(), Math.ceil((d.getMonth()+1)/3),
    d.getMonth()+1, MONTH_NAMES[d.getMonth()],
    Math.ceil(d.getDate()/7), d.getDay(), DAY_NAMES[d.getDay()],
    (d.getDay()===0||d.getDay()===6)?1:0
  );
}

// Agents — ~12 per site
const geoRows = db.prepare('SELECT geo_key, site_name FROM dim_geography').all();
const insertAgent = db.prepare(`
  INSERT INTO dim_agent (agent_id, agent_name, team, supervisor, tenure_band, skill_level, geo_key)
  VALUES (?,?,?,?,?,?,?)
`);
const FIRST = ['Alex','Jordan','Taylor','Morgan','Casey','Riley','Jamie','Drew','Avery','Quinn','Blake','Sage'];
const LAST  = ['Smith','Johnson','Williams','Brown','Jones','Garcia','Martinez','Davis','Lopez','Wilson'];
let agentCounter = 1;
for (const geo of geoRows) {
  for (let i = 0; i < 12; i++) {
    const fn = FIRST[i % FIRST.length];
    const ln = LAST[Math.floor(Math.random()*LAST.length)];
    insertAgent.run(
      `AGT-${String(agentCounter).padStart(4,'0')}`,
      `${fn} ${ln}`,
      `Team ${String.fromCharCode(65+Math.floor(i/4))}`,
      SUPERVISORS[Math.floor(Math.random()*SUPERVISORS.length)],
      TENURE_BANDS[Math.floor(Math.random()*TENURE_BANDS.length)],
      SKILL_LEVELS[Math.floor(Math.random()*SKILL_LEVELS.length)],
      geo.geo_key
    );
    agentCounter++;
  }
}

// ════════════════════════════════════════════════════════════
//  SYNTHETIC FACT DATA
// ════════════════════════════════════════════════════════════
const dateKeys  = db.prepare('SELECT date_key FROM dim_date ORDER BY date_key').all().map(r=>r.date_key);
const agents    = db.prepare('SELECT agent_key, geo_key FROM dim_agent').all();
const channels  = db.prepare('SELECT channel_key FROM dim_channel').all();
const queues    = db.prepare('SELECT queue_key FROM dim_queue').all();

// Site performance profiles — inject regional variation
const SITE_PROFILES = {
  'PHX-01': { aht:1.0, fcr:1.0, csat:1.0, cost:1.0 },
  'AUS-01': { aht:0.95,fcr:1.05,csat:1.02,cost:0.98},
  'ATL-01': { aht:1.05,fcr:0.97,csat:0.98,cost:1.0 },
  'LAX-01': { aht:1.1, fcr:0.93,csat:0.96,cost:1.15},
  'YYZ-01': { aht:0.98,fcr:1.02,csat:1.01,cost:1.05},
  'GDL-01': { aht:0.9, fcr:0.95,csat:0.97,cost:0.7 },
  'LHR-01': { aht:1.05,fcr:1.03,csat:1.03,cost:1.2 },
  'MUC-01': { aht:1.08,fcr:1.05,csat:1.04,cost:1.18},
  'CDG-01': { aht:1.1, fcr:0.98,csat:1.0, cost:1.15},
  'JNB-01': { aht:0.95,fcr:0.92,csat:0.94,cost:0.75},
  'MNL-01': { aht:0.88,fcr:0.9, csat:0.93,cost:0.6 },
  'BLR-01': { aht:0.85,fcr:0.92,csat:0.95,cost:0.55},
  'SYD-01': { aht:1.02,fcr:1.01,csat:1.0, cost:1.1 },
  'TYO-01': { aht:1.15,fcr:1.08,csat:1.06,cost:1.25},
  'GRU-01': { aht:0.92,fcr:0.91,csat:0.93,cost:0.65},
  'BOG-01': { aht:0.9, fcr:0.93,csat:0.95,cost:0.6 },
};

function rand(min, max) { return Math.random()*(max-min)+min; }
function randInt(min, max) { return Math.floor(rand(min,max+1)); }
function clamp(v,min,max) { return Math.max(min,Math.min(max,v)); }
function profile(siteMap, agentGeoKey) {
  const geoRow = geoRows.find(g=>g.geo_key===agentGeoKey);
  return SITE_PROFILES[geoRow?.site_name] || { aht:1.0,fcr:1.0,csat:1.0,cost:1.0 };
}

const insertFact = db.prepare(`
  INSERT INTO fact_interactions
  (date_key,geo_key,agent_key,channel_key,queue_key,
   talk_time_sec,hold_time_sec,acw_time_sec,wait_time_sec,aht_sec,
   fcr_flag,transferred_flag,escalated_flag,abandoned_flag,repeat_contact,
   csat_score,nps_score,ces_score,qa_score,sentiment_score,
   cost_usd,sla_met_flag,occupancy_pct,adherence_pct)
  VALUES
  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
`);

const insertMany = db.transaction((rows) => {
  for (const r of rows) insertFact.run(...r);
});

// Generate ~150 interactions per day across all sites
console.log('Seeding fact table...');
let total = 0;
for (const dk of dateKeys) {
  const batch = [];
  const contactsToday = randInt(120, 200);
  for (let i = 0; i < contactsToday; i++) {
    const agent   = agents[randInt(0, agents.length-1)];
    const p       = profile(SITE_PROFILES, agent.geo_key);
    const channel = channels[randInt(0, channels.length-1)];
    const queue   = queues[randInt(0, queues.length-1)];

    const talk    = Math.round(rand(120, 480) * p.aht);
    const hold    = Math.round(rand(0, 90)   * p.aht);
    const acw     = Math.round(rand(30, 120) * p.aht);
    const wait    = Math.round(rand(10, 180));
    const aht     = talk + hold + acw;

    const fcrBase = 0.78 * p.fcr;
    const fcr     = Math.random() < clamp(fcrBase,0,1) ? 1 : 0;
    const xfer    = Math.random() < 0.12 ? 1 : 0;
    const esc     = Math.random() < 0.05 ? 1 : 0;
    const aband   = Math.random() < 0.08 ? 1 : 0;
    const repeat  = fcr===0 && Math.random()<0.3 ? 1 : 0;

    const csatBase= 3.9 * p.csat;
    const csat    = aband ? null : clamp(+(rand(-0.4,0.4)+csatBase).toFixed(1),1,5);
    const nps     = aband ? null : randInt(0,10);
    const ces     = aband ? null : clamp(+(rand(3,6.5)*p.csat).toFixed(1),1,7);
    const qa      = clamp(+(rand(70,98)*p.fcr).toFixed(1),0,100);
    const sent    = clamp(+(rand(-0.3,0.8)*p.csat-0.1).toFixed(3),-1,1);

    const cost    = clamp(+(rand(2.5,8.5)*p.cost*(aht/300)).toFixed(2),0.5,25);
    const sla     = wait <= 30 ? 1 : (Math.random()<0.7?1:0);
    const occ     = clamp(+(rand(72,92)).toFixed(1),0,100);
    const adh     = clamp(+(rand(85,98)).toFixed(1),0,100);

    batch.push([
      dk, agent.geo_key, agent.agent_key, channel.channel_key, queue.queue_key,
      talk, hold, acw, wait, aht,
      fcr, xfer, esc, aband, repeat,
      csat, nps, ces, qa, sent,
      cost, sla, occ, adh
    ]);
  }
  insertMany(batch);
  total += batch.length;
}

console.log(`✓ Seeded ${total.toLocaleString()} interactions across ${dateKeys.length} days`);
console.log(`✓ Database written to: ${DB_PATH}`);
db.close();