519 lines
14 KiB
TypeScript
519 lines
14 KiB
TypeScript
import { PrismaClient, CompetitionCategory, OceanIssue, TeamMemberRole } from '@prisma/client'
|
|
import * as fs from 'fs'
|
|
import * as path from 'path'
|
|
import { fileURLToPath } from 'url'
|
|
import Papa from 'papaparse'
|
|
|
|
const __filename = fileURLToPath(import.meta.url)
|
|
const __dirname = path.dirname(__filename)
|
|
|
|
const prisma = new PrismaClient()
|
|
|
|
// CSV Column Mapping
|
|
interface CandidatureRow {
|
|
'Full name': string
|
|
'Application status': string
|
|
'Category': string
|
|
'Comment ': string // Note the space after 'Comment'
|
|
'Country': string
|
|
'Date of creation': string
|
|
'E-mail': string
|
|
'How did you hear about MOPC?': string
|
|
'Issue': string
|
|
'Jury 1 attribués': string
|
|
'MOPC team comments': string
|
|
'Mentorship': string
|
|
'PHASE 1 - Submission': string
|
|
'PHASE 2 - Submission': string
|
|
"Project's name": string
|
|
'Team members': string
|
|
'Tri par zone': string
|
|
'Téléphone': string
|
|
'University': string
|
|
}
|
|
|
|
// Map CSV category strings to enum values
|
|
function mapCategory(category: string): CompetitionCategory | null {
|
|
if (!category) return null
|
|
const lower = category.toLowerCase()
|
|
if (lower.includes('start-up') || lower.includes('startup')) {
|
|
return 'STARTUP'
|
|
}
|
|
if (lower.includes('business concept')) {
|
|
return 'BUSINESS_CONCEPT'
|
|
}
|
|
return null
|
|
}
|
|
|
|
// Map CSV issue strings to enum values
|
|
function mapOceanIssue(issue: string): OceanIssue | null {
|
|
if (!issue) return null
|
|
const lower = issue.toLowerCase()
|
|
|
|
if (lower.includes('pollution')) return 'POLLUTION_REDUCTION'
|
|
if (lower.includes('climate') || lower.includes('sea-level')) return 'CLIMATE_MITIGATION'
|
|
if (lower.includes('technology') || lower.includes('innovation')) return 'TECHNOLOGY_INNOVATION'
|
|
if (lower.includes('shipping') || lower.includes('yachting')) return 'SUSTAINABLE_SHIPPING'
|
|
if (lower.includes('blue carbon')) return 'BLUE_CARBON'
|
|
if (lower.includes('habitat') || lower.includes('restoration') || lower.includes('ecosystem')) return 'HABITAT_RESTORATION'
|
|
if (lower.includes('community') || lower.includes('capacity') || lower.includes('coastal')) return 'COMMUNITY_CAPACITY'
|
|
if (lower.includes('fishing') || lower.includes('aquaculture') || lower.includes('blue food')) return 'SUSTAINABLE_FISHING'
|
|
if (lower.includes('awareness') || lower.includes('education') || lower.includes('consumer')) return 'CONSUMER_AWARENESS'
|
|
if (lower.includes('acidification')) return 'OCEAN_ACIDIFICATION'
|
|
|
|
return 'OTHER'
|
|
}
|
|
|
|
// Parse team members string into array
|
|
function parseTeamMembers(teamMembersStr: string): { name: string; email?: string }[] {
|
|
if (!teamMembersStr) return []
|
|
|
|
// Split by comma or semicolon
|
|
const members = teamMembersStr.split(/[,;]/).map((m) => m.trim()).filter(Boolean)
|
|
|
|
return members.map((name) => ({
|
|
name: name.trim(),
|
|
// No emails in CSV, just names with titles
|
|
}))
|
|
}
|
|
|
|
// Extract country code from location string or return ISO code directly
|
|
function extractCountry(location: string): string | null {
|
|
if (!location) return null
|
|
|
|
// If already a 2-letter ISO code, return it directly
|
|
const trimmed = location.trim()
|
|
if (/^[A-Z]{2}$/.test(trimmed)) return trimmed
|
|
|
|
// Common country mappings from the CSV data
|
|
const countryMappings: Record<string, string> = {
|
|
'tunisie': 'TN',
|
|
'tunisia': 'TN',
|
|
'royaume-uni': 'GB',
|
|
'uk': 'GB',
|
|
'united kingdom': 'GB',
|
|
'angleterre': 'GB',
|
|
'england': 'GB',
|
|
'espagne': 'ES',
|
|
'spain': 'ES',
|
|
'inde': 'IN',
|
|
'india': 'IN',
|
|
'france': 'FR',
|
|
'états-unis': 'US',
|
|
'usa': 'US',
|
|
'united states': 'US',
|
|
'allemagne': 'DE',
|
|
'germany': 'DE',
|
|
'italie': 'IT',
|
|
'italy': 'IT',
|
|
'portugal': 'PT',
|
|
'monaco': 'MC',
|
|
'suisse': 'CH',
|
|
'switzerland': 'CH',
|
|
'belgique': 'BE',
|
|
'belgium': 'BE',
|
|
'pays-bas': 'NL',
|
|
'netherlands': 'NL',
|
|
'australia': 'AU',
|
|
'australie': 'AU',
|
|
'japon': 'JP',
|
|
'japan': 'JP',
|
|
'chine': 'CN',
|
|
'china': 'CN',
|
|
'brésil': 'BR',
|
|
'brazil': 'BR',
|
|
'mexique': 'MX',
|
|
'mexico': 'MX',
|
|
'canada': 'CA',
|
|
'maroc': 'MA',
|
|
'morocco': 'MA',
|
|
'egypte': 'EG',
|
|
'egypt': 'EG',
|
|
'afrique du sud': 'ZA',
|
|
'south africa': 'ZA',
|
|
'nigeria': 'NG',
|
|
'kenya': 'KE',
|
|
'ghana': 'GH',
|
|
'senegal': 'SN',
|
|
'sénégal': 'SN',
|
|
'côte d\'ivoire': 'CI',
|
|
'ivory coast': 'CI',
|
|
'indonesia': 'ID',
|
|
'indonésie': 'ID',
|
|
'philippines': 'PH',
|
|
'vietnam': 'VN',
|
|
'thaïlande': 'TH',
|
|
'thailand': 'TH',
|
|
'malaisie': 'MY',
|
|
'malaysia': 'MY',
|
|
'singapour': 'SG',
|
|
'singapore': 'SG',
|
|
'grèce': 'GR',
|
|
'greece': 'GR',
|
|
'turquie': 'TR',
|
|
'turkey': 'TR',
|
|
'pologne': 'PL',
|
|
'poland': 'PL',
|
|
'norvège': 'NO',
|
|
'norway': 'NO',
|
|
'suède': 'SE',
|
|
'sweden': 'SE',
|
|
'danemark': 'DK',
|
|
'denmark': 'DK',
|
|
'finlande': 'FI',
|
|
'finland': 'FI',
|
|
'irlande': 'IE',
|
|
'ireland': 'IE',
|
|
'autriche': 'AT',
|
|
'austria': 'AT',
|
|
// Additional mappings from CSV data (French names, accented variants)
|
|
'nigéria': 'NG',
|
|
'tanzanie': 'TZ',
|
|
'tanzania': 'TZ',
|
|
'ouganda': 'UG',
|
|
'uganda': 'UG',
|
|
'zambie': 'ZM',
|
|
'zambia': 'ZM',
|
|
'somalie': 'SO',
|
|
'somalia': 'SO',
|
|
'jordanie': 'JO',
|
|
'jordan': 'JO',
|
|
'bulgarie': 'BG',
|
|
'bulgaria': 'BG',
|
|
'indonesie': 'ID',
|
|
'macédoine du nord': 'MK',
|
|
'north macedonia': 'MK',
|
|
'jersey': 'JE',
|
|
'kazakhstan': 'KZ',
|
|
'cameroun': 'CM',
|
|
'cameroon': 'CM',
|
|
'vanuatu': 'VU',
|
|
'bénin': 'BJ',
|
|
'benin': 'BJ',
|
|
'argentine': 'AR',
|
|
'argentina': 'AR',
|
|
'srbija': 'RS',
|
|
'serbia': 'RS',
|
|
'kraljevo': 'RS',
|
|
'kosovo': 'XK',
|
|
'pristina': 'XK',
|
|
'xinjiang': 'CN',
|
|
'haïti': 'HT',
|
|
'haiti': 'HT',
|
|
'sri lanka': 'LK',
|
|
'luxembourg': 'LU',
|
|
'congo': 'CG',
|
|
'brazzaville': 'CG',
|
|
'colombie': 'CO',
|
|
'colombia': 'CO',
|
|
'bogota': 'CO',
|
|
'ukraine': 'UA',
|
|
}
|
|
|
|
const lower = location.toLowerCase()
|
|
|
|
for (const [key, code] of Object.entries(countryMappings)) {
|
|
if (lower.includes(key)) {
|
|
return code
|
|
}
|
|
}
|
|
|
|
return null
|
|
}
|
|
|
|
async function main() {
|
|
console.log('Starting candidatures import...\n')
|
|
|
|
// Read the CSV file
|
|
const csvPath = path.join(__dirname, '../docs/candidatures_2026.csv')
|
|
|
|
if (!fs.existsSync(csvPath)) {
|
|
console.error(`CSV file not found at ${csvPath}`)
|
|
process.exit(1)
|
|
}
|
|
|
|
const csvContent = fs.readFileSync(csvPath, 'utf-8')
|
|
|
|
// Parse CSV
|
|
const parseResult = Papa.parse<CandidatureRow>(csvContent, {
|
|
header: true,
|
|
skipEmptyLines: true,
|
|
})
|
|
|
|
if (parseResult.errors.length > 0) {
|
|
console.warn('CSV parsing warnings:', parseResult.errors)
|
|
}
|
|
|
|
const rows = parseResult.data
|
|
console.log(`Found ${rows.length} candidatures in CSV\n`)
|
|
|
|
// Get or create program
|
|
let program = await prisma.program.findFirst({
|
|
where: {
|
|
name: 'Monaco Ocean Protection Challenge',
|
|
year: 2026,
|
|
},
|
|
})
|
|
|
|
if (!program) {
|
|
program = await prisma.program.create({
|
|
data: {
|
|
name: 'Monaco Ocean Protection Challenge',
|
|
year: 2026,
|
|
status: 'ACTIVE',
|
|
description: 'The Monaco Ocean Protection Challenge is a flagship program promoting innovative solutions for ocean conservation.',
|
|
},
|
|
})
|
|
console.log('Created program:', program.name, program.year)
|
|
} else {
|
|
console.log('Using existing program:', program.name, program.year)
|
|
}
|
|
|
|
// Get or create Round 1
|
|
let round = await prisma.round.findFirst({
|
|
where: {
|
|
programId: program.id,
|
|
slug: 'mopc-2026-round-1',
|
|
},
|
|
})
|
|
|
|
if (!round) {
|
|
round = await prisma.round.create({
|
|
data: {
|
|
programId: program.id,
|
|
name: 'Round 1 - Semi-Finalists Selection',
|
|
slug: 'mopc-2026-round-1',
|
|
status: 'ACTIVE',
|
|
roundType: 'EVALUATION',
|
|
submissionStartDate: new Date('2025-09-01'),
|
|
submissionEndDate: new Date('2026-01-31'),
|
|
votingStartAt: new Date('2026-02-15'),
|
|
votingEndAt: new Date('2026-02-28'),
|
|
requiredReviews: 3,
|
|
settingsJson: {
|
|
gracePeriod: { hours: 24 },
|
|
allowLateSubmissions: true,
|
|
},
|
|
},
|
|
})
|
|
console.log('Created round:', round.name)
|
|
} else {
|
|
console.log('Using existing round:', round.name)
|
|
}
|
|
|
|
console.log('\nImporting candidatures...\n')
|
|
|
|
let imported = 0
|
|
let skipped = 0
|
|
let errors = 0
|
|
|
|
for (const row of rows) {
|
|
try {
|
|
const projectName = row["Project's name"]?.trim()
|
|
const email = row['E-mail']?.trim()
|
|
|
|
if (!projectName || !email) {
|
|
console.log(`Skipping row: missing project name or email`)
|
|
skipped++
|
|
continue
|
|
}
|
|
|
|
// Check if project already exists
|
|
const existingProject = await prisma.project.findFirst({
|
|
where: {
|
|
programId: program.id,
|
|
OR: [
|
|
{ title: projectName },
|
|
{ submittedByEmail: email },
|
|
],
|
|
},
|
|
})
|
|
|
|
if (existingProject) {
|
|
console.log(`Skipping duplicate: ${projectName} (${email})`)
|
|
skipped++
|
|
continue
|
|
}
|
|
|
|
// Get or create user
|
|
let user = await prisma.user.findUnique({
|
|
where: { email },
|
|
})
|
|
|
|
if (!user) {
|
|
user = await prisma.user.create({
|
|
data: {
|
|
email,
|
|
name: row['Full name']?.trim() || 'Unknown',
|
|
role: 'APPLICANT',
|
|
status: 'ACTIVE',
|
|
phoneNumber: row['Téléphone']?.trim() || null,
|
|
},
|
|
})
|
|
}
|
|
|
|
// Parse date
|
|
let submittedAt: Date | null = null
|
|
if (row['Date of creation']) {
|
|
const dateStr = row['Date of creation'].trim()
|
|
const parsed = new Date(dateStr)
|
|
if (!isNaN(parsed.getTime())) {
|
|
submittedAt = parsed
|
|
}
|
|
}
|
|
|
|
// Create project
|
|
const project = await prisma.project.create({
|
|
data: {
|
|
programId: program.id,
|
|
title: projectName,
|
|
description: row['Comment ']?.trim() || null,
|
|
competitionCategory: mapCategory(row['Category']),
|
|
oceanIssue: mapOceanIssue(row['Issue']),
|
|
country: extractCountry(row['Country']),
|
|
geographicZone: row['Tri par zone']?.trim() || null,
|
|
institution: row['University']?.trim() || null,
|
|
wantsMentorship: row['Mentorship']?.toLowerCase() === 'true',
|
|
phase1SubmissionUrl: row['PHASE 1 - Submission']?.trim() || null,
|
|
phase2SubmissionUrl: row['PHASE 2 - Submission']?.trim() || null,
|
|
referralSource: row['How did you hear about MOPC?']?.trim() || null,
|
|
applicationStatus: row['Application status']?.trim() || 'Received',
|
|
internalComments: row['MOPC team comments']?.trim() || null,
|
|
submissionSource: 'CSV',
|
|
submittedByEmail: email,
|
|
submittedByUserId: user.id,
|
|
submittedAt: submittedAt || new Date(),
|
|
metadataJson: {
|
|
importedFrom: 'candidatures_2026.csv',
|
|
importedAt: new Date().toISOString(),
|
|
originalPhone: row['Téléphone']?.trim(),
|
|
},
|
|
},
|
|
})
|
|
|
|
// Create round-project association
|
|
await prisma.roundProject.create({
|
|
data: {
|
|
roundId: round.id,
|
|
projectId: project.id,
|
|
status: 'SUBMITTED',
|
|
},
|
|
})
|
|
|
|
// Create team lead membership
|
|
await prisma.teamMember.create({
|
|
data: {
|
|
projectId: project.id,
|
|
userId: user.id,
|
|
role: 'LEAD',
|
|
title: 'Team Lead',
|
|
},
|
|
})
|
|
|
|
// Parse and create team members
|
|
const teamMembers = parseTeamMembers(row['Team members'])
|
|
const leadName = row['Full name']?.trim().toLowerCase()
|
|
|
|
for (const member of teamMembers) {
|
|
// Skip if it's the lead (already added)
|
|
if (member.name.toLowerCase() === leadName) continue
|
|
|
|
// Since we don't have emails for team members, we create placeholder accounts
|
|
// They can claim their accounts later
|
|
const memberEmail = `${member.name.toLowerCase().replace(/[^a-z0-9]/g, '.')}@pending.mopc.local`
|
|
|
|
let memberUser = await prisma.user.findUnique({
|
|
where: { email: memberEmail },
|
|
})
|
|
|
|
if (!memberUser) {
|
|
memberUser = await prisma.user.create({
|
|
data: {
|
|
email: memberEmail,
|
|
name: member.name,
|
|
role: 'APPLICANT',
|
|
status: 'INVITED',
|
|
metadataJson: {
|
|
isPendingEmailVerification: true,
|
|
originalName: member.name,
|
|
},
|
|
},
|
|
})
|
|
}
|
|
|
|
// Check if membership already exists
|
|
const existingMembership = await prisma.teamMember.findUnique({
|
|
where: {
|
|
projectId_userId: {
|
|
projectId: project.id,
|
|
userId: memberUser.id,
|
|
},
|
|
},
|
|
})
|
|
|
|
if (!existingMembership) {
|
|
await prisma.teamMember.create({
|
|
data: {
|
|
projectId: project.id,
|
|
userId: memberUser.id,
|
|
role: 'MEMBER',
|
|
},
|
|
})
|
|
}
|
|
}
|
|
|
|
console.log(`Imported: ${projectName} (${email}) - ${teamMembers.length} team members`)
|
|
imported++
|
|
} catch (err) {
|
|
console.error(`Error importing row:`, err)
|
|
errors++
|
|
}
|
|
}
|
|
|
|
// Backfill: update any existing projects with null country
|
|
console.log('\nBackfilling missing country codes...\n')
|
|
let backfilled = 0
|
|
const nullCountryProjects = await prisma.project.findMany({
|
|
where: { programId: program.id, country: null },
|
|
select: { id: true, submittedByEmail: true, title: true },
|
|
})
|
|
|
|
for (const project of nullCountryProjects) {
|
|
// Find the matching CSV row by email or title
|
|
const matchingRow = rows.find(
|
|
(r) =>
|
|
r['E-mail']?.trim() === project.submittedByEmail ||
|
|
r["Project's name"]?.trim() === project.title
|
|
)
|
|
if (matchingRow?.['Country']) {
|
|
const countryCode = extractCountry(matchingRow['Country'])
|
|
if (countryCode) {
|
|
await prisma.project.update({
|
|
where: { id: project.id },
|
|
data: { country: countryCode },
|
|
})
|
|
console.log(` Updated: ${project.title} → ${countryCode}`)
|
|
backfilled++
|
|
}
|
|
}
|
|
}
|
|
console.log(` Backfilled: ${backfilled} projects\n`)
|
|
|
|
console.log('\n========================================')
|
|
console.log(`Import complete!`)
|
|
console.log(` Imported: ${imported}`)
|
|
console.log(` Skipped: ${skipped}`)
|
|
console.log(` Errors: ${errors}`)
|
|
console.log(` Backfilled: ${backfilled}`)
|
|
console.log('========================================\n')
|
|
}
|
|
|
|
main()
|
|
.catch((e) => {
|
|
console.error(e)
|
|
process.exit(1)
|
|
})
|
|
.finally(async () => {
|
|
await prisma.$disconnect()
|
|
})
|