from django.shortcuts import render, redirect , get_object_or_404
from django.contrib.auth import authenticate, login, logout
from django.contrib import messages
from .forms import *
from .models import *
from django.db import connection, transaction
from django.views.decorators.cache import never_cache
from django.core import serializers
from django.http import JsonResponse
from django.template.loader import render_to_string
from django.forms.models import model_to_dict
from django_user_agents.utils import get_user_agent
from django.conf import settings
from django.db.models import Q
from django.db.models import F
from datetime import datetime
from datetime import timedelta
from django.core.mail import EmailMessage
from django.http import HttpResponse
from django.contrib.auth.models import User
from urllib.parse import parse_qs
from django.http import FileResponse
# apparently openpyxl import * is not working to replace from openpyxl import Workbook
#import openpyxl.workbook.workbook
from openpyxl import Workbook, load_workbook #to add to requirement.txt
from openpyxl.styles import * #to add to requirement.txt
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
import re
import subprocess
import os
import operator
from functools import reduce
import copy
from urllib.parse import parse_qs
import json
#from openpyxl.styles import Font, Alignment, PatternFill




####################################################################################
# REFACTOR STEP 3 - dual-write helpers for new relational tables
# Important: old field names and old logic are kept. These helpers only mirror data
# into the new clean relational tables added in step 1.
####################################################################################

def _legacy_split_ids(raw_value):
    return [str(x).strip() for x in (raw_value or '').strip(';').split(';') if str(x).strip() and str(x).strip() != '0']


def _legacy_join_ids(values, include_zero=False):
    clean_values = []
    for value in values or []:
        value = str(value or '').strip(';').strip()
        if value and value != '0' and value not in clean_values:
            clean_values.append(value)

    if include_zero:
        return ';0;' + ';'.join(clean_values) + ';' if clean_values else ';0;'

    return ';' + ';'.join(clean_values) + ';' if clean_values else ''



def _get_contact_ids_for_company(company_id):
    # STEP 28 : lecture directe depuis table_company_contacts.
    # Le champ legacy company_members_id n'est plus utilisé comme fallback ici.
    company = models_companies.objects.filter(id_company=company_id).first()
    if not company:
        return []

    return [
        str(contact_id)
        for contact_id in models_company_contacts.objects.filter(
            company=company
        ).exclude(contact_id='0').values_list('contact__id_contact', flat=True)
        if str(contact_id)
    ]


def _get_company_ids_for_contact(contact_id, include_zero=False):
    # STEP 28 : lecture directe depuis table_company_contacts.
    # Le champ legacy company_members_id n'est plus utilisé comme fallback ici.
    contact = models_contacts.objects.filter(id_contact=contact_id).first()
    ids = []

    if contact:
        ids = list(models_company_contacts.objects.filter(
            contact=contact
        ).exclude(company_id='0').values_list('company__id_company', flat=True))

    ids = [str(x) for x in ids if str(x) and str(x) != '0']

    if include_zero:
        ids.append('0')

    return ids


def _get_project_ids_for_company(company_id):
    # STEP 28 : lecture directe depuis table_company_projects.
    # Le champ legacy company_projects_id n'est plus utilisé comme fallback ici.
    company = models_companies.objects.filter(id_company=company_id).first()
    if not company:
        return []

    return [
        str(project_id)
        for project_id in models_company_projects.objects.filter(
            company=company
        ).exclude(project_id='0').values_list('project__id_project', flat=True)
        if str(project_id)
    ]


def _get_company_ids_for_project(project_id):
    # STEP 28 : lecture directe depuis table_company_projects.
    # Le champ legacy company_projects_id n'est plus utilisé comme fallback ici.
    project = models_projects.objects.filter(id_project=project_id).first()
    ids = []

    if project:
        ids = list(models_company_projects.objects.filter(
            project=project
        ).exclude(company_id='0').values_list('company__id_company', flat=True))

    return [str(x) for x in ids if str(x) and str(x) != '0']


def _get_address_ids_for_company(company_id):
    # STEP 28 : lecture directe depuis table_company_addresses.
    # Le champ legacy company_addresses_id n'est plus utilisé comme fallback ici.
    company = models_companies.objects.filter(id_company=company_id).first()
    if not company:
        return []

    return [
        str(address_id)
        for address_id in models_company_addresses.objects.filter(
            company=company
        ).exclude(address_id='0').values_list('address__id_address', flat=True)
        if str(address_id)
    ]


def _get_sales_document_ids_for_project(project_id):
    # STEP 28 : lecture directe depuis table_project_sales_documents.
    # Le champ legacy project_sales_document n'est plus utilisé comme fallback ici.
    project = models_projects.objects.filter(id_project=project_id).first()
    if not project:
        return []

    return [
        str(sales_document_id)
        for sales_document_id in models_project_sales_documents.objects.filter(
            project=project
        ).exclude(sales_document_id='0').values_list('sales_document__id_sales_documents', flat=True)
        if str(sales_document_id)
    ]


def _get_company_ids_for_interaction(interaction):
    # STEP 28 : lecture directe depuis table_interaction_companies.
    # Le champ legacy interaction_company_id n'est plus utilisé comme fallback ici.
    if not interaction:
        return []

    return [
        str(company_id)
        for company_id in models_interaction_companies.objects.filter(
            interaction=interaction
        ).exclude(company_id='0').values_list('company__id_company', flat=True)
        if str(company_id)
    ]

def _sync_company_contact_link(company_id, contact_id, add=True):
    company_id = str(company_id or '').strip(';').strip()
    contact_id = str(contact_id or '').strip(';').strip()

    if not company_id or not contact_id or company_id == '0' or contact_id == '0':
        return

    company = models_companies.objects.filter(id_company=company_id).first()
    contact = models_contacts.objects.filter(id_contact=contact_id).first()

    if not company or not contact:
        return

    if add:
        models_company_contacts.objects.get_or_create(company=company, contact=contact)
    else:
        models_company_contacts.objects.filter(company=company, contact=contact).delete()

    _rebuild_company_members_legacy_from_relations(company.id_company)

def _sync_company_contacts_from_legacy(company):
    # PALIER FINAL C1 : conversion depuis company_members_id désactivée.
    # Les liens actifs sont uniquement dans table_company_contacts.
    return


def _rebuild_company_members_legacy_from_relations(company_id):
    # PALIER FINAL B : table_company_contacts est maintenant la seule source du lien société/contact.
    # On ne reconstruit plus le miroir legacy company_members_id.
    return


def _sync_company_project_link(company_id, project_id, add=True):
    company_id = str(company_id or '').strip(';').strip()
    project_id = str(project_id or '').strip(';').strip()

    if not company_id or not project_id or company_id == '0' or project_id == '0':
        return

    company = models_companies.objects.filter(id_company=company_id).first()
    project = models_projects.objects.filter(id_project=project_id).first()

    if not company or not project:
        return

    if add:
        models_company_projects.objects.get_or_create(company=company, project=project)
    else:
        models_company_projects.objects.filter(company=company, project=project).delete()

    _rebuild_company_projects_legacy_from_relations(company.id_company)


def _sync_company_projects_from_legacy(company):
    # PALIER FINAL C1 : conversion depuis company_projects_id désactivée.
    # Les liens actifs sont uniquement dans table_company_projects.
    return


def _rebuild_company_projects_legacy_from_relations(company_id):
    # PALIER FINAL B : table_company_projects est maintenant la seule source du lien société/projet.
    # On ne reconstruit plus le miroir legacy company_projects_id.
    return


def _sync_company_address_link(company_id, address_id, add=True):
    company_id = str(company_id or '').strip(';').strip()
    address_id = str(address_id or '').strip(';').strip()

    if not company_id or not address_id or company_id == '0' or address_id == '0':
        return

    company = models_companies.objects.filter(id_company=company_id).first()
    address = models_addresses.objects.filter(id_address=address_id).first()

    if not company or not address:
        return

    if add:
        models_company_addresses.objects.get_or_create(company=company, address=address)
    else:
        models_company_addresses.objects.filter(company=company, address=address).delete()

    _rebuild_company_addresses_legacy_from_relations(company.id_company)


def _sync_company_addresses_from_legacy(company):
    # PALIER FINAL C1 : conversion depuis company_addresses_id désactivée.
    # Les liens actifs sont uniquement dans table_company_addresses.
    return


def _rebuild_company_addresses_legacy_from_relations(company_id):
    # PALIER FINAL B : table_company_addresses est maintenant la seule source du lien société/adresse.
    # On ne reconstruit plus le miroir legacy company_addresses_id.
    return


def _rebuild_company_relation_legacy_fields_from_relations(company_id):
    # PALIER FINAL B : les miroirs relationnels société sont gelés.
    # Les liens actifs sont exclusivement dans les tables relationnelles.
    return

def _sync_project_sales_document_link(project_id, sales_document_id, add=True):
    project_id = str(project_id or '').strip(';').strip()
    sales_document_id = str(sales_document_id or '').strip(';').strip()

    if not project_id or not sales_document_id or project_id == '0' or sales_document_id == '0':
        return

    project = models_projects.objects.filter(id_project=project_id).first()
    sales_document = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()

    if not project or not sales_document:
        return

    if add:
        models_project_sales_documents.objects.get_or_create(project=project, sales_document=sales_document)
    else:
        models_project_sales_documents.objects.filter(project=project, sales_document=sales_document).delete()

    _rebuild_project_sales_documents_legacy_from_relations(project.id_project)


def _sync_project_sales_documents_from_legacy(project):
    # PALIER FINAL C1 : conversion depuis project_sales_document désactivée.
    # Les liens actifs sont uniquement dans table_project_sales_documents.
    return


def _rebuild_project_sales_documents_legacy_from_relations(project_id):
    # PALIER FINAL B : table_project_sales_documents est maintenant la seule source du lien projet/document.
    # On ne reconstruit plus le miroir legacy project_sales_document.
    return

def _safe_int(value, default=0):
    try:
        if value in [None, '']:
            return default
        return int(value)
    except (TypeError, ValueError):
        return default

def _sync_sales_document_lines_from_post(doc_instance, post_data):
    if not doc_instance:
        return

    # PALIER D8d : le POST de l'éditeur principal ne doit plus supprimer les lignes
    # dynamiques absentes de son DOM fixe. Chaque ligne présente dans le POST est
    # remplacée, tandis que les lignes supplémentaires créées par le prototype sont conservées.
    detected_line_numbers = set()
    for key in post_data.keys():
        key = str(key)
        for prefix in [
            'sales_documents_index_line_',
            'sales_documents_quantity_',
            'sales_documents_description_',
            'sales_documents_description_free_',
            'sales_documents_price_',
            'sales_documents_discount_',
            'sales_documents_calculated_price_',
            'sales_documents_discounted_price_',
            'sales_documents_vat_',
        ]:
            if key.startswith(prefix):
                suffix = key.replace(prefix, '', 1)
                if suffix.isdigit():
                    detected_line_numbers.add(int(suffix))
                break

    if not detected_line_numbers:
        detected_line_numbers = set(range(1, 26))

    used_positions = set()
    ordered_line_numbers = sorted(detected_line_numbers)
    max_fallback_position = max(len(ordered_line_numbers), max(ordered_line_numbers, default=25), 25)

    # Ne remplace que les lignes effectivement envoyées par le formulaire.
    # Une ligne 26+ créée dans le prototype reste donc intacte lors d'une sauvegarde
    # de l'ancien éditeur principal limité à 25 lignes.
    models_sales_document_lines.objects.filter(
        sales_document=doc_instance,
        sales_documents_line_number__in=ordered_line_numbers,
    ).delete()

    preserved_positions = models_sales_document_lines.objects.filter(
        sales_document=doc_instance,
    ).values_list('sales_documents_line_position', flat=True)
    used_positions.update(position for position in preserved_positions if position)

    for i in ordered_line_numbers:
        raw_position = post_data.get(f'sales_documents_index_line_{i}', '')
        line_position = _safe_int(raw_position, i)

        if line_position < 1 or line_position in used_positions:
            line_position = i

        if line_position in used_positions:
            for fallback in range(1, max_fallback_position + 1):
                if fallback not in used_positions:
                    line_position = fallback
                    break

        used_positions.add(line_position)

        product_id = (post_data.get(f'sales_documents_description_{i}', '') or '').strip()
        product = None
        if product_id and product_id not in ['#', '0']:
            product = models_products.objects.filter(id_product=product_id).first()

        models_sales_document_lines.objects.create(
            sales_document=doc_instance,
            sales_documents_line_number=i,
            sales_documents_line_position=line_position,
            product=product,
            sales_documents_quantity=post_data.get(f'sales_documents_quantity_{i}', '') or '',
            sales_documents_description_free=post_data.get(f'sales_documents_description_free_{i}', '') or '',
            sales_documents_price=post_data.get(f'sales_documents_price_{i}', '') or '',
            sales_documents_discount=post_data.get(f'sales_documents_discount_{i}', '') or '',
            sales_documents_calculated_price=post_data.get(f'sales_documents_calculated_price_{i}', '') or '',
            sales_documents_discounted_price=post_data.get(f'sales_documents_discounted_price_{i}', '') or '',
            sales_documents_vat=_safe_int(post_data.get(f'sales_documents_vat_{i}', ''), 0),
        )

def _hydrate_sales_document_legacy_fields_from_relation_lines(doc_instance):
    """
    STEP 30:
    Remplit en mémoire les anciens champs sales_documents_*_1..25 depuis
    table_sales_document_lines, sans jamais relire les anciennes colonnes comme fallback.

    But : table_sales_document_lines devient la seule source de lecture pour les lignes.
    Les anciens noms restent seulement des miroirs techniques pour les templates/forms/exports.
    """
    if not doc_instance:
        return False

    lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc_instance)
        .select_related('product')
        .order_by('sales_documents_line_number')
    )

    # Remise à zéro en mémoire des 25 anciennes lignes techniques.
    # Important : même si aucune ligne relationnelle n'existe, on n'utilise plus
    # les anciennes colonnes sales_documents_*_1..25 comme fallback de lecture.
    for i in range(1, 26):
        setattr(doc_instance, f'sales_documents_index_line_{i}', str(i))
        setattr(doc_instance, f'sales_documents_quantity_{i}', '')
        setattr(doc_instance, f'sales_documents_description_{i}', '')
        setattr(doc_instance, f'sales_documents_description_free_{i}', '')
        setattr(doc_instance, f'sales_documents_price_{i}', '')
        setattr(doc_instance, f'sales_documents_discount_{i}', '')
        setattr(doc_instance, f'sales_documents_calculated_price_{i}', '')
        setattr(doc_instance, f'sales_documents_discounted_price_{i}', '')
        setattr(doc_instance, f'sales_documents_vat_{i}', 0)

    for line in lines:
        i = _safe_int(getattr(line, 'sales_documents_line_number', ''), 0)
        if i < 1 or i > 25:
            continue

        setattr(doc_instance, f'sales_documents_index_line_{i}', str(line.sales_documents_line_position or i))
        setattr(doc_instance, f'sales_documents_quantity_{i}', line.sales_documents_quantity or '')
        setattr(doc_instance, f'sales_documents_description_{i}', str(line.product_id or ''))
        setattr(doc_instance, f'sales_documents_description_free_{i}', line.sales_documents_description_free or '')
        setattr(doc_instance, f'sales_documents_price_{i}', line.sales_documents_price or '')
        setattr(doc_instance, f'sales_documents_discount_{i}', line.sales_documents_discount or '')
        setattr(doc_instance, f'sales_documents_calculated_price_{i}', line.sales_documents_calculated_price or '')
        setattr(doc_instance, f'sales_documents_discounted_price_{i}', line.sales_documents_discounted_price or '')
        setattr(doc_instance, f'sales_documents_vat_{i}', line.sales_documents_vat or 0)

    return bool(lines)

def _rebuild_sales_document_lines_legacy_from_relations(doc_instance):
    """
    STEP 20: table_sales_document_lines devient la source principale des lignes.
    Les anciens champs sales_documents_*_1..25 restent reconstruits uniquement
    pour compatibilité temporaire avec les formulaires/templates existants.
    """
    if not doc_instance:
        return

    lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc_instance)
        .select_related('product')
        .order_by('sales_documents_line_number')
    )

    line_by_number = {
        _safe_int(getattr(line, 'sales_documents_line_number', ''), 0): line
        for line in lines
    }

    update_fields = []

    for i in range(1, 26):
        line = line_by_number.get(i)

        if line:
            values = {
                f'sales_documents_index_line_{i}': str(line.sales_documents_line_position or i),
                f'sales_documents_quantity_{i}': line.sales_documents_quantity or '',
                f'sales_documents_description_{i}': str(line.product_id or ''),
                f'sales_documents_description_free_{i}': line.sales_documents_description_free or '',
                f'sales_documents_price_{i}': line.sales_documents_price or '',
                f'sales_documents_discount_{i}': line.sales_documents_discount or '',
                f'sales_documents_calculated_price_{i}': line.sales_documents_calculated_price or '',
                f'sales_documents_discounted_price_{i}': line.sales_documents_discounted_price or '',
                f'sales_documents_vat_{i}': line.sales_documents_vat or 0,
            }
        else:
            values = {
                f'sales_documents_index_line_{i}': str(i),
                f'sales_documents_quantity_{i}': '',
                f'sales_documents_description_{i}': '',
                f'sales_documents_description_free_{i}': '',
                f'sales_documents_price_{i}': '',
                f'sales_documents_discount_{i}': '',
                f'sales_documents_calculated_price_{i}': '',
                f'sales_documents_discounted_price_{i}': '',
                f'sales_documents_vat_{i}': 0,
            }

        for field_name, value in values.items():
            setattr(doc_instance, field_name, value)
            update_fields.append(field_name)

    doc_instance.save(update_fields=update_fields)



def _sync_sales_document_lines_from_instance(doc_instance):
    """
    REFACTOR STEP 10:
    Synchronise table_sales_document_lines depuis les anciens champs encore présents
    sur l'instance. Utile notamment après duplication d'un document.
    """
    if not doc_instance:
        return

    models_sales_document_lines.objects.filter(sales_document=doc_instance).delete()
    used_positions = set()

    for i in range(1, 26):
        raw_position = getattr(doc_instance, f'sales_documents_index_line_{i}', '')
        line_position = _safe_int(raw_position, i)

        if line_position < 1 or line_position > 25 or line_position in used_positions:
            line_position = i

        if line_position in used_positions:
            for fallback in range(1, 26):
                if fallback not in used_positions:
                    line_position = fallback
                    break

        used_positions.add(line_position)

        product_id = str(getattr(doc_instance, f'sales_documents_description_{i}', '') or '').strip()
        product = None
        if product_id and product_id not in ['#', '0']:
            product = models_products.objects.filter(id_product=product_id).first()

        models_sales_document_lines.objects.create(
            sales_document=doc_instance,
            sales_documents_line_number=i,
            sales_documents_line_position=line_position,
            product=product,
            sales_documents_quantity=getattr(doc_instance, f'sales_documents_quantity_{i}', '') or '',
            sales_documents_description_free=getattr(doc_instance, f'sales_documents_description_free_{i}', '') or '',
            sales_documents_price=getattr(doc_instance, f'sales_documents_price_{i}', '') or '',
            sales_documents_discount=getattr(doc_instance, f'sales_documents_discount_{i}', '') or '',
            sales_documents_calculated_price=getattr(doc_instance, f'sales_documents_calculated_price_{i}', '') or '',
            sales_documents_discounted_price=getattr(doc_instance, f'sales_documents_discounted_price_{i}', '') or '',
            sales_documents_vat=_safe_int(getattr(doc_instance, f'sales_documents_vat_{i}', ''), 0),
        )

def _sync_sales_document_deposits_from_legacy(doc_instance):
    if not doc_instance:
        return

    ids = _legacy_split_ids(getattr(doc_instance, 'sales_documents_deposit_id', ''))

    def _split_keep_zero(raw_value):
        return [str(x).strip() for x in (raw_value or '').strip(';').split(';') if str(x).strip()]

    numbers = _split_keep_zero(getattr(doc_instance, 'sales_documents_deposit_number', ''))
    dates = _split_keep_zero(getattr(doc_instance, 'sales_documents_deposit_date', ''))
    amounts = _split_keep_zero(getattr(doc_instance, 'sales_documents_deposit_amount', ''))
    methods = _split_keep_zero(getattr(doc_instance, 'sales_documents_deposit_method', ''))

    models_sales_document_deposits.objects.filter(sales_document=doc_instance).delete()

    for idx, deposit_id in enumerate(ids):
        models_sales_document_deposits.objects.create(
            sales_document=doc_instance,
            sales_documents_deposit_id=deposit_id,
            sales_documents_deposit_number=numbers[idx] if idx < len(numbers) else '',
            sales_documents_deposit_date=dates[idx] if idx < len(dates) else '',
            sales_documents_deposit_amount=amounts[idx] if idx < len(amounts) else '',
            sales_documents_deposit_method=methods[idx] if idx < len(methods) else '',
        )

def _hydrate_sales_document_legacy_deposits_from_relation_deposits(doc_instance):
    if not doc_instance:
        return False

    # STEP 29 : table_sales_document_deposits est désormais la source de lecture.
    # On remet d'abord les anciens champs en mémoire à vide pour éviter qu'une
    # ancienne valeur legacy stale soit affichée si aucune ligne relationnelle n'existe.
    doc_instance.sales_documents_deposit_id = ''
    doc_instance.sales_documents_deposit_number = ''
    doc_instance.sales_documents_deposit_date = ''
    doc_instance.sales_documents_deposit_amount = ''
    doc_instance.sales_documents_deposit_method = ''

    deposits = list(
        models_sales_document_deposits.objects
        .filter(sales_document=doc_instance)
        .order_by('id_sales_document_deposit')
    )

    if not deposits:
        return True

    def _join_with_semi(values):
        clean_values = [str(value).strip() for value in values if str(value).strip()]
        return ';' + ';'.join(clean_values) + ';' if clean_values else ''

    doc_instance.sales_documents_deposit_id = _join_with_semi([d.sales_documents_deposit_id for d in deposits])
    doc_instance.sales_documents_deposit_number = _join_with_semi([d.sales_documents_deposit_number for d in deposits])
    doc_instance.sales_documents_deposit_date = _join_with_semi([d.sales_documents_deposit_date for d in deposits])
    doc_instance.sales_documents_deposit_amount = _join_with_semi([d.sales_documents_deposit_amount for d in deposits])
    doc_instance.sales_documents_deposit_method = _join_with_semi([d.sales_documents_deposit_method for d in deposits])

    return True

def _rebuild_interaction_company_legacy_from_relations(interaction_id):
    # PALIER FINAL B : table_interaction_companies est maintenant la seule source du lien interaction/société.
    # On ne reconstruit plus le miroir legacy interaction_company_id.
    return

def _sync_interaction_companies_from_ids(interaction_instance, company_ids):
    if not interaction_instance:
        return

    # STEP 27 : table_interaction_companies pilote maintenant directement
    # le lien interaction/société. Le champ legacy interaction_company_id
    # est reconstruit ensuite uniquement pour compatibilité temporaire.
    clean_company_ids = []
    for company_id in company_ids or []:
        company_id = str(company_id or '').strip(';').strip()
        if company_id and company_id != '0' and company_id not in clean_company_ids:
            clean_company_ids.append(company_id)

    models_interaction_companies.objects.filter(interaction=interaction_instance).delete()

    for company_id in clean_company_ids:
        company = models_companies.objects.filter(id_company=company_id).first()
        if company:
            models_interaction_companies.objects.get_or_create(
                interaction=interaction_instance,
                company=company
            )

    _rebuild_interaction_company_legacy_from_relations(interaction_instance.id_interaction)


def _sync_interaction_companies_from_legacy(interaction_instance):
    # PALIER FINAL C1 : conversion depuis interaction_company_id désactivée.
    # Les liens actifs sont uniquement dans table_interaction_companies.
    return


####################################################################################
def export_to_excel_or_pdf(request, xlsx_or_pdf, current_sales_documents_id):
    try:


        if xlsx_or_pdf not in ['xlsx', 'pdf', 'pdf_mail']:
            return JsonResponse({'error': 'Invalid export format'}, status=400)

        sales_documents_queryset = models_sales_documents.objects.filter(id_sales_documents=current_sales_documents_id)
        if not sales_documents_queryset.exists():
            return JsonResponse({'error': 'Document not found'}, status=404)

        doc = sales_documents_queryset[0]
        _hydrate_sales_document_legacy_fields_from_relation_lines(doc)

        billing_address_id = getattr(doc, 'sales_documents_billing_address_id', '')
        billing_address = models_addresses.objects.filter(id_address=billing_address_id).first()
        line1 = f"{billing_address.address_number or ''} {billing_address.address_street_name_1 or ''}".strip() if billing_address else ""
        line2 = f"{billing_address.address_postal_code or ''} - {billing_address.address_city or ''}".strip() if billing_address else ""

        contact_id = getattr(doc, 'sales_documents_ref_contact_id', '')

        document_type = request.GET.get('document_type', '')
        if document_type == 'quote':
            document_type = 'Devis'
        if document_type == 'delivery_note':
            document_type = 'Bon de livraison'
        if document_type == 'invoice':
            document_type = 'Facture'
        document_number = request.GET.get('document_number', '')
        company_id = request.GET.get('company_num', '')
        contact = models_contacts.objects.filter(id_contact=contact_id).first()
        company = models_companies.objects.filter(id_company=company_id).first()
        contact_str_1 = f"{contact.contact_gender or ''} {contact.contact_last_name or ''} {contact.contact_first_name or ''}".strip() if contact else ""
        contact_str_2 = f"{contact.contact_phone_1 or ''} {contact.contact_email_1 or ''}".strip() if contact else ""
        company_str = f"{company.company_name or ''}".strip() if company else ""

        total_cat1 = request.GET.get('total_category_1', '')
        total_cat2 = request.GET.get('total_category_2', '')
        total_cat3 = request.GET.get('total_category_3', '')

        product_id_to_name = {
            str(p.id_product): p.product_denomination
            for p in models_products.objects.all()
        }

        doc_dict = model_to_dict(doc)
        suffixes = sorted({k.split("_")[-1] for k in doc_dict if k.split("_")[-1].isdigit()})
        list_of_line_dicts = [
            {k: v for k, v in doc_dict.items() if k.endswith(f"_{suffix}")}
            for suffix in suffixes
        ]
        index_line_map = []
        for line_dict in list_of_line_dicts:
            for k, v in line_dict.items():
                if "sales_documents_index_line_" in k:
                    index_line_map.append({str(v): k.split("_")[-1]})

        workbook = load_workbook(os.path.join(settings.BASE_DIR, "Tonal_Devis_Template.xlsx"))
        worksheet = workbook["Sheet_1"]

        worksheet.page_margins.left = 0.12
        worksheet.page_margins.right = 0.12
        worksheet.page_margins.top = 0.12
        worksheet.page_margins.bottom = 0.12
        worksheet.column_dimensions['A'].hidden = True
        worksheet.page_setup.horizontalCentered = True

        worksheet.cell(row=2, column=5, value=document_type).alignment = Alignment(horizontal='left', vertical='center')
        worksheet.cell(row=2, column=9, value=document_number).alignment = Alignment(horizontal='right', vertical='center')
        worksheet.merge_cells(start_row=5, start_column=6, end_row=5, end_column=9)
        worksheet.cell(row=5, column=6, value=company_str).alignment = Alignment(horizontal='left', vertical='center')
        worksheet.merge_cells(start_row=6, start_column=6, end_row=6, end_column=9)
        worksheet.cell(row=6, column=6, value=line1).alignment = Alignment(horizontal='left', vertical='center')
        worksheet.merge_cells(start_row=7, start_column=6, end_row=7, end_column=9)
        worksheet.cell(row=7, column=6, value=line2).alignment = Alignment(horizontal='left', vertical='center')
        worksheet.merge_cells(start_row=8, start_column=6, end_row=8, end_column=9)
        worksheet.cell(row=8, column=6, value=contact_str_1).alignment = Alignment(horizontal='left', vertical='center')
        worksheet.merge_cells(start_row=9, start_column=6, end_row=9, end_column=9)
        worksheet.cell(row=9, column=6, value=contact_str_2).alignment = Alignment(horizontal='left', vertical='center')

        img = Image(os.path.join(settings.BASE_DIR, "Tonal_logo.png"))
        img.anchor = TwoCellAnchor(
            editAs="twoCell",
            _from=AnchorMarker(col=1, row=0, colOff=30000, rowOff=30000),
            to=AnchorMarker(col=4, row=4, colOff=-30000, rowOff=-30000)
        )
        worksheet.add_image(img)

        category_total_map = {
            'materiel': {'total': total_cat1, 'fill': PatternFill(start_color="fae9db", end_color="fae9db", fill_type="solid")},
            'etude, conception, realisation': {'total': total_cat3, 'fill': PatternFill(start_color="ebf0df", end_color="ebf0df", fill_type="solid")},
            'transport': {'total': total_cat2, 'fill': PatternFill(start_color="dce5f0", end_color="dce5f0", fill_type="solid")},
        }

        row = 16
        for index in range(len(list_of_line_dicts)):
            row += 1
            line_content, desc, suffix = [], "", ""

            for idx_dict in index_line_map:
                if str(index + 1) in idx_dict:
                    suffix = idx_dict[str(index + 1)]
                    product_id = getattr(doc, f"sales_documents_description_{suffix}", "")
                    desc = product_id_to_name.get(str(product_id), "").strip()
                    desc_key = desc.lower()

                    desc_free = getattr(doc, f"sales_documents_description_free_{suffix}", "")
                    price = getattr(doc, f"sales_documents_price_{suffix}", "")
                    discount = getattr(doc, f"sales_documents_discount_{suffix}", "")
                    quantity = getattr(doc, f"sales_documents_quantity_{suffix}", "")
                    calculated_price = getattr(doc, f"sales_documents_calculated_price_{suffix}", "")
                    discounted_price = getattr(doc, f"sales_documents_discounted_price_{suffix}", "")
                    line_content = [desc, desc_free, price, quantity, calculated_price, discount, discounted_price]

            category_info = category_total_map.get(desc_key, None)
            is_last_of_category = True
            for future_index in range(index + 1, len(list_of_line_dicts)):
                for idx_dict in index_line_map:
                    if str(future_index + 1) in idx_dict:
                        future_suffix = idx_dict[str(future_index + 1)]
                        future_product_id = getattr(doc, f"sales_documents_description_{future_suffix}", "")
                        future_desc = product_id_to_name.get(str(future_product_id), "").strip().lower()
                        if future_desc == desc_key:
                            is_last_of_category = False
                            break
                if not is_last_of_category:
                    break

            if category_info and is_last_of_category:
                line_content[-1] = category_info['total']
            else:
                line_content[-1] = discounted_price

            fill = category_info['fill'] if category_info else None

            if desc_key == 'materiel':
                worksheet.cell(row=row, column=2, value=desc.upper()).alignment = Alignment(horizontal='left', vertical='center')
                worksheet.cell(row=row, column=2).font = Font(name='Arial', size=10, bold=True)
                if fill: worksheet.cell(row=row, column=2).fill = fill

                worksheet.cell(row=row, column=3, value="").alignment = Alignment(vertical='center')
                if fill: worksheet.cell(row=row, column=3).fill = fill

                worksheet.cell(row=row, column=4, value=desc_free).alignment = Alignment(horizontal='right', vertical='center')
                worksheet.cell(row=row, column=4).font = Font(name='Arial', size=10)
                if fill: worksheet.cell(row=row, column=4).fill = fill

                for col_index, value in zip(range(5, 10), line_content[2:]):
                    cell = worksheet.cell(row=row, column=col_index, value=value)
                    if col_index == 5:
                        cell.alignment = Alignment(horizontal='left', vertical='center')
                        cell.font = Font(name='Arial', size=10, bold=True)
                    elif col_index == 6:
                        cell.alignment = Alignment(horizontal='right', vertical='center')
                        cell.font = Font(name='Arial', size=10, bold=True)
                    elif col_index == 7:
                        cell.alignment = Alignment(horizontal='left', vertical='center')
                    elif col_index == 8:
                        cell.alignment = Alignment(horizontal='center', vertical='center')
                    elif col_index == 9:
                        cell.alignment = Alignment(horizontal='right', vertical='center')
                    if fill:
                        cell.fill = fill
                    if fill and col_index == 9 and is_last_of_category:
                        cell.font = Font(name='Arial', size=10, bold=True)
            else:
                for col in range(2, 5):
                    cell = worksheet.cell(row=row, column=col)
                    cell.alignment = Alignment(vertical='center')
                    if fill:
                        cell.fill = fill

                worksheet.cell(row=row, column=2, value=f"{desc} {desc_free}").alignment = Alignment(horizontal='left', vertical='center')
                worksheet.cell(row=row, column=2).font = Font(name='Arial', size=10, bold=True if fill else False)
                worksheet.merge_cells(start_row=row, start_column=2, end_row=row, end_column=4)

                for col_index, value in zip(range(5, 10), line_content[2:]):
                    cell = worksheet.cell(row=row, column=col_index, value=value)
                    if col_index in [5, 6, 7, 8]:
                        cell.alignment = Alignment(horizontal='center', vertical='center')
                    elif col_index == 9:
                        cell.alignment = Alignment(horizontal='right', vertical='center')
                    if fill:
                        cell.fill = fill
                    if fill and col_index == 9 and is_last_of_category:
                        cell.font = Font(name='Arial', size=10, bold=True)

        worksheet.print_area = f"A1:I{max(row + 1, 54)}"

        excel_path = os.path.join(settings.BASE_DIR, f"Tonal_Devis_{current_sales_documents_id}.xlsx")
        workbook.save(excel_path)

        if xlsx_or_pdf == 'xlsx':
            with open(excel_path, 'rb') as f:
                file_data = f.read()
            os.remove(excel_path)
            response = HttpResponse(
                file_data,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = f'attachment; filename=Tonal_Devis_{current_sales_documents_id}.xlsx'
            return response

        subprocess.run([
            'libreoffice', '--headless', '--convert-to', 'pdf', '--outdir', settings.BASE_DIR, excel_path
        ], check=True)

        pdf_path = os.path.join(settings.BASE_DIR, f"Tonal_Devis_{current_sales_documents_id}.pdf")
        if not os.path.exists(pdf_path):
            return JsonResponse({'error': 'PDF conversion failed'}, status=500)

        with open(pdf_path, 'rb') as f:
            pdf_data = f.read()

        # Supprime les fichiers temporaires
        os.remove(pdf_path)
        if os.path.exists(excel_path):
            os.remove(excel_path)

        if xlsx_or_pdf == 'pdf_mail':
            email_to = [e.strip() for e in request.POST.get('to', '').replace(';', ',').split(',') if e.strip()]
            email_cc = [e.strip() for e in request.POST.get('cc', '').replace(';', ',').split(',') if e.strip()]
            email_bcc = [e.strip() for e in request.POST.get('bcc', '').replace(';', ',').split(',') if e.strip()]

            subject = request.POST.get('subject', 'Votre devis PDF')
            body_html = request.POST.get('content', '')

            if not email_to:
                return JsonResponse({'error': 'Adresse e-mail du destinataire manquante'}, status=400)

            try:
                email = EmailMessage(
                    subject=subject,
                    body=body_html,
                    from_email=settings.DEFAULT_FROM_EMAIL,
                    to=email_to,
                    cc=email_cc,
                    bcc=email_bcc,
                )

                email.content_subtype = 'html'
                email.attach(f"Tonal_Devis_{current_sales_documents_id}.pdf", pdf_data, 'application/pdf')

                # ✅ Ajout de la pièce jointe personnalisée (si présente)
                for custom_file in request.FILES.getlist('custom_attachment'):
                    email.attach(custom_file.name, custom_file.read(), custom_file.content_type)

                email.send()

                return JsonResponse({'message': f'PDF envoyé à {email_to}'})
            except Exception as e:
                return JsonResponse({'error': f'Échec de l\'envoi du mail : {str(e)}'}, status=500)

        # Sinon : retour PDF standard (téléchargement)
        response = HttpResponse(pdf_data, content_type='application/pdf')
        response['Content-Disposition'] = f'attachment; filename=Tonal_Devis_{current_sales_documents_id}.pdf'
        return response


    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)

def get_email_form_contacts_of_company(request):
    company_id = request.GET.get('company_id')

    if not company_id:
        return JsonResponse({'error': 'Missing company_id'}, status=400)

    company = models_companies.objects.filter(id_company=company_id).first()
    if not company:
        return JsonResponse({'error': 'Company not found'}, status=404)

    # STEP 5: lecture prioritaire depuis table_company_contacts, avec fallback ancien champ company_members_id.
    contact_ids = _get_contact_ids_for_company(company_id)
    contacts = models_contacts.objects.filter(id_contact__in=contact_ids)

    data = [
        {
            'id': c.id_contact,
            'email': c.contact_email_1,
            'first_name': c.contact_first_name,
            'last_name': c.contact_last_name,
        }
        for c in contacts if c.contact_email_1
    ]

    return JsonResponse({'contacts': data})


def get_email_form_django_users(request):
    users = User.objects.filter(email__isnull=False).exclude(email="").order_by('last_name', 'first_name')

    data = [
        {
            'email': user.email,
            'first_name': user.first_name,
            'last_name': user.last_name
        }
        for user in users
    ]
    return JsonResponse({'users': data})


####################################################################################
#Mobile section redo all
def update_record_mobile(request, pk):

    if request.user.is_authenticated:

        #selectbox_all_companies = models_companies.objects.filter(company_members_id__icontains=";"+str(pk)+";")  # list all the companies where pk is a member of

        pk_format = str(';'+str(pk)+';')

        # STEP 36: les sélections mobiles ne lisent plus company_members_id / company_projects_id.
        # Elles utilisent les tables relationnelles, puis gardent les mêmes variables attendues par le template.
        company_ids_for_contact = _get_company_ids_for_contact(pk, include_zero=True)
        query_all_companies_where_pk_is_found = models_companies.objects.filter(id_company__in=company_ids_for_contact)
        selectbox_all_companies = [model_to_dict(company) for company in query_all_companies_where_pk_is_found]
        selectbox_all_companies = sorted(selectbox_all_companies, key=lambda x: x['id_company'] == '0')

        all_project_ids = []
        for company_id in company_ids_for_contact:
            if str(company_id) != '0':
                all_project_ids.extend(_get_project_ids_for_company(company_id))
        all_project_ids = list(dict.fromkeys(str(project_id) for project_id in all_project_ids if str(project_id) and str(project_id) != '0'))
        company_projects_ids_of_all_companies_where_pk_is_found = _legacy_join_ids(all_project_ids)

        query_most_recent_project = models_projects.objects.filter(id_project__in=all_project_ids).order_by('-project_last_modification_date')
        selectbox_projects = [model_to_dict(l) for l in query_most_recent_project]

        if selectbox_projects == []:
            selectbox_most_recent_project = [{'id_project': '0', 'project_last_modification_date': '0', 'project_denomination': 'Comapany to save first'}] #'project_last_modification_date': '0' is mandatory
            formated_most_recent_project_id = str(';' + str([d['id_project'] for d in selectbox_most_recent_project]).replace("[", '').replace("]",'').replace("'", '') + ';')
        else:
            selectbox_most_recent_project = [selectbox_projects[0]]
            formated_most_recent_project_id = str(';'+str([d['id_project'] for d in selectbox_most_recent_project]).replace("[", '').replace("]",'').replace("'", '') +';')

        most_recent_project_id = formated_most_recent_project_id.replace(';', '')
        company_ids_for_most_recent_project = _get_company_ids_for_project(most_recent_project_id)
        selectbox_most_recent_company_members_id = ''
        for company_id in company_ids_for_contact:
            if str(company_id) != '0' and str(company_id) in company_ids_for_most_recent_project:
                selectbox_most_recent_company_members_id = str(company_id)
                break
        if not selectbox_most_recent_company_members_id:
            selectbox_most_recent_company_members_id = next((str(company_id) for company_id in company_ids_for_contact if str(company_id) != '0'), '')

        selected_company = models_companies.objects.filter(id_company=selectbox_most_recent_company_members_id).first()
        selectbox_most_recent_company = [model_to_dict(selected_company)] if selected_company else []
        selected_company_project_ids = _get_project_ids_for_company(selectbox_most_recent_company_members_id)
        selected_company_member_ids = _get_contact_ids_for_company(selectbox_most_recent_company_members_id)
        selectbox_most_recent_company_test = _legacy_join_ids(selected_company_project_ids)
        selectbox_most_recent_company_members = _legacy_join_ids(selected_company_member_ids, include_zero=True)

        query_default_project = models_projects.objects.filter(
            id_project__in=selected_company_project_ids
        ).order_by('-project_last_modification_date')
        selectbox_company_project = [model_to_dict(l) for l in query_default_project]

        # REFONTE D4b SAFE : conserver l'option technique id_project=0
        # dans le menu projets même quand la page est ouverte depuis un double-clic contact.
        # Sans ce ré-ajout, le menu projet affiche uniquement les projets liés à la société,
        # et l'option "Nouveau projet" ne réapparaît qu'après un changement manuel de société.
        zero_project = models_projects.objects.filter(id_project='0').first()
        if zero_project and not any(str(p.get('id_project')) == '0' for p in selectbox_company_project):
            selectbox_company_project.append(model_to_dict(zero_project))

        if selectbox_company_project == []:
            selectbox_company_project = [
                {'id_project': '0', 'project_last_modification_date': '0', 'project_denomination': 'Save company first'}] #'project_last_modification_date': '0' is mandatory

        ############################################################################################################

        member_ids = selected_company_member_ids[:]
        if '0' not in member_ids:
            member_ids.append('0')
        if str(pk) not in member_ids:
            member_ids.insert(0, str(pk))

        query_selectbox_members = models_contacts.objects.filter(id_contact__in=member_ids)
        selectbox_members = [model_to_dict(l) for l in query_selectbox_members]
        selectbox_members = sorted(selectbox_members, key=lambda x: x['id_contact'] == '0')

        query_default_selected_member = models_contacts.objects.filter(id_contact=pk)
        default_selected_member = [model_to_dict(l) for l in query_default_selected_member]
        default_selected_member_id = str(
            [d['id_contact'] for d in default_selected_member]).replace("[", '').replace("]", '').replace("'",'').replace(";", '')

        if not selectbox_members:
            selectbox_members = default_selected_member

        current_record = models_contacts.objects.get(id_contact=pk)
        current_record2 = models_projects.objects.get(id_project=formated_most_recent_project_id.replace(";",""))  # id= can be replace by any field on the DB
        # current_record3 = models_companies.objects.get(project_last_modification_date='202310041200') # id= can be replace by any field on the DB

        form = forms_contacts(request.POST or None, instance=current_record, pk=pk)
        form2 = forms_projects(request.POST or None, instance=current_record2)
        # form3 = forms_companies(request.POST or None, instance=current_record3)

        if form.is_valid():
            form.save()


        if form2.is_valid():
            form2.save()

        if form.is_valid() or form2.is_valid():
            return redirect('test')


        return render(request, 'update_record_mobile.html', {'form':form,
                                                             'form2': form2,
                                                             'selectbox_company_project':selectbox_company_project,
                                                             'selectbox_all_companies':selectbox_all_companies,
                                                             'selectbox_members':selectbox_members,
                                                             'default_selected_member_id':default_selected_member_id,
                                                             'selectbox_most_recent_company_members_id':selectbox_most_recent_company_members_id,
                                                             'formated_most_recent_project_id' : formated_most_recent_project_id.replace(";",""),
                                                             'selectbox_projects' : selectbox_projects,
                                                             'selectbox_most_recent_project':selectbox_most_recent_project
                                                             })
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def article_filter_mobile(request):

    data = dict()
    search = request.POST.get('txtSearch')  # have to pass it also in Ajax
    select_main_search_prefilter = request.POST.get('select_main_search_prefilter')  # have to pass it also in Ajax

    def build_companies_by_contact():
        companies = {}
        relations = models_company_contacts.objects.exclude(contact_id='0').select_related('company')
        for relation in relations:
            companies.setdefault(str(relation.contact_id), []).append(relation.company.company_name)
        return {contact_id: ' ; '.join(names) for contact_id, names in companies.items()}

    def add_company_name(main_searched_contact_found):
        companies = build_companies_by_contact()
        for d in main_searched_contact_found:
            d['company_name'] = companies.get(str(d['id_contact']), ' ')
        return main_searched_contact_found

    if not search == "":
        if select_main_search_prefilter == "name":
            locations = models_contacts.objects.raw("SELECT * FROM table_contacts "
                                         "WHERE "
                                         "contact_first_name RLIKE %s OR "
                                         "contact_last_name RLIKE %s",
                                                    [search,search])
            main_searched_contact_found = [model_to_dict(l) for l in locations]
            main_searched_contact_found = [l for l in main_searched_contact_found if l['id_contact'] != '0']
            main_searched_contact_found = add_company_name(main_searched_contact_found)

            data['html_table'] = render_to_string('grid_mobile.html', {'main_searched_contact_found': main_searched_contact_found},
                                                  request=request)
            return JsonResponse(data)
        if select_main_search_prefilter == "everywhere":
            # STEP 36: plus de recherche dans company_members_id / company_projects_id.
            # La recherche reste directe sur les champs métier, puis utilise les nouvelles tables pour retrouver les contacts liés.
            project_matches = models_projects.objects.filter(
                Q(id_project__icontains=search) |
                Q(project_last_modification_date__icontains=search) |
                Q(project_denomination__icontains=search)
            ).exclude(id_project='0')

            company_matches = models_companies.objects.filter(
                Q(id_company__icontains=search) |
                Q(company_name__icontains=search)
            ).exclude(id_company='0')

            company_ids = [str(company.id_company) for company in company_matches]
            company_ids += [
                str(company_id)
                for company_id in models_company_projects.objects.filter(
                    project_id__in=project_matches.values_list('id_project', flat=True)
                ).values_list('company__id_company', flat=True)
            ]
            company_ids = list(dict.fromkeys(company_id for company_id in company_ids if company_id and company_id != '0'))

            contact_ids = [
                str(contact_id)
                for contact_id in models_company_contacts.objects.filter(
                    company_id__in=company_ids
                ).exclude(contact_id='0').values_list('contact__id_contact', flat=True)
            ]
            contact_ids = list(dict.fromkeys(contact_ids))
            number_of = _legacy_join_ids(contact_ids)

            locations = models_contacts.objects.filter(
                Q(id_contact__icontains=search) |
                Q(id_contact__in=contact_ids) |
                Q(contact_first_name__icontains=search) |
                Q(contact_last_name__icontains=search) |
                Q(contact_email_1__icontains=search) |
                Q(contact_phone_1__icontains=search) |
                Q(contact_phone_2__icontains=search) |
                Q(contact_email_2__icontains=search) |
                Q(contact_gender__icontains=search) |
                Q(contact_language__icontains=search) |
                Q(test__icontains=search)
            )

            main_searched_contact_found = [model_to_dict(l) for l in locations]
            main_searched_contact_found = [l for l in main_searched_contact_found if l['id_contact'] != '0']
            main_searched_contact_found = add_company_name(main_searched_contact_found)

            data['html_table'] = render_to_string('grid_mobile.html', {'main_searched_contact_found': main_searched_contact_found,'number_of':number_of},request=request)
            return JsonResponse(data)
    else:
        locations = models_contacts.objects.raw("SELECT * FROM table_contacts")
        main_searched_contact_found = [model_to_dict(l) for l in locations]
        main_searched_contact_found = [l for l in main_searched_contact_found if l['id_contact'] != '0']
        main_searched_contact_found = add_company_name(main_searched_contact_found)

        data['html_table'] = render_to_string('grid_mobile.html', {'main_searched_contact_found': main_searched_contact_found},request=request)


        return JsonResponse(data)

####################################################################################
#Grid main search section
def main_search_records(request):
    data = dict()

    search = request.POST.get('txtSearch')
    select_main_search_prefilter = request.POST.get('select_main_search_prefilter')

    if search:
        if select_main_search_prefilter == "name":
            main_searched_contact_found_query = models_contacts.objects.filter(Q(contact_first_name__icontains=search) | Q(contact_last_name__icontains=search))
            main_searched_contact_found = [model_to_dict(l) for l in main_searched_contact_found_query]
            main_searched_contact_found = [l for l in main_searched_contact_found if str(l['id_contact']) != '0']

            data['html_table'] = render_to_string('grid.html',{'main_searched_contact_found': main_searched_contact_found,
                                                               'main_searched_company_found': [],
                                                               },
                                                  request=request)
            return JsonResponse(data)

        elif select_main_search_prefilter == "company_name":
            main_searched_company_found_query = models_companies.objects.filter(
                company_name__icontains=search
            )
            main_searched_company_found = [model_to_dict(l) for l in main_searched_company_found_query]
            main_searched_company_found = [l for l in main_searched_company_found if str(l['id_company']) != '0']

            data['html_table'] = render_to_string('grid.html',{'main_searched_contact_found': [],
                                                               'main_searched_company_found': main_searched_company_found,
                                                               },
                                                  request=request)
            return JsonResponse(data)

        elif select_main_search_prefilter == "everywhere":
            main_searched_contact_found_query = models_contacts.objects.filter(
                Q(id_contact__icontains=search) |
                Q(contact_first_name__icontains=search) |
                Q(contact_last_name__icontains=search) |
                Q(contact_email_1__icontains=search) |
                Q(contact_phone_1__icontains=search) |
                Q(contact_email_2__icontains=search) |
                Q(contact_phone_2__icontains=search) |
                Q(contact_gender__icontains=search) |
                Q(contact_language__icontains=search) |
                Q(test__icontains=search)
            )
            # STEP 31 : recherche principale stricte.
            # Les champs relationnels legacy comme company_members_id ne servent plus au filtrage direct.
            # Les relations restent consultables via les sous-grilles après sélection.
            main_searched_company_found_query = models_companies.objects.filter(
                Q(id_company__icontains=search) |
                Q(company_name__icontains=search) |
                Q(company_vat_number__icontains=search) |
                Q(company_type__icontains=search)
            )

            # STEP 31 : recherche principale stricte.
            # project_sales_document est un miroir legacy relationnel, il n'est plus utilisé pour filtrer.
            main_searched_project_found_query = models_projects.objects.filter(
                Q(id_project__icontains=search) |
                Q(project_last_modification_date__icontains=search) |
                Q(project_denomination__icontains=search) |
                Q(project_maturity__icontains=search) |
                Q(project_credibility__icontains=search)
            )

            # STEP 31 : recherche principale stricte.
            # interaction_company_id est un miroir legacy relationnel, il n'est plus utilisé pour filtrer.
            main_searched_interaction_found_query = models_interactions.objects.filter(
                Q(id_interaction__icontains=search) |
                Q(interaction_full_date__icontains=search) |
                Q(interaction_date__icontains=search) |
                Q(interaction_time__icontains=search) |
                Q(interaction_user__icontains=search) |
                Q(interaction_contact_id__icontains=search) |
                Q(interaction_project_id__icontains=search) |
                Q(interaction_content__icontains=search) |
                Q(interaction_type__icontains=search) |
                Q(interaction_mode__icontains=search)
            )

            main_searched_contact_found = [model_to_dict(l) for l in main_searched_contact_found_query]
            main_searched_contact_found = [l for l in main_searched_contact_found if l['id_contact'] != '0'] #comparison to 0 not working if DB format is auto implemented number. VARCHAR works

            main_searched_company_found = [model_to_dict(l) for l in main_searched_company_found_query]
            main_searched_company_found = [l for l in main_searched_company_found if l['id_company'] != '0']

            main_searched_project_found = [model_to_dict(l) for l in main_searched_project_found_query]
            main_searched_project_found = [l for l in main_searched_project_found if l['id_project'] != '0']

            main_searched_interaction_found = [model_to_dict(l) for l in main_searched_interaction_found_query]
            main_searched_interaction_found = [l for l in main_searched_interaction_found if l['id_interaction'] != '0']

            data['html_table'] = render_to_string('grid.html', {'main_searched_contact_found': main_searched_contact_found,
                                                            'main_searched_company_found': main_searched_company_found,
                                                            'main_searched_project_found': main_searched_project_found,
                                                            'main_searched_interaction_found': main_searched_interaction_found,
                                                            },
                                                  request=request)
            return JsonResponse(data)
    else:
        main_searched_contact_found_query = models_contacts.objects.all()
        main_searched_company_found_query = models_companies.objects.all()
        main_searched_project_found_query = models_projects.objects.all()
        main_searched_interaction_found_query = models_interactions.objects.all()

        main_searched_contact_found = [model_to_dict(l) for l in main_searched_contact_found_query]
        main_searched_contact_found = [l for l in main_searched_contact_found if l['id_contact'] != '0'] #comparison to 0 not working if DB format is auto implemented number. VARCHAR works

        main_searched_company_found = [model_to_dict(l) for l in main_searched_company_found_query]
        main_searched_company_found = [l for l in main_searched_company_found if l['id_company'] != '0']

        main_searched_project_found = [model_to_dict(l) for l in main_searched_project_found_query]
        main_searched_project_found = [l for l in main_searched_project_found if l['id_project'] != '0']

        main_searched_interaction_found = [model_to_dict(l) for l in main_searched_interaction_found_query]
        main_searched_interaction_found = [l for l in main_searched_interaction_found if l['id_interaction'] != '0']

        data['html_table'] = render_to_string('grid.html', {'main_searched_contact_found': main_searched_contact_found,
                                                            'main_searched_company_found': main_searched_company_found,
                                                            'main_searched_project_found': main_searched_project_found,
                                                            'main_searched_interaction_found': main_searched_interaction_found,
                                                            },
                                              request=request)


        return JsonResponse(data)

def _clean_selected_grid_ids(selected_rows_raw):
    if not selected_rows_raw:
        selected_rows_raw = '00'

    selected_rows = selected_rows_raw.split("%")
    cleaned = []

    for value in selected_rows:
        value = str(value or '').replace(';', '').strip()
        if value and value != '00':
            cleaned.append(value)

    return cleaned


def _dicts_without_zero(queryset, id_field):
    rows = [model_to_dict(l) for l in queryset]
    return [l for l in rows if str(l.get(id_field, '')) != '0']




def _format_contact_relation_dicts(contact_rows):
    # STEP 6C: sub_grid.js expects first_name / last_name, while the Django model fields are contact_first_name / contact_last_name.
    # Keep all original keys and add display aliases only.
    for row in contact_rows:
        row['first_name'] = row.get('contact_first_name', '')
        row['last_name'] = row.get('contact_last_name', '')
    return contact_rows

def search_contact_relations(request):
    data = dict()

    selected_contact_ids = _clean_selected_grid_ids(request.POST.get('selected_rows'))

    # STEP 32: sous-grilles relationnelles lues uniquement depuis table_company_contacts.
    company_ids = set()
    for contact_id in selected_contact_ids:
        company_ids.update(_get_company_ids_for_contact(contact_id))

    company_filter = models_companies.objects.filter(id_company__in=list(company_ids))

    company_relations = _dicts_without_zero(company_filter, 'id_company')

    # STEP 32: projets liés lus uniquement depuis table_company_projects.
    project_ids = set()
    for company in company_relations:
        project_ids.update(_get_project_ids_for_company(company.get('id_company')))

    project_filter = models_projects.objects.filter(id_project__in=list(project_ids))
    project_relations = _dicts_without_zero(project_filter, 'id_project')

    # Interaction stays direct by contact FK-like legacy field.
    interaction_filter = models_interactions.objects.filter(interaction_contact_id__in=selected_contact_ids)
    interaction_relations = _dicts_without_zero(interaction_filter, 'id_interaction')

    data['html_table'] = render_to_string('sub_grid.html', {'company_relations': company_relations,
                                                            'interaction_relations':interaction_relations,
                                                            'project_relations':project_relations,
                                                            },
                                          request=request)
    return JsonResponse(data)


def search_company_relations(request):
    data = dict()

    selected_company_ids = _clean_selected_grid_ids(request.POST.get('selected_rows'))

    # Company
    company_filter = models_companies.objects.filter(id_company__in=selected_company_ids)
    company_relations = _dicts_without_zero(company_filter, 'id_company')

    # STEP 32: contacts liés lus uniquement depuis table_company_contacts.
    contact_ids = set()
    for company_id in selected_company_ids:
        contact_ids.update(_get_contact_ids_for_company(company_id))

    contact_filter = models_contacts.objects.filter(id_contact__in=list(contact_ids))
    contact_relations = _format_contact_relation_dicts(_dicts_without_zero(contact_filter, 'id_contact'))

    # STEP 32: projets liés lus uniquement depuis table_company_projects.
    project_ids = set()
    for company_id in selected_company_ids:
        project_ids.update(_get_project_ids_for_company(company_id))

    project_filter = models_projects.objects.filter(id_project__in=list(project_ids))
    project_relations = _dicts_without_zero(project_filter, 'id_project')

    # STEP 32: interactions liées lues uniquement depuis table_interaction_companies.
    relation_interaction_ids = list(models_interaction_companies.objects.filter(
        company__id_company__in=selected_company_ids
    ).values_list('interaction__id_interaction', flat=True))

    interaction_filter = models_interactions.objects.filter(id_interaction__in=relation_interaction_ids)

    interaction_relations = _dicts_without_zero(interaction_filter, 'id_interaction')

    data['html_table'] = render_to_string('sub_grid.html', {'contact_relations':contact_relations,
                                                            'company_relations':company_relations,
                                                            'project_relations': project_relations,
                                                            'interaction_relations':interaction_relations,
                                                            },
                                          request=request)
    return JsonResponse(data)


def search_project_relations(request):
    data = dict()

    selected_project_ids = _clean_selected_grid_ids(request.POST.get('selected_rows'))

    # STEP 32: sociétés liées lues uniquement depuis table_company_projects.
    company_ids = set()
    for project_id in selected_project_ids:
        company_ids.update(_get_company_ids_for_project(project_id))

    company_filter = models_companies.objects.filter(id_company__in=list(company_ids))

    company_relations = _dicts_without_zero(company_filter, 'id_company')

    # STEP 32: contacts liés lus uniquement depuis table_company_contacts.
    contact_ids = set()
    for company in company_relations:
        contact_ids.update(_get_contact_ids_for_company(company.get('id_company')))

    contact_filter = models_contacts.objects.filter(id_contact__in=list(contact_ids))
    contact_relations = _format_contact_relation_dicts(_dicts_without_zero(contact_filter, 'id_contact'))

    # Interaction stays direct by project FK-like legacy field.
    interaction_filter = models_interactions.objects.filter(interaction_project_id__in=selected_project_ids)
    interaction_relations = _dicts_without_zero(interaction_filter, 'id_interaction')

    data['html_table'] = render_to_string('sub_grid.html', {'company_relations': company_relations,
                                                            'interaction_relations':interaction_relations,
                                                            'contact_relations':contact_relations,
                                                            },
                                          request=request)
    return JsonResponse(data)


def search_interaction_relations(request):

    data = dict()

    selected_interaction_ids = _clean_selected_grid_ids(request.POST.get('selected_rows'))

    # Interaction
    interaction_filter = models_interactions.objects.filter(id_interaction__in=selected_interaction_ids)
    interaction_relations = _dicts_without_zero(interaction_filter, 'id_interaction')

    # Contact
    get_all_contacts_id = [x['interaction_contact_id'] for x in interaction_relations]
    get_all_contacts_id = [x for x in get_all_contacts_id if x]
    contact_filter = models_contacts.objects.filter(id_contact__in=get_all_contacts_id)
    contact_relations = _format_contact_relation_dicts(_dicts_without_zero(contact_filter, 'id_contact'))

    # STEP 32: sociétés liées lues uniquement depuis table_interaction_companies.
    company_ids = set()
    for interaction_obj in interaction_filter:
        company_ids.update(_get_company_ids_for_interaction(interaction_obj))

    company_filter = models_companies.objects.filter(id_company__in=list(company_ids))
    company_relations = _dicts_without_zero(company_filter, 'id_company')

    # Project
    get_all_projects_id = [x['interaction_project_id'] for x in interaction_relations]
    get_all_projects_id = [x for x in get_all_projects_id if x]
    project_filter = models_projects.objects.filter(id_project__in=get_all_projects_id)
    project_relations = _dicts_without_zero(project_filter, 'id_project')

    data['html_table'] = render_to_string('sub_grid.html', {'contact_relations':contact_relations,
                                                            'company_relations':company_relations,
                                                            'project_relations': project_relations,
                                                            'interaction_relations':interaction_relations,
                                                            'selected_rows_in_grid': selected_interaction_ids,
                                                            },
                                          request=request)
    return JsonResponse(data)

####################################################################################

def home(request):
    user_agent = get_user_agent(request)


    if user_agent.is_pc:

        records = models_contacts.objects.all()
        # Check to see if logging in
        if request.method == 'POST':
            username = request.POST['username']
            password = request.POST['password']
            # Authenticate
            user = authenticate(request, username=username, password=password)
            if user is not None:
                login(request, user)
                messages.success(request, "You Have Been Logged In PC!")
                return redirect('home')
            else:
                messages.success(request, "There Was An Error Logging In, Please Try Again...")
                return redirect('home')
        else:
            return render(request, 'home.html', {'records':records})

    elif user_agent.is_mobile:

        records = models_contacts.objects.all()
        # Check to see if logging in
        if request.method == 'POST':
            username = request.POST['username']
            password = request.POST['password']
            # Authenticate
            user = authenticate(request, username=username, password=password)
            if user is not None:
                login(request, user)
                messages.success(request, "You Have Been Logged In MOBILE!")
                return redirect('test')
            else:
                messages.success(request, "There Was An Error Logging In, Please Try Again...")
                return redirect('test')
        else:
            return render(request, 'test.html', {'records': records})

def logout_user(request):
    logout(request)
    messages.success(request, "You Have Been Logged Out...")
    return redirect('home')

def register_user(request):
    if request.method == 'POST':
        form = SignUpForm(request.POST)
        if form.is_valid():
            form.save()
            # Authenticate and login
            username = form.cleaned_data['username']
            password = form.cleaned_data['password1']
            user = authenticate(username=username, password=password)
            login(request, user)
            messages.success(request, "You Have Successfully Registered! Welcome!")
            return redirect('home')
    else:
        form = SignUpForm()
        return render(request, 'register.html', {'form':form})

    return render(request, 'register.html', {'form':form})

def customer_record(request, pk):
    if request.user.is_authenticated:
        # Look Up Records
        customer_record = models_contacts.objects.get(id_contact=pk)
        return render(request, 'record.html', {'customer_record':customer_record})
    else:
        messages.success(request, "You Must Be Logged In To View That Page...")
        return redirect('home')

def delete_record(request, pk):
    if request.user.is_authenticated:
        delete_it = models_contacts.objects.get(id_contact=pk)
        delete_it.delete()
        messages.success(request, "Record Deleted Successfully...")
        return redirect('home')
    else:
        messages.success(request, "You Must Be Logged In To Do That...")
        return redirect('home')

def add_record(request):
    if request.user.is_authenticated:

        form = forms_add_contacts(request.POST or None)
        form2 = forms_companies(request.POST or None)

        if request.method == "POST":
            if form.is_valid():
                form.save()
                form2.save()
                messages.success(request, "Record Added...")
                return redirect('home')
        return render(request, 'add_record.html', {'form':form,'form2':form2})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def add_or_remove_existing_contact_to_current_company(request):

    add_or_remove = request.POST.get('add_or_remove')
    existing_contact_id = request.POST.get('existing_contact_id')
    current_company = request.POST.get('current_company')

    # STEP 22: table_company_contacts est maintenant la source principale pour ajouter/retirer
    # un contact d'une société. L'ancien champ company_members_id est seulement reconstruit
    # par _sync_company_contact_link() pour compatibilité temporaire.
    if add_or_remove == "add":
        relation_exists = models_company_contacts.objects.filter(
            company_id=current_company,
            contact_id=existing_contact_id
        ).exists()

        if not relation_exists:
            _sync_company_contact_link(current_company, existing_contact_id, add=True)

            result_2 = 'Contact added to company !'
            return JsonResponse({"result_2": result_2})

        else:
            result_2 = 'existe deja'
            return JsonResponse({"result_2": result_2})

    if add_or_remove == "remove":
        relation_exists = models_company_contacts.objects.filter(
            company_id=current_company,
            contact_id=existing_contact_id
        ).exists()

        if relation_exists:
            _sync_company_contact_link(current_company, existing_contact_id, add=False)

            result_2 = 'Contact removed from company !'
            return JsonResponse({"result_2":result_2})

        else:
            result_2 = 'Not a member of the currrent company'
            return JsonResponse({"result_2": result_2})

    return JsonResponse({"result_2": "Invalid action"}, status=400)

####################################################################################
def update_selectbox_sales_documents_edit(request):
    if request.user.is_authenticated:
        project_num = request.GET.get('project_num')

        # STEP 5: lecture prioritaire depuis table_project_sales_documents, avec fallback project_sales_document.
        list_sales_documents = _get_sales_document_ids_for_project(project_num)

        sales_documents_qs = models_sales_documents.objects.filter(id_sales_documents__in=list_sales_documents)

        documents = []

        for doc in sales_documents_qs:
            # STEP 11: les acomptes/paiements sont lus en priorité depuis table_sales_document_deposits.
            # Les anciens champs ;id;id; restent hydratés en mémoire pour conserver le JS existant.
            _hydrate_sales_document_legacy_deposits_from_relation_deposits(doc)

            invoice_id = (doc.sales_documents_invoice_id or '').strip()
            delivery_note_id = (getattr(doc, 'sales_documents_delivery_note_id', '') or '').strip()
            purchase_order_id = (doc.sales_documents_purchase_order_id or '').strip()
            quote_id = (doc.sales_documents_quote_id or '').strip()
            invoice_number = (doc.sales_documents_invoice_number or '').strip()
            delivery_note_number = (getattr(doc, 'sales_documents_delivery_note_number', '') or '').strip()
            purchase_order_number = (doc.sales_documents_purchase_order_number or '').strip()
            quote_number = (doc.sales_documents_quote_number or '').strip()

            timeline_step = (doc.sales_documents_timeline_step or '').strip()

            doc_types = []
            if timeline_step == 'invoice':
                doc_types.append("invoice")
                doc_types.append("delivery_note")
                doc_types.append("deposit")
                doc_types.append("quote")

            if timeline_step == 'delivery_note':
                doc_types.append("delivery_note")
                doc_types.append("deposit")
                doc_types.append("quote")

            if timeline_step == 'deposit':
                doc_types.append("deposit")
                doc_types.append("quote")

            if timeline_step == 'quote':
                doc_types.append("quote")

            if not doc_types:
                doc_types.append("quote")

            if "invoice" in doc_types:
                main_type = "invoice"
            elif "delivery_note" in doc_types:
                main_type = "delivery_note"
            elif "deposit" in doc_types:
                main_type = "deposit"
            else:
                main_type = "quote"

            valid_steps = ['quote', 'deposit', 'delivery_note', 'invoice']
            step_raw = str(doc.sales_documents_timeline_step or '').strip().lower()
            step = step_raw if step_raw in valid_steps else 'quote'

            documents.append({
                "id": doc.id_sales_documents,
                "denomination": doc.sales_documents_denomination,
                "last_mod": doc.sales_documents_last_modification_date,
                "invoice_id" : invoice_id,
                "delivery_note_id" : delivery_note_id,
                "purchase_order_id" : purchase_order_id,
                "quote_id" : quote_id,
                "invoice_number": invoice_number,
                "delivery_note_number": delivery_note_number,
                "purchase_order_number": purchase_order_number,
                "quote_number": quote_number,
                "types": doc_types,
                "main_type": main_type,
                "step": step,
                "deposit_id": doc.sales_documents_deposit_id or '',
                "deposit_number": doc.sales_documents_deposit_number or '',
                "deposit_date": doc.sales_documents_deposit_date or '',
                "deposit_amount": doc.sales_documents_deposit_amount or '',
                "deposit_method": doc.sales_documents_deposit_method or '',
                "rent_or_sale": doc.sales_documents_rent_or_sale or''
            })

        # Option technique ID=0 : elle sert à créer un nouveau document depuis le menu.
        # Elle ne doit pas être une vraie relation dans table_project_sales_documents,
        # donc on la réinjecte ici côté interface, comme pour contact/projet ID=0.
        if not any(str(doc.get("id")) == "0" for doc in documents):
            zero_doc = models_sales_documents.objects.filter(id_sales_documents='0').first()
            documents.append({
                "id": "0",
                "denomination": (zero_doc.sales_documents_denomination if zero_doc else "Nouveau document"),
                "last_mod": "0",
                "invoice_id": "",
                "delivery_note_id": "",
                "purchase_order_id": "",
                "quote_id": "",
                "invoice_number": "",
                "delivery_note_number": "",
                "purchase_order_number": "",
                "quote_number": "",
                "types": ["quote"],
                "main_type": "quote",
                "step": "quote",
                "deposit_id": "",
                "deposit_number": "",
                "deposit_date": "",
                "deposit_amount": "",
                "deposit_method": "",
                "rent_or_sale": ""
            })

        documents_sorted = sorted(documents, key=lambda x: x['last_mod'], reverse=True)

        return JsonResponse({
            "documents": documents_sorted
        })

    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')


def save_deposit_info(request):
    if not request.user.is_authenticated or request.method != 'POST':
        return JsonResponse({"error": "unauthorized"}, status=403)

    doc_id = request.POST.get("doc_id")
    new_id = (request.POST.get("deposit_id") or '0').strip()
    new_number = (request.POST.get("deposit_number") or '0').strip()
    new_date = (request.POST.get("deposit_date") or '0').strip()
    new_amount = (request.POST.get("deposit_amount") or '0').strip()
    new_method = (request.POST.get("deposit_method") or '0').strip()

    try:
        doc = models_sales_documents.objects.get(id_sales_documents=doc_id)

        # STEP 19:
        # table_sales_document_deposits devient la source principale.
        # Les anciens champs sales_documents_deposit_* sont reconstruits ensuite
        # uniquement pour compatibilité temporaire avec le JS/templates existants.
        existing_relation = (
            models_sales_document_deposits.objects
            .filter(sales_document=doc, sales_documents_deposit_id=new_id)
            .first()
        )

        if existing_relation:
            existing_relation.sales_documents_deposit_number = new_number
            existing_relation.sales_documents_deposit_date = new_date
            existing_relation.sales_documents_deposit_amount = new_amount
            existing_relation.sales_documents_deposit_method = new_method
            existing_relation.save()
        else:
            models_sales_document_deposits.objects.create(
                sales_document=doc,
                sales_documents_deposit_id=new_id,
                sales_documents_deposit_number=new_number,
                sales_documents_deposit_date=new_date,
                sales_documents_deposit_amount=new_amount,
                sales_documents_deposit_method=new_method,
            )

        _hydrate_sales_document_legacy_deposits_from_relation_deposits(doc)
        doc.save(update_fields=[
            'sales_documents_deposit_id',
            'sales_documents_deposit_number',
            'sales_documents_deposit_date',
            'sales_documents_deposit_amount',
            'sales_documents_deposit_method',
        ])

        return JsonResponse({"success": True})
    except models_sales_documents.DoesNotExist:
        return JsonResponse({"error": "Document introuvable"}, status=404)


def delete_deposit_info(request):
    if not request.user.is_authenticated or request.method != "POST":
        return JsonResponse({"error": "unauthorized"}, status=403)

    doc_id = request.POST.get("doc_id")
    deposit_id_to_remove = request.POST.get("deposit_id")

    try:
        doc = models_sales_documents.objects.get(id_sales_documents=doc_id)

        # STEP 19:
        # Suppression depuis la nouvelle table relationnelle d'abord.
        # Si une vieille donnée n'a pas encore été synchronisée, on tente une synchro
        # depuis les champs legacy puis on réessaie.
        deleted_count, _ = models_sales_document_deposits.objects.filter(
            sales_document=doc,
            sales_documents_deposit_id=deposit_id_to_remove,
        ).delete()

        if deleted_count == 0:
            _sync_sales_document_deposits_from_legacy(doc)
            deleted_count, _ = models_sales_document_deposits.objects.filter(
                sales_document=doc,
                sales_documents_deposit_id=deposit_id_to_remove,
            ).delete()

        if deleted_count == 0:
            return JsonResponse({"error": "ID non trouvé"}, status=404)

        if _hydrate_sales_document_legacy_deposits_from_relation_deposits(doc):
            doc.save(update_fields=[
                'sales_documents_deposit_id',
                'sales_documents_deposit_number',
                'sales_documents_deposit_date',
                'sales_documents_deposit_amount',
                'sales_documents_deposit_method',
            ])
        else:
            doc.sales_documents_deposit_id = ''
            doc.sales_documents_deposit_number = ''
            doc.sales_documents_deposit_date = ''
            doc.sales_documents_deposit_amount = ''
            doc.sales_documents_deposit_method = ''
            doc.save(update_fields=[
                'sales_documents_deposit_id',
                'sales_documents_deposit_number',
                'sales_documents_deposit_date',
                'sales_documents_deposit_amount',
                'sales_documents_deposit_method',
            ])

        return JsonResponse({"success": True})
    except models_sales_documents.DoesNotExist:
        return JsonResponse({"error": "Document introuvable"}, status=404)


@never_cache # evite le rechargement en cache, sinon quand on refresh la page certaines données ne sont pas actualisé (par exemple le dernier projet modifié)
def update_record(request, pk):
    #in the futur, get rid of the ';' by first query all company_members_id, creating a list with ',' and then search pk_format without ';', see examples in search_contact_relations()

    if request.user.is_authenticated:
        pk_format = str(';' + str(pk) + ';')

        # STEP 8 - focus management when update_record is opened from homepage grids.
        # Normal menu changes still keep their existing most-recent-date logic.
        focus_type = request.GET.get('focus_type', '')
        focus_id = request.GET.get('focus_id', '')

        # STEP 36: l'initialisation update_record ne lit plus company_members_id / company_projects_id.
        # Les mêmes variables sont conservées pour ne pas changer les templates/JS.
        company_ids_for_contact = _get_company_ids_for_contact(pk, include_zero=True)
        query_all_companies_where_pk_is_found = models_companies.objects.filter(id_company__in=company_ids_for_contact)
        selectbox_all_companies = [model_to_dict(company) for company in query_all_companies_where_pk_is_found]
        selectbox_all_companies = sorted(selectbox_all_companies, key=lambda x: x['id_company'] == '0')

        all_project_ids = []
        for company_id in company_ids_for_contact:
            if str(company_id) != '0':
                all_project_ids.extend(_get_project_ids_for_company(company_id))
        all_project_ids = list(dict.fromkeys(str(project_id) for project_id in all_project_ids if str(project_id) and str(project_id) != '0'))
        company_projects_ids_of_all_companies_where_pk_is_found = _legacy_join_ids(all_project_ids)

        query_most_recent_project = models_projects.objects.filter(id_project__in=all_project_ids).order_by('-project_last_modification_date')

        selectbox_projects = [model_to_dict(l) for l in query_most_recent_project]
        # if no project is present in the company, it sends back to the create new project which has the ID 0
        if selectbox_projects == []:
            selectbox_most_recent_project = [{'id_project': '0', 'project_last_modification_date': '0', 'project_denomination': 'plz save company first'}]  # 'project_last_modification_date': '0' is mandatory
            formated_most_recent_project_id = str(';' + str([d['id_project'] for d in selectbox_most_recent_project]).replace("[", '').replace("]", '').replace("'", '') + ';')
        else:
            selectbox_most_recent_project = [selectbox_projects[0]]
            formated_most_recent_project_id = str(';' + str([d['id_project'] for d in selectbox_most_recent_project]).replace("[", '').replace("]", '').replace("'", '') + ';')

        most_recent_project_id = formated_most_recent_project_id.replace(';', '')
        company_ids_for_most_recent_project = _get_company_ids_for_project(most_recent_project_id)
        selectbox_most_recent_company_members_id = ''
        for company_id in company_ids_for_contact:
            if str(company_id) != '0' and str(company_id) in company_ids_for_most_recent_project:
                selectbox_most_recent_company_members_id = str(company_id)
                break
        if not selectbox_most_recent_company_members_id:
            selectbox_most_recent_company_members_id = next((str(company_id) for company_id in company_ids_for_contact if str(company_id) != '0'), '')

        selected_company = models_companies.objects.filter(id_company=selectbox_most_recent_company_members_id).first()
        selectbox_most_recent_company = [model_to_dict(selected_company)] if selected_company else []
        selected_company_project_ids = _get_project_ids_for_company(selectbox_most_recent_company_members_id)
        selected_company_member_ids = _get_contact_ids_for_company(selectbox_most_recent_company_members_id)
        selectbox_most_recent_company_test = _legacy_join_ids(selected_company_project_ids)
        selectbox_most_recent_company_members = _legacy_join_ids(selected_company_member_ids, include_zero=True)

        query_default_project = models_projects.objects.filter(
            id_project__in=selected_company_project_ids
        ).order_by('-project_last_modification_date')

        selectbox_company_project = [model_to_dict(l) for l in query_default_project]

        # REFONTE D4b SAFE : conserver l'option technique id_project=0
        # dans le menu projets même quand la page est ouverte depuis un double-clic contact.
        # Sans ce ré-ajout, le menu projet affiche uniquement les projets liés à la société,
        # et l'option "Nouveau projet" ne réapparaît qu'après un changement manuel de société.
        zero_project = models_projects.objects.filter(id_project='0').first()
        if zero_project and not any(str(p.get('id_project')) == '0' for p in selectbox_company_project):
            selectbox_company_project.append(model_to_dict(zero_project))

        # if no project is present in the company, it sends back to the create new project which has the ID 0
        if selectbox_company_project == []:
            selectbox_company_project = [{'id_project': '0', 'project_last_modification_date': '0', 'project_denomination': 'Seva company first'}]  # 'project_last_modification_date': '0' is mandatory

        ############################################################################################################

        member_ids = selected_company_member_ids[:]
        if '0' not in member_ids:
            member_ids.append('0')
        if str(pk) not in member_ids:
            member_ids.insert(0, str(pk))

        query_selectbox_members = models_contacts.objects.filter(id_contact__in=member_ids)

        selectbox_members = [model_to_dict(l) for l in query_selectbox_members]
        selectbox_members = sorted(selectbox_members, key=lambda x: x['id_contact'] == '0')

        # Get default member by matching pk to populate selectbox
        query_default_selected_member = models_contacts.objects.filter(
            id_contact=pk
        )

        default_selected_member = [model_to_dict(l) for l in query_default_selected_member]
        default_selected_member_id = str([d['id_contact'] for d in default_selected_member]).replace("[", '').replace("]", '').replace("'", '').replace(";", '')

        if not selectbox_members:
            selectbox_members = default_selected_member

        # STEP 8B - when opened from a contact double-click, force exact clicked contact.
        # This avoids the old id_contact__icontains behavior selecting another contact
        # when a short legacy ID such as "4" is contained in newer timestamp IDs.
        if focus_type == 'contact' and focus_id:
            forced_contact = models_contacts.objects.filter(id_contact=focus_id).first()
            if forced_contact:
                default_selected_member_id = str(forced_contact.id_contact)
                forced_contact_dict = model_to_dict(forced_contact)
                selectbox_members = [
                    member for member in selectbox_members
                    if str(member.get('id_contact')) != str(forced_contact.id_contact)
                ]
                selectbox_members.insert(0, forced_contact_dict)

        list_product_denomination = models_products.objects.values_list('product_denomination', flat=True)
        list_product_denomination = list(list_product_denomination)

        list_id_product = models_products.objects.values_list('id_product', flat=True)
        list_id_product = list(list_id_product)

        # STEP 8 - if the page was opened from a double-click on a grid item,
        # force the clicked object to be visible/selected on initial page load.
        # This does not change the behavior of custom dropdown changes after load.
        if focus_type and focus_id:
            forced_company_id = None
            forced_project_id = None

            if focus_type == 'company':
                forced_company_id = focus_id

            elif focus_type == 'project':
                forced_project_id = focus_id
                related_companies = _get_company_ids_for_project(focus_id)
                if related_companies:
                    forced_company_id = related_companies[0]

            elif focus_type == 'interaction':
                interaction = models_interactions.objects.filter(id_interaction=focus_id).first()
                if interaction:
                    if getattr(interaction, 'interaction_project_id', '') and interaction.interaction_project_id != '0':
                        forced_project_id = interaction.interaction_project_id
                        related_companies = _get_company_ids_for_project(forced_project_id)
                        if related_companies:
                            forced_company_id = related_companies[0]

                    if not forced_company_id:
                        related_companies = _get_company_ids_for_interaction(interaction)
                        if related_companies:
                            forced_company_id = related_companies[0]

            if forced_company_id:
                forced_company = models_companies.objects.filter(id_company=forced_company_id).first()
                if forced_company:
                    # Keep all companies linked to the contact, but force clicked company first.
                    company_ids_for_contact = _get_company_ids_for_contact(pk, include_zero=True)
                    if str(forced_company_id) not in company_ids_for_contact:
                        company_ids_for_contact.insert(0, str(forced_company_id))

                    forced_companies_qs = models_companies.objects.filter(id_company__in=company_ids_for_contact)
                    forced_company_map = {str(company.id_company): model_to_dict(company) for company in forced_companies_qs}
                    ordered_company_ids = [str(forced_company_id)] + [str(x) for x in company_ids_for_contact if str(x) != str(forced_company_id)]
                    selectbox_all_companies = [forced_company_map[x] for x in ordered_company_ids if x in forced_company_map]
                    selectbox_most_recent_company = [model_to_dict(forced_company)]
                    selectbox_most_recent_company_members_id = str(forced_company.id_company)

                    member_ids = _get_contact_ids_for_company(forced_company.id_company)
                    if '0' not in member_ids:
                        member_ids.append('0')
                    if str(pk) not in member_ids:
                        member_ids.insert(0, str(pk))

                    forced_members_qs = models_contacts.objects.filter(id_contact__in=member_ids)
                    forced_members_map = {str(contact.id_contact): model_to_dict(contact) for contact in forced_members_qs}
                    ordered_member_ids = [str(pk)] + [str(x) for x in member_ids if str(x) != str(pk)]
                    selectbox_members = [forced_members_map[x] for x in ordered_member_ids if x in forced_members_map]
                    default_selected_member_id = str(pk)
                    selectbox_most_recent_company_members = _legacy_join_ids(member_ids, include_zero=True)

                    project_ids_for_company = _get_project_ids_for_company(forced_company.id_company)
                    if '0' not in project_ids_for_company:
                        project_ids_for_company.append('0')
                    if forced_project_id and str(forced_project_id) not in project_ids_for_company:
                        project_ids_for_company.insert(0, str(forced_project_id))

                    forced_projects_qs = models_projects.objects.filter(id_project__in=project_ids_for_company)
                    forced_projects_map = {str(project.id_project): model_to_dict(project) for project in forced_projects_qs}

                    if forced_project_id:
                        ordered_project_ids = [str(forced_project_id)] + [str(x) for x in project_ids_for_company if str(x) != str(forced_project_id)]
                    else:
                        real_project_ids = [str(x) for x in project_ids_for_company if str(x) != '0']
                        sorted_project_ids = list(
                            models_projects.objects
                            .filter(id_project__in=real_project_ids)
                            .order_by('-project_last_modification_date')
                            .values_list('id_project', flat=True)
                        )
                        ordered_project_ids = [str(x) for x in sorted_project_ids]
                        if '0' in project_ids_for_company:
                            ordered_project_ids.append('0')

                    selectbox_company_project = [forced_projects_map[x] for x in ordered_project_ids if x in forced_projects_map]

                    if not selectbox_company_project:
                        selectbox_company_project = [{'id_project': '0', 'project_last_modification_date': '0', 'project_denomination': 'Seva company first'}]

        return render(request, 'update_record.html', {
            'selectbox_company_project': selectbox_company_project,
            'selectbox_all_companies': selectbox_all_companies,
            'selectbox_members': selectbox_members,
            'default_selected_member_id': default_selected_member_id,
            'selectbox_most_recent_company_members_id': selectbox_most_recent_company_members_id,
            'selectbox_most_recent_company_members': selectbox_most_recent_company_members,
            'selectbox_most_recent_company': selectbox_most_recent_company,

        })
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

####################################################################################
def update_project_mobile(request):
    if request.user.is_authenticated:

        project_num = request.GET.get('project_num')  # have to pass it also in Ajax

        current_record2 = models_projects.objects.get(id_project=project_num) # id= can be replace by any field on the DB
        projects_form = forms_projects(request.POST or None, instance=current_record2)

        if projects_form.is_valid():
            projects_instance = projects_form.save(commit=False)
            projects_instance.project_denomination = projects_instance.project_denomination[:200]
            projects_instance.save()


        return render(request, 'projects_mobile.html', {'projects_form':projects_form})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def update_company_mobile(request):
    if request.user.is_authenticated:

        company_num = request.GET.get('company_num')  # have to pass it also in Ajax

        current_record8 = models_companies.objects.get(id_company=company_num) # id= can be replace by any field on the DB
        company_form = forms_companies(request.POST or None, instance=current_record8)

        if company_form.is_valid():
            company_instance = company_form.save(commit=False)
            company_instance.company_name = company_instance.company_name[:200]
            company_instance.save()



        return render(request, 'company_mobile.html', {'company_form':company_form, 'company_num':company_num})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def update_contact_mobile(request): #pk is due in forms.py inclass forms_contacts
    if request.user.is_authenticated:

        contact_num = request.GET.get('contact_num')  # have to pass it also in Ajax

        current_record2 = models_contacts.objects.get(id_contact=contact_num) # id= can be replace by any field on the DB
        contact_form = forms_contacts(request.POST or None, instance=current_record2, pk=contact_num)


        return render(request, 'contacts_mobile.html', {'contact_form':contact_form, 'contact_num':contact_num})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def update_interaction(request):

    if request.user.is_authenticated:
        #current_user = request.user.first_name+' '+request.user.last_name
        current_user = request.user.id
        current_contact_id = request.GET.get('current_contact_id')
        current_contact_name = request.GET.get('current_contact_name')
        interaction_id = request.GET.get('interaction_id')  # can maybe be removed and replaced by 0 if always = 0
        current_company_id = request.GET.get('current_company_id')
        current_project_id = request.GET.get('current_project_id')
        current_project_name = request.GET.get('current_project_name')

        new_interaction_request = models_interactions.objects.get(id_interaction=interaction_id)
        new_interaction_form = forms_interactions(request.POST or None, instance=new_interaction_request,initial={"interaction_user":current_user,
                                                                                                                  "interaction_contact_id":current_contact_id,
                                                                                                                  "interaction_contact_name":current_contact_name,
                                                                                                                  "interaction_company_id":current_company_id,
                                                                                                                  "interaction_project_id": current_project_id,
                                                                                                                  "interaction_project_name":current_project_name,
                                                                                                                  })


        return render(request, 'interaction_mobile.html', {'new_interaction_form':new_interaction_form,
                                                           'current_user':current_user,
                                                           'current_company_id':current_company_id,})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')
####################################################################################

def update_sales_documents_and_company_addresses(request):
    if not request.user.is_authenticated:
        return JsonResponse({'error': 'Utilisateur non connecté'}, status=403)

    if request.method == "POST":
        id_company = request.POST.get('id_company')
        id_sales_document = request.POST.get('id_sales_document')
        selectbox_id = request.POST.get('selectbox_id')
        id_address = request.POST.get('id_address')

        try:
            if selectbox_id == "selectbox_company_address":
                company = models_companies.objects.get(id_company=id_company)
                company.company_head_quarter_address_id = id_address
                company.save()
                _sync_company_address_link(id_company, id_address, add=True)

            elif selectbox_id == "selectbox_billing_address" and id_sales_document != '0':
                sales_document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
                sales_document.sales_documents_billing_address_id = id_address
                sales_document.save()

            elif selectbox_id == "selectbox_delivery_address" and id_sales_document != '0':
                sales_document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
                sales_document.sales_documents_delivery_address_id = id_address
                sales_document.save()

            return JsonResponse({'success': True})
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=500)

    return JsonResponse({'error': 'Méthode non autorisée'}, status=405)

def update_address_mobile(request):
    if request.user.is_authenticated:

        id_address = request.GET.get('id_address')  # have to pass it also in Ajax

        form_address_query = models_addresses.objects.get(id_address=id_address)  # id= can be replace by any field on the DB
        form_address = forms_addresses(request.POST or None, instance=form_address_query)

        if form_address.is_valid():
            address_instance = form_address.save(commit=False)
            address_instance.address_title = address_instance.address_title[:200]
            address_instance.address_type = address_instance.address_type[:200]
            address_instance.save()


        return render(request, 'address_mobile.html', {'form_address': form_address, 'id_address': id_address})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def _serialize_sales_document_line_for_json(line):
    product = getattr(line, 'product', None)
    return {
        'id_sales_document_line': line.id_sales_document_line,
        'sales_document_id': line.sales_document_id,
        'sales_documents_line_number': line.sales_documents_line_number,
        'sales_documents_line_position': line.sales_documents_line_position,
        'product_id': line.product_id or '',
        'product_reference': getattr(product, 'product_reference', '') if product else '',
        'product_denomination': getattr(product, 'product_denomination', '') if product else '',
        'product_category': getattr(product, 'product_category', '') if product else '',
        'sales_documents_quantity': line.sales_documents_quantity or '',
        'sales_documents_description_free': line.sales_documents_description_free or '',
        'sales_documents_price': line.sales_documents_price or '',
        'sales_documents_discount': line.sales_documents_discount or '',
        'sales_documents_calculated_price': line.sales_documents_calculated_price or '',
        'sales_documents_discounted_price': line.sales_documents_discounted_price or '',
        'sales_documents_vat': line.sales_documents_vat or 0,
    }


def get_sales_document_lines_json(request):
    """
    PALIER D2 : API lecture JSON des lignes document depuis table_sales_document_lines.

    Objectif : poser une base propre pour un futur front dynamique sans toucher au
    front actuel en 25 lignes fixes.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    sales_document_id = (
        request.GET.get('sales_document_id')
        or request.GET.get('id_sales_documents')
        or request.GET.get('selectbox_update_record_sales_documents_edit_id')
        or ''
    ).strip()

    if not sales_document_id:
        return JsonResponse({'result': 'error', 'message': 'sales_document_id manquant'}, status=400)

    doc = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()
    if not doc:
        return JsonResponse({'result': 'error', 'message': 'document introuvable'}, status=404)

    lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .select_related('product')
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )

    max_line_number = max([line.sales_documents_line_number for line in lines], default=0)
    max_position = max([line.sales_documents_line_position for line in lines], default=0)

    return JsonResponse({
        'result': 'success',
        'sales_document_id': doc.id_sales_documents,
        'line_count': len(lines),
        'next_line_number': max_line_number + 1,
        'next_position': max_position + 1,
        'lines': [_serialize_sales_document_line_for_json(line) for line in lines],
    })



def get_sales_document_products_json(request):
    """
    PALIER D3 : API lecture JSON des produits disponibles pour les futures lignes
    document dynamiques.

    Objectif : préparer le futur rendu JS des lignes sans toucher au front actuel.
    Cette route ne modifie aucune donnée.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    products_queryset = models_products.objects.all().order_by('product_category', 'product_denomination', 'id_product')

    products = []
    categories = []
    seen_categories = []

    for product in products_queryset:
        category = getattr(product, 'product_category', '') or ''
        if category and category not in seen_categories:
            seen_categories.append(category)
            categories.append(category)

        products.append({
            'id': str(getattr(product, 'id_product', '') or ''),
            'category': category,
            'reference': getattr(product, 'product_reference', '') or '',
            'denomination': getattr(product, 'product_denomination', '') or '',
            'prix_achat': getattr(product, 'product_prix_achat', '') or '',
            'prix_vente': getattr(product, 'product_prix_vente', '') or '',
            'prix_location': getattr(product, 'product_prix_location', '') or '',
            'vat': getattr(product, 'product_vat', '') or '',
        })

    return JsonResponse({
        'result': 'success',
        'product_count': len(products),
        'categories': categories,
        'products': products,
    })

def _clean_dynamic_line_text(value, max_len=None):
    value = str(value or '').strip()
    if max_len is not None:
        return value[:max_len]
    return value


def save_sales_document_lines_json(request):
    """
    PALIER D2 : API écriture JSON des lignes document vers table_sales_document_lines.

    Cette route n'est pas encore branchée au front actuel. Elle sert de base au futur
    éditeur dynamique : nombre de lignes libre, sans limite 25 côté API.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    if request.method != 'POST':
        return JsonResponse({'result': 'method not allowed'}, status=405)

    try:
        payload = json.loads(request.body.decode('utf-8') or '{}')
    except Exception:
        return JsonResponse({'result': 'error', 'message': 'JSON invalide'}, status=400)

    sales_document_id = _clean_dynamic_line_text(payload.get('sales_document_id') or payload.get('id_sales_documents'))
    raw_lines = payload.get('lines', [])

    if not sales_document_id:
        return JsonResponse({'result': 'error', 'message': 'sales_document_id manquant'}, status=400)

    if not isinstance(raw_lines, list):
        return JsonResponse({'result': 'error', 'message': 'lines doit être une liste'}, status=400)

    doc = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()
    if not doc:
        return JsonResponse({'result': 'error', 'message': 'document introuvable'}, status=404)

    clean_lines = []
    used_positions = set()
    used_numbers = set()

    for index, raw_line in enumerate(raw_lines, start=1):
        if not isinstance(raw_line, dict):
            continue

        line_number = _safe_int(raw_line.get('sales_documents_line_number'), index)
        line_position = _safe_int(raw_line.get('sales_documents_line_position'), index)

        if line_number < 1 or line_number in used_numbers:
            line_number = index
        while line_number in used_numbers:
            line_number += 1

        if line_position < 1 or line_position in used_positions:
            line_position = index
        while line_position in used_positions:
            line_position += 1

        used_numbers.add(line_number)
        used_positions.add(line_position)

        product_id = _clean_dynamic_line_text(raw_line.get('product_id') or raw_line.get('sales_documents_description'))
        product = None
        if product_id and product_id not in ['#', '0']:
            product = models_products.objects.filter(id_product=product_id).first()

        clean_lines.append(models_sales_document_lines(
            sales_document=doc,
            sales_documents_line_number=line_number,
            sales_documents_line_position=line_position,
            product=product,
            sales_documents_quantity=_clean_dynamic_line_text(raw_line.get('sales_documents_quantity'), 4),
            sales_documents_description_free=_clean_dynamic_line_text(raw_line.get('sales_documents_description_free'), 200),
            sales_documents_price=_clean_dynamic_line_text(raw_line.get('sales_documents_price'), 10),
            sales_documents_discount=_clean_dynamic_line_text(raw_line.get('sales_documents_discount'), 10),
            sales_documents_calculated_price=_clean_dynamic_line_text(raw_line.get('sales_documents_calculated_price'), 100),
            sales_documents_discounted_price=_clean_dynamic_line_text(raw_line.get('sales_documents_discounted_price'), 10),
            sales_documents_vat=_safe_int(raw_line.get('sales_documents_vat'), 0),
        ))

    models_sales_document_lines.objects.filter(sales_document=doc).delete()
    if clean_lines:
        models_sales_document_lines.objects.bulk_create(clean_lines)

    if payload.get('rebuild_legacy_first_25') is True:
        _rebuild_sales_document_lines_legacy_from_relations(doc)

    saved_lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .select_related('product')
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )

    return JsonResponse({
        'result': 'success',
        'sales_document_id': doc.id_sales_documents,
        'line_count': len(saved_lines),
        'lines': [_serialize_sales_document_line_for_json(line) for line in saved_lines],
    })


def add_sales_document_line_json(request):
    """
    PALIER D4b : API ajout d'une ligne document dynamique.

    Route volontairement isolée : elle ajoute une ligne dans
    table_sales_document_lines sans toucher au front actuel en 25 lignes fixes.
    Elle n'est pas encore branchée à sales_documents_edit.js.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    if request.method != 'POST':
        return JsonResponse({'result': 'method not allowed'}, status=405)

    payload = {}
    content_type = request.META.get('CONTENT_TYPE', '') or ''
    if 'application/json' in content_type:
        try:
            payload = json.loads(request.body.decode('utf-8') or '{}')
        except Exception:
            return JsonResponse({'result': 'error', 'message': 'JSON invalide'}, status=400)
    else:
        payload = request.POST

    sales_document_id = _clean_dynamic_line_text(
        payload.get('sales_document_id')
        or payload.get('id_sales_documents')
        or payload.get('selectbox_update_record_sales_documents_edit_id')
    )

    if not sales_document_id:
        return JsonResponse({'result': 'error', 'message': 'sales_document_id manquant'}, status=400)

    doc = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()
    if not doc:
        return JsonResponse({'result': 'error', 'message': 'document introuvable'}, status=404)

    existing_lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )

    used_numbers = {line.sales_documents_line_number for line in existing_lines}
    used_positions = {line.sales_documents_line_position for line in existing_lines}

    requested_number = _safe_int(payload.get('sales_documents_line_number'), 0)
    requested_position = _safe_int(payload.get('sales_documents_line_position'), 0)

    line_number = requested_number if requested_number > 0 else (max(used_numbers, default=0) + 1)
    while line_number in used_numbers:
        line_number += 1

    line_position = requested_position if requested_position > 0 else (max(used_positions, default=0) + 1)
    while line_position in used_positions:
        line_position += 1

    product_id = _clean_dynamic_line_text(payload.get('product_id') or payload.get('sales_documents_description'))
    product = None
    if product_id and product_id not in ['#', '0']:
        product = models_products.objects.filter(id_product=product_id).first()

    line = models_sales_document_lines.objects.create(
        sales_document=doc,
        sales_documents_line_number=line_number,
        sales_documents_line_position=line_position,
        product=product,
        sales_documents_quantity=_clean_dynamic_line_text(payload.get('sales_documents_quantity'), 4),
        sales_documents_description_free=_clean_dynamic_line_text(payload.get('sales_documents_description_free'), 200),
        sales_documents_price=_clean_dynamic_line_text(payload.get('sales_documents_price'), 10),
        sales_documents_discount=_clean_dynamic_line_text(payload.get('sales_documents_discount'), 10),
        sales_documents_calculated_price=_clean_dynamic_line_text(payload.get('sales_documents_calculated_price'), 100),
        sales_documents_discounted_price=_clean_dynamic_line_text(payload.get('sales_documents_discounted_price'), 10),
        sales_documents_vat=_safe_int(payload.get('sales_documents_vat'), 0),
    )

    if payload.get('rebuild_legacy_first_25') in [True, 'true', '1', 1]:
        _rebuild_sales_document_lines_legacy_from_relations(doc)

    lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .select_related('product')
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )

    return JsonResponse({
        'result': 'success',
        'sales_document_id': doc.id_sales_documents,
        'line_count': len(lines),
        'added_line': _serialize_sales_document_line_for_json(line),
        'lines': [_serialize_sales_document_line_for_json(current_line) for current_line in lines],
    })



def delete_sales_document_line_json(request):
    """
    PALIER D4c : API suppression d'une ligne document dynamique.

    Route volontairement isolée : elle supprime une ligne dans
    table_sales_document_lines sans toucher au front actuel en 25 lignes fixes.
    Elle n'est pas encore branchée à sales_documents_edit.js.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    if request.method != 'POST':
        return JsonResponse({'result': 'method not allowed'}, status=405)

    payload = {}
    content_type = request.META.get('CONTENT_TYPE', '') or ''
    if 'application/json' in content_type:
        try:
            payload = json.loads(request.body.decode('utf-8') or '{}')
        except Exception:
            return JsonResponse({'result': 'error', 'message': 'JSON invalide'}, status=400)
    else:
        payload = request.POST

    sales_document_id = _clean_dynamic_line_text(
        payload.get('sales_document_id')
        or payload.get('id_sales_documents')
        or payload.get('selectbox_update_record_sales_documents_edit_id')
    )
    line_id = _clean_dynamic_line_text(
        payload.get('id_sales_document_line')
        or payload.get('sales_document_line_id')
        or payload.get('line_id')
    )
    line_number = _safe_int(payload.get('sales_documents_line_number'), 0)
    line_position = _safe_int(payload.get('sales_documents_line_position'), 0)

    if not sales_document_id:
        return JsonResponse({'result': 'error', 'message': 'sales_document_id manquant'}, status=400)

    doc = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()
    if not doc:
        return JsonResponse({'result': 'error', 'message': 'document introuvable'}, status=404)

    line_queryset = models_sales_document_lines.objects.filter(sales_document=doc)

    if line_id:
        line = line_queryset.filter(id_sales_document_line=line_id).first()
    elif line_number > 0:
        line = line_queryset.filter(sales_documents_line_number=line_number).first()
    elif line_position > 0:
        line = line_queryset.filter(sales_documents_line_position=line_position).first()
    else:
        line = None

    if not line:
        return JsonResponse({'result': 'error', 'message': 'ligne introuvable'}, status=404)

    deleted_line = _serialize_sales_document_line_for_json(line)

    try:
        with transaction.atomic():
            line.delete()

            remaining_lines = list(
                models_sales_document_lines.objects
                .filter(sales_document=doc)
                .select_related('product')
                .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
            )

            if payload.get('renumber_after_delete') in [True, 'true', '1', 1] and remaining_lines:
                # Les deux colonnes sont protégées par des contraintes UNIQUE par document.
                # Une renumérotation directe peut donc entrer temporairement en collision
                # lorsque le numéro et la position historiques ne sont plus alignés.
                # On déplace d'abord toutes les valeurs dans une zone temporaire libre,
                # puis on réattribue proprement 1..N dans l'ordre visuel courant.
                max_existing_value = max(
                    max(current_line.sales_documents_line_number for current_line in remaining_lines),
                    max(current_line.sales_documents_line_position for current_line in remaining_lines),
                    len(remaining_lines),
                )
                temporary_offset = max_existing_value + len(remaining_lines) + 100
                remaining_line_ids = [
                    current_line.id_sales_document_line
                    for current_line in remaining_lines
                ]

                models_sales_document_lines.objects.filter(
                    sales_document=doc,
                    id_sales_document_line__in=remaining_line_ids,
                ).update(
                    sales_documents_line_number=F('sales_documents_line_number') + temporary_offset,
                    sales_documents_line_position=F('sales_documents_line_position') + temporary_offset,
                )

                for index, current_line_id in enumerate(remaining_line_ids, start=1):
                    models_sales_document_lines.objects.filter(
                        sales_document=doc,
                        id_sales_document_line=current_line_id,
                    ).update(
                        sales_documents_line_position=index,
                        sales_documents_line_number=index,
                    )

                remaining_lines = list(
                    models_sales_document_lines.objects
                    .filter(sales_document=doc)
                    .select_related('product')
                    .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
                )

            if payload.get('rebuild_legacy_first_25') in [True, 'true', '1', 1]:
                _rebuild_sales_document_lines_legacy_from_relations(doc)

    except Exception as error:
        return JsonResponse({
            'result': 'error',
            'message': 'La suppression a été annulée : ' + str(error),
        }, status=500)

    return JsonResponse({
        'result': 'success',
        'sales_document_id': doc.id_sales_documents,
        'line_count': len(remaining_lines),
        'deleted_line': deleted_line,
        'lines': [_serialize_sales_document_line_for_json(current_line) for current_line in remaining_lines],
    })



def reorder_sales_document_lines_json(request):
    """
    PALIER D9b : API de réordonnancement atomique des lignes document dynamiques.

    Payload accepté :
    - sales_document_id
    - ordered_line_ids: [id_sales_document_line, ...]
      ou lines: [{id_sales_document_line: ..., sales_documents_line_position: ...}, ...]

    Les numéros et positions sont déplacés temporairement hors de leur plage avant
    la réattribution 1..N, afin d'éviter toute collision avec les contraintes UNIQUE.
    """
    if not request.user.is_authenticated:
        return JsonResponse({'result': 'unauthorized'}, status=403)

    if request.method != 'POST':
        return JsonResponse({'result': 'method not allowed'}, status=405)

    payload = {}
    content_type = request.META.get('CONTENT_TYPE', '') or ''
    if 'application/json' in content_type:
        try:
            payload = json.loads(request.body.decode('utf-8') or '{}')
        except Exception:
            return JsonResponse({'result': 'error', 'message': 'JSON invalide'}, status=400)
    else:
        payload = request.POST

    sales_document_id = _clean_dynamic_line_text(
        payload.get('sales_document_id')
        or payload.get('id_sales_documents')
        or payload.get('selectbox_update_record_sales_documents_edit_id')
    )

    if not sales_document_id:
        return JsonResponse({'result': 'error', 'message': 'sales_document_id manquant'}, status=400)

    doc = models_sales_documents.objects.filter(id_sales_documents=sales_document_id).first()
    if not doc:
        return JsonResponse({'result': 'error', 'message': 'document introuvable'}, status=404)

    existing_lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )
    existing_by_id = {str(line.id_sales_document_line): line for line in existing_lines}

    raw_ordered_ids = payload.get('ordered_line_ids')
    raw_lines = payload.get('lines')
    ordered_ids = []

    if isinstance(raw_ordered_ids, list):
        ordered_ids = [_clean_dynamic_line_text(value) for value in raw_ordered_ids]
    elif isinstance(raw_lines, list):
        sortable_lines = []
        for index, raw_line in enumerate(raw_lines, start=1):
            if not isinstance(raw_line, dict):
                continue
            line_id = _clean_dynamic_line_text(
                raw_line.get('id_sales_document_line')
                or raw_line.get('sales_document_line_id')
                or raw_line.get('line_id')
            )
            if not line_id:
                continue
            position = _safe_int(raw_line.get('sales_documents_line_position'), index)
            sortable_lines.append((position, index, line_id))
        ordered_ids = [line_id for position, index, line_id in sorted(sortable_lines)]
    else:
        return JsonResponse({'result': 'error', 'message': 'ordered_line_ids ou lines manquant'}, status=400)

    clean_ordered_ids = []
    ignored_ids = []
    for line_id in ordered_ids:
        if not line_id:
            continue
        if line_id not in existing_by_id:
            ignored_ids.append(line_id)
            continue
        if line_id not in clean_ordered_ids:
            clean_ordered_ids.append(line_id)

    # Toute ligne omise par le navigateur est conservée à la fin dans son ordre actuel.
    for line in existing_lines:
        line_id = str(line.id_sales_document_line)
        if line_id not in clean_ordered_ids:
            clean_ordered_ids.append(line_id)

    renumber_line_numbers = payload.get('renumber_line_numbers') not in [False, 'false', '0', 0]

    try:
        with transaction.atomic():
            if clean_ordered_ids:
                max_existing_value = max(
                    max(line.sales_documents_line_position for line in existing_lines),
                    max(line.sales_documents_line_number for line in existing_lines),
                    len(existing_lines),
                )
                temporary_offset = max_existing_value + len(existing_lines) + 100

                ids_for_update = [existing_by_id[line_id].id_sales_document_line for line_id in clean_ordered_ids]

                # Première phase : libérer intégralement la plage 1..N pour éviter
                # les collisions temporaires sur les deux contraintes UNIQUE.
                temporary_updates = {
                    'sales_documents_line_position': F('sales_documents_line_position') + temporary_offset,
                }
                if renumber_line_numbers:
                    temporary_updates['sales_documents_line_number'] = F('sales_documents_line_number') + temporary_offset

                models_sales_document_lines.objects.filter(
                    sales_document=doc,
                    id_sales_document_line__in=ids_for_update,
                ).update(**temporary_updates)

                # Deuxième phase : réattribuer l'ordre définitif 1..N.
                for index, line_id in enumerate(clean_ordered_ids, start=1):
                    update_values = {
                        'sales_documents_line_position': index,
                    }
                    if renumber_line_numbers:
                        update_values['sales_documents_line_number'] = index

                    models_sales_document_lines.objects.filter(
                        sales_document=doc,
                        id_sales_document_line=existing_by_id[line_id].id_sales_document_line,
                    ).update(**update_values)

            if payload.get('rebuild_legacy_first_25') in [True, 'true', '1', 1]:
                _rebuild_sales_document_lines_legacy_from_relations(doc)

    except Exception as error:
        return JsonResponse({
            'result': 'error',
            'message': 'Le réordonnancement a été annulé : ' + str(error),
        }, status=500)

    reordered_lines = list(
        models_sales_document_lines.objects
        .filter(sales_document=doc)
        .select_related('product')
        .order_by('sales_documents_line_position', 'sales_documents_line_number', 'id_sales_document_line')
    )

    return JsonResponse({
        'result': 'success',
        'sales_document_id': doc.id_sales_documents,
        'line_count': len(reordered_lines),
        'ignored_ids': ignored_ids,
        'lines': [_serialize_sales_document_line_for_json(current_line) for current_line in reordered_lines],
    })

####################################################################################

def update_sales_documents_edit(request):
    if not request.user.is_authenticated:
        return JsonResponse({"error": "unauthorized"}, status=403)

    sales_documents_id = request.GET.get('selectbox_update_record_sales_documents_edit_id')
    instance = models_sales_documents.objects.get(id_sales_documents=sales_documents_id)
    _hydrate_sales_document_legacy_fields_from_relation_lines(instance)

    # ✅ L'ID=0 est une option technique "nouveau document".
    # On ne doit jamais reprendre la valeur stockée sur la ligne technique,
    # sinon le toggle Vente/Location se pré-sélectionne à tort.
    if str(sales_documents_id) == '0':
        instance.sales_documents_rent_or_sale = ''

    # 📝 Formulaires
    form_edit = forms_sales_documents_products(request.POST or None, instance=instance)
    form_infos = forms_sales_documents_infos(request.POST or None, instance=instance)
    form_billing = forms_sales_documents_billing(request.POST or None, instance=instance)
    form_delivery = forms_sales_documents_delivery(request.POST or None, instance=instance)
    form_payment = forms_sales_documents_payment_conditions(request.POST or None, instance=instance)

    # 📦 Produits : liste complète + map
    product_list =  [{"id": "", "denomination": "", "category": ""}]+[{"id": "#", "denomination": "Ajouter un nouveau produit", "category": ""}]+[
        {"id": p.id_product, "denomination": p.product_denomination, "category": p.product_category}
        for p in models_products.objects.all()
    ]

    product_id_to_name = {
        p.id_product: p.product_denomination
        for p in models_products.objects.all()
    }

    # 🔄 Valeurs sélectionnées pour l'édition
    selected_denomination_vars = {}
    for i in range(1, 26):
        field_name = f"sales_documents_description_{i}"
        product_id = getattr(instance, field_name, "")
        selected_value = product_id_to_name.get(product_id, "")
        selected_denomination_vars[f"selected_denomination_{i}"] = selected_value

    # 📌 Contexte commun
    base_context = {
        "product_list": product_list,
        "list_product_denomination": list(models_products.objects.values_list('product_denomination', flat=True)),
        "list_id_product": list(models_products.objects.values_list('id_product', flat=True)),
        **selected_denomination_vars
    }

    # 🔍 🟢 Calcul du prochain numéro de paiement (next_number)
    # STEP 29 : table_sales_document_deposits est la source principale.
    # Les anciens champs sales_documents_deposit_* restent reconstruits ailleurs
    # pour compatibilité temporaire, mais ne sont plus relus ici.
    numbers = [
        str(number).strip()
        for number in models_sales_document_deposits.objects.values_list('sales_documents_deposit_number', flat=True)
        if str(number).strip()
    ]

    if not numbers:
        # Aucun paiement → premier numéro
        next_number = 'FA01'
    else:
        # Fonction pour extraire la partie numérique (ex: "FA34:FA44" → "34")
        def extract_number(n):
            n_clean = n.split(':')[0]  # Prend le premier numéro si multiple (séparé par :)
            return int(''.join(filter(str.isdigit, n_clean)) or 0)

        max_num = max([extract_number(n) for n in numbers])
        next_number = f'FA{max_num + 1:02d}'

    # 🔁 Réponse complète avec le next_number ajouté
    return JsonResponse({
        "html_edit": render_to_string('sales_documents_products.html', {
            "context_form_sales_documents_document_edit": form_edit,
            **base_context
        }, request=request),
        "html_infos": render_to_string('sales_documents_infos.html', {
            "form": form_infos
        }, request=request),
        "html_billing": render_to_string('sales_documents_billing.html', {
            "form": form_billing
        }, request=request),
        "html_delivery": render_to_string('sales_documents_delivery.html', {
            "form": form_delivery
        }, request=request),
        "html_payment_conditions": render_to_string('sales_documents_payment_conditions.html', {
            "form": form_payment
        }, request=request),

        "list_product_denomination_json": list(models_products.objects.values_list('id_product', 'product_denomination')),
        "list_id_product_json": list(models_products.objects.values_list('id_product', flat=True)),

        "next_deposit_number": next_number  # ✅ Ajout du prochain numéro pour JS
    })





def create_product_ajax(request):
    if request.method == 'POST':
        # 🔐 Sécurise les champs POST
        line_index = request.POST.get('line_index')  # 📌 Ligne à modifier (1 à 25)
        document_id = request.POST.get('document_id')  # 🆔 ID du document concerné

        # 🛠️ Création du produit dans models_products
        product = models_products.objects.create(
            id_product=(datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16],
            product_last_modification_date=(datetime.now() + timedelta(hours=2, seconds=3)).strftime('%Y%m%d%H%M%S%f')[:16],
            product_last_modification_user=request.user.id,
            product_category=request.POST.get('product_category'),
            product_reference=request.POST.get('product_reference'),
            product_denomination=request.POST.get('product_denomination'),
            product_prix_achat=request.POST.get('product_prix_achat'),
            product_prix_vente=request.POST.get('product_prix_vente'),
            product_prix_location=request.POST.get('product_prix_location'),
            product_poids=request.POST.get('product_poids'),
            product_volume=request.POST.get('product_volume'),
            product_dimensions=request.POST.get('product_dimensions'),
            product_consommation={},
            product_quantite_colis={},
            product_sous_traitant={},
        )


        # ✅ Réponse
        return JsonResponse({
            'status': 'success',
            'new_product_id': product.id_product,
            'new_product_denomination': product.product_denomination
        })

    return JsonResponse({'status': 'error', 'message': 'Méthode non autorisée'}, status=405)




def update_sales_documents_last_modification_date(request):
    sales_document_id = request.POST.get('sales_document_id')

    if not sales_document_id:
        return JsonResponse({"error": "ID manquant"}, status=400)

    # 🧪 On vérifie d'abord si l'ID existe vraiment en base
    if not models_sales_documents.objects.filter(id_sales_documents=sales_document_id).exists():
        # 📦 Si le document n'existe pas encore (nouveau), on renvoie juste la date à injecter côté client
        new_date = (datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16]
        return JsonResponse({
            "updated_sales_documents_last_modification_date": new_date,
            "status": "not_saved_yet"
        })

    try:
        doc = models_sales_documents.objects.get(id_sales_documents=sales_document_id)
        new_date = (datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16]
        doc.sales_documents_last_modification_date = new_date
        doc.save()
        return JsonResponse({
            "updated_sales_documents_last_modification_date": new_date,
            "status": "saved"
        })
    except Exception as e:
        return JsonResponse({"error": str(e)}, status=500)

def update_project_last_modification_date(request):

    project_id = request.POST['project_id']
    current_projects_id = models_projects.objects.get(id_project=project_id)
    current_projects_id.project_last_modification_date = (datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16]
    current_projects_id.save()  # this will update only

    updated_project_last_modification_date = models_projects.objects.filter(id_project=project_id).values_list('project_last_modification_date', flat=True)

    return JsonResponse({"updated_project_last_modification_date":updated_project_last_modification_date[0]})
####################################################################################

#Maybe can be donne in only one view for all contact, companies, addresses, project and documents ?
def check_project_last_modification_date(request):
    if request.method != 'POST':
        return JsonResponse({'status': 'error', 'message': 'Requête non valide.'})

    # 📥 Données POST
    project_id = request.POST.get('project_id')
    current_date = request.POST.get('current_date')
    serialized_form = request.POST.get('current_form', '')

    # 🔍 Récupère le projet en base
    try:
        project = models_projects.objects.get(id_project=project_id)
    except models_projects.DoesNotExist:
        return JsonResponse({'status': 'error', 'message': 'Projet non trouvé.'})

    # ✅ Si la date n’a pas changé, pas besoin de comparer le contenu
    if project.project_last_modification_date == current_date:
        return JsonResponse({'status': 'ok'})

    # 👤 Récupération des infos utilisateur ayant modifié le projet
    try:
        user = User.objects.get(id=int(project.project_last_modification_user))
        user_last_name = user.last_name
        user_first_name = user.first_name
    except (User.DoesNotExist, ValueError, TypeError):
        user_last_name = "Inconnu"
        user_first_name = "Inconnu"

    # 🔄 Décodage du formulaire sérialisé reçu (clé = nom du champ, valeur = texte)
    from_form = {k: v[0] for k, v in parse_qs(serialized_form).items()}

    # 🚫 Champs à ignorer dans la comparaison
    ignored_fields = {
        'csrfmiddlewaretoken',
        'project_last_modification_user',
        'project_last_modification_date',
    }

    # ✅ Liste de tous les champs du modèle à vérifier
    all_fields_to_check = [
        field.name for field in models_projects._meta.fields if field.name not in ignored_fields
    ]

    modifications = {}  # Dictionnaire des différences détectées

    # 🔍 Comparaison champ par champ (formulaire vs base)
    for key in all_fields_to_check:
        if not hasattr(project, key):
            continue  # Sécurité

        db_value = getattr(project, key)
        db_value = '' if db_value is None else str(db_value)

        # ⚠️ Si le champ n’est pas dans le form, on suppose qu’il est vide
        client_value = from_form.get(key, '')

        # 🟡 Si la valeur diffère, on l’ajoute aux conflits
        if client_value != db_value:
            modifications[key] = {
                'ancienne': db_value,
                'nouvelle': client_value
            }

    # ✅ Si aucune réelle différence, on évite un conflit inutile
    if not modifications:
        return JsonResponse({
            'status': 'no_conflict',
            'message': 'La date a changé, mais les données n’ont pas été modifiées.',
            'user_last_name': user_last_name,
            'user_first_name': user_first_name
        })

    # ❌ Conflit détecté avec différences → retour complet
    return JsonResponse({
        'status': 'conflict',
        'user_last_name': user_last_name,
        'user_first_name': user_first_name,
        'modifications': modifications
    })

def check_address_last_modification_date(request):
    if request.method == 'POST':
        address_id = request.POST.get('address_id')
        current_date = request.POST.get('current_date')

        try:
            address = models_addresses.objects.get(id_address=address_id)
        except models_addresses.DoesNotExist:
            return JsonResponse({'status': 'error', 'message': 'Adresse non trouvée.'})

        if address.address_last_modification_date != current_date:
            try:
                user_id = int(address.address_last_modification_user)  # conversion explicite
                user = User.objects.get(id=user_id)
                user_last_name = user.last_name
                user_first_name = user.first_name
            except (User.DoesNotExist, ValueError):
                user_last_name = "Inconnu"
                user_first_name = "Inconnu"

            return JsonResponse({
                'status': 'conflict',
                'user_last_name': user_last_name,
                'user_first_name': user_first_name
            })

        return JsonResponse({'status': 'ok'})

    return JsonResponse({'status': 'error', 'message': 'Requête non valide.'})

####################################################################################

def sales_documents_all_forms_save_without_refresh(request):
    # ✅ Vérifie que la requête est bien en POST
    if request.method != "POST":
        return JsonResponse({"result": "method not allowed"}, status=405)

    # 🔎 Récupère les données essentielles du POST
    id_sales_documents = request.POST.get('id_sales_documents')
    new_tag = request.POST.get('new_sales_documents_tag')  # '0' = création, sinon édition
    project_num = request.POST.get('project_num')
    document_type_number_to_be_created = request.POST.get('document_type_number_to_be_created')
    next_timeline_step = request.POST.get('next_timeline_step')

    # 📦 Variables de réponse initiales
    result = "unknown"
    updated_project_sales_documents_list = []
    errors = {}

    # ✅ Vente/Location est obligatoire : si aucun choix n'est envoyé, on refuse le save.
    # Le JS affiche déjà le bandeau rouge avant l'appel AJAX, mais ce garde-fou serveur
    # évite qu'un document soit sauvegardé sans choix si le JS est contourné ou cassé.
    sales_documents_rent_or_sale_choice = (request.POST.get('sales_documents_rent_or_sale') or '').strip()
    if sales_documents_rent_or_sale_choice not in ['rent', 'sale']:
        return JsonResponse({
            "result": "save failed",
            "errors": {
                "sales_documents_rent_or_sale": ["Sélectionnez Location ou Vente avant de sauvegarder."]
            },
            "updated_project_sales_documents_list": updated_project_sales_documents_list
        })

    # 🔁 Si c’est une création de document
    if new_tag == '0':
        # ➕ Crée un nouvel objet vide (pas encore en DB)
        instance = models_sales_documents(id_sales_documents=id_sales_documents)

        # 🔗 Le lien projet/document sera créé après sauvegarde réelle du document,
        # via table_project_sales_documents, puis l'ancien champ project_sales_document
        # sera reconstruit automatiquement pour compatibilité temporaire.

        # 🔒 Pas de valeurs à préserver en création
        old_billing = ''
        old_delivery = ''

    else:
        # 📝 Sinon, c’est une édition d’un document existant
        instance = models_sales_documents.objects.get(id_sales_documents=id_sales_documents)

        # 🔒 Sauvegarde des valeurs d’adresses avant écrasement par les formulaires
        previous = models_sales_documents.objects.get(id_sales_documents=id_sales_documents)
        old_billing = previous.sales_documents_billing_address_id
        old_delivery = previous.sales_documents_delivery_address_id

    # 📝 Création des 5 formulaires liés au même `instance`
    form_main = forms_sales_documents_products(request.POST, instance=instance)
    form_infos = forms_sales_documents_infos(request.POST, instance=instance)
    form_billing = forms_sales_documents_billing(request.POST, instance=instance)
    form_delivery = forms_sales_documents_delivery(request.POST, instance=instance)
    form_payment = forms_sales_documents_payment_conditions(request.POST, instance=instance)

    # ✅ Si TOUS les formulaires sont valides
    if all([form_main.is_valid(), form_infos.is_valid(), form_billing.is_valid(), form_delivery.is_valid(), form_payment.is_valid()]):
        # 💾 Sauvegarde de tous les formulaires
        form_main.save()
        form_infos.save()
        form_billing.save()
        form_delivery.save()
        form_payment.save()

        # 🔁 Recharge l’instance depuis la BDD pour manipulations finales
        doc_instance = models_sales_documents.objects.get(id_sales_documents=id_sales_documents)

        # 🔐 Réinjecte les valeurs sensibles (adresses) qui ne doivent pas être écrasées
        doc_instance.sales_documents_billing_address_id = old_billing
        doc_instance.sales_documents_delivery_address_id = old_delivery
        # 🔐 Sauvegarde l'utilisateur django qui a sauvegardé
        doc_instance.sales_documents_last_modification_user = request.user.id

        # 🟢 Si un numéro doit être généré (quote / delivery_note / invoice)
        if document_type_number_to_be_created in ['quote', 'delivery_note', 'invoice']:
            # 📅 Date du jour au format YYYYMMDD
            today_str = datetime.now().strftime("%Y%m%d")

            # 🟡 Détermine le préfixe et la colonne concernée
            if document_type_number_to_be_created == 'quote':
                column_name = 'sales_documents_quote_number'
                rent_or_sale = doc_instance.sales_documents_rent_or_sale
                if rent_or_sale == 'rent':
                    prefix = 'DL'
                elif rent_or_sale == 'sale':
                    prefix = 'DV'
                else:
                    prefix = 'ERROR'
            elif document_type_number_to_be_created == 'delivery_note':
                prefix = 'BL'
                column_name = 'sales_documents_delivery_note_number'
            elif document_type_number_to_be_created == 'invoice':
                prefix = 'FA'
                column_name = 'sales_documents_invoice_number'

            # 🔍 Recherche des numéros existants du jour
            all_numbers = models_sales_documents.objects.values_list(column_name, flat=True)
            numbers_today = []
            for raw in all_numbers:
                if not raw:
                    continue
                parts = [s.strip() for s in raw.split(';') if s.strip()]
                for part in parts:
                    if part.startswith(f"{prefix}{today_str}"):
                        numbers_today.append(part)

            # 🔢 Calcule le prochain numéro à générer
            if not numbers_today:
                next_number = f"{prefix}{today_str}/V1"
            else:
                max_version = 1
                for n in numbers_today:
                    if '/V' in n:
                        try:
                            version = int(n.split('/V')[1])
                            max_version = max(max_version, version)
                        except:
                            pass
                next_number = f"{prefix}{today_str}/V{max_version + 1}"

            # ✅ Assigne le numéro généré au champ correspondant
            setattr(doc_instance, column_name, next_number)

        # STEP 26 : les lignes document ne sont plus écrites directement
        # dans les anciens champs sales_documents_description_1..25 depuis le POST.
        # table_sales_document_lines pilote les lignes, puis les anciens champs
        # sont reconstruits plus bas par _rebuild_sales_document_lines_legacy_from_relations().
        # Cela évite qu'un champ legacy masqué/stale écrase la nouvelle source propre.

        # 🔁 Mise à jour du lien projet/document depuis la nouvelle table relationnelle.
        _sync_project_sales_document_link(project_num, id_sales_documents, add=True)
        updated_project_ids = _get_sales_document_ids_for_project(project_num)
        updated_project_sales_documents_list = _legacy_join_ids(updated_project_ids) if updated_project_ids else []

        # 🟢 Si le document est à un stade timeline défini, on met à jour la colonne correspondante
        if next_timeline_step in ['quote', 'deposit', 'delivery_note', 'invoice']:
            doc_instance.sales_documents_timeline_step = next_timeline_step

            # ✅ ✅ ✅ AJOUT demandé : si c’est un step important → valider le projet parent
            if next_timeline_step in ['deposit', 'delivery_note', 'invoice']:
                try:
                    project_to_validate = models_projects.objects.get(id_project=project_num)
                    project_to_validate.project_maturity = 'Validated'
                    project_to_validate.project_credibility = 'Validated'
                    project_to_validate.save()
                except models_projects.DoesNotExist:
                    pass  # 🛑 Si le projet n'existe pas, ne plante pas

        # ✅ Sauvegarde finale du document
        doc_instance.save()

        # ✅ STEP 20: les lignes sont d'abord écrites dans table_sales_document_lines,
        # puis les anciens champs sales_documents_*_1..25 sont reconstruits
        # pour compatibilité temporaire avec le reste du CRM.
        _sync_sales_document_lines_from_post(doc_instance, request.POST)
        _rebuild_sales_document_lines_legacy_from_relations(doc_instance)
        _sync_sales_document_deposits_from_legacy(doc_instance)

        # ✅ Tout est validé
        result = "save success"

    else:
        # ❌ Échec de validation → capture les erreurs des formulaires
        if not form_main.is_valid():
            errors['main'] = form_main.errors
        if not form_infos.is_valid():
            errors['infos'] = form_infos.errors
        if not form_billing.is_valid():
            errors['billing'] = form_billing.errors
        if not form_delivery.is_valid():
            errors['delivery'] = form_delivery.errors
        if not form_payment.is_valid():
            errors['payment'] = form_payment.errors

        result = "save failed"

    # 📤 Réponse JSON avec résultat et erreurs (si présentes)
    return JsonResponse({
        "result": result,
        "errors": errors,
        "updated_project_sales_documents_list": updated_project_sales_documents_list
    })

def update_toggle_sales_documents_save_without_refresh(request):
    try:
        id_sales_document = request.POST.get("id_sales_document")
        value = request.POST.get("sales_documents_rent_or_sale")

        if not id_sales_document or not value:
            return JsonResponse({"error": "ID ou valeur manquante."}, status=400)

        # Récupère le document concerné
        sales_document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)

        # Met à jour la colonne avec la nouvelle valeur
        sales_document.sales_documents_rent_or_sale = value
        sales_document.save()

        return JsonResponse({"message": "Mise à jour réussie."}, status=200)

    except models_sales_documents.DoesNotExist:
        return JsonResponse({"error": "Document introuvable."}, status=404)
    except Exception as e:
        return JsonResponse({"error": str(e)}, status=500)

def duplicate_sales_document(request):
    if request.method != "POST":
        return JsonResponse({"result": "method not allowed"}, status=405)

    source_doc_id = request.POST.get('source_doc_id')
    target_project_id = request.POST.get('target_project_id')

    try:
        source_doc = models_sales_documents.objects.get(id_sales_documents=source_doc_id)
        _hydrate_sales_document_legacy_fields_from_relation_lines(source_doc)
        project = models_projects.objects.get(id_project=target_project_id)
    except models_sales_documents.DoesNotExist:
        return JsonResponse({"result": "error", "error": "Document source introuvable"})
    except models_projects.DoesNotExist:
        return JsonResponse({"result": "error", "error": "Projet cible introuvable"})

    # 🆕 Génère un nouvel ID à 16 chiffres
    now = datetime.now()
    new_id = (datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16]
    new_modif_date = (datetime.now() + timedelta(hours=2, milliseconds=22)).strftime('%Y%m%d%H%M%S%f')[:16]


    # 💾 Clone du document
    doc_copy = copy.copy(source_doc)
    doc_copy.pk = None  # Pour créer un nouveau record
    doc_copy.id_sales_documents = new_id
    doc_copy.sales_documents_denomination = f"{source_doc.sales_documents_denomination}_duplicated"
    doc_copy.sales_documents_timeline_step = "quote"
    doc_copy.sales_documents_last_modification_date = new_modif_date
    doc_copy.sales_documents_last_modification_user = request.user.id

    # 🧹 Nettoyage des champs spécifiques
    for field in [
        'sales_documents_quote_id',
        'sales_documents_quote_number',

        'sales_documents_purchase_order_id',
        'sales_documents_purchase_order_number',

        'sales_documents_delivery_note_id',
        'sales_documents_delivery_note_number',

        'sales_documents_invoice_id',
        'sales_documents_invoice_number',

        'sales_documents_deposit_id',
        'sales_documents_deposit_number',
        'sales_documents_deposit_date',
        'sales_documents_deposit_amount',

        'sales_documents_ref_contact_id'
    ]:
        setattr(doc_copy, field, '')

    doc_copy.save()
    _sync_sales_document_lines_from_instance(doc_copy)

    # 🔗 Ajout du document au projet via table_project_sales_documents,
    # puis reconstruction du vieux champ project_sales_document pour compatibilité.
    _sync_project_sales_document_link(target_project_id, new_id, add=True)

    return JsonResponse({"result": "success", "new_doc_id": new_id})

def update_contact_mobile_save_without_refresh(request):
    #contact_num = request.POST['id_contact']
    #contact_num = pk

    contact_id = request.POST['id_contact']
    create_new_contact_from_serialized_data = request.POST['selectbox_current_company']
    new_contact_tag = request.POST['new_contact_tag']

    if new_contact_tag == '0':

        # STEP 22: ne plus écrire directement dans company_members_id avant la création réelle
        # du contact. On sauvegarde d'abord le contact, puis _sync_company_contact_link()
        # crée la relation dans table_company_contacts et reconstruit company_members_id.
        relation_exists = models_company_contacts.objects.filter(
            company_id=create_new_contact_from_serialized_data,
            contact_id=contact_id
        ).exists()

        result_2 = 'existe deja' if relation_exists else 'OK'

        # instance_create_new_contact = models_contacts.objects.get(id_contact=create_new_contact_from_serialized_data)  # 20240828183412
        contact_form = forms_contacts(request.POST or None, pk=contact_id)

        if request.method == "POST":
            if contact_form.is_valid():
                contact_instance = contact_form.save(commit=False)
                contact_instance.contact_first_name = contact_instance.contact_first_name[:100]
                contact_instance.contact_last_name = contact_instance.contact_last_name[:100]
                contact_instance.save()
                _sync_company_contact_link(create_new_contact_from_serialized_data, contact_instance.id_contact, add=True)
                result = 'save success'
            else:
                result = 'save failed'
            return JsonResponse({"result": result, "result_2":result_2})
        else:
            return render(request, 'contacts_mobile.html', context={"result": 0})
    else:

        current_record2 = models_contacts.objects.get(id_contact=contact_id)  # id= can be replace by any field on the DB
        contact_form = forms_contacts(request.POST or None, instance=current_record2, pk=contact_id)

        if request.method == "POST":
            if contact_form.is_valid():
                contact_instance = contact_form.save(commit=False)
                contact_instance.contact_first_name = contact_instance.contact_first_name[:100]
                contact_instance.contact_last_name = contact_instance.contact_last_name[:100]
                contact_instance.save()
                result = 'save success'
                result_2 = 'OK saved existing'
            else :
                result = 'save failed'
                result_2 = 'save failed existing'
            return JsonResponse({"result": result,  "result_2":result_2})
        else:
            return render(request, 'contacts_mobile.html', context={"result": 0})


def update_company_mobile_save_without_refresh(request):

    company_id = request.POST['id_company']
    current_contact_id = request.POST['selectbox_current_contact']
    new_company_tag = request.POST['new_company_tag']

    if new_company_tag == '0':

        company_form = forms_companies(request.POST or None)

        if request.method == "POST":
            if company_form.is_valid():
                company_instance = company_form.save(commit=False)
                company_instance.company_name = company_instance.company_name[:200]
                company_instance.save()
                _sync_company_contact_link(company_instance.id_company, current_contact_id, add=True)
                _rebuild_company_relation_legacy_fields_from_relations(company_instance.id_company)
                result = 'save success'
            else:
                result = 'save failed'
            return JsonResponse({"result": result})
        else:
            return render(request, 'company_mobile.html', context={"result": 0})
    else:

        current_record2 = models_companies.objects.get(id_company=company_id)  # id= can be replace by any field on the DB
        company_form = forms_companies(request.POST or None, instance=current_record2)

        if request.method == "POST":
            if company_form.is_valid():
                company_instance = company_form.save(commit=False)
                company_instance.company_name = company_instance.company_name[:200]
                company_instance.save()
                _sync_company_contact_link(company_instance.id_company, current_contact_id, add=True)
                _rebuild_company_relation_legacy_fields_from_relations(company_instance.id_company)
                result = 'save success'

            else :
                result = 'save failed '+company_id

            return JsonResponse({"result": result})
        else:
            return render(request, 'company_mobile.html', context={"result": 0})

def update_project_mobile_save_without_refresh(request):
    if request.method != "POST":
        return JsonResponse({"error": "Invalid request method"}, status=405)

    # Sécurité : récupération des données POST avec .get()
    project_id = request.POST.get('id_project')
    company_id = request.POST.get('selectbox_current_company')
    new_project_tag = request.POST.get('new_project_tag')

    if not project_id or not company_id or new_project_tag is None:
        return JsonResponse({"error": "Missing required POST data"}, status=400)

    # initialise variables
    result = "save failed"
    result_2 = "unknown"

    if new_project_tag == '0':
        # Création d'un nouveau projet
        company = models_companies.objects.filter(id_company=company_id).first()
        if not company:
            return JsonResponse({"error": "Company not found"}, status=404)

        # STEP 23: table_company_projects pilote le lien société/projet.
        # On ne modifie plus directement company_projects_id ici.
        # L'ancien champ reste reconstruit automatiquement après création du projet.
        existing_project_ids = _get_project_ids_for_company(company_id)

        if str(project_id) not in [str(pid) for pid in existing_project_ids]:
            result_2 = "OK"
        else:
            result_2 = "existe deja"

        project_form = forms_projects(request.POST or None)
        if project_form.is_valid():
            project_instance = project_form.save(commit=False)

            new_sales_documents_id = (datetime.now() + timedelta(hours=2)).strftime('%Y%m%d%H%M%S%f')[:16]
            new_sales_documents_last_modification_date = (datetime.now() + timedelta(hours=2, seconds=5)).strftime('%Y%m%d%H%M%S%f')[:16]
            new_sales_documents_quote_id = (datetime.now() + timedelta(hours=2, seconds=9)).strftime('%Y%m%d%H%M%S%f')[:16]

            new_sales_documents = models_sales_documents.objects.create(
                id_sales_documents=new_sales_documents_id,
                sales_documents_denomination=(project_instance.project_denomination+' V1')[:200],
                sales_documents_last_modification_date=new_sales_documents_last_modification_date,
                sales_documents_quote_id=new_sales_documents_quote_id,
                sales_documents_rent_or_sale='',
            )
            # STEP 17B: lorsqu'un nouveau projet crée automatiquement son document initial,
            # on crée immédiatement les 25 lignes relationnelles techniques correspondantes.
            _sync_sales_document_lines_from_instance(new_sales_documents)
            project_instance.project_denomination = project_instance.project_denomination[:200]
            # PALIER FINAL B : ne plus initialiser le miroir legacy project_sales_document.
            project_instance.save()
            _sync_company_project_link(company_id, project_instance.id_project, add=True)
            _sync_project_sales_document_link(project_instance.id_project, new_sales_documents_id, add=True)

            result = "save success"
        else:
            result = f"save failed: {project_form.errors.as_json()}"

        return JsonResponse({"result": result, "result_2": result_2})

    else:
        # Mise à jour d’un projet existant
        try:
            current_project = models_projects.objects.get(id_project=project_id)
        except models_projects.DoesNotExist:
            return JsonResponse({"error": "Project not found"}, status=404)

        project_form = forms_projects(request.POST or None, instance=current_project)

        if project_form.is_valid():
            project_instance = project_form.save(commit=False)
            project_instance.project_denomination = project_instance.project_denomination[:200]
            project_instance.save()
            _sync_company_project_link(company_id, project_id, add=True)

            # STEP 24 : le lien projet/document reste piloté par table_project_sales_documents.
            # On ne relit plus project_sales_document depuis le formulaire projet, car ce champ
            # legacy peut être masqué/stale. On le reconstruit depuis la table relationnelle.
            _rebuild_project_sales_documents_legacy_from_relations(project_instance.id_project)

            result = "save success"
            result_2 = "OK saved existing"
        else:
            result = "save failed"
            result_2 = "save failed existing"

        return JsonResponse({"result": result, "result_2": result_2})

def update_interaction_save_without_refresh(request):

    # PALIER FINAL B - FIX : interaction_company_id n'est plus rendu côté front.
    # On conserve un champ de contexte non-legacy pour savoir depuis quelle société
    # l'interaction a été créée, puis on complète aussi via le projet sélectionné.
    posted_company_ids = []

    for company_id in _legacy_split_ids(request.POST.get('interaction_context_company_id', '')):
        if company_id not in posted_company_ids:
            posted_company_ids.append(company_id)

    posted_project_id = str(request.POST.get('interaction_project_id', '') or '').strip()
    if posted_project_id and posted_project_id != '0':
        for company_id in _get_company_ids_for_project(posted_project_id):
            if company_id not in posted_company_ids:
                posted_company_ids.append(company_id)

    new_interaction_form = forms_interactions(request.POST or None)

    if request.method == "POST":
        if new_interaction_form.is_valid():
            new_interaction_instance = new_interaction_form.save(commit=False)
            new_interaction_instance.interaction_content = new_interaction_instance.interaction_content[:59000]

            # PALIER FINAL C1 : interaction_company_id n'est plus écrit côté serveur.

            new_interaction_instance.save()

            # STEP 18B + 27 : ne jamais casser la réponse AJAX après la sauvegarde réelle.
            # L'utilisateur doit recevoir la confirmation et les historiques doivent se recharger,
            # même si une reconstruction relationnelle rencontre une donnée ancienne inattendue.
            try:
                _sync_interaction_companies_from_ids(new_interaction_instance, posted_company_ids)
            except Exception as e:
                return JsonResponse({
                    "result": "save success",
                    "sync_warning": str(e)
                })

            result = 'save success'
        else:
            result = 'save failed'
        return JsonResponse({"result": result})
    else:
        return render(request, 'interaction_mobile.html', context={"result": 0})

def update_address_mobile_save_without_refresh(request):
    if request.method != "POST":
        return JsonResponse({"result": "invalid request"})

    address_id = request.POST.get('id_address')
    company_num = request.POST.get('company_num')
    id_sales_document = request.POST.get('id_sales_document')
    div_container_if_new_address =request.POST.get('div_container_if_new_address')

    if not address_id:
        return JsonResponse({"result": "missing address_id"})

    address_exists = models_addresses.objects.filter(id_address=address_id).exists()

    if address_exists:
        address_instance = models_addresses.objects.get(id_address=address_id)
        address_form = forms_addresses(request.POST, instance=address_instance)
    else:
        address_form = forms_addresses(request.POST)

    if address_form.is_valid():
        # Si nouvelle adresse : forcer l'ID
        if not address_exists:
            address_form.instance.id_address = address_id

        address_form.save()

        # STEP 16 : le lien société/adresse est désormais piloté par table_company_addresses.
        # L'ancien champ company_addresses_id est reconstruit automatiquement par _sync_company_address_link().
        if not address_exists:
            try:
                models_companies.objects.get(id_company=company_num)
                _sync_company_address_link(company_num, address_id, add=True)
            except models_companies.DoesNotExist:
                return JsonResponse({"result": "company not found"})

        # if adresse is new, 'div_container_if_new_address' returns the id of the parent div of the form to know if its company, billing or delivery to update with new id_address for selectbox to have created address as selected otion
        if div_container_if_new_address:
            if div_container_if_new_address == 'div_form_update_record_company_address_container':
                company = models_companies.objects.get(id_company=company_num)
                company.company_head_quarter_address_id = address_id
                company.save()
                _sync_company_address_link(company_num, address_id, add=True)
            if not id_sales_document == '0':
                if div_container_if_new_address == 'div_form_update_record_billing_address_container':
                    sales_document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
                    sales_document.sales_documents_billing_address_id = address_id
                    sales_document.save()
                if div_container_if_new_address == 'div_form_update_record_delivery_address_container':
                    sales_document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
                    sales_document.sales_documents_delivery_address_id = address_id
                    sales_document.save()

        return JsonResponse({"result": "save success"})
    else:
        return JsonResponse({"result": "save failed"})

####################################################################################

def update_selectbox_contact_mobile(request):
    if not request.user.is_authenticated:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

    contact_num = request.GET.get('contact_num')
    company_num = request.GET.get('company_num')

    # STEP 5: membres lus depuis table_company_contacts, fallback ancien champ company_members_id.
    if company_num == '0':
        members_queryset = models_contacts.objects.filter(id_contact=contact_num)
    else:
        member_ids = _get_contact_ids_for_company(company_num)
        members_queryset = models_contacts.objects.filter(id_contact__in=member_ids)

    selectbox_members = [model_to_dict(contact) for contact in members_queryset]

    # STEP 5B: conserver l'option technique ID=0 dans le menu contacts.
    # Elle sert toujours à créer un nouveau contact depuis le menu déroulant.
    zero_contact = models_contacts.objects.filter(id_contact='0').first()
    if zero_contact and not any(str(c.get('id_contact')) == '0' for c in selectbox_members):
        selectbox_members.append(model_to_dict(zero_contact))

    selectbox_members = sorted(
        selectbox_members,
        key=lambda x: x['id_contact'] == '0'
    )

    default_selected = models_contacts.objects.filter(id_contact=contact_num).first()
    default_selected_member_id = str(default_selected.id_contact) if default_selected else ''

    return render(request, 'selectbox_contact_mobile.html', {
        'selectbox_members': selectbox_members,
        'default_selected_member_id': default_selected_member_id,
    })


def update_selectbox_company_mobile(request):
    if not request.user.is_authenticated:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

    contact_num = request.GET.get('contact_num')
    company_num = request.GET.get('company_num')

    # STEP 5: sociétés lues depuis table_company_contacts, fallback ancien champ company_members_id.
    if contact_num == '0':
        companies_queryset = models_companies.objects.filter(id_company=company_num)
    else:
        company_ids = _get_company_ids_for_contact(contact_num, include_zero=True)
        companies_queryset = models_companies.objects.filter(id_company__in=company_ids)

    selectbox_all_companies = [model_to_dict(company) for company in companies_queryset]
    selectbox_all_companies = sorted(selectbox_all_companies, key=lambda x: x['id_company'] == '0')

    # STEP 5: projets lus depuis table_company_projects pour chaque société.
    all_project_ids = []
    for company in selectbox_all_companies:
        all_project_ids.extend(_get_project_ids_for_company(company.get('id_company')))

    company_projects_ids_of_all_companies_where_pk_is_found = ';'.join(all_project_ids)

    selected_company = models_companies.objects.filter(id_company=company_num).first()
    selectbox_most_recent_company = [model_to_dict(selected_company)] if selected_company else []
    selectbox_most_recent_company_members_id = selected_company.id_company if selected_company else ''

    return render(request, 'selectbox_company_mobile.html', {
        'selectbox_all_companies': selectbox_all_companies,
        'company_projects_ids_of_all_companies_where_pk_is_found': company_projects_ids_of_all_companies_where_pk_is_found,
        'company_num': company_num,
        'selectbox_most_recent_company_members_id': selectbox_most_recent_company_members_id,
    })


def update_selectbox_project_mobile(request):
    if not request.user.is_authenticated:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

    company_num = request.GET.get('company_num')

    # STEP 5: projets lus depuis table_company_projects, fallback company_projects_id.
    company_projects_ids = _get_project_ids_for_company(company_num)

    projects_queryset = models_projects.objects.filter(id_project__in=company_projects_ids).order_by('-project_last_modification_date')
    selectbox_company_project = [model_to_dict(project) for project in projects_queryset]

    # STEP 5B: conserver l'option technique ID=0 dans le menu projets.
    # Elle sert toujours à créer un nouveau projet depuis le menu déroulant.
    zero_project = models_projects.objects.filter(id_project='0').first()
    if zero_project and not any(str(p.get('id_project')) == '0' for p in selectbox_company_project):
        selectbox_company_project.append(model_to_dict(zero_project))

    if not selectbox_company_project:
        selectbox_company_project = [{
            'id_project': '0',
            'project_last_modification_date': '0',
            'project_denomination': 'To add a project please save new company first !'
        }]

    return render(request, 'selectbox_projects_mobile.html', {
        'selectbox_company_project': selectbox_company_project,
    })


def update_selectbox_address_mobile(request):
    company_num = request.GET.get('company_num')
    id_sales_document = request.GET.get('id_sales_document')

    # STEP 5: adresses lues depuis table_company_addresses, fallback company_addresses_id.
    address_ids_list = _get_address_ids_for_company(company_num)

    addresses_queryset = models_addresses.objects.filter(id_address__in=address_ids_list)

    grouped_addresses = {}

    for address in addresses_queryset:
        address_type = address.address_type if address.address_type else "Autre"

        if address_type not in grouped_addresses:
            grouped_addresses[address_type] = []

        grouped_addresses[address_type].append({
            'address_id': address.id_address,
            'address_title': address.address_title,
            'address_street_name_1': address.address_street_name_1,
            'address_postal_code': address.address_postal_code,
            'address_city': address.address_city,
        })

    all_company_addresses = dict(grouped_addresses)

    current_company_address = models_companies.objects.filter(id_company=company_num).values_list('company_head_quarter_address_id', flat=True).first()
    current_billing_address = models_sales_documents.objects.filter(id_sales_documents=id_sales_document).values_list('sales_documents_billing_address_id', flat=True).first()
    current_delivery_address = models_sales_documents.objects.filter(id_sales_documents=id_sales_document).values_list('sales_documents_delivery_address_id', flat=True).first()

    return JsonResponse({
        'all_company_addresses': all_company_addresses,
        'current_company_address' : current_company_address,
        'current_billing_address' : current_billing_address,
        'current_delivery_address' : current_delivery_address,
    })


def get_sales_documents_ref_contact(request):
    id_sales_document = request.GET.get("id_sales_document")

    try:
        document = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
        return JsonResponse({
            "sales_documents_ref_contact_id": document.sales_documents_ref_contact_id or ""
        })
    except models_sales_documents.DoesNotExist:
        return JsonResponse({"sales_documents_ref_contact_id": ""})

def save_sales_documents_ref_contact(request):
    if request.method == "POST":
        id_sales_document = request.POST.get("id_sales_document")
        contact_id = request.POST.get("contact_id")

        try:
            doc = models_sales_documents.objects.get(id_sales_documents=id_sales_document)
            doc.sales_documents_ref_contact_id = contact_id
            doc.save()
            return JsonResponse({"success": True})
        except models_sales_documents.DoesNotExist:
            return JsonResponse({"success": False, "error": "Document non trouvé"}, status=404)

    return JsonResponse({"success": False, "error": "Requête invalide"}, status=400)

####################################################################################


####################################################################################
# Open update_record from any grid object
####################################################################################
def open_update_record_from_any_object(request, object_type, object_id):
    if not request.user.is_authenticated:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

    contact_id = None

    if object_type == "contact":
        contact_id = object_id

    elif object_type == "company":
        relation = (
            models_company_contacts.objects
            .filter(company_id=object_id)
            .exclude(contact_id='0')
            .select_related('contact')
            .order_by('-contact__contact_last_modification_date')
            .first()
        )

        if relation:
            contact_id = relation.contact_id

    elif object_type == "project":
        company_project = (
            models_company_projects.objects
            .filter(project_id=object_id)
            .select_related('company')
            .first()
        )

        if company_project:
            relation = (
                models_company_contacts.objects
                .filter(company=company_project.company)
                .exclude(contact_id='0')
                .select_related('contact')
                .order_by('-contact__contact_last_modification_date')
                .first()
            )
            if relation:
                contact_id = relation.contact_id

        # STEP 36 : plus de fallback via company_projects_id / company_members_id ici.
        # Si la relation n'existe pas dans les nouvelles tables, on laisse la redirection échouer proprement.

    elif object_type == "interaction":
        interaction = models_interactions.objects.filter(id_interaction=object_id).first()

        if interaction and interaction.interaction_contact_id and interaction.interaction_contact_id != '0':
            contact_id = interaction.interaction_contact_id

        if not contact_id and interaction:
            relation = (
                models_interaction_companies.objects
                .filter(interaction=interaction)
                .select_related('company')
                .first()
            )

            if relation:
                company_contact = (
                    models_company_contacts.objects
                    .filter(company=relation.company)
                    .exclude(contact_id='0')
                    .select_related('contact')
                    .order_by('-contact__contact_last_modification_date')
                    .first()
                )
                if company_contact:
                    contact_id = company_contact.contact_id

    if contact_id:
        return redirect(f'/update_record/{contact_id}?focus_type={object_type}&focus_id={object_id}')

    messages.success(request, "Aucun contact associé trouvé.")
    return redirect('home')


def test(request):
    return render(request, 'test.html')

def grid(request):
    return render(request, 'grid.html')

def get_sales_documents_product_price(request):
    if request.user.is_authenticated:
        product_id = request.GET.get('product_id')

        product_info = models_products.objects.filter(id_product=product_id).values(
            'product_prix_vente',
            'product_vat'
        ).first()

        if product_info:
            return JsonResponse({
                'success': True,
                'product_prix_vente': product_info['product_prix_vente'],
                'product_vat': product_info['product_vat']
            })
        else:
            return JsonResponse({'success': False, 'error': 'Produit non trouvé'})
    else:
        return JsonResponse({'success': False, 'error': 'Utilisateur non authentifié'})


####################################################################################
#Interaction
def update_contact_interaction(request): #pk is due in forms.py inclass forms_contacts
    if request.user.is_authenticated:

        contact_num = request.GET.get('contact_num')  # have to pass it also in Ajax

        #query_interaction_form = models_interactions.objects.raw("SELECT * FROM table_interactions WHERE '" + contact_num + "' LIKE interaction_contact_id ORDER by interaction_full_date DESC ")  # used to be selectbox_companies_default
        all_contact_interactions_query = models_interactions.objects.filter(interaction_contact_id=contact_num).order_by('-interaction_full_date')
        all_contact_interactions_with_id_replaced_by_name = []
        # Get current django user' name for every interecations of contact_num based on django user's id recorded in table_interaction as his id
        for interaction in all_contact_interactions_query:
            interaction_dict = model_to_dict(interaction)

            try:
                # get user name by query auth_user table
                user = User.objects.get(id=interaction.interaction_user)
                user_full_name = f"{user.first_name} {user.last_name}"
            except User.DoesNotExist:
                user_full_name = "Unknown user - error"

            try:
                contact = models_contacts.objects.get(id_contact=interaction.interaction_contact_id)
                contact_full_name = f"{contact.contact_first_name} {contact.contact_last_name}"
            except models_contacts.DoesNotExist:
                contact_full_name = "Unknown contact - error"

            # teste si le champ est vide ou non avant le get
            if interaction.interaction_project_id:
                try:
                    project = models_projects.objects.get(id_project=interaction.interaction_project_id)
                    project_full_name = f"{project.project_denomination}"
                except models_projects.DoesNotExist:
                    project_full_name = "error project"
            else:
                project_full_name = ""

            # Remplacer dans le dict
            interaction_dict['interaction_user'] = user_full_name
            interaction_dict['interaction_contact_id'] = contact_full_name
            interaction_dict['interaction_project_id'] = project_full_name

            all_contact_interactions_with_id_replaced_by_name.append(interaction_dict)

        return render(request, 'contact_interaction.html', {'all_contact_interactions_with_id_replaced_by_name':all_contact_interactions_with_id_replaced_by_name})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def update_company_interaction(request): #pk is due in forms.py inclass forms_contacts
    if request.user.is_authenticated:

        company_num = request.GET.get('company_num')  # have to pass it also in Ajax

        # STEP 5: interactions lues depuis table_interaction_companies, fallback interaction_company_id.
        company = models_companies.objects.filter(id_company=company_num).first()
        relation_ids = []
        if company:
            relation_ids = list(models_interaction_companies.objects.filter(
                company=company
            ).values_list('interaction__id_interaction', flat=True))

        all_company_interactions_query = models_interactions.objects.filter(
            id_interaction__in=relation_ids
        ).order_by('-interaction_full_date', '-id_interaction')

        all_company_interactions_with_id_replaced_by_name = []
        for interaction in all_company_interactions_query:
            interaction_dict = model_to_dict(interaction)

            try:
                user = User.objects.get(id=interaction.interaction_user)
                user_full_name = f"{user.first_name} {user.last_name}"
            except User.DoesNotExist:
                user_full_name = "Unknown user - error"

            try:
                contact = models_contacts.objects.get(id_contact=interaction.interaction_contact_id)
                contact_full_name = f"{contact.contact_first_name} {contact.contact_last_name}"
            except models_contacts.DoesNotExist:
                contact_full_name = "Unknown contact - error"

            if interaction.interaction_project_id:
                try:
                    project = models_projects.objects.get(id_project=interaction.interaction_project_id)
                    project_full_name = f"{project.project_denomination}"
                except models_projects.DoesNotExist:
                    project_full_name = "error project"
            else:
                project_full_name = ""

            interaction_dict['interaction_user'] = user_full_name
            interaction_dict['interaction_contact_id'] = contact_full_name
            interaction_dict['interaction_project_id'] = project_full_name

            all_company_interactions_with_id_replaced_by_name.append(interaction_dict)

        return render(request, 'company_interaction.html', {'all_company_interactions_with_id_replaced_by_name':all_company_interactions_with_id_replaced_by_name})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')


def update_project_interaction(request): #pk is due in forms.py inclass forms_contacts
    if request.user.is_authenticated:

        project_num = request.GET.get('project_num')  # have to pass it also in Ajax

        # query_interaction_form = models_interactions.objects.raw("SELECT * FROM table_interactions WHERE '" + contact_num + "' LIKE interaction_contact_id ORDER by interaction_full_date DESC ")  # used to be selectbox_companies_default
        all_project_interactions_query = models_interactions.objects.filter(interaction_project_id=project_num).order_by('-interaction_full_date')
        all_project_interactions_with_id_replaced_by_name = []
        # Get current django user' name for every interecations of contact_num based on django user's id recorded in table_interaction as his id
        for interaction in all_project_interactions_query:
            interaction_dict = model_to_dict(interaction)

            try:
                # get user name by query auth_user table
                user = User.objects.get(id=interaction.interaction_user)
                user_full_name = f"{user.first_name} {user.last_name}"
            except User.DoesNotExist:
                user_full_name = "Unknown user - error"

            try:
                contact = models_contacts.objects.get(id_contact=interaction.interaction_contact_id)
                contact_full_name = f"{contact.contact_first_name} {contact.contact_last_name}"
            except models_contacts.DoesNotExist:
                contact_full_name = "Unknown contact - error"

            # teste si le champ est vide ou non avant le get
            if interaction.interaction_project_id:
                try:
                    project = models_projects.objects.get(id_project=interaction.interaction_project_id)
                    project_full_name = f"{project.project_denomination}"
                except models_projects.DoesNotExist:
                    project_full_name = "error project"
            else:
                project_full_name = ""

            # Remplacer dans le dict
            interaction_dict['interaction_user'] = user_full_name
            interaction_dict['interaction_contact_id'] = contact_full_name
            interaction_dict['interaction_project_id'] = project_full_name

            all_project_interactions_with_id_replaced_by_name.append(interaction_dict)


        return render(request, 'project_interaction.html', {'all_project_interactions_with_id_replaced_by_name':all_project_interactions_with_id_replaced_by_name})
    else:
        messages.success(request, "You Must Be Logged In...")
        return redirect('home')

def update_selectbox_interaction_project(request):

    contact_id = request.GET.get('contact_id')
    if not contact_id:
        return JsonResponse({'grouped_projects': {}})

    # STEP 5: entreprises liées au contact depuis table_company_contacts, fallback company_members_id.
    company_ids = _get_company_ids_for_contact(contact_id, include_zero=False)
    companies = models_companies.objects.filter(id_company__in=company_ids).exclude(id_company='0')

    list_selectbox_interaction_project_grouped_json_formated = {}

    for company in companies:
        project_ids = _get_project_ids_for_company(company.id_company)
        projects = models_projects.objects.filter(id_project__in=project_ids).exclude(id_project='0')

        for project in projects:
            list_selectbox_interaction_project_grouped_json_formated.setdefault(company.company_name, []).append({
                'valeur': str(project.id_project),
                'projet': project.project_denomination
            })

    return JsonResponse({'list_selectbox_interaction_project_grouped_json_formated': list_selectbox_interaction_project_grouped_json_formated})