372 lines
16 KiB
JavaScript
372 lines
16 KiB
JavaScript
const { PrismaClient } = require('@prisma/client');
|
|
const { PrismaPg } = require('@prisma/adapter-pg');
|
|
const { Pool } = require('pg');
|
|
require('dotenv').config();
|
|
|
|
const dbUrl = new URL(process.env.DATABASE_URL_WALKTHROUGH || "postgresql://special_hotel_user:SpecialHotel1235%2F%2A-%2B@pg.gaboggamer.online/special_hotel_walkthrough?schema=public");
|
|
|
|
const pool = new Pool({
|
|
host: dbUrl.hostname,
|
|
port: dbUrl.port ? parseInt(dbUrl.port) : 5432,
|
|
user: decodeURIComponent(dbUrl.username),
|
|
password: decodeURIComponent(dbUrl.password),
|
|
database: dbUrl.pathname.substring(1).split('?')[0],
|
|
ssl: false
|
|
});
|
|
|
|
const adapter = new PrismaPg(pool);
|
|
const prisma = new PrismaClient({ adapter });
|
|
|
|
const PASSWORD_HASH = '$2b$10$lRn/GrwzaEGxWqqQGGz0f.44nBywObKkGWRajlxWeDGZ3o4f7ayjy'; // bcrypt of 'password123'
|
|
|
|
async function main() {
|
|
console.log("=== STARTING WALKTHROUGH SEED SCRIPT ===");
|
|
|
|
// 1. Clean existing records in the walkthrough database to ensure clean seeding
|
|
console.log("Cleaning database...");
|
|
// Disable RLS temporarily for bulk seed operations
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "regions" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "hotels" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "users" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "goals" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "sales_results" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "settlements" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "audit_logs" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "compensation_plans" DISABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "calculation_rules" DISABLE ROW LEVEL SECURITY;`);
|
|
|
|
await prisma.auditLog.deleteMany();
|
|
await prisma.notification.deleteMany();
|
|
await prisma.settlement.deleteMany();
|
|
await prisma.salesResult.deleteMany();
|
|
await prisma.goal.deleteMany();
|
|
await prisma.calculationRule.deleteMany();
|
|
await prisma.compensationPlan.deleteMany();
|
|
await prisma.user.deleteMany();
|
|
await prisma.hotel.deleteMany();
|
|
await prisma.region.deleteMany();
|
|
|
|
// 2. Seed Regions
|
|
console.log("Seeding regions...");
|
|
const bogota = await prisma.region.create({ data: { name: 'Bogotá', code: 'BOG' } });
|
|
const antioquia = await prisma.region.create({ data: { name: 'Antioquia', code: 'ANT' } });
|
|
const caribe = await prisma.region.create({ data: { name: 'Caribe', code: 'CAR' } });
|
|
const valle = await prisma.region.create({ data: { name: 'Valle', code: 'VAL' } });
|
|
|
|
// 3. Seed Hotels
|
|
console.log("Seeding hotels...");
|
|
const p93 = await prisma.hotel.create({ data: { name: 'Estelar Parque de la 93', code: 'EST-P93', regionId: bogota.id } });
|
|
const mde = await prisma.hotel.create({ data: { name: 'Estelar Medellin', code: 'EST-MDE', regionId: antioquia.id } });
|
|
const ctg = await prisma.hotel.create({ data: { name: 'Estelar Cartagena', code: 'EST-CTG', regionId: caribe.id } });
|
|
const cali = await prisma.hotel.create({ data: { name: 'Estelar Cali', code: 'EST-CALI', regionId: valle.id } });
|
|
|
|
// 4. Seed Users
|
|
console.log("Seeding users...");
|
|
const usersToSeed = [
|
|
{ username: 'admin', email: 'admin@estelar.com', role: 'admin', hotelId: p93.id, area: 'Sistemas' },
|
|
{ username: 'director', email: 'director@estelar.com', role: 'director', hotelId: p93.id, area: 'Comercial' },
|
|
{ username: 'analista', email: 'analista@estelar.com', role: 'analyst', hotelId: p93.id, area: 'Finanzas' },
|
|
{ username: 'consulta', email: 'consulta@estelar.com', role: 'auditor', hotelId: p93.id, area: 'Auditoria' },
|
|
|
|
// Managers
|
|
{ username: 'gerente_bog', email: 'gerente.bog@estelar.com', role: 'hotel_manager', hotelId: p93.id, area: 'Administracion' },
|
|
{ username: 'gerente_mde', email: 'gerente.mde@estelar.com', role: 'hotel_manager', hotelId: mde.id, area: 'Administracion' },
|
|
{ username: 'gerente_ctg', email: 'gerente.ctg@estelar.com', role: 'hotel_manager', hotelId: ctg.id, area: 'Administracion' },
|
|
{ username: 'gerente_cali', email: 'gerente.cali@estelar.com', role: 'hotel_manager', hotelId: cali.id, area: 'Administracion' },
|
|
|
|
// Leaders
|
|
{ username: 'lider_bog', email: 'lider.bog@estelar.com', role: 'commercial_leader', hotelId: p93.id, area: 'Ventas' },
|
|
{ username: 'lider_mde', email: 'lider.mde@estelar.com', role: 'commercial_leader', hotelId: mde.id, area: 'Ventas' },
|
|
{ username: 'lider_ctg', email: 'lider.ctg@estelar.com', role: 'commercial_leader', hotelId: ctg.id, area: 'Ventas' },
|
|
{ username: 'lider_cali', email: 'lider.cali@estelar.com', role: 'commercial_leader', hotelId: cali.id, area: 'Ventas' },
|
|
|
|
// Collaborators
|
|
{ username: 'colab_bog_1', email: 'colab.bog1@estelar.com', role: 'collaborator', hotelId: p93.id, area: 'Ventas' },
|
|
{ username: 'colab_bog_2', email: 'colab.bog2@estelar.com', role: 'collaborator', hotelId: p93.id, area: 'Ventas' },
|
|
{ username: 'colab_mde_1', email: 'colab.mde1@estelar.com', role: 'collaborator', hotelId: mde.id, area: 'Ventas' },
|
|
{ username: 'colab_mde_2', email: 'colab.mde2@estelar.com', role: 'collaborator', hotelId: mde.id, area: 'Ventas' },
|
|
{ username: 'colaborador_mde', email: 'colaborador.mde@estelar.com', role: 'collaborator', hotelId: mde.id, area: 'Ventas' },
|
|
{ username: 'colab_ctg_1', email: 'colab.ctg1@estelar.com', role: 'collaborator', hotelId: ctg.id, area: 'Ventas' },
|
|
{ username: 'colab_ctg_2', email: 'colab.ctg2@estelar.com', role: 'collaborator', hotelId: ctg.id, area: 'Ventas' },
|
|
{ username: 'colab_cali_1', email: 'colab.cali1@estelar.com', role: 'collaborator', hotelId: cali.id, area: 'Ventas' },
|
|
{ username: 'colab_cali_2', email: 'colab.cali2@estelar.com', role: 'collaborator', hotelId: cali.id, area: 'Ventas' }
|
|
];
|
|
|
|
const seededUsers = {};
|
|
for (const u of usersToSeed) {
|
|
const created = await prisma.user.create({
|
|
data: {
|
|
username: u.username,
|
|
email: u.email,
|
|
passwordHash: PASSWORD_HASH,
|
|
role: u.role,
|
|
hotelId: u.hotelId,
|
|
area: u.area,
|
|
status: 'ACTIVE'
|
|
}
|
|
});
|
|
seededUsers[u.username] = created;
|
|
}
|
|
|
|
// 5. Seed Compensation Plans & Rules
|
|
console.log("Seeding compensation plans...");
|
|
// Plan Q1 (Percentage, Inactive)
|
|
const planQ1 = await prisma.compensationPlan.create({
|
|
data: {
|
|
name: 'Plan Comisiones Q1 - Porcentaje Fijo',
|
|
code: 'PLAN-2026-Q1',
|
|
validityStart: new Date('2026-01-01'),
|
|
validityEnd: new Date('2026-03-31'),
|
|
type: 'PERCENTAGE',
|
|
percentageRate: 0.025, // 2.5%
|
|
maxCap: 15000.00,
|
|
status: 'INACTIVE',
|
|
version: 1,
|
|
createdBy: seededUsers['admin'].id
|
|
}
|
|
});
|
|
|
|
// Plan Q2 (Scale, Active)
|
|
const planQ2 = await prisma.compensationPlan.create({
|
|
data: {
|
|
name: 'Plan Comisiones Q2 - Escala Escalonada',
|
|
code: 'PLAN-2026-Q2',
|
|
validityStart: new Date('2026-04-01'),
|
|
validityEnd: new Date('2026-06-30'),
|
|
type: 'SCALE',
|
|
status: 'ACTIVE',
|
|
version: 1,
|
|
createdBy: seededUsers['admin'].id
|
|
}
|
|
});
|
|
|
|
// Calculation rules for Q2 Plan
|
|
console.log("Seeding calculation rules...");
|
|
await prisma.calculationRule.createMany({
|
|
data: [
|
|
{ planId: planQ2.id, type: 'TIER', minAchievement: 0.0, maxAchievement: 0.8, rate: 0.0, payoutAmount: 0.0 },
|
|
{ planId: planQ2.id, type: 'TIER', minAchievement: 0.8, maxAchievement: 1.0, rate: 0.02, payoutAmount: 100.0 },
|
|
{ planId: planQ2.id, type: 'TIER', minAchievement: 1.0, maxAchievement: 9.9, rate: 0.03, payoutAmount: 250.0 }
|
|
]
|
|
});
|
|
|
|
// 6. Seed Goals, Sales Results and Settlements
|
|
console.log("Seeding goals, sales, and settlements...");
|
|
const collaborators = [
|
|
'colab_bog_1', 'colab_bog_2',
|
|
'colab_mde_1', 'colab_mde_2', 'colaborador_mde',
|
|
'colab_ctg_1', 'colab_ctg_2',
|
|
'colab_cali_1', 'colab_cali_2'
|
|
];
|
|
|
|
const periods = ['2026-01', '2026-02', '2026-03', '2026-04', '2026-05', '2026-06'];
|
|
|
|
// Seed structured sales, goals and settlements month-by-month
|
|
for (const period of periods) {
|
|
const isQ2 = period >= '2026-04';
|
|
const plan = isQ2 ? planQ2 : planQ1;
|
|
|
|
for (const username of collaborators) {
|
|
const user = seededUsers[username];
|
|
|
|
// Goal Setup
|
|
let goalAmount = 70000.00;
|
|
if (username.endsWith('2')) goalAmount = 60000.00;
|
|
if (username === 'colaborador_mde') goalAmount = 80000.00;
|
|
|
|
// Random variance for each user and month
|
|
const hashVal = (username.length + period.charCodeAt(6)) % 10;
|
|
const achievementMultiplier = 0.75 + (hashVal * 0.04); // ranges from 0.75 to 1.15
|
|
const salesAmount = goalAmount * achievementMultiplier;
|
|
|
|
// Create Goal
|
|
const goal = await prisma.goal.create({
|
|
data: {
|
|
targetType: 'INDIVIDUAL',
|
|
targetId: user.id,
|
|
period,
|
|
amount: goalAmount
|
|
}
|
|
});
|
|
|
|
// Create Sales Entries
|
|
const entryCount = 4;
|
|
const amountPerEntry = salesAmount / entryCount;
|
|
const salesCountPerEntry = Math.round(5 + hashVal / 2);
|
|
|
|
for (let i = 0; i < entryCount; i++) {
|
|
// Build unique idempotency key
|
|
const idempotencyKey = `sale-${user.id}-${period}-${i}`;
|
|
|
|
// Seed normal sale
|
|
await prisma.salesResult.create({
|
|
data: {
|
|
source: 'EXCEL',
|
|
hotelId: user.hotelId,
|
|
userId: user.id,
|
|
period,
|
|
amount: amountPerEntry,
|
|
salesCount: salesCountPerEntry,
|
|
status: 'PROCESSED',
|
|
idempotencyKey,
|
|
uploadedBy: seededUsers['gerente_mde'].id, // Seed uploaded by MDE manager for convenience
|
|
isAnomaly: false
|
|
}
|
|
});
|
|
}
|
|
|
|
// Add a flagged outlier anomaly for colab_bog_1 in March
|
|
if (username === 'colab_bog_1' && period === '2026-03') {
|
|
await prisma.salesResult.create({
|
|
data: {
|
|
source: 'EXCEL',
|
|
hotelId: user.hotelId,
|
|
userId: user.id,
|
|
period,
|
|
amount: 85000.00,
|
|
salesCount: 45,
|
|
status: 'PROCESSED',
|
|
idempotencyKey: `anomaly-outlier-bog1-mar`,
|
|
uploadedBy: seededUsers['gerente_bog'].id,
|
|
isAnomaly: true,
|
|
flaggedReason: {
|
|
en: "Outlier transaction: Sale amount exceeds standard region limits.",
|
|
es: "Transacción atípica: El monto de venta supera los límites regionales estándar."
|
|
}
|
|
}
|
|
});
|
|
}
|
|
|
|
// Compute Commission & Bonus
|
|
let commission = 0;
|
|
let bonus = 0;
|
|
const achievement = salesAmount / goalAmount;
|
|
|
|
if (!isQ2) {
|
|
// Percentage plan: 2.5% of total sales
|
|
commission = salesAmount * 0.025;
|
|
bonus = 0;
|
|
} else {
|
|
// Scale plan rules
|
|
if (achievement >= 0.8 && achievement < 1.0) {
|
|
commission = salesAmount * 0.02;
|
|
bonus = 100.00;
|
|
} else if (achievement >= 1.0) {
|
|
commission = salesAmount * 0.03;
|
|
bonus = 250.00;
|
|
}
|
|
}
|
|
|
|
// Retroactive Clawback deduction for colab_mde_1 in May 2026
|
|
let adjustmentAmount = 0.00;
|
|
let adjustmentNotes = null;
|
|
let originalSettlementId = null;
|
|
|
|
if (username === 'colab_mde_1' && period === '2026-05') {
|
|
// Clawback applied for returned sale of $15000 in March 2026 (PLAN Q1 rate: 2.5%)
|
|
adjustmentAmount = -375.00;
|
|
adjustmentNotes = "Deducción de retroactivo: Devolución de reserva de ventas de marzo (PLAN-2026-Q1).";
|
|
|
|
// Find their March settlement to link as original
|
|
const marchSettlement = await prisma.settlement.findFirst({
|
|
where: { userId: user.id, period: '2026-03' }
|
|
});
|
|
if (marchSettlement) {
|
|
originalSettlementId = marchSettlement.id;
|
|
}
|
|
}
|
|
|
|
const totalPayout = commission + bonus + adjustmentAmount;
|
|
|
|
// Settlement Status: APPROVED for Jan-May, PENDING for June
|
|
const isJune = period === '2026-06';
|
|
const settlementStatus = isJune ? 'PENDING' : 'APPROVED';
|
|
|
|
const managerMap = {
|
|
[p93.id]: seededUsers['gerente_bog'].id,
|
|
[mde.id]: seededUsers['gerente_mde'].id,
|
|
[ctg.id]: seededUsers['gerente_ctg'].id,
|
|
[cali.id]: seededUsers['gerente_cali'].id
|
|
};
|
|
|
|
await prisma.settlement.create({
|
|
data: {
|
|
period,
|
|
planId: plan.id,
|
|
userId: user.id,
|
|
salesAmount,
|
|
goalAmount,
|
|
achievementPercentage: achievement,
|
|
calculatedCommission: commission,
|
|
calculatedBonus: bonus,
|
|
adjustmentAmount,
|
|
totalPayout,
|
|
status: settlementStatus,
|
|
approvedBy: isJune ? null : managerMap[user.hotelId],
|
|
approvedAt: isJune ? null : new Date(`${period}-05T14:30:00Z`),
|
|
originalSettlementId,
|
|
adjustmentNotes,
|
|
aiAudited: true,
|
|
aiAuditNotes: {
|
|
en: isJune
|
|
? "Calculated under PLAN-2026-Q2. Validation checks successfully completed."
|
|
: "Compliant commission settlement. Payout successfully audited.",
|
|
es: isJune
|
|
? "Calculado bajo el PLAN-2026-Q2. Revisiones de validación completadas exitosamente."
|
|
: "Liquidación de comisiones conforme. Pago auditado correctamente."
|
|
}
|
|
}
|
|
});
|
|
}
|
|
}
|
|
|
|
// 7. Seed Audit Logs Trail
|
|
console.log("Seeding audit logs...");
|
|
const logs = [
|
|
{ userId: seededUsers['admin'].id, action: 'LOGIN', targetTable: 'users', targetId: seededUsers['admin'].id, prev: null, next: null, ip: '::1', date: '2026-01-02T09:00:00Z' },
|
|
{ userId: seededUsers['admin'].id, action: 'CREATE', targetTable: 'compensation_plans', targetId: planQ1.id, prev: null, next: { id: planQ1.id, code: planQ1.code, status: 'DRAFT' }, ip: '127.0.0.1', date: '2026-01-02T09:15:00Z' },
|
|
{ userId: seededUsers['admin'].id, action: 'UPDATE', targetTable: 'compensation_plans', targetId: planQ1.id, prev: { status: 'DRAFT' }, next: { status: 'ACTIVE' }, ip: '127.0.0.1', date: '2026-01-05T10:00:00Z' },
|
|
{ userId: seededUsers['gerente_mde'].id, action: 'LOGIN', targetTable: 'users', targetId: seededUsers['gerente_mde'].id, prev: null, next: null, ip: '10.8.0.5', date: '2026-02-05T08:30:00Z' },
|
|
{ userId: seededUsers['gerente_mde'].id, action: 'APPROVE', targetTable: 'settlements', targetId: 1, prev: { status: 'PENDING' }, next: { status: 'APPROVED' }, ip: '10.8.0.5', date: '2026-02-05T08:45:00Z' },
|
|
{ userId: seededUsers['gerente_bog'].id, action: 'LOGIN', targetTable: 'users', targetId: seededUsers['gerente_bog'].id, prev: null, next: null, ip: '10.8.0.6', date: '2026-02-05T09:12:00Z' },
|
|
{ userId: seededUsers['gerente_bog'].id, action: 'APPROVE', targetTable: 'settlements', targetId: 2, prev: { status: 'PENDING' }, next: { status: 'APPROVED' }, ip: '10.8.0.6', date: '2026-02-05T09:18:00Z' }
|
|
];
|
|
|
|
for (const log of logs) {
|
|
await prisma.auditLog.create({
|
|
data: {
|
|
userId: log.userId,
|
|
action: log.action,
|
|
targetTable: log.targetTable,
|
|
targetId: log.targetId,
|
|
previousValue: log.prev,
|
|
newValue: log.next,
|
|
ipAddress: log.ip,
|
|
createdAt: new Date(log.date)
|
|
}
|
|
});
|
|
}
|
|
|
|
// 8. Re-enable Row-Level Security Policies via SQL
|
|
console.log("Configuring database RLS boundaries...");
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "regions" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "hotels" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "goals" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "sales_results" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "settlements" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "audit_logs" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "compensation_plans" ENABLE ROW LEVEL SECURITY;`);
|
|
await prisma.$executeRawUnsafe(`ALTER TABLE "calculation_rules" ENABLE ROW LEVEL SECURITY;`);
|
|
|
|
// Disconnect temporary prisma connection before running manual execute
|
|
await prisma.$disconnect();
|
|
|
|
console.log("Walkthrough seeding completed successfully!");
|
|
}
|
|
|
|
main()
|
|
.catch(async (e) => {
|
|
console.error("Error during walkthrough seeding:", e);
|
|
process.exit(1);
|
|
});
|