info('Starting migration: media_cooperation → cooperations...');
$legacyConn = DB::connection('mysql_second');
$query = $legacyConn->table('media_cooperation')->whereNull('deleted_at');
try {
$totalCount = $query->count();
} catch (\Exception $e) {
$this->error('Table media_cooperation not found or error accessing legacy database.');
$this->error($e->getMessage());
return;
}
if ($totalCount === 0) {
$this->warn('No media_cooperation found in legacy database.');
return;
}
$this->info("Found {$totalCount} legacy media_cooperation records.");
// Pre-cache migrated cooperation titles
$this->migratedTitles = DB::table('cooperations')
->pluck('title')
->map(fn ($t) => strtolower(trim($t)))
->flip()
->all();
// Pre-cache proposal media
$this->migratedProposalIds = DB::table('media')
->where('model_type', CooperationProposal::class)
->where('collection_name', 'proposals')
->pluck('model_id')
->flip()
->all();
// Pre-load User to Partner Media mapping
$this->loadUserToMediaMap();
// Load all records and group by name
$all = $query->orderBy('id')->get();
$groups = $all->groupBy(fn ($item) => strtolower(trim($item->name)));
$this->info("Grouped into {$groups->count()} unique cooperation group(s).");
foreach ($groups as $groupKey => $group) {
$this->processGroup($groupKey, $group);
}
$this->newLine();
$this->info('media_cooperation migration completed successfully!');
}
/**
* Load mapping from User ID to Partner Media ID.
* Path: User -> Company -> PartnerMedia
*/
private function loadUserToMediaMap(): void
{
$this->userToMediaMap = DB::table('partner_media')
->join('companies', 'partner_media.company_id', '=', 'companies.id')
->whereNotNull('companies.user_id')
->pluck('partner_media.id', 'companies.user_id')
->all();
}
/**
* Process a single group of media_cooperation records.
*/
private function processGroup(string $groupKey, Collection $group): void
{
$first = $group->first();
$label = Str::limit($first->name, 40);
$this->output->write("[group] Processing: {$label}... ");
try {
// Check if cooperation already exists by title
$existingCooperation = DB::table('cooperations')
->whereRaw('LOWER(TRIM(title)) = ?', [$groupKey])
->first();
if ($existingCooperation) {
$cooperationId = $existingCooperation->id;
$this->output->write('JOINING EXISTING... ');
} else {
$cooperationId = $this->insertCooperation(
name: $first->name,
description: $first->description,
initialDate: $group->min('begin'),
finalDate: $group->max('end'),
status: CooperationStatus::COMPLETED->value,
);
$this->output->write('CREATED NEW... ');
}
// Optional: insert cooperation_media if the legacy table has a 'media' column with IDs
if (isset($first->media)) {
$this->insertCooperationMedia($group, $cooperationId);
}
// Migrate proposals belonging to this group's media_cooperation
$this->migrateProposals(
group: $group,
cooperationId: $cooperationId
);
$this->output->writeln('DONE');
} catch (\Exception $e) {
$this->output->writeln('FAILED: '.$e->getMessage().'');
}
}
private function insertCooperation(
string $name,
?string $description,
?string $initialDate,
?string $finalDate,
string $status
): int {
DB::table('cooperations')->insert([
'title' => $name,
'initial_submission_date' => $initialDate,
'final_submission_date' => $finalDate,
'description' => $description ?? '',
'status' => $status,
'created_at' => now(),
'updated_at' => now(),
]);
return (int) DB::getPdo()->lastInsertId();
}
private function insertCooperationMedia(Collection $group, int $cooperationId): void
{
$validMediaIds = DB::table('partner_media')->pluck('id')->toArray();
$mediaStatusMap = [];
foreach ($group as $item) {
if (empty($item->media)) {
continue;
}
$ids = array_map('trim', explode(',', $item->media));
$statuses = array_map('trim', explode(',', $item->status ?? ''));
foreach ($ids as $index => $rawId) {
$mediaId = (int) $rawId;
if (! in_array($mediaId, $validMediaIds)) {
continue;
}
$legacyStatus = $statuses[$index] ?? '1';
$mediaStatusMap[$mediaId] = $this->mapApprovalStatus($legacyStatus);
}
}
foreach ($mediaStatusMap as $mediaId => $approvalStatus) {
DB::table('cooperation_media')->insert([
'cooperation_id' => $cooperationId,
'partner_media_id' => $mediaId,
'status' => $approvalStatus,
'created_at' => now(),
'updated_at' => now(),
]);
}
}
private function migrateProposals(Collection $group, int $cooperationId): void
{
$legacyConn = DB::connection('mysql_second');
$legacyIds = $group->pluck('id')->toArray();
$legacyProposals = $legacyConn->table('propose_media_cooperation')
->whereIn('media_cooperation_id', $legacyIds)
->whereNull('deleted_at')
->get();
foreach ($legacyProposals as $legacyProposal) {
$this->processProposal($legacyProposal, $cooperationId);
}
}
private function processProposal(object $legacy, int $cooperationId): void
{
$userId = $legacy->enhancer;
$partnerMediaId = $this->userToMediaMap[$userId] ?? null;
if (! $partnerMediaId) {
// Fallback or skip if media not found
return;
}
// Check if this proposal already exists to avoid duplication
$exists = DB::table('cooperation_proposals')
->where('cooperation_id', $cooperationId)
->where('partner_media_id', $partnerMediaId)
->exists();
if ($exists) {
return;
}
DB::table('cooperation_proposals')->insert([
'cooperation_id' => $cooperationId,
'partner_media_id' => $partnerMediaId,
'e_catalog' => $legacy->e_catalog ?? null,
'status' => $this->mapApprovalStatus((string) $legacy->status),
'submitted_at' => $legacy->created_at ?? now(),
'created_at' => $legacy->created_at ?? now(),
'updated_at' => $legacy->updated_at ?? now(),
]);
$proposalId = (int) DB::getPdo()->lastInsertId();
// Migrate offer_file if exists and not already migrated
if (! empty($legacy->offer_file) && ! isset($this->migratedProposalIds[$proposalId])) {
$this->importOfferFile($proposalId, $legacy);
}
}
private function importOfferFile(int $proposalId, object $legacy): void
{
$legacyPath = trim($legacy->offer_file);
if (empty($legacyPath) || $legacyPath === '-' || $legacyPath === 'no-image.png') {
return;
}
// Adjust path based on where these files are stored in S3
$sourcePath = 'old-data/mediacooperations/'.$legacyPath;
$fileName = basename($sourcePath);
$legacyDir = dirname($sourcePath);
DB::table('media')->insert([
'model_type' => CooperationProposal::class,
'model_id' => $proposalId,
'uuid' => (string) Str::uuid(),
'collection_name' => 'cooperations',
'name' => pathinfo($fileName, PATHINFO_FILENAME),
'file_name' => $fileName,
'mime_type' => $this->guessMime($fileName),
'disk' => 's3',
'conversions_disk' => 's3',
'size' => 0,
'manipulations' => '[]',
'custom_properties' => json_encode([
'feature' => 'cooperations',
'date' => Carbon::parse($legacy->created_at ?? now())->toDateString(),
'doc_type' => 'proposal-attachment',
'legacy_dir' => $legacyDir,
]),
'generated_conversions' => '[]',
'responsive_images' => '[]',
'order_column' => 1,
'created_at' => now(),
'updated_at' => now(),
]);
}
private function guessMime(string $fileName): string
{
return match (strtolower(pathinfo($fileName, PATHINFO_EXTENSION))) {
'pdf' => 'application/pdf',
'jpg', 'jpeg' => 'image/jpeg',
'png' => 'image/png',
'doc' => 'application/msword',
'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
default => 'application/octet-stream',
};
}
private function mapApprovalStatus(string $legacyStatus): int
{
return match ($legacyStatus) {
'0' => ApprovalStatus::PENDING->value,
'1' => ApprovalStatus::ACCEPTED->value,
'2' => ApprovalStatus::REJECTED->value,
default => ApprovalStatus::ACCEPTED->value,
};
}
}