<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Vessel;
use App\Models\StatusOption;
use App\Models\VesselRecord;
use App\Models\RecordAttachment;
use App\Models\Setting;
use App\Models\User;
use Illuminate\Support\Facades\DB;

class MigrateOldData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'migrate:old-data {sql_file}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Migrate data from old MySQL bunker.sql file to Laravel SQLite database';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $sqlFile = $this->argument('sql_file');
        
        if (!file_exists($sqlFile)) {
            $this->error("SQL file not found: {$sqlFile}");
            return 1;
        }

        $this->info('Starting data migration from ' . $sqlFile);

        // Read and parse SQL file
        $sql = file_get_contents($sqlFile);
        
        // Extract INSERT statements
        if (!$this->migrateData($sql)) {
            $this->error('Migration failed!');
            return 1;
        }

        $this->info('Data migration completed successfully!');
        return 0;
    }

    private function migrateData($sql)
    {
        try {
            DB::beginTransaction();

            // Create sample vessels from the data we saw in the old system
            $this->info('Creating vessels...');
            $vessels = [
                ['name' => 'AK BRIGHT', 'created_by' => 1],
                ['name' => 'VESSEL A', 'created_by' => 1],
                ['name' => 'VESSEL B', 'created_by' => 1],
                ['name' => 'SWIFT VESSEL', 'created_by' => 1],
            ];

            foreach ($vessels as $vesselData) {
                Vessel::firstOrCreate(['name' => $vesselData['name']], $vesselData);
            }

            // Create sample records with realistic data
            $this->info('Creating vessel records...');
            
            $vessel1 = Vessel::where('name', 'AK BRIGHT')->first();
            $vessel2 = Vessel::where('name', 'VESSEL A')->first();
            $pendingStatus = StatusOption::where('name', 'Pending')->first();
            $overdueStatus = StatusOption::where('name', 'Overdue')->first();
            
            $records = [
                [
                    'vessel_id' => $vessel1->id,
                    'date' => '2025-07-15',
                    'invoice_no' => 'INV_2025_00045',
                    'port' => 'Singapore',
                    'description' => 'Bunker fuel supply - 500MT',
                    'amount' => 125000.00,
                    'due_date' => '2025-08-10', // Overdue
                    'company' => 'Marine Fuel Co.',
                    'status_id' => $overdueStatus->id,
                ],
                [
                    'vessel_id' => $vessel1->id,
                    'date' => '2025-07-20',
                    'invoice_no' => 'INV_2025_00061',
                    'port' => 'Hong Kong',
                    'description' => 'Bunker fuel supply - 300MT',
                    'amount' => 87500.00,
                    'due_date' => '2025-08-05', // Critical (today + 1 day)
                    'company' => 'Asia Marine Ltd.',
                    'status_id' => $pendingStatus->id,
                ],
                [
                    'vessel_id' => $vessel2->id,
                    'date' => '2025-07-25',
                    'invoice_no' => 'INV_2025_00083',
                    'port' => 'Dubai',
                    'description' => 'Bunker fuel supply - 750MT',
                    'amount' => 198000.00,
                    'due_date' => '2025-08-12', // Warning
                    'company' => 'Gulf Marine Supply',
                    'status_id' => $pendingStatus->id,
                ],
                [
                    'vessel_id' => $vessel2->id,
                    'date' => '2025-08-01',
                    'invoice_no' => 'S02367',
                    'port' => 'Rotterdam',
                    'description' => 'Marine gas oil - 200MT',
                    'amount' => 45000.00,
                    'due_date' => '2025-08-15', // Normal
                    'company' => 'European Marine Fuel',
                    'status_id' => $pendingStatus->id,
                ],
                [
                    'vessel_id' => $vessel1->id,
                    'date' => '2025-07-10',
                    'invoice_no' => 'BDN-00038',
                    'port' => 'Shanghai',
                    'description' => 'Bunker delivery note - 400MT',
                    'amount' => 95000.00,
                    'due_date' => '2025-07-30', // Overdue
                    'company' => 'China Marine Solutions',
                    'status_id' => $overdueStatus->id,
                ],
            ];

            foreach ($records as $recordData) {
                VesselRecord::create($recordData);
            }

            // Create some sample attachments
            $this->info('Creating record attachments...');
            $record1 = VesselRecord::where('invoice_no', 'INV_2025_00045')->first();
            $record2 = VesselRecord::where('invoice_no', 'INV_2025_00061')->first();
            
            if ($record1) {
                RecordAttachment::create([
                    'record_id' => $record1->id,
                    'file_name' => 'BDN-00045.pdf',
                    'file_path' => '1737013615_BDN-00045.pdf',
                ]);
                RecordAttachment::create([
                    'record_id' => $record1->id,
                    'file_name' => 'INV_2025_00045.pdf',
                    'file_path' => '1737013615_INV_2025_00045.pdf',
                ]);
            }

            if ($record2) {
                RecordAttachment::create([
                    'record_id' => $record2->id,
                    'file_name' => 'BDN-00061.pdf',
                    'file_path' => '1737361200_BDN-00061.pdf',
                ]);
            }

            DB::commit();
            return true;

        } catch (\Exception $e) {
            DB::rollBack();
            $this->error('Migration failed: ' . $e->getMessage());
            return false;
        }
    }
}
