<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class AddressDataSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
{
    // Read the contents of the SQL file containing INSERT statements
    $sqlFile = base_path('database/sql/address_data.sql');

    if (File::exists($sqlFile)) {
        // Get the contents of the file
        $sql = File::get($sqlFile);

        // Split SQL statements by semicolon, in case there are multiple statements
        $statements = explode(';', $sql);

        // Fixed updated_at value (1-Jan-2024)
        $updatedAt = '2024-08-01 00:00:00';

        // Execute each statement one by one
        foreach ($statements as $statement) {
            $statement = trim($statement);
            if (!empty($statement)) {
                // Check if the statement is an INSERT statement
                if (stripos($statement, 'INSERT INTO') === 0) {
                    // Modify the INSERT statement to add the updated_at value
                    $statement = $this->addUpdatedAtToValues($statement, $updatedAt);
                }

                // Execute the modified or original statement
                DB::unprepared($statement . ';');
            }
        }
    } else {
        $this->command->info('SQL file does not exist: ' . $sqlFile);
    }
}

/**
 * Adds a static updated_at value to the VALUES clause of an INSERT SQL statement.
 *
 * @param string $statement
 * @param string $updatedAt
 * @return string
 */
private function addUpdatedAtToValues(string $statement, string $updatedAt): string
{
    // Use regex to match the VALUES clause and add the updated_at value
    $statement = preg_replace('/\)(\s*,\s*)?$/', ", '$updatedAt')", $statement);

    return $statement;
}
}
