from odoo import api, SUPERUSER_ID
from openupgradelib import openupgrade
import xml.etree.ElementTree as ET
import lxml.etree as ET
from odoo import models, fields, api, _
import logging

_logger = logging.getLogger(__name__)


# upgrade version in manifest
# add try-catch in migration mehtod
# problem is : o2m/m2m field can't find in this table

def _get_record_xml_ids(env, remove_fields):
    query = """
        SELECT ir_model_data.module, ir_model_data.name, ir_model_data.id
        FROM ir_ui_view
        JOIN ir_model_data ON ir_model_data.res_id = ir_ui_view.id
        WHERE (ir_ui_view.arch_db::text ILIKE '%offer_msg%'
                OR ir_ui_view.arch_db::text ILIKE '%product_tags_ids%')
        AND ir_model_data.model = 'ir.ui.view'
        AND ir_model_data.module = 'ecommerce';
    """
    try:
        env.cr.execute(query)
        results = env.cr.fetchall()
        xml_ids = ['{}.{}'.format(result[0], result[1]) for result in results]
        return xml_ids
    except Exception as e:
        _logger.exception("An error occurred while fetching XML IDs: {}".format(e))
        return []


def _get_table_with_column(env, remove_fields):
    column_spec = []
    query = """
        SELECT table_name
        FROM information_schema.columns
        WHERE column_name ILIKE %s;
    """
    try:
        for field in remove_fields:
            env.cr.execute(query, (field,))
            tables = env.cr.fetchall()
            column_spec.extend([(table[0], field) for table in tables])
    except Exception as e:
        _logger.exception("An error occurred while fetching column information: {}".format(e))
    return column_spec


@openupgrade.migrate()
def migrate(env, version):
    remove_fields = ['product_tags_ids', 'offer_msg']
    xml_ids = _get_record_xml_ids(env, remove_fields)
    openupgrade.delete_records_safely_by_xml_id(env, xml_ids, True)

    column_spec = _get_table_with_column(env, remove_fields)
    openupgrade.drop_columns(env, column_spec)
    # openupgrade.drop_columns(
    #     env.cr,
    #     [
    #         ("product_request", "offer_msg"),
    #         ("product_request", "product_tags_ids"),
    #     ]
    # )