import os
import mysql.connector
import requests
from datetime import datetime
from dotenv import load_dotenv
from pathlib import Path

# Load environment variables from .env.local
env_path = Path(__file__).parents[1] / '.env.local'
load_dotenv(env_path)

def get_db_connection():
    return mysql.connector.connect(
        host=os.getenv('DB_HOST'),
        user=os.getenv('DB_USER'),
        password=os.getenv('DB_PASSWORD'),
        database=os.getenv('DB_NAME')
    )

def sync_rentman_crew():
    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)
    sync_id = None

    try:
        if not os.getenv('RENTMAN_API_KEY'):
            raise Exception('RENTMAN_API_KEY is not configured')

        # Begin transaction
        connection.start_transaction()

        # Create sync history record
        cursor.execute(
            'INSERT INTO SyncHistory (sync_status) VALUES (%s)',
            ['SUCCESS']
        )
        sync_id = cursor.lastrowid

        # Fetch from Rentman API
        api_key = os.getenv('RENTMAN_API_KEY').strip()
        api_url = f"{os.getenv('RENTMAN_API_URL')}/crew"
        
        response = requests.get(
            api_url,
            headers={
                'Authorization': f'Bearer {api_key}',
                'Content-Type': 'application/json'
            }
        )

        if not response.ok:
            raise Exception(f'Rentman API Error: {response.status_code}')

        data = response.json()
        added = 0
        updated = 0

        for member in data['data']:
            # Check if crew member exists
            cursor.execute(
                'SELECT id FROM Crew WHERE rentman_id = %s',
                [member['id']]
            )
            existing_member = cursor.fetchone()

            if not existing_member:
                # Insert new crew member
                cursor.execute(
                    '''INSERT INTO Crew 
                    (rentman_id, displayname, firstname, middle_name, lastname,
                    email, phone, street, housenumber, city, postal_code, last_synced)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
                    [
                        member['id'],
                        member['displayname'],
                        member['firstname'],
                        member['middle_name'],
                        member['lastname'],
                        member['email'],
                        member['phone'],
                        member['street'],
                        member['housenumber'],
                        member['city'],
                        member['postal_code'],
                        sync_id
                    ]
                )
                added += 1
            else:
                # Update existing crew member
                cursor.execute(
                    '''UPDATE Crew 
                    SET displayname = %s, firstname = %s, middle_name = %s,
                        lastname = %s, email = %s, phone = %s, street = %s,
                        housenumber = %s, city = %s, postal_code = %s, last_synced = %s
                    WHERE rentman_id = %s''',
                    [
                        member['displayname'],
                        member['firstname'],
                        member['middle_name'],
                        member['lastname'],
                        member['email'],
                        member['phone'],
                        member['street'],
                        member['housenumber'],
                        member['city'],
                        member['postal_code'],
                        sync_id,
                        member['id']
                    ]
                )
                updated += 1

        # Update sync history
        cursor.execute(
            'UPDATE SyncHistory SET projects_added = %s, projects_updated = %s WHERE id = %s',
            [added, updated, sync_id]
        )

        # Commit transaction
        connection.commit()
        print(f'Sync completed successfully. Added: {added}, Updated: {updated}, SyncID: {sync_id}')

    except Exception as error:
        if connection.in_transaction:
            connection.rollback()
        print(f'Sync Error: {str(error)}')
        raise

    finally:
        cursor.close()
        connection.close()

if __name__ == '__main__':
    try:
        sync_rentman_crew()
    except Exception as e:
        print(f'Script failed: {str(e)}')
        exit(1)