150 lines
4.2 KiB
JavaScript
150 lines
4.2 KiB
JavaScript
// Emits prisma/seed.sql — the plain-SQL twin of prisma/seed.ts, for baking into
|
|
// the MySQL image (see Dockerfile.db). 275 SubCostItem rows are not hand-
|
|
// writable, and the container has no Node/Prisma to run seed.ts with.
|
|
//
|
|
// node prisma/generate-seed-sql.mjs > prisma/seed.sql
|
|
//
|
|
// The constants and arithmetic below mirror prisma/seed.ts verbatim. If seed.ts
|
|
// changes, change them here too and regenerate.
|
|
|
|
const REGIONS = ["AMEA IT", "EMEA IT", "NABU IT", "PH IT", "JP IT"];
|
|
const YEARS = [2022, 2023, 2024, 2025, 2026];
|
|
|
|
const ITEMS = [
|
|
{
|
|
name: "Cloud Hosting",
|
|
subs: [
|
|
{ name: "Compute (VMs)", base: 40000 },
|
|
{ name: "Storage", base: 18000 },
|
|
{ name: "Networking / Egress", base: 9000 },
|
|
],
|
|
},
|
|
{
|
|
name: "Software Licenses",
|
|
subs: [
|
|
{ name: "Productivity Suite", base: 22000 },
|
|
{ name: "Design Tools", base: 8000 },
|
|
],
|
|
},
|
|
{
|
|
name: "Security Tools",
|
|
subs: [
|
|
{ name: "Endpoint Protection", base: 15000 },
|
|
{ name: "SIEM", base: 20000 },
|
|
{ name: "Vulnerability Scanning", base: 6000 },
|
|
],
|
|
},
|
|
{
|
|
name: "Support & Maintenance",
|
|
subs: [{ name: "Vendor Support Contract", base: 30000 }],
|
|
},
|
|
{
|
|
name: "Network Infra",
|
|
subs: [
|
|
{ name: "Circuits / Bandwidth", base: 25000 },
|
|
{ name: "Hardware Refresh", base: 12000 },
|
|
],
|
|
},
|
|
];
|
|
|
|
const REGION_WEIGHT = {
|
|
"NABU IT": 1.4,
|
|
"EMEA IT": 1.2,
|
|
"AMEA IT": 1.0,
|
|
"JP IT": 0.9,
|
|
"PH IT": 0.7,
|
|
};
|
|
|
|
const REGION_AI = {
|
|
"NABU IT": { base: 12, step: 6 },
|
|
"EMEA IT": { base: 10, step: 5 },
|
|
"AMEA IT": { base: 8, step: 5 },
|
|
"JP IT": { base: 15, step: 4 },
|
|
"PH IT": { base: 6, step: 4 },
|
|
};
|
|
|
|
function yearFactor(year) {
|
|
return Math.pow(1.08, year - 2022);
|
|
}
|
|
|
|
// Fixed so regenerating yields a byte-identical file (empty diff).
|
|
const TS = "2026-01-01 00:00:00.000";
|
|
|
|
function q(s) {
|
|
return `'${String(s).replace(/\\/g, "\\\\").replace(/'/g, "''")}'`;
|
|
}
|
|
|
|
const yearRows = YEARS.map((y) => ` (${y})`);
|
|
const costRows = [];
|
|
const subRows = [];
|
|
const pctRows = [];
|
|
|
|
let costId = 0;
|
|
let subId = 0;
|
|
let pctId = 0;
|
|
|
|
for (const region of REGIONS) {
|
|
const weight = REGION_WEIGHT[region];
|
|
for (const year of YEARS) {
|
|
const yf = yearFactor(year);
|
|
for (const item of ITEMS) {
|
|
costId += 1;
|
|
costRows.push(
|
|
` (${costId},${q(item.name)},${q(region)},${year},${q(TS)},${q(TS)})`
|
|
);
|
|
for (const s of item.subs) {
|
|
subId += 1;
|
|
const subCost = Math.round((s.base * weight * yf) / 100) * 100;
|
|
subRows.push(` (${subId},${q(s.name)},${subCost},${costId})`);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
for (const region of REGIONS) {
|
|
const ai = REGION_AI[region];
|
|
for (const year of YEARS) {
|
|
pctId += 1;
|
|
const pct = Math.min(60, ai.base + (year - 2022) * ai.step);
|
|
pctRows.push(` (${pctId},${q(region)},${year},${pct})`);
|
|
}
|
|
}
|
|
|
|
const out = [];
|
|
out.push("-- GENERATED FILE — do not edit by hand.");
|
|
out.push("-- Regenerate with: node prisma/generate-seed-sql.mjs > prisma/seed.sql");
|
|
out.push(
|
|
`-- Rows: CostItem=${costId}, SubCostItem=${subId}, TrendAiPercentage=${pctId}, Year=${YEARS.length}`
|
|
);
|
|
out.push("");
|
|
// Explicit IDs mean the FK targets are known up front; disabling the check lets
|
|
// the inserts run in any order and keeps the file order-independent.
|
|
out.push("SET FOREIGN_KEY_CHECKS=0;");
|
|
out.push("");
|
|
out.push("INSERT INTO `Year` (`year`) VALUES");
|
|
out.push(yearRows.join(",\n") + ";");
|
|
out.push("");
|
|
out.push(
|
|
"INSERT INTO `CostItem` (`id`,`name`,`region`,`year`,`createdAt`,`updatedAt`) VALUES"
|
|
);
|
|
out.push(costRows.join(",\n") + ";");
|
|
out.push("");
|
|
out.push("INSERT INTO `SubCostItem` (`id`,`name`,`subCost`,`costItemId`) VALUES");
|
|
out.push(subRows.join(",\n") + ";");
|
|
out.push("");
|
|
out.push(
|
|
"INSERT INTO `TrendAiPercentage` (`id`,`region`,`year`,`percentage`) VALUES"
|
|
);
|
|
out.push(pctRows.join(",\n") + ";");
|
|
out.push("");
|
|
// Explicit IDs leave AUTO_INCREMENT at 1 in some engines/versions; reset it or
|
|
// the first app-side insert collides with a seeded row.
|
|
out.push(`ALTER TABLE \`CostItem\` AUTO_INCREMENT = ${costId + 1};`);
|
|
out.push(`ALTER TABLE \`SubCostItem\` AUTO_INCREMENT = ${subId + 1};`);
|
|
out.push(`ALTER TABLE \`TrendAiPercentage\` AUTO_INCREMENT = ${pctId + 1};`);
|
|
out.push("");
|
|
out.push("SET FOREIGN_KEY_CHECKS=1;");
|
|
out.push("");
|
|
|
|
process.stdout.write(out.join("\n"));
|