main.py

import os
from openpyxl import load_workbook
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
import base64
import xmlrpc.client
from dotenv import load_dotenv

load_dotenv()

# Google Drive API setup
SCOPES = ['https://www.googleapis.com/auth/drive.readonly']
creds = None
if creds is None or not creds.valid:
    flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
    creds = flow.run_local_server(port=0)
drive_service = build('drive', 'v3', credentials=creds)

# Odoo connection details
url = os.getenv("URL")
db = os.getenv("DB")
username = os.getenv("USERNAME")
password = os.getenv("PASSWORD")
common = xmlrpc.client.ServerProxy(f'{url}/xmlrpc/2/common')
uid = common.authenticate(db, username, password, {})
models = xmlrpc.client.ServerProxy(f'{url}/xmlrpc/2/object')

# Path to the Excel file
excel_path = "matched_products.xlsx"

# Load the workbook and select the first sheet
workbook = load_workbook(excel_path)
sheet = workbook["Sheet1"]

def get_file_id_from_name(file_name, drive_service):
    try:
        query = f"name = '{file_name}'"
        results = drive_service.files().list(q=query, fields="files(id, name)").execute()
        items = results.get('files', [])
        if items:
            return items[0]['id']
        print(f"❌ File '{file_name}' not found in Google Drive.")
        return None
    except HttpError as error:
        print(f"❌ Error fetching file from Drive: {error}")
        return None

def get_image_data(file_id, drive_service):
    try:
        request = drive_service.files().get_media(fileId=file_id)
        image_data = request.execute()
        return base64.b64encode(image_data).decode('utf-8')
    except HttpError as error:
        print(f"❌ Failed to fetch image data: {error}")
        return None

def resolve_external_id_to_template_id(external_id):
    try:
        # Split "__export__.product_template_4447_a5aab893"
        if external_id.startswith("__export__."):
            module, name = external_id.split(".", 1)
        else:
            module, name = "custom_module", external_id

        domain = [('model', '=', 'product.template'), ('module', '=', module), ('name', '=', name.split('.')[-1])]
        data = models.execute_kw(db, uid, password, 'ir.model.data', 'search_read',
                                 [domain], {'fields': ['res_id'], 'limit': 1})
        if data:
            return data[0]['res_id']
        return None
    except Exception as e:
        print(f"❌ Error resolving external_id {external_id}: {e}")
        return None

print("🚀 Starting image upload process...")

# Skip header row
for row in sheet.iter_rows(min_row=2, values_only=True):
    external_id, image_filename = row

    if not external_id or not image_filename:
        print(f"⚠️ Skipping row with missing data: {row}")
        continue

    # Step 1: Find the image file on Drive
    file_id = get_file_id_from_name(image_filename, drive_service)
    if not file_id:
        print(f"❌ Skipping {external_id} due to missing image file: {image_filename}")
        continue

    image_data = get_image_data(file_id, drive_service)
    if not image_data:
        print(f"❌ Failed to download image: {image_filename}")
        continue

    # Step 2: Resolve external ID to template ID
    template_id = resolve_external_id_to_template_id(external_id)
    if not template_id:
        print(f"❌ Could not find product.template for external_id: {external_id}")
        continue

    # Step 3: Upload the image as product.image
    media_data = {
        'name': f"Image for {external_id}",
        'image_1920': image_data,
        'product_tmpl_id': template_id,
    }
    try:
        media_id = models.execute_kw(db, uid, password, 'product.image', 'create', [media_data])
        print(f"✅ Uploaded image for {external_id} → Media ID: {media_id}")
    except Exception as e:
        print(f"❌ Failed to create product.image for {external_id}: {e}")

print("✅ Process complete.")