import os
import pyodbc
import requests
from django.conf import settings
from django.db import connection


def get_all_price_master_data(auth_token):
    """service to get all price master data"""
    address_api = settings.PO_API_URL+"customers/"
    headers = {
        'x-api-key': os.getenv('API_KEY'),
        'x-content-type': 'application/json',
        'x-auth-token': auth_token,
    }
    payload = {}
    try:
        response = requests.post(address_api, json=payload,headers=headers)
        if response.status_code == 200:
            address_data = response.json()
            if address_data["data"].get("City"):
               return address_data["data"].get("City")
            else:
                return ""
        else:
            print(f" address  API returned an error: {response.status_code} - {response.text}")
            return []
    except requests.RequestException as e:
        print(f"Error fetching address data: {e}")
        return []


def connect_sql_server_live():
    # Connection configuration
    server = 'SRVPSQL01'
    database = 'pvsmaster_gent'
    username = 'parvathy'
    password = 'Tyc@n123!'
    driver = '{ODBC Driver 17 for SQL Server}'

    conn = pyodbc.connect(
        f'DRIVER={driver};'
        f'SERVER={server};'
        f'DATABASE={database};'
        f'UID={username};'
        f'PWD={password}'
    )

    cursor = conn.cursor()

    return cursor, conn


def connect_sql_server_uat():
    # Connection configuration
    import pyodbc

    server = '94.136.187.179'
    database = 'pvsmaster_gent'
    username = 'Tycan'
    password = 'Tyc@n#2024'
    driver = '{ODBC Driver 17 for SQL Server}'

    conn = pyodbc.connect(
        f'DRIVER={driver};'
        f'SERVER={server};'
        f'DATABASE={database};'
        f'UID={username};'
        f'PWD={password}'
    )
    cursor = conn.cursor()
    return cursor, conn

from django.db import connections


def update_address_master():
    """service to update address master data"""

    live_cursor, live_conn = connect_sql_server_live()
    uat_cursor, uat_conn = connect_sql_server_uat()

    live_cursor.execute("""
        SELECT 
            CustomerMasterId,
            CustomerNickName,
            CustomerName,
            Type,
            AddressLine1,
            AddressLine2,
            AddressLine3,
            City,
            CityMapping,
            State,
            Country,
            PostalCode,
            PreferredDeliveryTime,
            PreferredDeliveryTimeInterval,
            DebtorCode,
            StateCode,
            CountryCode, ContactPerson, JobDescription, Phone, Fax, Email, ExactERPAddressId,
            CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, IsDeleted, ArticleCode, HasOriginStatement
        FROM AddressMaster
    """)
    addresses = live_cursor.fetchall()
    #
    uat_cursor.execute("TRUNCATE TABLE AddressMaster")


    for addr in addresses:
        addr = tuple(addr)
        uat_cursor.execute("""
            INSERT INTO AddressMaster (
                CustomerMasterId,
                CustomerNickName,
                CustomerName,
                Type,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                City,
                CityMapping,
                State,
                Country,
                PostalCode,
                PreferredDeliveryTime,
                PreferredDeliveryTimeInterval,
                DebtorCode,
                StateCode,
                CountryCode,
                ContactPerson,
                JobDescription,
                Phone,
                Fax,
                Email,
                ExactERPAddressId,
                CreatedBy,
                CreatedDate,
                UpdatedBy,
                UpdatedDate,
                IsDeleted,
                ArticleCode,
                HasOriginStatement
            )
            VALUES (
                 ?, ?, ?, ?, ?, ?, ?, ?,
                 ?, ?, ?, ?, ?, ?, ?, ?,
                 ?, ?, ?, ?, ?, ?, ?, ?,
                 ?, ?, ?, ?, ?, ?
            )
        """, addr)
    uat_conn.commit()




    return {"message": "Data updated" }