<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_Version_123 extends CI_Migration{


    public function up() {

        // Truncate the templates table
        $this->db->truncate('tbl_crmcon_email_builder');
        $this->db->truncate('tblemailtemplates');
        $templine = '';
        $etempline = '';
        
        // Check if the dump.sql file exists
        $lines = file(FCPATH . 'application/migrations/files/tbl_crmcon_email_builder.sql');
        $dump_sql_file = FCPATH . 'application/migrations/files/tbl_crmcon_email_builder.sql';

        // Email Templates
        $elines = file(FCPATH . 'application/migrations/files/branchemailtemplates.sql');
        $edump_sql_file = FCPATH . 'application/migrations/files/branchemailtemplates.sql'; // skipped for check atm

        // Check if the dump  file exists and proceed with the query on lines loop
        if (file_exists($dump_sql_file)) {
            
            // Loop lines
            foreach($lines as $line) {
                // Skip comments if there are any
                if (substr($line, 0, 2) == '--' || $line == '')
                    continue;

                $templine.= $line;
        
                // If it has a semicolon at the end, it's the end of the query so can process this templine
                if (substr(trim($line), -1, 1) == ';') {
                    // Perform the query
                    $this->db->query($templine);
                    // Reset temp variable to empty
                    $templine = '';
                }
            }
        }

        // Get all crmcon results
        $query = $this->db->get('tbl_crmcon_email_builder');
        $results = $query->result_array();

        // Loop through the results
        foreach ($results as $result) {

            $emailtemplateInsert = $this->findEmailTemplateById($elines, $result['emailtemplateid']);

            if ($emailtemplateInsert !== null) {
                $emailtemplateInsert = preg_replace("/\bVALUES\s*\(\s*\d+\s*,\s*/i", "VALUES(NULL, ", $emailtemplateInsert, 1);

                
                if (substr($emailtemplateInsert, 0, 2) == '--' || $emailtemplateInsert == '')
                    continue;

                $etempline.= $emailtemplateInsert;
                
                if (substr(trim($emailtemplateInsert), -1, 1) == ';') {
                    // Perform the query
                    $this->db->query($etempline);
                    // Reset temp variable to empty
                    $etempline = '';
                }

                // Get inserted
                $lineID = $this->db->insert_id();
                $result['emailtemplateid'] = $lineID;
                
                // make sure we have id
                if (!isset($result['id']) || empty($result['id'])) {
                    echo "Error:missing id." . PHP_EOL;
                    continue;
                }

                // Update crmcon item with emailtemplate
                $this->db->where('id', $result['id']);
                $this->db->update('tbl_crmcon_email_builder', $result);
            }
        }
    }

	public function down()	{

	}

    /**
     * Return from file emailtemplateid line
     */
    function findEmailTemplateById($elines, $emailtemplateid) {
        foreach ($elines as $insert) {
            preg_match("/INSERT INTO `tblemailtemplates` VALUES\((\d+),/", $insert, $matches);
            if (isset($matches[1])) {
                $foundEmailtemplateid = $matches[1];
                if ($foundEmailtemplateid == $emailtemplateid) {
                    return $insert;
                }
            }
        }
        return null; // Return null if no match is found
    }
}