<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_POST["btnUpdate"])) {
    $data = [
        'db_host' => $_POST['db_host'],
        'db_user' => $_POST['db_user'],
        'db_password' => $_POST['db_password'],
        'db_name' => $_POST['db_name']
    ];
    try {
        $connection = new mysqli($data['db_host'], $data['db_user'], $data['db_password'], $data['db_name']);
        if ($connection->connect_error) {
            $error = "Failed to connect to database, please check your database credentials!";
        } else {
            $connection->query("SET CHARACTER SET utf8mb4");
            $connection->query("SET NAMES utf8mb4");

            update($connection);
            $success = 'The update has been successfully completed! Please delete the "update_database.php" file.';
            $connection->close();
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}

function runQuery($sql)
{
    global $connection;
    $result = mysqli_query($connection, $sql);

    // IF THE QUERY FAILS, STOP EVERYTHING AND SHOW THE ERROR.
    if ($result === false) {
        die("SQL Error: " . mysqli_error($connection) . "<br><br>Failed Query:<br>" . $sql);
    }

    return $result;
}

function update($connection)
{
    updateFrom24To25($connection);
    updateFrom25To26($connection);
}

function updateFrom24To25($connection)
{
    $tblAffiliateEarnings = "CREATE TABLE `affiliate_earnings` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `referrer_id` int(11) DEFAULT NULL,
        `order_id` int(11) DEFAULT NULL,
        `product_id` int(11) DEFAULT NULL,
        `seller_id` int(11) DEFAULT NULL,
        `commission_rate` tinyint(4) DEFAULT NULL,
        `earned_amount` bigint(20) DEFAULT NULL,
        `currency` varchar(20) DEFAULT 'USD',
        `exchange_rate` double DEFAULT 1,
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblAffiliateLinks = "CREATE TABLE `affiliate_links` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `referrer_id` int(11) DEFAULT NULL,
        `product_id` int(11) DEFAULT NULL,
        `seller_id` int(11) DEFAULT NULL,
        `lang_id` int(11) DEFAULT NULL,
        `link_short` varchar(100) DEFAULT NULL,
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblPagesVendor = "CREATE TABLE `pages_vendor` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` int(11) DEFAULT NULL,
        `content_shop_policies` text DEFAULT NULL,
        `status_shop_policies` tinyint(1) DEFAULT 0
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblProductSearchIndexes = "CREATE TABLE `product_search_indexes` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `product_id` int(11) DEFAULT NULL,
        `lang_id` int(11) DEFAULT NULL,
        `search_index` varchar(1000) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblProductTags = "CREATE TABLE `product_tags` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `tag` varchar(255) DEFAULT NULL,
        `product_id` int(11) DEFAULT NULL,
        `lang_id` int(11) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblTaxes = "CREATE TABLE `taxes` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name_data` text DEFAULT NULL,
        `tax_rate` double NOT NULL,
        `is_all_countries` tinyint(1) DEFAULT 0,
        `country_ids` text DEFAULT NULL,
        `state_ids` text DEFAULT NULL,
        `product_sales` tinyint(1) DEFAULT 1,
        `service_payments` tinyint(1) DEFAULT 1,
        `status` tinyint(1) DEFAULT 1,
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblUserLoginActivities = "CREATE TABLE `user_login_activities` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` int(11) DEFAULT NULL,
        `ip_address` varchar(100) DEFAULT NULL,
        `user_agent` text DEFAULT NULL,
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblWalletDeposits = "CREATE TABLE `wallet_deposits` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` int(11) DEFAULT NULL,
        `payment_method` varchar(50) DEFAULT NULL,
        `payment_id` varchar(100) DEFAULT NULL,
        `deposit_amount` varchar(30) DEFAULT NULL,
        `currency` varchar(20) DEFAULT 'USD',
        `payment_status` tinyint(1) DEFAULT 0,
        `ip_address` varchar(100) DEFAULT NULL,
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    $tblWalletExpenses = "CREATE TABLE `wallet_expenses` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` int(11) DEFAULT NULL,
        `payment_id` varchar(100) DEFAULT NULL,
        `expense_item_id` varchar(30) DEFAULT NULL,
        `expense_type` varchar(255) DEFAULT NULL,
        `expense_amount` bigint(20) DEFAULT NULL,
        `expense_detail` varchar(255) DEFAULT NULL,
        `currency` varchar(20) DEFAULT 'USD',
        `created_at` timestamp NULL DEFAULT current_timestamp()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";

    runQuery($tblAffiliateEarnings);
    runQuery($tblAffiliateLinks);
    runQuery($tblPagesVendor);
    runQuery($tblProductSearchIndexes);
    runQuery($tblProductTags);
    runQuery($tblTaxes);
    runQuery($tblUserLoginActivities);
    runQuery($tblWalletDeposits);
    runQuery($tblWalletExpenses);

    runQuery("ALTER TABLE bank_transfers ADD COLUMN `report_type` varchar(30) DEFAULT 'order'");
    runQuery("ALTER TABLE bank_transfers ADD COLUMN `report_item_id` INT");
    runQuery("ALTER TABLE brands ADD COLUMN `category_data` TEXT");
    runQuery("ALTER TABLE brands ADD COLUMN `show_on_slider` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE categories ADD COLUMN `show_description` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE coupons ADD COLUMN `is_public` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE custom_fields_options ADD COLUMN `name_data` TEXT");
    runQuery("ALTER TABLE earnings ADD COLUMN `affiliate_commission` bigint(20) DEFAULT 0;");
    runQuery("ALTER TABLE earnings ADD COLUMN `affiliate_commission_rate` DOUBLE DEFAULT 0;");
    runQuery("ALTER TABLE earnings ADD COLUMN `affiliate_discount` bigint(20) DEFAULT 0;");
    runQuery("ALTER TABLE earnings ADD COLUMN `affiliate_discount_rate` DOUBLE DEFAULT 0;");
    runQuery('ALTER TABLE general_settings ADD COLUMN `fea_categories_design` varchar(30) DEFAULT "round_boxes"');
    runQuery("ALTER TABLE general_settings ADD COLUMN `cache_static_system` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE general_settings ADD COLUMN `approve_after_editing` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE general_settings ADD COLUMN `email_options` TEXT");
    runQuery("ALTER TABLE general_settings ADD COLUMN `pwa_logo` TEXT");
    runQuery("ALTER TABLE general_settings ADD COLUMN `allow_free_plan_multiple_times` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE general_settings ADD COLUMN `single_country_mode` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE general_settings ADD COLUMN `single_country_id` INT");
    runQuery("ALTER TABLE general_settings ADD COLUMN `refund_system` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE general_settings ADD COLUMN `affiliate_status` TINYINT(1) DEFAULT 0");
    runQuery('ALTER TABLE general_settings ADD COLUMN `affiliate_type` varchar(30) DEFAULT "site_based"');
    runQuery("ALTER TABLE general_settings ADD COLUMN `affiliate_image` varchar(255)");
    runQuery("ALTER TABLE general_settings ADD COLUMN `affiliate_commission_rate` DOUBLE DEFAULT 0;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `affiliate_discount_rate` DOUBLE DEFAULT 0;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `auto_approve_orders` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE general_settings ADD COLUMN `auto_approve_orders_days` smallint(6) DEFAULT 10;");
    runQuery('ALTER TABLE general_settings ADD COLUMN `logo_size` varchar(30) DEFAULT "160x60"');
    runQuery("ALTER TABLE general_settings ADD COLUMN `profile_number_of_sales` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE homepage_banners ADD COLUMN `lang_id` INT DEFAULT 1");
    runQuery("ALTER TABLE invoices ADD COLUMN `client_tax_number` varchar(255)");
    runQuery("ALTER TABLE membership_transactions ADD COLUMN `global_taxes_data` TEXT");
    runQuery("ALTER TABLE orders ADD COLUMN `affiliate_data` TEXT");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `cash_on_delivery_debt_limit` bigint(20) DEFAULT 1000;");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `wallet_deposit` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `pay_with_wallet_balance` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `additional_invoice_info` TEXT");
    runQuery("ALTER TABLE products ADD COLUMN `is_edited` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE products ADD COLUMN `is_active` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE products ADD COLUMN `is_affiliate` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE products ADD COLUMN `updated_at` timestamp NULL DEFAULT NULL");
    runQuery("ALTER TABLE product_settings ADD COLUMN `pagination_per_page` smallint(6) DEFAULT 60");
    runQuery("ALTER TABLE product_settings ADD COLUMN `sort_by_featured_products` TINYINT(1) DEFAULT 1");
    runQuery("ALTER TABLE promoted_transactions ADD COLUMN `global_taxes_data` TEXT");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `variation_option_ids` varchar(255)");
    runQuery("ALTER TABLE settings ADD COLUMN `social_media_data` TEXT");
    runQuery("ALTER TABLE settings ADD COLUMN `affiliate_description` TEXT");
    runQuery("ALTER TABLE settings ADD COLUMN `affiliate_content` longtext");
    runQuery("ALTER TABLE settings ADD COLUMN `affiliate_faq` mediumtext");
    runQuery("ALTER TABLE settings ADD COLUMN `affiliate_works` mediumtext");
    runQuery("ALTER TABLE settings ADD COLUMN `bulk_upload_documentation` TEXT");
    runQuery("ALTER TABLE shipping_zones ADD COLUMN `estimated_delivery` TEXT");
    runQuery("ALTER TABLE users ADD COLUMN `social_media_data` TEXT");
    runQuery("ALTER TABLE users ADD COLUMN `shop_request_reject_reason` TEXT");
    runQuery("ALTER TABLE users ADD COLUMN `shop_request_date` timestamp NULL DEFAULT NULL");
    runQuery("ALTER TABLE users ADD COLUMN `vat_rates_data_state` TEXT");
    runQuery("ALTER TABLE users ADD COLUMN `is_affiliate` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE users ADD COLUMN `vendor_affiliate_status` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE users ADD COLUMN `affiliate_commission_rate` DOUBLE DEFAULT 0;");
    runQuery("ALTER TABLE users ADD COLUMN `affiliate_discount_rate` DOUBLE DEFAULT 0;");
    runQuery("ALTER TABLE users ADD COLUMN `tax_registration_number` varchar(255)");
    runQuery("ALTER TABLE users ADD COLUMN `vacation_mode` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE users ADD COLUMN `vacation_message` TEXT");
    runQuery("ALTER TABLE users ADD COLUMN `commission_debt` bigint(20);");
    runQuery("ALTER TABLE users ADD COLUMN `account_delete_req` TINYINT(1) DEFAULT 0");
    runQuery("ALTER TABLE users ADD COLUMN `account_delete_req_date` timestamp NULL DEFAULT NULL");

    //add indexes
    runQuery("ALTER TABLE product_search_indexes ADD FULLTEXT(search_index);");
    runQuery("ALTER TABLE product_search_indexes ADD INDEX idx_product_id (product_id);");
    runQuery("ALTER TABLE product_search_indexes ADD INDEX idx_lang_id (lang_id);");
    runQuery("ALTER TABLE categories ADD INDEX idx_parent_tree (parent_tree);");
    runQuery("ALTER TABLE categories ADD INDEX idx_slug (slug);");
    runQuery("ALTER TABLE custom_fields ADD INDEX idx_field_order (field_order);");
    runQuery("ALTER TABLE custom_fields ADD INDEX idx_is_product_filter (is_product_filter);");
    runQuery("ALTER TABLE location_countries ADD INDEX idx_status (status);");
    runQuery("ALTER TABLE pages_vendor ADD INDEX idx_user_id (user_id);");
    runQuery("ALTER TABLE products ADD INDEX idx_slug (slug);");
    runQuery("ALTER TABLE products ADD INDEX idx_sku (sku);");
    runQuery("ALTER TABLE products ADD INDEX idx_is_edited (is_edited);");
    runQuery("ALTER TABLE products ADD INDEX idx_is_active (is_active);");
    runQuery("ALTER TABLE product_tags ADD INDEX idx_tag (tag);");
    runQuery("ALTER TABLE product_tags ADD INDEX idx_product_id (product_id);");
    runQuery("ALTER TABLE product_tags ADD INDEX idx_lang_id (lang_id);");
    runQuery("ALTER TABLE users ADD INDEX idx_country_id (country_id);");
    runQuery("ALTER TABLE users ADD INDEX idx_state_id (state_id);");
    runQuery("ALTER TABLE users ADD INDEX idx_city_id (city_id);");
    runQuery("ALTER TABLE users ADD INDEX idx_vacation_mode (vacation_mode);");
    runQuery("ALTER TABLE users ADD INDEX idx_is_membership_plan_expired (is_membership_plan_expired);");
    runQuery("ALTER TABLE user_login_activities ADD INDEX idx_user_id (user_id);");

    //add new routes
    $sql = "INSERT INTO `routes` (`route_key`, `route`) VALUES
    ('service_payment_completed', 'service-payment-completed'),
    ('wallet', 'wallet'),
    ('affiliate_program', 'affiliate-program'),
    ('payments', 'payments'),
    ('cash_on_delivery', 'cash-on-delivery'),
    ('shop_policies', 'shop-policies'),
    ('affiliate', 'affiliate'),
    ('coupon_products', 'coupon-products'),
    ('delete_account', 'delete-account'),
    ('my_reviews', 'my-reviews'),
    ('affiliate_links', 'affiliate-links');";
    runQuery($sql);

    //delete old routes
    $sql = "DELETE FROM routes WHERE `route_key` IN ('membership_payment_completed', 'payment_history', 'payouts', 'promote_payment_completed', 'set_payout_account', 'withdraw_money');";
    runQuery($sql);

    //delete old translations
    $sql = "DELETE FROM language_translations WHERE `label` IN (
        'add_a_comment',
        'approve_before_publishing',
        'bank_transfer_notifications',
        'bulk_category_upload_exp',
        'define_new_tax',
        'download_license_key',
        'enter_location',
        'featured_products_transactions',
        'keywords_exp',
        'location_explanation',
        'membership_transactions',
        'msg_accept_bank_transfer',
        'msg_error_cart_unapproved_products',
        'msg_shop_request_declined',
        'no_thanks',
        'payment_history',
        'search_products',
        'select_your_country',
        'send_email_shop_opening_request',
        'sort_by',
        'update_location',
        'x_url');";
    runQuery($sql);

    //add new translations
    $p["view_cart"] = "View Cart";
    $p["product_cart_summary"] = "Product cart summary";
    $p["product_added_to_cart"] = "Product successfully added to your cart!";
    $p["profile_settings"] = "Profile Settings";
    $p["wallet"] = "Wallet";
    $p["wallet_balance"] = "Wallet Balance";
    $p["new_payout_request"] = "New Payout Request";
    $p["referral_earnings"] = "Referral Earnings";
    $p["reference_code"] = "Reference Code";
    $p["create_affiliate_link"] = "Create affiliate link";
    $p["affiliate_link"] = "Affiliate Link";
    $p["copy"] = "Copy";
    $p["copy_link"] = "Copy Link";
    $p["affiliate_program"] = "Affiliate Program";
    $p["frequently_asked_questions"] = "Frequently Asked Questions";
    $p["question"] = "Question";
    $p["answer"] = "Answer";
    $p["add_question"] = "Add Question";
    $p["how_it_works"] = "How It Works";
    $p["join_program"] = "Join Program";
    $p["joined_affiliate_program"] = "You joined the affiliate program";
    $p["affiliate_link_exp"] = "To maximize your affiliate earnings, share this link in blog posts, on social media, and in email campaigns. Include it in YouTube video descriptions, on your website, and in online forums.";
    $p["program_type"] = "Program Type";
    $p["affiliate_site_based"] = "Site-based (for all products, site pays the commission)";
    $p["affiliate_seller_based"] = "Seller-based (for products selected by the seller, seller pays the commission)";
    $p["enable_for_all_products"] = "Enable For All Products";
    $p["enable_only_for_selected_products"] = "Enable Only for Selected Products (products can be selected from the products page)";
    $p["affiliate_program_vendor_exp"] = "The affiliate program allows you, as a seller, to pay external partners a commission for promoting your products and driving sales through their unique links, helping you reach a wider audience and increase revenue. When you activate this system, there will be an option to create an affiliate link on your product page. All users can participate in this program by creating their own links for your products.";
    $p["referrer_commission_rate"] = "Referrer Commission Rate";
    $p["buyer_discount_rate"] = "Buyer Discount Rate";
    $p["referral_discount"] = "Referral Discount";
    $p["referrer_commission"] = "Referrer Commission";
    $p["commissions_discounts"] = "Commissions & Discounts";
    $p["pay_wallet_balance_exp"] = "Pay with your wallet balance";
    $p["pay_wallet_balance_warning"] = "The order amount will be deducted from your wallet balance. If you approve, please click the button below to complete the purchase.";
    $p["expense"] = "Expense";
    $p["expenses"] = "Expenses";
    $p["expense_amount"] = "Expense Amount";
    $p["purchase"] = "Purchase";
    $p["affiliate"] = "Affiliate";
    $p["delete_from_affiliate_program"] = "Delete from Affiliate Program";
    $p["add_to_affiliate_program"] = "Add to Affiliate Program";
    $p["removed_from_affiliate_program"] = "You have been removed from the affiliate program.";
    $p["wrong_password"] = "Wrong password!";
    $p["enter_your_password"] = "Enter your password";
    $p["login_to_user_account_exp"] = "Your current session will be terminated and a new session will be created for the account of the user you selected.";
    $p["auto_approve_orders"] = "Auto-Approve Unapproved Orders (after x days)";
    $p["excel"] = "Excel";
    $p["chat_messages"] = "Chat Messages";
    $p["sender"] = "Sender";
    $p["receiver"] = "Receiver";
    $p["reject_permanently"] = "Reject Permanently";
    $p["mgs_reject_open_shop"] = "Your request to open a store has been rejected!";
    $p["mgs_reject_open_shop_permanently"] = "Your request to open a store has been permanently rejected!";
    $p["permanently_rejected"] = "Permanently Rejected";
    $p["shop_opening_request_emails"] = "Shop opening request emails";
    $p["allow_free_plan_multiple_times"] = "Allow Free Plan to be Used Multiple Times";
    $p["msg_membership_activated"] = "Your membership plan has been successfully activated!";
    $p["filter_products_location"] = "Filter products by location";
    $p["add_comment"] = "Add Comment";
    $p["show_description_category_page"] = "Show Description on Category Page";
    $p["add_products"] = "Add Products";
    $p["edit_products"] = "Edit Products";
    $p["product_id_not_defined"] = "Product ID is not defined.";
    $p["bulk_upload_documentation"] = "Bulk Upload Documentation";
    $p["edit_brand"] = "Edit Brand";
    $p["user_details"] = "User Details";
    $p["twitter_url"] = "X (Twitter) URL";
    $p["twitch_url"] = "Twitch Url";
    $p["discord_url"] = "Discord Url";
    $p["user_login_activities"] = "User Login Activities";
    $p["user_agent"] = "User Agent";
    $p["number_short_thousand"] = "k";
    $p["number_short_million"] = "m";
    $p["number_short_billion"] = "b";
    $p["pwa_logo"] = "PWA Logo";
    $p["show_on_slider"] = "Show on Slider";
    $p["tags_product_exp"] = "Add relevant keywords for your product to increase visibility in search results";
    $p["edited_products"] = "Edited Products";
    $p["product_approval_new_products"] = "Product Approval for New Products";
    $p["product_approval_edited_products"] = "Product Approval for Edited Products";
    $p["enable_dont_hide_products"] = "Enable, Do Not Hide Products";
    $p["enable_hide_products"] = "Enable, Hide Products Until Approved";
    $p["logo_size"] = "Logo Size";
    $p["theme"] = "Theme";
    $p["grid_layout"] = "Grid Layout";
    $p["round_boxes"] = "Round Boxes";
    $p["shop_by_category"] = "Shop By Category";
    $p["filter_by_keyword"] = "Filter by keyword";
    $p["keyword"] = "Keyword";
    $p["search_products_categories_brands"] = "Search for products, categories or brands";
    $p["bulk_custom_field_upload"] = "Bulk Custom Field Upload";
    $p["post_comment"] = "Post Comment";
    $p["load_more_reviews"] = "Load more reviews";
    $p["load_more_comments"] = "Load more comments";
    $p["additional_invoice_information"] = "Additional Invoice Information";
    $p["support_system_emails"] = "Support system emails";
    $p["additional_invoice_information_exp"] = "VAT Number, Company No etc.";
    $p["tax_registration_number"] = "Tax Registration Number";
    $p["msg_support_new_message"] = "New Support Message";
    $p["msg_support_message_received"] = "Your Support Message has been Received";
    $p["msg_support_message_received_exp"] = "Thank you for reaching out to us. We have received your support message and will get back to you shortly.";
    $p["msg_support_message_replied"] = "Your Support Ticket Has Been Replied";
    $p["msg_support_message_replied_exp"] = "Please click the button below to view the ticket details.";
    $p["support_ticket"] = "Ticket";
    $p["single_country_mode"] = "Single Country Mode";
    $p["vacation_mode"] = "Vacation Mode";
    $p["vacation_message"] = "Vacation Message";
    $p["vendor_on_vacation"] = "Vendor on Vacation";
    $p["vendor_on_vacation_exp"] = "This vendor is currently on vacation and is not available to process orders or respond to messages.";
    $p["refund_system"] = "Refund System";
    $p["public_coupon"] = "Public Coupon";
    $p["public_coupon_exp"] = "Public coupons are visible to all users";
    $p["my_reviews"] = "My Reviews";
    $p["vendor_on_vacation_vendor_exp"] = "Vacation mode allows you to pause your store for a certain period of time";
    $p["view_pdf_file"] = "View PDF File";
    $p["add_funds"] = "Add Funds";
    $p["enter_amount"] = "Enter Amount";
    $p["deposit_amount"] = "Deposit Amount";
    $p["wallet_deposits"] = "Wallet Deposits";
    $p["wallet_deposit"] = "Wallet Deposit";
    $p["deposits"] = "Deposits";
    $p["bank_transfer_reports"] = "Bank Transfer Reports";
    $p["report_type"] = "Report Type";
    $p["commission_debt"] = "Commission Debt";
    $p["commission_debt_limit_exp"] = "Cash on Delivery commissions will be automatically deducted from your wallet balance. If your wallet has insufficient funds, the commission will be added as a debt. When these debts exceed the specified debt limit, this payment option will be automatically disabled for your store.";
    $p["commission_debt_limit"] = "Commission Debt Limit";
    $p["add_funds_pay_debt"] = "Add funds to your wallet to pay your debt. The debt will be automatically deducted.";
    $p["you_have_no_debt"] = "You have no debt.";
    $p["cod_option_disabled"] = "Cash on Delivery payment option has been disabled until your commission debt is paid.";
    $p["sales_number"] = "Sales";
    $p["show_number_sales_profile"] = "Show Number of Sales on Profile";
    $p["paying_wallet_balance"] = "Paying with Wallet Balance";
    $p["footer_bottom"] = "Footer Bottom";
    $p["edit_tax"] = "Edit Tax";
    $p["add_tax"] = "Add Tax";
    $p["msg_cart_select_location"] = "Please select your location to proceed with your purchase.";
    $p["apply_for_product_sales"] = "Apply for Product Sales";
    $p["apply_for_service_payments"] = "Apply for Service Payments";
    $p["all_locations"] = "All Locations";
    $p["shop_policies"] = "Shop Policies";
    $p["vendor_vat_rates_exp"] = "The VAT rate you set for a country will apply to all states within that country. However, if you want a state to have its own unique tax rate, you can specify a different VAT rate for that state.";
    $p["pagination_product"] = "Pagination (Number of products on each page)";
    $p["show_previous_products"] = "Show Previous Products";
    $p["select_products"] = "Select Products";
    $p["select_for_coupon"] = "Select for Coupon";
    $p["static_cache_system"] = "Static Cache System";
    $p["static_cache_system_exp"] = "While the cache system is used for products that are updated more frequently, static cache is applied to records that do not change often (such as categories, custom fields, settings, etc.). If any changes occur in these records, the cache files are automatically refreshed.";
    $p["delete_account"] = "Delete Account";
    $p["delete_account_exp"] = "Deleting your account is permanent and cannot be reversed. All data, including preferences and subscriptions, will be lost. The process requires admin approval, which may take some time. Please enter your password and confirm to proceed.";
    $p["delete_account_submit_exp"] = "Your account deletion request has been submitted and is awaiting admin approval. If you wish to cancel this request, please contact site administration through the Help Center.";
    $p["msg_request_received"] = "Your request has been received!";
    $p["account_deletion_requests"] = "Account Deletion Requests";
    $p["product_search_listing"] = "Product Search & Listing";
    $p["show_featured_products_first_search"] = "Show Featured Products First in Search Results";
    $p["estimated_delivery"] = "Estimated Delivery";
    $p["no_delivery_this_location"] = "No delivery to this location";
    $p["msg_vendor_membership_plan_expired"] = "Your membership plan has expired, so your products will no longer be published on the site. If you would like your products to continue being published on the site, please renew your membership plan.";
    $p["affiliate_links"] = "Affiliate Links";
    addTranslations($p);

    //add update records
    runQuery("UPDATE products SET is_active = 0 WHERE status = 0 OR visibility = 0 OR is_sold = 1 OR is_deleted = 1 OR is_draft = 1");

    //set email options
    $result = runQuery("SELECT * FROM general_settings WHERE id = 1");
    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $dataOptions = [
            'new_product' => !empty($row['send_email_new_product']) ? 1 : 0,
            'new_order' => !empty($row['send_email_buyer_purchase']) ? 1 : 0,
            'order_shipped' => !empty($row['send_email_order_shipped']) ? 1 : 0,
            'contact_messages' => !empty($row['send_email_contact_messages']) ? 1 : 0,
            'shop_opening_request' => !empty($row['send_email_shop_opening_request']) ? 1 : 0,
            'bidding_system' => !empty($row['send_email_bidding_system']) ? 1 : 0,
            'support_system' => 0,
        ];
        $dataOptions = serialize($dataOptions);
        $stmt = $connection->prepare("UPDATE general_settings SET email_options = ?");
        $stmt->bind_param("s", $dataOptions);
        $stmt->execute();
    }

    //set settings
    $result = runQuery("SELECT * FROM settings ORDER BY id;");
    if ($result->num_rows > 0) {
        while ($row = mysqli_fetch_array($result)) {
            $data = [
                'facebook_url' => !empty($row['facebook_url']) ? $row['facebook_url'] : '',
                'twitter_url' => !empty($row['twitter_url']) ? $row['twitter_url'] : '',
                'instagram_url' => !empty($row['instagram_url']) ? $row['instagram_url'] : '',
                'tiktok_url' => !empty($row['tiktok_url']) ? $row['tiktok_url'] : '',
                'whatsapp_url' => !empty($row['whatsapp_url']) ? $row['whatsapp_url'] : '',
                'youtube_url' => !empty($row['youtube_url']) ? $row['youtube_url'] : '',
                'discord_url' => '',
                'telegram_url' => !empty($row['telegram_url']) ? $row['telegram_url'] : '',
                'pinterest_url' => !empty($row['pinterest_url']) ? $row['pinterest_url'] : '',
                'linkedin_url' => !empty($row['linkedin_url']) ? $row['linkedin_url'] : '',
                'twitch_url' => '',
                'vk_url' => !empty($row['vk_url']) ? $row['vk_url'] : '',
            ];
            $socialMediaData = serialize($data);
            $affiliateDescription = 'a:2:{s:5:"title";s:53:"Boost Your Earnings with the Modesy Affiliate Program";s:11:"description";s:196:"Are you a content creator, blogger, influencer, or simply someone with a strong online presence? If so, Modesy has an exciting opportunity for you to turn your online influence into real earnings.";}';
            $affiliateContent = 'a:2:{s:5:"title";s:38:"Why Join the Modesy Affiliate Program?";s:7:"content";s:1745:"<p>Modesy, a leading e-commerce platform known for its diverse range of products and exceptional customer service, is thrilled to introduce its Affiliate Program. This program offers you a chance to earn lucrative commissions by promoting Modesy\'s products. Here is everything you need to know about why the Modesy Affiliate Program is perfect for you.<br><br><strong>1. Attractive Commission Rates</strong><br>Modesy offers competitive commission rates that ensure you are rewarded generously for your efforts. Every time someone makes a purchase through your referral link, you earn a commission. The more you promote, the more you earn.</p>
    <p><strong>2. Wide Range of Products</strong><br>With Modesy’s extensive catalog, you have endless opportunities to promote products that resonate with your audience. Whether your niche is tech gadgets, fashion, beauty products, or home decor, Modesy has something for everyone.</p>
    <p><strong>3. Easy-to-Use Tools</strong><br>The Modesy Affiliate Program provides you with a suite of tools to make your promotional efforts seamless. From custom referral links to detailed performance reports, you’ll have everything you need to track your success and optimize your strategies.</p>
    <p><strong>4. Reliable Support</strong><br>Modesy values its affiliates and offers dedicated support to help you succeed. Whether you have questions about the program or need tips on how to maximize your earnings, the Modesy support team is always ready to assist you.</p>
    <p><strong>5. Timely Payments</strong><br>Modesy ensures that your hard-earned commissions are paid out on time. With a straightforward payout process, you can focus on what you do best – promoting great products and earning money.</p>";}';
            $affiliateFaq = 'a:8:{i:0;a:3:{s:1:"o";s:1:"1";s:1:"q";s:36:"How do I join the Affiliate program?";s:1:"a";s:110:"Simply click the "Join Now" button and fill out the registration form. Once approved, you can start promoting!";}i:1;a:3:{s:1:"o";s:1:"2";s:1:"q";s:45:"Who can participate in the Affiliate Program?";s:1:"a";s:215:"Anyone with an online presence, including bloggers, social media influencers, website owners, and content creators, can join the affiliate program. As long as you can promote our products, you’re welcome to apply!";}i:2;a:3:{s:1:"o";s:1:"3";s:1:"q";s:40:"Where can I generate my Affiliate links?";s:1:"a";s:222:"You can generate your affiliate links directly from any product detail page on our website. Once logged in, visit the product page you want to promote, and you’ll find an option to create your affiliate link right there.";}i:3;a:3:{s:1:"o";s:1:"4";s:1:"q";s:28:"What products can I promote?";s:1:"a";s:162:"You can promote any product from our store that is included in the affiliate program and earn commission on any qualifying sales made through your affiliate link.";}i:4;a:3:{s:1:"o";s:1:"5";s:1:"q";s:46:"How long is the validity of an Affiliate link?";s:1:"a";s:211:"An affiliate link is valid for 30 days from the moment a person clicks on it and opens the product page. If the product is purchased during this period, the affiliate commission will be applied for that product.";}i:5;a:3:{s:1:"o";s:1:"6";s:1:"q";s:20:"How much can I earn?";s:1:"a";s:120:"There is no limit to how much you can earn. Your earnings depend on the sales you generate through your affiliate links.";}i:6;a:3:{s:1:"o";s:1:"7";s:1:"q";s:37:"How do I track my Affiliate earnings?";s:1:"a";s:96:"You can track your affiliate program earnings in the "Referral Earnings" section of your wallet.";}i:7;a:3:{s:1:"o";s:1:"8";s:1:"q";s:35:"How do I get my Affiliate earnings?";s:1:"a";s:188:"Once your earnings exceed the minimum payout limit, you can request a payment from the "Payouts" section of your wallet. Simply submit a payout request, and your payment will be processed.";}}';
            $affiliateWorks = 'a:3:{i:0;a:2:{s:5:"title";s:23:"Sign up for the program";s:11:"description";s:77:"Join the Modesy affiliate program by completing a simple registration process";}i:1;a:2:{s:5:"title";s:34:"Create and share your referral URL";s:11:"description";s:77:"Generate a referral URL and share it on your website, email, or social media.";}i:2;a:2:{s:5:"title";s:15:"Earn commission";s:11:"description";s:64:"Earn commissions on every sale made through your affiliate links";}}';
            $bulkUploadDocumentation = '<p>With the bulk product upload feature, you can upload your products in bulk with the help of a CSV file.<br><br>Bulk upload has options to add new products and edit existing products:<br><br><strong>Add Products: </strong>To add new products, download the CSV template, add your products to this CSV file and upload it from this section. You can see detailed explanations of all required or optional columns in the table below. When adding your data, you need to pay attention to the data type of these columns.<br><br><strong>Edit Products: </strong>To edit products, you need to add an "id" column to the CSV template. You can see the ID numbers of your products on the "products" page. After adding the "id" column, you need to add the column names you want to edit. <br>For example, if you want to update the stock and prices of your products, your CSV template should be like this:<br><span style="color: rgb(35, 111, 161);">"id","price","price_discounted","stock"</span><br><br>Example:<br><span style="color: rgb(35, 111, 161);">"id","price","price_discounted","stock"</span><br><span style="color: rgb(132, 63, 161);">"1","30","20","1000"</span><br><span style="color: rgb(132, 63, 161);">"5","40","40","500"</span><br><br><span style="color: rgb(186, 55, 42);">* To update the product price, you need to add both "price" and "price_discounted" columns to your CSV file.<br><br><br></span></p>
    <p><span style="font-size: 12pt;"><strong>CSV Columns</strong></span></p><table style="width: 100%;" class="table table-bordered"><tbody><tr><th>Column</th><th>Description</th></tr><tr><td style="width: 180px;">title</td>
    <td>Data Type: Text <br><strong>Required</strong><br>Example: Modern grey couch and pillows</td></tr><tr><td style="width: 180px;">slug</td><td>Data Type: Text <br><strong>Optional</strong> <small>(If you leave it empty, it will be generated automatically.)</small> <br>Example: modern-grey-couch-and-pillows</td>
    </tr><tr><td style="width: 180px;">sku</td><td>Data Type: Text <br><strong>Optional</strong><br>Example: MD-GR-6898</td></tr><tr><td style="width: 180px;">category_id</td><td>Data Type: Number <br><strong>Required</strong><br>Example: 1</td></tr><tr><td style="width: 180px;">price</td>
    <td>Data Type: Decimal/Number <br><strong>Required</strong><br>Example 1: 50<br>Example 2: 45.90<br>Example 3: 3456.25</td></tr><tr><td style="width: 180px;">price_discounted</td><td>Data Type: Decimal/Number <br><strong>Optional</strong><br>Example 1: 40<br>Example 2: 35.90<br>Example 3: 2456.25</td>
    </tr><tr><td style="width: 180px;">vat_rate</td><td>Data Type: Number <br><strong>Optional</strong><br>Example: 8</td></tr><tr><td style="width: 180px;">stock</td><td>Data Type: Number <br><strong>Required</strong><br>Example: 100</td></tr><tr><td style="width: 180px;">short_description</td>
    <td>Data Type: Text <br><strong>Optional</strong><br>Example: It is a nice and comfortable couch</td></tr><tr><td style="width: 180px;">description</td><td>Data Type: Text <br><strong>Optional</strong><br>Example: It is a nice and comfortable couch...</td></tr><tr><td style="width: 180px;">tags</td>
    <td>Data Type: Text <br><strong>Optional</strong><br>Example: nice, comfortable, couch</td></tr><tr><td style="width: 180px;">image_url</td><td>Data Type: Text <br><strong>Optional</strong><br>Example 1:<br>https://upload.wikimedia.org/wikipedia/commons/7/70/Labrador-sea-paamiut.jpg<br><br>Example 2:<br>https://upload.wikimedia.org/wikipedia/commons/7/70/Labrador-sea-paamiut.jpg,<br>https://upload.wikimedia.org/wikipedia/commons/thumb/4/42/Shaqi_jrvej.jpg/1600px-Shaqi_jrvej.jpg<br><br><span style="color: rgb(186, 55, 42);">*You can add multiple image links by placing commas between them.</span></td>
    </tr><tr><td style="width: 180px;">external_link</td><td>Data Type: Text <br><strong>Optional</strong><br>Example: https://domain.com/product_url</td></tr><tr><td style="width: 180px;">updated_at</td><td>Data Type: Timestamp <br><strong>Optional</strong><br>Example: 2024-06-30 10:27:00 <br><br><span style="color: rgb(186, 55, 42);">*If you leave it blank, the system will not assign an update date.</span></td></tr><tr>
    <td style="width: 180px;">created_at</td><td>Data Type: Timestamp <br><strong>Optional</strong><br>Example: 2024-06-30 10:27:00 <br><br><span style="color: rgb(186, 55, 42);">*If you leave it blank, the system will automatically assign the current date.</span></td></tr></tbody></table><p><br><br><br><br><br></p>';
            $stmt = $connection->prepare("UPDATE settings SET social_media_data = ?, affiliate_description = ?, affiliate_content = ?, affiliate_faq = ?, affiliate_works = ?, bulk_upload_documentation = ? WHERE id = ?");
            $stmt->bind_param("ssssssi", $socialMediaData, $affiliateDescription, $affiliateContent, $affiliateFaq, $affiliateWorks, $bulkUploadDocumentation, $row['id']);
            $stmt->execute();
        }
    }

    //set users
    $result = runQuery("SELECT * FROM users;");
    if ($result->num_rows > 0) {
        while ($row = mysqli_fetch_array($result)) {
            $data = [
                'facebook_url' => !empty($row['facebook_url']) ? $row['facebook_url'] : '',
                'twitter_url' => !empty($row['twitter_url']) ? $row['twitter_url'] : '',
                'instagram_url' => !empty($row['instagram_url']) ? $row['instagram_url'] : '',
                'tiktok_url' => !empty($row['tiktok_url']) ? $row['tiktok_url'] : '',
                'whatsapp_url' => !empty($row['whatsapp_url']) ? $row['whatsapp_url'] : '',
                'youtube_url' => !empty($row['youtube_url']) ? $row['youtube_url'] : '',
                'discord_url' => '',
                'telegram_url' => !empty($row['telegram_url']) ? $row['telegram_url'] : '',
                'pinterest_url' => !empty($row['pinterest_url']) ? $row['pinterest_url'] : '',
                'linkedin_url' => !empty($row['linkedin_url']) ? $row['linkedin_url'] : '',
                'twitch_url' => '',
                'vk_url' => !empty($row['vk_url']) ? $row['vk_url'] : '',
                'personal_website_url' => !empty($row['personal_website_url']) ? $row['personal_website_url'] : ''
            ];
            $socialMediaData = serialize($data);
            $stmt = $connection->prepare("UPDATE users SET social_media_data = ? WHERE id = ?");
            $stmt->bind_param("si", $socialMediaData, $row['id']);
            $stmt->execute();
        }
    }

    //update custom field options
    $options = runQuery("SELECT * FROM custom_fields_options;");
    if (!empty($options->num_rows)) {
        while ($option = mysqli_fetch_array($options)) {
            $optionsLang = runQuery("SELECT * FROM custom_fields_options_lang WHERE `option_id` =" . $option['id'] . "   ORDER BY id;");
            $data = array();
            if (!empty($optionsLang->num_rows)) {
                while ($optionLang = mysqli_fetch_array($optionsLang)) {
                    $item = [
                        'lang_id' => $optionLang['lang_id'],
                        'name' => $optionLang['option_name']
                    ];
                    array_push($data, $item);
                }
            }
            if (!empty($data)) {
                $data = serialize($data);
                $stmt = $connection->prepare("UPDATE custom_fields_options SET `name_data` = ? WHERE `id` = ?");
                $stmt->bind_param("si", $data, $option['id']);
                $stmt->execute();
            }
        }
    }

    runQuery("UPDATE general_settings SET version='2.5' WHERE id='1'");
    runQuery("ALTER TABLE bank_transfers DROP COLUMN `user_type`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_new_product`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_buyer_purchase`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_contact_messages`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_order_shipped`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_shop_opening_request`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `send_email_bidding_system`;");
    runQuery("ALTER TABLE payment_settings DROP COLUMN `global_taxes_data`;");
    runQuery("ALTER TABLE settings DROP COLUMN `facebook_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `twitter_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `instagram_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `pinterest_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `linkedin_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `vk_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `whatsapp_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `telegram_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `youtube_url`;");
    runQuery("ALTER TABLE settings DROP COLUMN `tiktok_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `facebook_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `twitter_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `instagram_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `pinterest_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `linkedin_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `vk_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `whatsapp_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `telegram_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `youtube_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `tiktok_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `personal_website_url`;");
    runQuery("ALTER TABLE users DROP COLUMN `cash_on_delivery_fee`;");
    runQuery("DROP TABLE custom_fields_options_lang;");

    //add product tags
    $rows = runQuery("SELECT pd.id AS id, pd.product_id AS product_id, pd.title AS title, pd.lang_id AS lang_id, pd.keywords AS keywords, 
       (SELECT sku FROM products WHERE products.id = pd.product_id LIMIT 1) AS sku FROM product_details pd");
    if (!empty($rows->num_rows)) {
        while ($row = mysqli_fetch_array($rows)) {

            try {
                $searchIndex = $row['title'];
                if (!empty($searchIndex)) {
                    $searchIndex = @mb_strtolower($searchIndex, 'UTF-8');
                }
                $searchIndex .= ' ' . $row['sku'];
                $arrayKeywords = !empty($row['keywords']) ? explode(',', $row['keywords']) : array();
                if (!empty($arrayKeywords) && count($arrayKeywords) > 0) {
                    $strKeywords = implode(' ', $arrayKeywords);
                    $strKeywords = mb_strtolower($strKeywords, 'UTF-8');
                    $searchIndex .= ' ' . $strKeywords;
                }
                $searchIndex = str_replace(['&', '#', '-', '_', ',', '"', "'"], '', $searchIndex ?? '');
                $stmt = $connection->prepare("INSERT INTO product_search_indexes (`product_id`, `lang_id`, `search_index`) VALUES (?, ?, ?)");
                $stmt->bind_param("iis", $row['product_id'], $row['lang_id'], $searchIndex);
                $stmt->execute();
                if (!empty($arrayKeywords) && count($arrayKeywords) > 0) {
                    $stmt = $connection->prepare("INSERT INTO product_tags (`product_id`, `lang_id`, `tag`) VALUES (?, ?, ?)");
                    foreach ($arrayKeywords as $item) {
                        $item = trim($item);
                        if (!empty($item)) {
                            $item = @mb_strtolower($item, 'UTF-8');
                            $stmt->bind_param("iis", $row['product_id'], $row['lang_id'], $item);
                            $stmt->execute();
                        }
                    }
                }
            } catch (Exception $e) {
            }

        }
    }

    runQuery("ALTER TABLE product_details DROP COLUMN `keywords`;");
    $stmt->close();
}

function updateFrom25To26($connection)
{
    $batchSize = 500;

    //fix timestamp invalid date error
    runQuery("UPDATE `users_membership_plans` SET `plan_end_date` = NULL WHERE CAST(`plan_end_date` AS CHAR(19)) = '0000-00-00 00:00:00';");

    //change tables
    runQuery("RENAME TABLE `categories_lang` TO `category_lang`;");
    runQuery("RENAME TABLE `order_products` TO `order_items`;");
    runQuery("RENAME TABLE `product_tags` TO `product_tags_old`;");
    runQuery("ALTER TABLE `categories` DROP INDEX `idx_visibility`;");

    //add new tables
    runQuery("CREATE TABLE `brand_category` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`brand_id` int DEFAULT NULL,
`category_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `brand_lang` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`brand_id` int DEFAULT NULL,
`lang_id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `carts` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`user_id` int DEFAULT NULL,
`session_id` varchar(128) DEFAULT NULL,
`currency_code` varchar(10) DEFAULT NULL,
`currency_code_base` varchar(10) DEFAULT NULL,
`exchange_rate` double DEFAULT '1',
`shipping_data` text,
`shipping_cost` decimal(12,2) DEFAULT '0.00',
`shipping_cost_data` text,
`coupon_code` varchar(50) DEFAULT NULL,
`payment_method` varchar(100) DEFAULT NULL,
`location_country_id` int DEFAULT NULL,
`location_state_id` int DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `cart_items` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`cart_id` int DEFAULT NULL,
`item_hash` varchar(40) DEFAULT NULL,
`product_id` int DEFAULT NULL,
`seller_id` int DEFAULT NULL,
`product_type` varchar(20) DEFAULT 'physical',
`listing_type` varchar(20) DEFAULT NULL,
`product_title` varchar(500) DEFAULT NULL,
`product_sku` varchar(255) DEFAULT NULL,
`quantity` int DEFAULT NULL,
`unit_price` decimal(12,2) DEFAULT '0.00',
`unit_price_base` decimal(12,2) DEFAULT '0.00',
`total_price` decimal(12,2) DEFAULT '0.00',
`product_vat` decimal(12,2) DEFAULT '0.00',
`product_vat_rate` double DEFAULT NULL,
`product_image_id` int DEFAULT NULL,
`product_image_data` text,
`purchase_type` varchar(50) DEFAULT NULL,
`quote_request_id` int DEFAULT '0',
`variant_hash` varchar(64) DEFAULT NULL,
`extra_options` text,
`extra_options_hash` text,
`product_options_snapshot` text,
`product_options_summary` text,
`is_stock_available` tinyint(1) DEFAULT '0',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `category_paths` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`ancestor_id` int DEFAULT NULL,
`descendant_id` int DEFAULT NULL,
`depth` smallint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `checkouts` (
`id` bigint AUTO_INCREMENT PRIMARY KEY,
`cart_id` int DEFAULT NULL,
`user_id` int DEFAULT NULL,
`session_id` varchar(128) DEFAULT NULL,
`checkout_token` varchar(36) DEFAULT NULL,
`checkout_type` varchar(10) DEFAULT 'product',
`payment_method` varchar(100) DEFAULT NULL,
`subtotal` decimal(12,2) DEFAULT NULL,
`shipping_cost` decimal(12,2) DEFAULT '0.00',
`grand_total` decimal(12,2) DEFAULT NULL,
`grand_total_base` decimal(12,2) DEFAULT NULL,
`cart_totals_data` text,
`currency_code` varchar(10) DEFAULT NULL,
`currency_code_base` varchar(10) DEFAULT NULL,
`exchange_rate` double DEFAULT '1',
`shipping_data` text,
`shipping_cost_data` text,
`coupon_code` varchar(50) DEFAULT NULL,
`service_type` varchar(30) DEFAULT NULL,
`service_data` text,
`service_tax_data` text,
`has_physical_product` tinyint(1) DEFAULT '0',
`has_digital_product` tinyint(1) DEFAULT '0',
`transaction_number` varchar(50) DEFAULT NULL,
`status` varchar(50) NOT NULL DEFAULT 'active',
`payment_url` varchar(1000) DEFAULT NULL,
`expires_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `checkout_items` (
`id` bigint AUTO_INCREMENT PRIMARY KEY,
`checkout_id` bigint DEFAULT NULL,
`product_id` int DEFAULT NULL,
`seller_id` int DEFAULT NULL,
`product_type` varchar(20) DEFAULT 'physical',
`listing_type` varchar(20) DEFAULT NULL,
`product_title` varchar(500) DEFAULT NULL,
`product_sku` varchar(255) DEFAULT NULL,
`quantity` int DEFAULT NULL,
`unit_price` decimal(12,2) DEFAULT '0.00',
`unit_price_base` decimal(12,2) DEFAULT NULL,
`total_price` decimal(12,2) DEFAULT '0.00',
`product_vat` decimal(12,2) DEFAULT '0.00',
`product_vat_rate` double DEFAULT NULL,
`product_image_id` int DEFAULT NULL,
`product_image_data` text,
`quote_request_id` int DEFAULT '0',
`product_options_snapshot` text,
`product_options_summary` text,
`extra_options` text,
`product_commission_rate` double DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `custom_field_lang` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`field_id` int DEFAULT NULL,
`lang_id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `custom_field_option_lang` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`option_id` int DEFAULT NULL,
`lang_id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `product_options` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`product_id` int NOT NULL,
`option_name_translations` text COLLATE utf8mb4_general_ci,
`option_type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`display_order` int DEFAULT '0',
`is_active` tinyint(1) DEFAULT '1',
`option_key` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `product_option_values` (
`id` bigint AUTO_INCREMENT PRIMARY KEY,
`option_id` int NOT NULL,
`value_name_translations` text COLLATE utf8mb4_general_ci,
`color_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`primary_swatch_image_id` varchar(2048) DEFAULT NULL,
`gallery_image_ids` text,
`display_order` int DEFAULT '0',
`value_key` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `product_option_variants` (
`id` bigint AUTO_INCREMENT PRIMARY KEY,
`product_id` int NOT NULL,
`sku` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`price` decimal(12,2) DEFAULT '0.00',
`price_discounted` decimal(12,2) DEFAULT '0.00',
`quantity` int DEFAULT NULL,
`weight` decimal(10,3) DEFAULT NULL,
`is_default` tinyint(1) DEFAULT '0',
`is_active` tinyint(1) DEFAULT '1',
`variant_hash` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `product_option_variant_values` (
`id` bigint AUTO_INCREMENT PRIMARY KEY,
`variant_id` bigint NOT NULL,
`value_id` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `product_tags` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`tag_id` int DEFAULT NULL,
`product_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    runQuery("CREATE TABLE `tags` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`tag` varchar(255) DEFAULT NULL,
`lang_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

    //add new columns
    runQuery("ALTER TABLE ad_spaces ADD COLUMN `storage` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE bank_transfers ADD COLUMN `storage` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE brands ADD COLUMN `storage` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE categories ADD COLUMN `is_commission_set` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE categories ADD COLUMN `commission_rate` DECIMAL(5, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE categories CHANGE visibility status TINYINT(1) DEFAULT 1;");
    runQuery("ALTER TABLE category_lang ADD COLUMN `meta_title` varchar(255);");
    runQuery("ALTER TABLE category_lang ADD COLUMN `meta_description` varchar(500);");
    runQuery("ALTER TABLE category_lang ADD COLUMN `meta_keywords` varchar(255);");
    runQuery("ALTER TABLE general_settings ADD COLUMN `turnstile_status` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `turnstile_site_key` varchar(100);");
    runQuery("ALTER TABLE general_settings ADD COLUMN `turnstile_secret_key` varchar(100);");
    runQuery("ALTER TABLE general_settings ADD COLUMN `storage_settings` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `affiliate_settings` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `email_settings` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `newsletter_settings` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `watermark_settings` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `index_products_per_row` mediumint DEFAULT 6;");
    runQuery("ALTER TABLE general_settings CHANGE cache_static_system static_cache_system TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `category_cache_system` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `show_vendor_contact_info_guests` TINYINT(1) DEFAULT 1;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `ai_writer` TEXT;");
    runQuery("ALTER TABLE general_settings ADD COLUMN `product_img_display_mode` varchar(20) DEFAULT 'cover';");
    runQuery("ALTER TABLE homepage_banners ADD COLUMN `storage` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE images ADD COLUMN `is_option_image` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE membership_transactions ADD COLUMN `checkout_token` varchar(36);");
    runQuery("ALTER TABLE orders ADD COLUMN `bank_transaction_number` varchar(50);");
    runQuery("ALTER TABLE orders ADD COLUMN `checkout_token` varchar(36);");
    runQuery("ALTER TABLE order_items ADD COLUMN `product_sku` varchar(100);");
    runQuery("ALTER TABLE order_items ADD COLUMN `image_id` INT;");
    runQuery("ALTER TABLE order_items ADD COLUMN `image_data` TEXT;");
    runQuery("ALTER TABLE order_items ADD COLUMN `product_options_snapshot` TEXT;");
    runQuery("ALTER TABLE order_items ADD COLUMN `product_options_summary` TEXT;");
    runQuery("ALTER TABLE payment_gateways ADD COLUMN `webhook_secret` varchar(500);");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `wallet_status` TINYINT(1) DEFAULT 1;");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `wallet_min_deposit` DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings ADD COLUMN `cart_location_selection` TINYINT(1) DEFAULT 1;");
    runQuery("ALTER TABLE products ADD COLUMN `shipping_dimensions` varchar(255);");
    runQuery("ALTER TABLE products ADD COLUMN `chargeable_weight` DECIMAL(10, 3);");
    runQuery("ALTER TABLE products ADD COLUMN `is_commission_set` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE products ADD COLUMN `commission_rate` DECIMAL(5, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE products ADD COLUMN `image_cache` TEXT;");
    runQuery("ALTER TABLE promoted_transactions ADD COLUMN `checkout_token` varchar(36);");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `product_sku` varchar(255);");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `variant_hash` varchar(64);");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `extra_options` TEXT;");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `extra_options_hash` TEXT;");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `product_options_snapshot` TEXT;");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `product_options_summary` TEXT;");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `product_image_id` INT;");
    runQuery("ALTER TABLE quote_requests ADD COLUMN `product_image_data` TEXT;");
    runQuery("ALTER TABLE transactions ADD COLUMN `checkout_token` varchar(36);");
    runQuery("ALTER TABLE users ADD COLUMN `storage_avatar` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE users ADD COLUMN `storage_cover` varchar(30) DEFAULT 'local';");
    runQuery("ALTER TABLE users ADD COLUMN `is_commission_set` TINYINT(1) DEFAULT 0;");
    runQuery("ALTER TABLE users ADD COLUMN `commission_rate` DECIMAL(5, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE users ADD COLUMN `payout_info` TEXT;");
    runQuery("ALTER TABLE shipping_zone_methods ADD COLUMN `cost_calculation_type` varchar(100);");
    runQuery("ALTER TABLE shipping_zone_methods ADD COLUMN `flat_rate_costs` TEXT;");
    runQuery("ALTER TABLE shipping_zone_methods ADD COLUMN `shipping_flat_cost` DECIMAL(12, 2);");
    runQuery("ALTER TABLE wallet_deposits ADD COLUMN `checkout_token` varchar(36);");
    runQuery("ALTER TABLE wallet_deposits ADD COLUMN `deposit_amount_base` DECIMAL(12, 2);");
    runQuery("ALTER TABLE wallet_deposits ADD COLUMN `currency_base` varchar(10);");
    runQuery("ALTER TABLE affiliate_earnings MODIFY COLUMN earned_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE coupons MODIFY COLUMN minimum_order_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE digital_sales MODIFY COLUMN price DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN sale_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN vat_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN commission DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN coupon_discount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN shipping_cost DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN earned_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN affiliate_commission DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE earnings MODIFY COLUMN affiliate_discount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE membership_plans MODIFY COLUMN price DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN price_subtotal DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN price_vat DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN price_shipping DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN price_total DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN coupon_discount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE orders MODIFY COLUMN transaction_fee DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE order_items MODIFY COLUMN product_unit_price DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE order_items MODIFY COLUMN product_vat DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE order_items MODIFY COLUMN product_total_price DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE order_items MODIFY COLUMN seller_shipping_cost DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE order_items MODIFY COLUMN commission_rate double;");
    runQuery("ALTER TABLE payment_gateways MODIFY COLUMN transaction_fee DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN cash_on_delivery_debt_limit DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN price_per_day DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN price_per_month DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN min_payout_paypal DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN min_payout_bitcoin DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN min_payout_iban DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payment_settings MODIFY COLUMN min_payout_swift DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE payouts MODIFY COLUMN amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE products MODIFY COLUMN price_discounted DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE quote_requests MODIFY COLUMN price_offered DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE users MODIFY COLUMN balance DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE users_membership_plans MODIFY COLUMN price DECIMAL(12, 2);");
    runQuery("ALTER TABLE shipping_zone_methods MODIFY COLUMN local_pickup_cost DECIMAL(12, 2);");
    runQuery("ALTER TABLE shipping_zone_methods MODIFY COLUMN free_shipping_min_amount DECIMAL(12, 2);");
    runQuery("ALTER TABLE wallet_deposits MODIFY COLUMN deposit_amount DECIMAL(12, 2) DEFAULT '0.00';");
    runQuery("ALTER TABLE wallet_expenses MODIFY COLUMN expense_amount DECIMAL(12, 2) DEFAULT '0.00';");


    //add route
    runQuery("INSERT INTO `routes` (`route_key`, `route`) VALUES ('checkout', 'checkout');");
    runQuery("
    INSERT INTO `payment_gateways` (`name`, `name_key`, `public_key`, `secret_key`, `webhook_secret`, `environment`, `transaction_fee`, `status`, `logos`) VALUES
('dLocal Go', 'dlocalgo', NULL, NULL, NULL, 'sandbox', '0.00', 0, 'visa,mastercard,amex,discover,boleto,d-local-go'),
('YooMoney', 'yoomoney', NULL, NULL, NULL, 'sandbox', '0.00', 0, 'visa,mastercard,maestro,mir,yoomoney');
    ");
    //add font inter
    runQuery("INSERT INTO `fonts` (`font_name`, `font_key`, `font_url`, `font_family`, `font_source`, `has_local_file`, `is_default`) VALUES
('Inter', 'inter', NULL, 'font-family: \"Inter\", sans-serif;', 'local', 1, 0);");

    //update prices
    runQuery("UPDATE affiliate_earnings SET earned_amount = ROUND(earned_amount / 100, 2) WHERE earned_amount IS NOT NULL AND earned_amount < 1000000000000;");
    runQuery("UPDATE coupons SET minimum_order_amount = ROUND(minimum_order_amount / 100, 2) WHERE minimum_order_amount IS NOT NULL AND minimum_order_amount < 1000000000000;");
    runQuery("UPDATE digital_sales SET price = ROUND(price / 100, 2) WHERE price IS NOT NULL AND price < 1000000000000;");
    runQuery("UPDATE `earnings` SET
`sale_amount` = CASE WHEN `sale_amount` IS NOT NULL AND `sale_amount` < 1000000000000 THEN ROUND(`sale_amount` / 100, 2) ELSE `sale_amount` END,
`vat_amount` = CASE WHEN `vat_amount` IS NOT NULL AND `vat_amount` < 1000000000000 THEN ROUND(`vat_amount` / 100, 2) ELSE `vat_amount` END,
`commission` = CASE WHEN `commission` IS NOT NULL AND `commission` < 1000000000000 THEN ROUND(`commission` / 100, 2) ELSE `commission` END,
`coupon_discount` = CASE WHEN `coupon_discount` IS NOT NULL AND `coupon_discount` < 1000000000000 THEN ROUND(`coupon_discount` / 100, 2) ELSE `coupon_discount` END,
`shipping_cost` = CASE WHEN `shipping_cost` IS NOT NULL AND `shipping_cost` < 1000000000000 THEN ROUND(`shipping_cost` / 100, 2) ELSE `shipping_cost` END,
`earned_amount` = CASE WHEN `earned_amount` IS NOT NULL AND `earned_amount` < 1000000000000 THEN ROUND(`earned_amount` / 100, 2) ELSE `earned_amount` END,
`affiliate_commission` = CASE WHEN `affiliate_commission` IS NOT NULL AND `affiliate_commission` < 1000000000000 THEN ROUND(`affiliate_commission` / 100, 2) ELSE `affiliate_commission` END,
`affiliate_discount` = CASE WHEN `affiliate_discount` IS NOT NULL AND `affiliate_discount` < 1000000000000 THEN ROUND(`affiliate_discount` / 100, 2) ELSE `affiliate_discount` END;");
    runQuery("UPDATE membership_plans SET price = ROUND(price / 100, 2) WHERE price IS NOT NULL AND price < 1000000000000;");
    runQuery("UPDATE `payment_settings` SET
`cash_on_delivery_debt_limit` = CASE WHEN `cash_on_delivery_debt_limit` IS NOT NULL AND `cash_on_delivery_debt_limit` < 1000000000000 THEN ROUND(`cash_on_delivery_debt_limit` / 100, 2) ELSE `cash_on_delivery_debt_limit` END,
`price_per_day` = CASE WHEN `price_per_day` IS NOT NULL AND `price_per_day` < 1000000000000 THEN ROUND(`price_per_day` / 100, 2) ELSE `price_per_day` END,
`price_per_month` = CASE WHEN `price_per_month` IS NOT NULL AND `price_per_month` < 1000000000000 THEN ROUND(`price_per_month` / 100, 2) ELSE `price_per_month` END,
`min_payout_paypal` = CASE WHEN `min_payout_paypal` IS NOT NULL AND `min_payout_paypal` < 1000000000000 THEN ROUND(`min_payout_paypal` / 100, 2) ELSE `min_payout_paypal` END,
`min_payout_bitcoin` = CASE WHEN `min_payout_bitcoin` IS NOT NULL AND `min_payout_bitcoin` < 1000000000000 THEN ROUND(`min_payout_bitcoin` / 100, 2) ELSE `min_payout_bitcoin` END,
`min_payout_iban` = CASE WHEN `min_payout_iban` IS NOT NULL AND `min_payout_iban` < 1000000000000 THEN ROUND(`min_payout_iban` / 100, 2) ELSE `min_payout_iban` END,
`min_payout_swift` = CASE WHEN `min_payout_swift` IS NOT NULL AND `min_payout_swift` < 1000000000000 THEN ROUND(`min_payout_swift` / 100, 2) ELSE `min_payout_swift` END;");
    runQuery("UPDATE payouts SET amount = ROUND(amount / 100, 2) WHERE amount IS NOT NULL AND amount < 1000000000000;");
    runQuery("UPDATE quote_requests SET price_offered = ROUND(price_offered / 100, 2) WHERE price_offered IS NOT NULL AND price_offered < 1000000000000;");
    runQuery("UPDATE users SET balance = ROUND(balance / 100, 2) WHERE balance IS NOT NULL AND balance < 1000000000000;");
    runQuery("UPDATE users_membership_plans SET price = ROUND(price / 100, 2) WHERE price IS NOT NULL AND price < 1000000000000;");
    runQuery("UPDATE shipping_zone_methods SET local_pickup_cost = ROUND(local_pickup_cost / 100, 2) WHERE local_pickup_cost IS NOT NULL AND local_pickup_cost < 1000000000000;");
    runQuery("UPDATE shipping_zone_methods SET free_shipping_min_amount = ROUND(free_shipping_min_amount / 100, 2) WHERE free_shipping_min_amount IS NOT NULL AND free_shipping_min_amount < 1000000000000;");
    runQuery("UPDATE wallet_deposits SET deposit_amount = ROUND(deposit_amount / 100, 2) WHERE deposit_amount IS NOT NULL AND deposit_amount < 1000000000000;");
    runQuery("UPDATE wallet_expenses SET expense_amount = ROUND(expense_amount / 100, 2) WHERE expense_amount IS NOT NULL AND expense_amount < 1000000000000;");

    //update brands
    $brands = runQuery("SELECT id, name_data, category_data FROM brands;");
    if ($brands && $brands->num_rows > 0) {
        $stmtLang = $connection->prepare("INSERT INTO brand_lang (brand_id, lang_id, name) VALUES (?, ?, ?)");
        $stmtCategories = $connection->prepare("INSERT INTO brand_category (brand_id, category_id) VALUES (?, ?)");
        $stmtLang->bind_param("iis", $brandId, $langId, $name);
        $stmtCategories->bind_param("ii", $brandId, $categoryId);
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = mysqli_fetch_array($brands)) {
            $rowCount++;
            $brandId = $row['id'];
            if (!empty($row['name_data'])) {
                $nameData = unserialize($row['name_data']);
                if ($nameData !== false && is_array($nameData) && !empty($nameData)) {
                    foreach ($nameData as $currentLangId => $currentName) {
                        $langId = $currentLangId;
                        $name = $currentName;
                        $stmtLang->execute();
                    }
                }
            }
            if (!empty($row['category_data'])) {
                $categoryIdsArray = explode(',', $row['category_data'] ?? '');
                foreach ($categoryIdsArray as $currentCategoryId) {
                    $trimmedCategoryId = trim($currentCategoryId ?? '');
                    if (!empty($trimmedCategoryId)) {
                        $categoryId = (int)$trimmedCategoryId;
                        $stmtCategories->execute();
                    }
                }
            }
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmtLang->close();
        $stmtCategories->close();
    }

    //update categories
    $updateQuery = "UPDATE category_lang SET meta_title = ?, meta_description = ?, meta_keywords = ? WHERE category_id = ?";
    $stmt = $connection->prepare($updateQuery);
    $stmt->bind_param(
        "sssi", $metaTitle, $metaDesc, $metaKeywords, $categoryId
    );
    $selectQuery = "SELECT id, title_meta_tag, description, keywords FROM categories";
    $categories = $connection->query($selectQuery);

    if ($categories && $categories->num_rows > 0) {
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = $categories->fetch_assoc()) {
            $rowCount++;
            $metaTitle = $row['title_meta_tag'];
            $metaDesc = $row['description'];
            $metaKeywords = $row['keywords'];
            $categoryId = $row['id'];
            $stmt->execute();
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmt->close();
    }

    //update custom_fields
    runQuery("UPDATE custom_fields SET field_type =
    CASE WHEN field_type = 'checkbox' THEN 'multi_select'
    WHEN field_type IN ('radio_button', 'dropdown') THEN 'single_select'
    ELSE field_type END;");

    //update custom field lang
    $insertQuery = "INSERT INTO custom_field_lang (field_id, lang_id, name) VALUES (?, ?, ?)";
    $stmt = $connection->prepare($insertQuery);
    $stmt->bind_param("iis", $fieldId, $langId, $name);
    $selectQuery = "SELECT id, name_array FROM custom_fields";
    $customFields = $connection->query($selectQuery);
    if ($customFields && $customFields->num_rows > 0) {
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = $customFields->fetch_assoc()) {
            $rowCount++;
            $fieldId = $row['id'];
            if (!empty($row['name_array'])) {
                $nameData = unserialize($row['name_array']);
                if ($nameData !== false && is_array($nameData) && !empty($nameData)) {
                    foreach ($nameData as $item) {
                        if (is_array($item) && isset($item['lang_id'], $item['name'])) {
                            $langId = $item['lang_id'];
                            $name = $item['name'];
                            $stmt->execute();
                        }
                    }
                }
            }
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmt->close();
    }

    //update custom field options
    $insertQuery = "INSERT INTO custom_field_option_lang (option_id, lang_id, name) VALUES (?, ?, ?)";
    $stmt = $connection->prepare($insertQuery);
    $stmt->bind_param("iis", $optionId, $langId, $name);
    $selectQuery = "SELECT id, name_data FROM custom_fields_options";
    $customFieldOptions = $connection->query($selectQuery);
    if ($customFieldOptions && $customFieldOptions->num_rows > 0) {
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = $customFieldOptions->fetch_assoc()) {
            $rowCount++;
            $optionId = $row['id'];
            if (!empty($row['name_data'])) {
                $nameData = unserialize($row['name_data']);
                if ($nameData !== false && is_array($nameData) && !empty($nameData)) {
                    foreach ($nameData as $item) {
                        if (is_array($item) && isset($item['lang_id'], $item['name'])) {
                            $langId = $item['lang_id'];
                            $name = $item['name'];
                            $stmt->execute();
                        }
                    }
                }
            }
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmt->close();
    }

    //set general settings
    $result = runQuery("SELECT * FROM general_settings WHERE id = 1");
    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $dataEmail = [
            'mail_service' => $row['mail_service'] ?? '',
            'mail_protocol' => $row['mail_protocol'] ?? '',
            'mail_encryption' => $row['mail_encryption'] ?? '',
            'mail_host' => $row['mail_host'] ?? '',
            'mail_port' => $row['mail_port'] ?? '',
            'mail_username' => $row['mail_username'] ?? '',
            'mail_password' => $row['mail_password'] ?? '',
            'mail_reply_to' => $row['mail_reply_to'] ?? '',
            'mail_title' => $row['mail_title'] ?? ''
        ];
        $serializedEmail = serialize($dataEmail);
        $dataWatermark = [
            'w_text' => $row['watermark_text'] ?? '',
            'w_font_size' => $row['watermark_font_size'] ?? '',
            'w_product_images' => $row['watermark_product_images'] ?? 0,
            'w_blog_images' => $row['watermark_blog_images'] ?? 0,
            'w_thumbnail_images' => $row['watermark_thumbnail_images'] ?? 0,
            'w_vrt_alignment' => $row['watermark_vrt_alignment'] ?? 'center',
            'w_hor_alignment' => $row['watermark_hor_alignment'] ?? 'center'
        ];
        $serializedWatermark = serialize($dataWatermark);
        $dataAffiliate = [
            'status' => $row['affiliate_status'] ?? 0,
            'type' => $row['affiliate_type'] ?? '',
            'image' => $row['affiliate_image'] ?? 0,
            'storage' => 'local',
            'commission_rate' => $row['affiliate_commission_rate'] ?? 0,
            'discount_rate' => $row['affiliate_discount_rate'] ?? 0
        ];
        $serializedAffiliate = serialize($dataAffiliate);
        $dataNewsletter = [
            'status' => $row['newsletter_status'] ?? 0,
            'is_popup_active' => $row['newsletter_popup'] ?? 0,
            'image' => $row['newsletter_image'] ?? '',
            'storage' => 'local'
        ];
        $serializedNewsletter = serialize($dataNewsletter);
        $stmt = $connection->prepare("UPDATE general_settings SET email_settings = ?, watermark_settings = ?, affiliate_settings = ?, newsletter_settings = ?  WHERE id = 1");
        $stmt->bind_param("ssss", $serializedEmail, $serializedWatermark, $serializedAffiliate, $serializedNewsletter);
        $stmt->execute();
        $stmt->close();
        $resultStorage = runQuery("SELECT * FROM storage_settings WHERE id = 1");
        if ($resultStorage && $resultStorage->num_rows > 0) {
            $rowStorage = $resultStorage->fetch_assoc();
            $dataStorage = [
                'storage' => $rowStorage['storage'] ?? 'local',
                'aws_key' => $rowStorage['aws_key'] ?? '',
                'aws_secret' => $rowStorage['aws_secret'] ?? '',
                'aws_bucket' => $rowStorage['aws_bucket'] ?? '',
                'aws_region' => $rowStorage['aws_region'] ?? '',
                'b2_key' => '',
                'b2_secret' => '',
                'b2_bucket' => '',
                'b2_endpoint_url' => '',
                'b2_public_url' => '',
                'r2_key' => '',
                'r2_secret' => '',
                'r2_bucket' => '',
                'r2_endpoint_url' => '',
                'r2_public_url' => ''
            ];
            $serializedStorage = serialize($dataStorage);
            $stmt = $connection->prepare("UPDATE general_settings SET storage_settings = ? WHERE id = 1");
            $stmt->bind_param("s", $serializedStorage);
            $stmt->execute();
            $stmt->close();
        }
    }

    //update order prices
    $updateQuery = "UPDATE orders SET price_subtotal = ?, price_vat = ?, price_shipping = ?, price_total = ?, coupon_discount = ?, transaction_fee = ? WHERE id = ?";
    $stmt = $connection->prepare($updateQuery);
    $stmt->bind_param("ssssssi", $priceSubtotal, $priceVat, $priceShipping, $priceTotal, $couponDiscount, $transactionFee, $orderId
    );
    $selectQuery = "SELECT * FROM orders";
    $orders = $connection->query($selectQuery);
    if ($orders && $orders->num_rows > 0) {
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = $orders->fetch_assoc()) {
            $rowCount++;
            $orderId = $row['id'];
            $priceSubtotal = '0.00';
            if (is_numeric($row['price_subtotal'])) {
                $priceSubtotal = number_format($row['price_subtotal'] / 100, 2, '.', '');
            }
            $priceVat = '0.00';
            if (is_numeric($row['price_vat'])) {
                $priceVat = number_format($row['price_vat'] / 100, 2, '.', '');
            }
            $priceShipping = '0.00';
            if (is_numeric($row['price_shipping'])) {
                $priceShipping = number_format($row['price_shipping'] / 100, 2, '.', '');
            }
            $priceTotal = '0.00';
            if (is_numeric($row['price_total'])) {
                $priceTotal = number_format($row['price_total'] / 100, 2, '.', '');
            }
            $couponDiscount = '0.00';
            if (is_numeric($row['coupon_discount'])) {
                $couponDiscount = number_format($row['coupon_discount'] / 100, 2, '.', '');
            }
            $transactionFee = '0.00';
            if (is_numeric($row['transaction_fee'])) {
                $transactionFee = number_format($row['transaction_fee'] / 100, 2, '.', '');
            }
            $stmt->execute();
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmt->close();
    }

    //update order items prices
    $updateQuery = "UPDATE order_items SET product_unit_price = ?, product_vat = ?, product_total_price = ?, seller_shipping_cost = ? WHERE id = ?";
    $stmt = $connection->prepare($updateQuery);
    $stmt->bind_param("ssssi",
        $productUnitPrice, $productVat, $productTotalPrice, $sellerShippingCost, $orderItemId
    );
    $selectQuery = "SELECT * FROM order_items";
    $orders = $connection->query($selectQuery);
    if ($orders && $orders->num_rows > 0) {
        $rowCount = 0;
        $connection->begin_transaction();
        while ($row = $orders->fetch_assoc()) {
            $rowCount++;
            $orderItemId = $row['id'];
            $productUnitPrice = '0.00';
            if (is_numeric($row['product_unit_price'])) {
                $productUnitPrice = number_format($row['product_unit_price'] / 100, 2, '.', '');
            }
            $productVat = '0.00';
            if (is_numeric($row['product_vat'])) {
                $productVat = number_format($row['product_vat'] / 100, 2, '.', '');
            }
            $productTotalPrice = '0.00';
            if (is_numeric($row['product_total_price'])) {
                $productTotalPrice = number_format($row['product_total_price'] / 100, 2, '.', '');
            }
            $sellerShippingCost = '0.00';
            if (is_numeric($row['seller_shipping_cost'])) {
                $sellerShippingCost = number_format($row['seller_shipping_cost'] / 100, 2, '.', '');
            }
            $stmt->execute();
            if ($rowCount % $batchSize === 0) {
                $connection->commit();
                $connection->begin_transaction();
            }
        }
        $connection->commit();
        $stmt->close();
    }

    //update products
    $updateQuery = "UPDATE products SET price = ?, price_discounted = ?, image_cache = ? WHERE id = ?";
    $stmtUpdateProduct = $connection->prepare($updateQuery);
    $stmtUpdateProduct->bind_param("sssi", $price, $priceDiscounted, $imageCacheJson, $productId);
    $imageQuery = "SELECT image_small as image, storage FROM images WHERE product_id = ? ORDER BY is_main DESC, id ASC LIMIT 2";
    $stmtSelectImages = $connection->prepare($imageQuery);
    $stmtSelectImages->bind_param("i", $productId);
    try {
        $selectProductsQuery = "SELECT id, price, price_discounted FROM products";
        $products = $connection->query($selectProductsQuery);
        if ($products && $products->num_rows > 0) {
            $rowCount = 0;
            $connection->begin_transaction();
            while ($row = $products->fetch_assoc()) {
                $rowCount++;
                $productId = $row['id'];
                $price = '0.00';
                if (is_numeric($row['price'])) {
                    $price = number_format($row['price'] / 100, 2, '.', '');
                }
                $priceDiscounted = '0.00';
                if (is_numeric($row['price_discounted'])) {
                    $priceDiscounted = number_format($row['price_discounted'] / 100, 2, '.', '');
                }
                $stmtSelectImages->execute();
                $imagesResult = $stmtSelectImages->get_result();
                $productImages = $imagesResult->fetch_all(MYSQLI_ASSOC);
                $imageCacheJson = json_encode($productImages);
                $stmtUpdateProduct->execute();
                if ($rowCount % $batchSize === 0) {
                    $connection->commit();
                    $connection->begin_transaction();
                }
            }
            $connection->commit();
        }
    } catch (Exception $e) {
    } finally {
        if (isset($stmtUpdateProduct)) $stmtUpdateProduct->close();
        if (isset($stmtSelectImages)) $stmtSelectImages->close();
    }

    //update tags
    runQuery("INSERT INTO tags (tag, lang_id) SELECT DISTINCT tag, lang_id FROM product_tags_old WHERE tag IS NOT NULL AND tag != ''");
    runQuery("INSERT INTO product_tags (product_id, tag_id) SELECT old.product_id, new.id FROM product_tags_old AS old JOIN tags AS new ON old.tag = new.tag AND old.lang_id = new.lang_id;");

    //update users
    try {
        $updateQuery = "UPDATE users SET payout_info = ? WHERE id = ?";
        $stmt = $connection->prepare($updateQuery);
        $stmt->bind_param("si", $serializedData, $userId);
        $selectQuery = "SELECT * FROM users_payout_accounts";
        $payoutAccounts = $connection->query($selectQuery);
        if ($payoutAccounts && $payoutAccounts->num_rows > 0) {
            $rowCount = 0;
            $connection->begin_transaction();
            while ($row = $payoutAccounts->fetch_assoc()) {
                $rowCount++;
                $userId = $row['user_id'];
                $payoutData = [
                    'paypal_email' => $row['payout_paypal_email'] ?? '',
                    'btc_address' => $row['payout_bitcoin_address'] ?? '',
                    'iban_full_name' => $row['iban_full_name'] ?? '',
                    'iban_country_id' => $row['iban_country_id'] ?? '',
                    'iban_bank_name' => $row['iban_bank_name'] ?? '',
                    'iban_number' => $row['iban_number'] ?? '',
                    'swift_full_name' => $row['swift_full_name'] ?? '',
                    'swift_address' => $row['swift_address'] ?? '',
                    'swift_state' => $row['swift_state'] ?? '',
                    'swift_city' => $row['swift_city'] ?? '',
                    'swift_postcode' => $row['swift_postcode'] ?? '',
                    'swift_country_id' => $row['swift_country_id'] ?? '',
                    'swift_bank_account_holder_name' => $row['swift_bank_account_holder_name'] ?? '',
                    'swift_iban' => $row['swift_iban'] ?? '',
                    'swift_code' => $row['swift_code'] ?? '',
                    'swift_bank_name' => $row['swift_bank_name'] ?? '',
                    'swift_bank_branch_city' => $row['swift_bank_branch_city'] ?? '',
                    'swift_bank_branch_country_id' => $row['swift_bank_branch_country_id'] ?? ''
                ];
                $serializedData = serialize($payoutData);
                $stmt->execute();
                if ($rowCount % $batchSize === 0) {
                    $connection->commit();
                    $connection->begin_transaction();
                }
            }
            $connection->commit();
        }
    } catch (Exception $e) {
    } finally {
        if (isset($stmt)) $stmt->close();
    }

    //delete removed translations
    runQuery("DELETE FROM language_translations WHERE label IN ('add_shipping_class','add_variation','always','aws_base_url','aws_key','aws_secret','aws_storage','backward','by',
    'charge_shipping_for_each_different_product','charge_shipping_for_each_product','confirm_variation','cost_calculation_type','created_variations','default_option', 'default_option_exp','edit_shipping_class',
    'edit_variation','fixed_shipping_cost_for_cart_total','forward','google_recaptcha','half_width','hourly', 'mailjet_email_address','mailjet_email_address_exp','msg_no_created_variations','msg_recaptcha',
    'option_display_type','pagseguro','parent_option','parent_variation', 'pause','priority_none','radio_button','row_width','select_existing_variation','send_email_subscribers','shipping_class','shipping_classes',
    'shipping_classes_exp', 'shipping_class_costs','show_option_images_on_slider','use_default_price','use_different_price_for_options','variations','variations_exp','variation_type','yearly');");

    //add new translations
    $p["meta_title"] = "Meta Title";
    $p["meta_description"] = "Meta Description";
    $p["meta_keywords"] = "Meta Keywords";
    $p["seo_metadata"] = "SEO & Metadata";
    $p["single_select"] = "Single Select";
    $p["multi_select"] = "Multi Select";
    $p["warning_custom_field_category"] = "The selected custom field will automatically apply to all subcategories of the chosen category. Therefore, there is no need to select subcategories individually.";
    $p["add_tag"] = "Add Tag";
    $p["msg_tag_exists"] = "This tag already exists!";
    $p["select_action"] = "Select Action";
    $p["ai_writer"] = "AI Writer";
    $p["ai_content_creator"] = "AI Content Creator";
    $p["enter_topic"] = "Enter topic";
    $p["generated_text"] = "Generated Text";
    $p["generate_text"] = "Generate Text";
    $p["generating_text"] = "Generating text...";
    $p["length_of_text"] = "Length of Text";
    $p["long"] = "Long";
    $p["medium"] = "Medium";
    $p["model"] = "Model";
    $p["msg_topic_empty"] = "Topic cannot be empty!";
    $p["refresh"] = "Refresh";
    $p["regenerate"] = "Regenerate";
    $p["short"] = "Short";
    $p["tone_academic"] = "Academic";
    $p["tone_casual"] = "Casual";
    $p["tone_critical"] = "Critical";
    $p["tone_formal"] = "Formal";
    $p["tone_humorous"] = "Humorous";
    $p["tone_inspirational"] = "Inspirational";
    $p["tone_persuasive"] = "Persuasive";
    $p["tone_professional"] = "Professional";
    $p["tone_style"] = "Tone/Style";
    $p["topic"] = "Topic";
    $p["use_text"] = "Use Text";
    $p["very_long"] = "Very Long";
    $p["very_short"] = "Very Short";
    $p["too_many_attempts"] = "Too many login attempts. Please try again after 5 minutes.";
    $p["product_options"] = "Product Options";
    $p["product_options_exp"] = "Add available options, like color or size that buyers can choose during checkout";
    $p["add_product_option"] = "Add Product Option";
    $p["variants"] = "Variants";
    $p["activate_selected"] = "Activate Selected";
    $p["deactivate_delected"] = "Deactivate Selected";
    $p["variant_name"] = "Variant Name";
    $p["add_value"] = "Add Value";
    $p["option_type"] = "Option Type";
    $p["domain"] = "Domain";
    $p["sender_email_address"] = "Sender Email Address";
    $p["weight"] = "Weight";
    $p["msg_change_product_option_type"] = "Changing the option type will delete all existing values/variants for this option. Are you sure you want to continue?";
    $p["confirm"] = "Confirm";
    $p["selected_images"] = "Selected Images";
    $p["manage_images"] = "Manage Images";
    $p["value"] = "Value";
    $p["option_name_placeholder"] = "e.g. Color, Size";
    $p["radio_buttons"] = "Radio Buttons";
    $p["swatch_color"] = "Swatch - Color";
    $p["swatch_image"] = "Swatch - Image";
    $p["text_input"] = "Text Input";
    $p["number_input"] = "Number Input";
    $p["create_variants"] = "Create Variants";
    $p["create_extra_options"] = "Create Extra Options";
    $p["decimal_seperator"] = "Decimal Seperator";
    $p["enter_two_characters"] = "Enter at least 2 characters";
    $p["searching"] = "Searching...";
    $p["endpoint_url"] = "Endpoint URL";
    $p["public_url"] = "Public URL";
    $p["special_offer"] = "Special Offer";
    $p["msg_cart_login_error"] = "Please log in to complete your purchase!";
    $p["complete_payment"] = "Complete Payment";
    $p["loading_payment_options"] = "Loading payment options...";
    $p["shop_id"] = "Shop ID";
    $p["cart_payment"] = "Cart Payment";
    $p["yoomoney"] = "YooMoney";
    $p["payment_option_load_error"] = "The selected payment method is currently unavailable. Please choose a different method or try again later.";
    $p["webhook_secret"] = "Webhook Secret";
    $p["confirm_and_pay"] = "Confirm and Pay";
    $p["dlocal_go"] = "dLocal Go";
    $p["razorpay"] = "Razorpay";
    $p["flutterwave"] = "Flutterwave";
    $p["midtrans"] = "Midtrans";
    $p["currency_not_supported"] = "The selected payment method cannot be used with the chosen currency. To complete your purchase, please select a supported currency or choose another payment method.";
    $p["supported_currencies"] = "Supported Currencies";
    $p["paytabs"] = "PayTabs";
    $p["dlocalgo"] = "dLocal Go";
    $p["kg"] = "kg";
    $p["dimensions"] = "Dimensions";
    $p["length"] = "Length";
    $p["cm"] = "cm";
    $p["calculated_weight"] = "Calculated Weight";
    $p["min_weight"] = "Min Weight";
    $p["max_weight"] = "Max Weight";
    $p["add_rate"] = "Add Rate";
    $p["flat_rate_rules"] = "Flat Rate Rules";
    $p["free_shipping_exp"] = "Get your order delivered for free";
    $p["local_pickup_exp"] = "Pick up your order directly from the store";
    $p["flat_rate_exp"] = "Standard shipping";
    $p["rebuild_category_paths"] = "Rebuild Category Paths";
    $p["key_id"] = "Key ID";
    $p["application_key"] = "Application Key";
    $p["watermark_text"] = "Watermark Text";
    $p["product_image_display_mode"] = "Product Image Display Mode";
    $p["img_display_fill_container"] = "Fill Container (Crop to Fit)";
    $p["img_display_show_full_image"] = "Show Full Image (Fit Inside)";
    $p["msg_processing_coupon_assignments"] = "Processing coupon assignments. Please do not close this window.";
    $p["product_cache_system"] = "Product Cache System";
    $p["static_content_cache"] = "Static Content Cache";
    $p["category_cache"] = "Category Cache";
    $p["reference"] = "Reference";
    $p["products_per_row_homepage"] = "Products per Row on Homepage";
    $p["show_vendor_contact_info_guests"] = "Show Vendor Contact Information to Guests";
    $p["all_sellers"] = "All Sellers";
    $p["only_affiliate_sellers"] = "Only Affiliate Sellers";
    $p["minimum_deposit_amount"] = "Minimum Deposit Amount";
    $p["automatically_calculated"] = "Automatically Calculated";
    $p["generated_sitemaps"] = "Generated Sitemaps";
    $p["leave_message"] = "Leave Message";
    $p["default_variant"] = "Default Variant";
    $p["msg_default_variant"] = "The default variant will automatically use the product's main price, discounted price, stock quantity and weight.";
    $p["msg_bot_verification_failed"] = "Bot verification failed. Please try again.";
    $p["cloudflare_turnstile"] = "Cloudflare Turnstile (CAPTCHA) ";
    $p["msg_verification_required"] = "Verification is required.";
    $p["cart_location_selection"] = "Cart Location Selection";
    $p["cart_location_selection_exp"] = "Require customers to select their location in the cart if it is not already known";
    $p["featured_product_fees"] = "Featured (Promoted) Product Fees";
    $p["mail_is_being_sent"] = "Mail is being sent. Please do not close this page until the process is finished!";
    $p["shipping_calculation_type"] = "Shipping Calculation Type";
    $p["shipping_by_total_weight"] = "Calculate by Total Weight";
    $p["shipping_by_per_order"] = "Fixed Price Per Order";
    $p["shipping_by_per_item"] = "Fixed Price Per Item";
    $p["shipping_cost_calculation_exp"] = "This cost will be applied based on your selection (per order or per item).";
    $p["shipping_method_exp"] = "To use this shipping zone, you need to add at least one shipping method";
    $p["local_pickup_cost"] = "Local Pickup Cost";
    $p["confirm_duplicate_product"] = "Do you want to create a duplicate of this product? It will be saved as a draft.";
    $p["duplicate"] = "Duplicate";
    addTranslations($p);

    //reset session records
    runQuery("TRUNCATE TABLE `ci_sessions`;");

    //add indexes
    runQuery("ALTER TABLE `affiliate_links` ADD INDEX `idx_link_short` (`link_short`);");
    runQuery("ALTER TABLE `brands` ADD INDEX `idx_name` (`name`), ADD INDEX `idx_show_on_slider` (`show_on_slider`);");
    runQuery("ALTER TABLE `brand_category` ADD INDEX `idx_brand_category` (`brand_id`, `category_id`);");
    runQuery("ALTER TABLE `brand_lang` ADD INDEX `idx_brand_lang` (`brand_id`, `lang_id`, `name`);");
    runQuery("ALTER TABLE `carts` ADD INDEX `idx_user_id` (`user_id`), ADD INDEX `idx_session_id` (`session_id`);");
    runQuery("ALTER TABLE `cart_items` ADD INDEX `idx_cart_id` (`cart_id`);");
    runQuery("ALTER TABLE `categories` ADD INDEX `idx_status` (`status`);");
    runQuery("ALTER TABLE `category_lang` ADD INDEX `idx_name` (`name`), ADD INDEX `idx_lang_category` (`lang_id`, `category_id`);");
    runQuery("ALTER TABLE `category_paths` ADD INDEX `idx_ancestor_id` (`ancestor_id`), ADD INDEX `idx_descendant_id` (`descendant_id`), ADD INDEX `idx_depth` (`depth`);");
    runQuery("ALTER TABLE `category_paths` ADD INDEX `idx_descendant_ancestor` (`descendant_id`, `ancestor_id`);");
    runQuery("ALTER TABLE `checkouts` ADD INDEX `idx_user_id` (`user_id`), ADD INDEX `idx_session_id` (`session_id`), ADD INDEX `idx_status` (`status`);");
    runQuery("ALTER TABLE `checkout_items` ADD INDEX `idx_checkout_id` (`checkout_id`);");
    runQuery("ALTER TABLE `ci_sessions` ADD INDEX `ci_sessions_id` (`id`);");
    runQuery("ALTER TABLE `custom_field_lang` ADD INDEX `idx_field_lang` (`field_id`, `lang_id`);");
    runQuery("ALTER TABLE `custom_field_option_lang` ADD INDEX `idx_option_lang` (`option_id`, `lang_id`);");
    runQuery("ALTER TABLE `images` ADD INDEX `idx_is_option_image` (`is_option_image`);");
    runQuery("ALTER TABLE `order_items` ADD INDEX `idx_image_id` (`image_id`);");
    runQuery("ALTER TABLE `products` ADD INDEX `idx_active_products` (`is_active`, `is_promoted`, `created_at`,`user_id`);");
    runQuery("ALTER TABLE `products` ADD INDEX `idx_category_products` (`is_active`, `category_id`, `is_promoted`, `created_at`, `user_id`);");
    runQuery("ALTER TABLE `products` ADD INDEX `idx_active_user_products` (`user_id`, `is_active`);");
    runQuery("ALTER TABLE `products` ADD INDEX `idx_products_admin` (`is_deleted`, `is_draft`, `status`, `user_id`, `visibility`);");
    runQuery("ALTER TABLE `product_details` ADD INDEX `idx_product_lang` (`product_id`, `lang_id`);");
    runQuery("ALTER TABLE `product_options` ADD INDEX `idx_product_id` (`product_id`), ADD INDEX `idx_is_active` (`is_active`);");
    runQuery("ALTER TABLE `product_option_values` ADD INDEX `idx_option_id` (`option_id`);");
    runQuery("ALTER TABLE `product_option_variants` ADD INDEX `idx_product_id` (`product_id`), ADD INDEX `idx_sku` (`sku`), ADD INDEX `idx_is_default` (`is_default`), ADD INDEX `idx_is_active` (`is_active`), ADD INDEX `idx_variant_hash` (`variant_hash`);");
    runQuery("ALTER TABLE `product_option_variant_values` ADD INDEX `idx_variant_value` (`variant_id`, `value_id`);");
    runQuery("ALTER TABLE `product_tags` ADD INDEX `idx_tag_id` (`tag_id`), ADD INDEX `idx_product_id` (`product_id`);");
    runQuery("ALTER TABLE `tags` ADD INDEX `idx_tag` (`tag`), ADD INDEX `idx_lang_id` (`lang_id`);");
    runQuery("ALTER TABLE `transactions` ADD INDEX `idx_checkout_token` (`checkout_token`);");
    runQuery("ALTER TABLE `users` ADD INDEX `idx_username` (`username`), ADD INDEX `idx_slug` (`slug`), ADD INDEX `idx_active_user` (`banned`, `vacation_mode`, `is_membership_plan_expired`);");
    runQuery("ALTER TABLE `users_membership_plans` ADD INDEX `idx_user_id` (`user_id`);");
    runQuery("ALTER TABLE `wallet_expenses` ADD INDEX `idx_user_id` (`user_id`);");
    runQuery("ALTER TABLE `wishlist` ADD INDEX `idx_product_user` (`product_id`,`user_id`);");
    runQuery("ALTER TABLE `comments` ADD INDEX `idx_product_comments` (`product_id`,`parent_id`,`status`);");

    runQuery("ALTER TABLE `orders` ADD CONSTRAINT `uq_orders_checkout_token` UNIQUE (`checkout_token`);");
    runQuery("ALTER TABLE `wallet_deposits` ADD CONSTRAINT `uq_deposits_checkout_token` UNIQUE (`checkout_token`);");
    runQuery("ALTER TABLE `promoted_transactions` ADD CONSTRAINT `uq_promoted_checkout_token` UNIQUE (`checkout_token`);");
    runQuery("ALTER TABLE `membership_transactions` ADD CONSTRAINT `uq_membership_checkout_token` UNIQUE (`checkout_token`);");

    $result = runQuery("SELECT COUNT(1) AS idx_count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = DATABASE() AND table_name = 'products' AND index_name = 'idx_created_at';");
    $row = $result->fetch_assoc();
    $idxCreatedAtExists = $row['idx_count'] ?? 0;
    if(!$idxCreatedAtExists){
        runQuery("ALTER TABLE `products` ADD INDEX `idx_created_at` (`created_at`);");
    }

    //update bulk upload documentation
    runQuery("UPDATE `settings` SET `bulk_upload_documentation` = '<p>With the <strong>bulk product upload</strong> feature, you can easily upload or update multiple products at once using a CSV file. This saves time and ensures consistency when managing a large number of products.</p>\n<p><br><strong>How It Works</strong></p>\n<p>There are two main ways to use the bulk upload feature:</p>\n<p><br><strong>1. Add New Products</strong></p>\n<ol>\n<li>Download the <strong>CSV template</strong> from this section.</li>\n<li>Add your products into the CSV file by filling in the required and optional columns.</li>\n<li>Upload the completed CSV file back into the system.</li>\n</ol>\n<p><em>Important:</em> Please pay attention to the data type of each column (e.g., numbers, text, dates). You can see detailed explanations of all required and optional columns in the table below.</p>\n<p><strong>2. Edit Existing Products</strong></p>\n<ol>\n<li>Add an <strong>\"id\"</strong> column to the CSV template. (You can find the product ID numbers on the <strong>Products</strong> page.)</li>\n<li>Include only the columns you want to update (for example, price, stock, etc.).</li>\n<li>Upload the updated CSV file to apply the changes.</li>\n</ol>\n<p>For example, if you want to update the stock and prices of your products, your CSV file should look like this:</p>\n<pre style=\"background: #f7f7f7; padding: 10px; border-radius: 6px; overflow-x: auto;\">\"id\",\"price\",\"price_discounted\",\"stock\"\n\"1\",\"30\",\"20\",\"1000\"\n\"5\",\"40\",\"40\",\"500\"\n</pre>\n<p style=\"color: #ba372a;\">* To update product prices, you must include both the <strong>\"price\"</strong> and <strong>\"price_discounted\"</strong> columns in your CSV file.</p>\n<hr>\n<p><strong>Multi-language Support</strong></p>\n<p>The CSV upload feature also supports <strong>multiple languages</strong>. This allows you to add product information in more than one language, making your store multilingual.</p>\n<p><strong>How to Add Multiple Languages</strong></p>\n<ol>\n<li>Each language in your system has a <strong>Language ID</strong> (for example, English = 1, German = 2). You can find the language ID numbers on the <strong>Languages</strong> page.</li>\n<li>In your CSV file, add extra columns for each language, following this format:\n<ul>\n<li>For the main language: <code>title</code>, <code>short_description</code>, <code>description</code>, <code>tags</code></li>\n<li>For a second language with ID 2: <code>title_lang2</code>, <code>short_description_lang2</code>, <code>description_lang2</code>, <code>tags_lang2</code></li>\n</ul>\n</li>\n<li>When you upload your CSV file, the system will automatically detect and save the translations.</li>\n</ol>\n<p><strong>Example</strong></p>\n<pre style=\"background: #f7f7f7; padding: 10px; border-radius: 6px; overflow-x: auto;\">\"id\",\"title\",\"short_description\",\"description\",\"tags\",....,\"title_lang2\",\"short_description_lang2\",\"description_lang2\",\"tags_lang2\"\n\"1\",\"Red Dress\",\"Beautiful red dress\",\"A lovely red dress for summer\",\"dress,red,fashion\",....,\"Rotes Kleid\",\"Schönes rotes Kleid\",\"Ein hübsches rotes Kleid für den Sommer\",\"kleid,rot,mode\"\n</pre>\n<p>In this example: - The product is added in English (main language). - The same product details are also added in German (Language ID: 2). - Both language versions will be available on your site.</p>\n<p><br><br></p>\n<p><span style=\"font-size: 12pt;\"><strong>CSV Columns</strong></span></p>\n<table style=\"width: 100%;\" class=\"table table-bordered\">\n<tbody>\n<tr>\n<th>Column</th>\n<th>Description</th>\n</tr>\n<tr>\n<td style=\"width: 180px;\">title</td>\n<td>Data Type: Text <br><strong>Required</strong><br>Example: Modern grey couch and pillows</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">slug</td>\n<td>Data Type: Text <br><strong>Optional</strong> <small>(If you leave it empty, it will be generated automatically.)</small> <br>Example: modern-grey-couch-and-pillows</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">sku</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example: MD-GR-6898</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">category_id</td>\n<td>Data Type: Number <br><strong>Required</strong><br>Example: 1</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">brand_id</td>\n<td>Data Type: Number <br><strong>Optional</strong><br>Example: 1</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">price</td>\n<td>Data Type: Decimal/Number <br><strong>Required</strong><br>Example: 50<br>Example: 45.90<br>Example: 3456.25</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">price_discounted</td>\n<td>Data Type: Decimal/Number <br><strong>Optional</strong><br>Example: 40<br>Example: 35.90<br>Example: 2456.25</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">vat_rate</td>\n<td>Data Type: Number (Percentage) <br><strong>Optional</strong><br>Example: 8</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">stock</td>\n<td>Data Type: Number <br><strong>Required</strong><br>Example: 100</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">short_description</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example: It is a nice and comfortable couch</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">description</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example: It is a nice and comfortable couch...</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">tags</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example: nice, comfortable, couch</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">image_url</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example:<br>https://upload.wikimedia.org/wikipedia/commons/7/70/Labrador-sea-paamiut.jpg<br><br>Example:<br>https://upload.wikimedia.org/wikipedia/commons/7/70/Labrador-sea-paamiut.jpg,<br>https://upload.wikimedia.org/wikipedia/commons/thumb/4/42/Shaqi_jrvej.jpg/1600px-Shaqi_jrvej.jpg<br><br><span style=\"color: rgb(186, 55, 42);\">*You can add multiple image links by placing commas between them.</span></td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">external_link</td>\n<td>Data Type: Text <br><strong>Optional</strong><br>Example: https://domain.com/product_url</td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">updated_at</td>\n<td>Data Type: Timestamp <br><strong>Optional</strong><br>Example: 2024-06-30 10:27:00 <br><br><span style=\"color: rgb(186, 55, 42);\">*If you leave it blank, the system will not assign an update date.</span></td>\n</tr>\n<tr>\n<td style=\"width: 180px;\">created_at</td>\n<td>Data Type: Timestamp <br><strong>Optional</strong><br>Example: 2024-06-30 10:27:00 <br><br><span style=\"color: rgb(186, 55, 42);\">*If you leave it blank, the system will automatically assign the current date.</span></td>\n</tr>\n</tbody>\n</table>';");

    //drop tables/columns
    runQuery("ALTER TABLE brands DROP COLUMN `name_data`;");
    runQuery("ALTER TABLE brands DROP COLUMN `category_data`;");
    runQuery("ALTER TABLE categories DROP COLUMN `tree_id`;");
    runQuery("ALTER TABLE categories DROP COLUMN `level`;");
    runQuery("ALTER TABLE categories DROP COLUMN `parent_tree`;");
    runQuery("ALTER TABLE categories DROP COLUMN `title_meta_tag`;");
    runQuery("ALTER TABLE categories DROP COLUMN `description`;");
    runQuery("ALTER TABLE categories DROP COLUMN `keywords`;");
    runQuery("ALTER TABLE custom_fields DROP COLUMN `name_array`;");
    runQuery("ALTER TABLE custom_fields DROP COLUMN `row_width`;");
    runQuery("ALTER TABLE custom_fields_options DROP COLUMN `name_data`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_service`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_protocol`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_encryption`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_host`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_port`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_username`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_password`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_reply_to`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mail_title`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `recaptcha_site_key`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `recaptcha_secret_key`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `recaptcha_lang`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mailjet_api_key`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mailjet_secret_key`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `mailjet_email_address`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_text`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_font_size`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_product_images`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_blog_images`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_thumbnail_images`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_vrt_alignment`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `watermark_hor_alignment`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `affiliate_status`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `affiliate_type`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `affiliate_image`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `affiliate_commission_rate`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `affiliate_discount_rate`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `newsletter_status`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `newsletter_popup`;");
    runQuery("ALTER TABLE general_settings DROP COLUMN `newsletter_image`;");
    runQuery("ALTER TABLE order_items DROP COLUMN `product_slug`;");
    runQuery("ALTER TABLE payment_gateways DROP COLUMN `base_currency`;");
    runQuery("ALTER TABLE products DROP COLUMN `shipping_class_id`;");
    runQuery("ALTER TABLE shipping_zone_methods DROP COLUMN `flat_rate_cost_calculation_type`;");
    runQuery("ALTER TABLE shipping_zone_methods DROP COLUMN `flat_rate_class_costs_array`;");
    runQuery("ALTER TABLE shipping_zone_methods DROP COLUMN `flat_rate_cost`;");
    runQuery("DROP TABLE storage_settings;");
    runQuery("DROP TABLE images_variation;");
    runQuery("DROP TABLE payments;");
    runQuery("DROP TABLE product_tags_old;");
    runQuery("DROP TABLE shipping_classes;");
    runQuery("DROP TABLE users_payout_accounts;");
    runQuery("DROP TABLE variations;");
    runQuery("DROP TABLE variation_options;");

    //clear cache
    $cacheDir = __DIR__ . '/writable/cache';
    if (is_dir($cacheDir)) {
        $files = glob($cacheDir . '/*');
        if ($files !== false) {
            foreach ($files as $file) {
                if (is_file($file) && basename($file) !== 'index.html') {
                    @unlink($file);
                }
            }
        }
    }
}

function addTranslations($translations)
{
    global $connection;

    $languagesResult = runQuery("SELECT id FROM languages;");
    if (!$languagesResult || $languagesResult->num_rows === 0) {
        return;
    }
    $languages = $languagesResult->fetch_all(MYSQLI_ASSOC);

    $placeholders = [];
    $params = [];
    $types = '';
    foreach ($translations as $key => $value) {
        $placeholders[] = '(?, ?, ?)';
        $params[] = null;
        $params[] = $key;
        $params[] = $value;
        $types .= 'iss';
    }
    if (empty($placeholders)) {
        return;
    }
    $sql = "INSERT IGNORE INTO language_translations (`lang_id`, `label`, `translation`) VALUES " . implode(', ', $placeholders);
    $stmt = $connection->prepare($sql);
    $stmt->bind_param($types, ...$params);
    foreach ($languages as $language) {
        for ($i = 0; $i < count($params); $i += 3) {
            $params[$i] = $language['id'];
        }
        $stmt->execute();
    }
    $stmt->close();
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Modesy - Update Wizard</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <link href="https://fonts.googleapis.com/css?family=Poppins:400,500,700" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.css" rel="stylesheet"/>
    <style>
        body {
            font-family: 'Poppins', sans-serif;
            color: #444 !important;
            font-size: 14px;
            background: #007991;
            background: -webkit-linear-gradient(to left, #007991, #6fe7c2);
            background: linear-gradient(to left, #007991, #6fe7c2);
        }

        .logo-cnt {
            text-align: center;
            color: #fff;
            padding: 60px 0 60px 0;
        }

        .logo-cnt .logo {
            font-size: 42px;
            line-height: 42px;
        }

        .logo-cnt p {
            font-size: 22px;
        }

        .install-box {
            width: 100%;
            padding: 30px;
            left: 0;
            right: 0;
            top: 0;
            bottom: 0;
            margin: auto;
            background-color: #fff;
            border-radius: 4px;
            display: block;
            float: left;
            margin-bottom: 100px;
        }

        .form-input {
            box-shadow: none !important;
            border: 1px solid #ddd;
            height: 44px;
            line-height: 44px;
            padding: 0 20px;
        }

        .form-input:focus {
            border-color: #239CA1 !important;
        }

        .btn-custom {
            background-color: #239CA1 !important;
            border-color: #239CA1 !important;
            border: 0 none;
            border-radius: 4px;
            box-shadow: none;
            color: #fff !important;
            font-size: 16px;
            font-weight: 300;
            height: 40px;
            line-height: 40px;
            margin: 0;
            min-width: 105px;
            padding: 0 20px;
            text-shadow: none;
            vertical-align: middle;
        }

        .btn-custom:hover, .btn-custom:active, .btn-custom:focus {
            background-color: #239CA1;
            border-color: #239CA1;
            opacity: .8;
        }

        .tab-content {
            width: 100%;
            float: left;
            display: block;
        }

        .tab-footer {
            width: 100%;
            float: left;
            display: block;
        }

        .buttons {
            display: block;
            float: left;
            width: 100%;
            margin-top: 30px;
        }

        .title {
            font-size: 24px;
            font-weight: 600;
            margin-bottom: 10px;
            margin-top: 0;
            text-align: center;
        }

        .sub-title {
            font-size: 14px;
            font-weight: 400;
            margin-bottom: 30px;
            margin-top: 0;
            text-align: center;
        }

        .alert {
            text-align: center;
        }

        .alert strong {
            font-weight: 500 !important;
        }

        .text-danger {
            color: #dc3545 !important;
        }
    </style>
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-md-8 col-sm-12 col-md-offset-2">
            <div class="row">
                <div class="col-sm-12 logo-cnt">
                    <h1>Modesy</h1>
                    <p>Welcome to the Update Wizard</p>
                </div>
            </div>
            <div class="row">
                <div class="col-sm-12">
                    <div class="install-box">
                        <h2 class="title">Update from v2.4.x to v2.6.3</h2>
                        <br><br>
                        <div class="messages">
                            <?php if (!empty($error)) { ?>
                                <div class="alert alert-danger">
                                    <strong><?= $error; ?></strong>
                                </div>
                            <?php } ?>
                            <?php if (!empty($success)) { ?>
                                <div class="alert alert-success">
                                    <strong><?= $success; ?></strong>
                                    <style>.alert-info {
                                            display: none;
                                        }</style>
                                </div>
                            <?php } ?>
                        </div>
                        <div class="step-contents">
                            <div class="tab-1">
                                <?php if (empty($success)): ?>
                                    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
                                        <input type="hidden" name="license_code" value="<?= !empty($license_code) ? $license_code : ''; ?>">
                                        <input type="hidden" name="purchase_code" value="<?= !empty($purchase_code) ? $purchase_code : ''; ?>">
                                        <div class="tab-content">
                                            <div class="tab_1">
                                                <p class="text-danger fw-medium">
                                                    <i class="fa fa-warning"></i>&nbsp;&nbsp;<strong>Important:</strong> Before starting the update, make sure to <strong>back up your database</strong>.
                                                    You can do this in phpMyAdmin by using the <em>"Export"</em> option and saving it as a <code>.sql</code> file.
                                                </p>

                                                <p class="text-danger fw-medium">
                                                    <i class="fa fa-warning"></i>&nbsp;&nbsp;<strong>Update duration</strong> depends on the number of records in your database.
                                                    If you have a large amount of data (e.g., 10,000+ products), you may need to increase the
                                                    <strong>max_execution_time</strong> value in your PHP settings to prevent the process from timing out.
                                                </p>

                                                <p class="text-danger fw-medium">
                                                    <i class="fa fa-warning"></i>&nbsp;&nbsp;<strong>If an error occurs</strong> or the process is interrupted, you will need to drop the database,
                                                    restore your backup using the <em>"Import"</em> option in phpMyAdmin, and try the update again.
                                                </p>

                                                <hr>
                                                <p class="text-success text-center" style="font-weight: 500;">Enter your database credentials and click the button to update the database.</p>
                                                <div class="form-group">
                                                    <label for="email">Host</label>
                                                    <input type="text" class="form-control form-input" name="db_host" placeholder="Host" value="<?= !empty($data['db_host']) ? $data['db_host'] : 'localhost'; ?>" required>
                                                </div>
                                                <div class="form-group">
                                                    <label for="email">Database Name</label>
                                                    <input type="text" class="form-control form-input" name="db_name" placeholder="Database Name" value="<?= !empty($data['db_name']) ? $data['db_name'] : ''; ?>" required>
                                                </div>
                                                <div class="form-group">
                                                    <label for="email">Username</label>
                                                    <input type="text" class="form-control form-input" name="db_user" placeholder="Username" value="<?= !empty($data['db_user']) ? $data['db_user'] : ''; ?>" required>
                                                </div>
                                                <div class="form-group">
                                                    <label for="email">Password</label>
                                                    <input type="text" class="form-control form-input" name="db_password" placeholder="Password" value="<?= !empty($data['db_password']) ? $data['db_password'] : ''; ?>">
                                                </div>
                                            </div>
                                        </div>
                                        <div class="buttons text-center">
                                            <button type="submit" name="btnUpdate" class="btn btn-success btn-custom" style="width: 100%; height: 50px;">Update My Database</button>
                                        </div>
                                    </form>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
</body>
</html>