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_projects():
    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
        print(f'Created sync record with ID: {sync_id}')

        # Fetch from Rentman API
        api_key = os.getenv('RENTMAN_API_KEY').strip()
        api_url = os.getenv('RENTMAN_API_URL')
        all_projects = []
        limit = 100
        offset = 0
        has_more = True

        print('Starting Rentman sync...')

        while has_more:
            params = {
                'fields': 'id,displayname,number,planperiod_start,planperiod_end,custom',
                'limit': limit,
                'offset': offset
            }

            print(f'Fetching projects with offset {offset}...')

            response = requests.get(
                f"{api_url}/projects",
                params=params,
                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()
            if not data['data']:
                has_more = False
            else:
                all_projects.extend(data['data'])
                offset += limit

        print(f'Found {len(all_projects)} total projects')

        # Filter and process projects ending in 2025 or 2026
        filtered_projects = []
        projects_to_remove = []
        # Make now timezone-aware with UTC
        now = datetime.now().astimezone()

        for project in all_projects:
            # Skip projects with missing dates
            if not project['planperiod_start'] or not project['planperiod_end']:
                print(f"Skipping project {project['id']} due to missing dates")
                continue

            try:
                # Parse dates and make them timezone-aware
                start_date = datetime.fromisoformat(project['planperiod_start'].replace('Z', '+00:00'))
                end_date = datetime.fromisoformat(project['planperiod_end'].replace('Z', '+00:00'))
                
                # Check if project ends in 2025 or 2026
                if end_date.year == 2025 or end_date.year == 2026:
                    # Check if project has custom_101 value of "3"
                    custom_101_value = project.get('custom', {}).get('custom_101')
                    
                    if custom_101_value == "3":
                        # Add to removal list
                        projects_to_remove.append(project['id'])
                        continue
                    
                    # Determine status
                    status = 'Gepland'
                    if now >= start_date and now <= end_date:
                        status = 'Actief'
                    elif now > end_date:
                        status = 'Afgerond'

                    filtered_projects.append({
                        'rentman_id': project['id'],
                        'number': project['number'] or '',
                        'displayname': project['displayname'] or 'Untitled Project',
                        'planperiod_start': project['planperiod_start'],
                        'planperiod_end': project['planperiod_end'],
                        'status': status
                    })
            except Exception as e:
                print(f"Error processing project {project['id']}: {str(e)}")
                continue

        print(f'Found {len(filtered_projects)} filtered projects ending in 2025 or 2026')
        print(f'Found {len(projects_to_remove)} projects with custom_101=3 to remove')

        # Remove projects with custom_101=3
        if projects_to_remove:
            placeholders = ', '.join(['%s'] * len(projects_to_remove))
            cursor.execute(
                f'DELETE FROM Projects WHERE rentman_id IN ({placeholders})',
                projects_to_remove
            )
            print(f'Removed {len(projects_to_remove)} projects with custom_101=3')

        added = 0
        updated = 0

        # Update database
        for project in filtered_projects:
            # Check if project exists
            cursor.execute(
                'SELECT id FROM Projects WHERE rentman_id = %s',
                [project['rentman_id']]
            )
            existing_project = cursor.fetchone()

            if not existing_project:
                # Insert new project
                cursor.execute(
                    '''INSERT INTO Projects 
                    (rentman_id, number, displayname, planperiod_start, 
                    planperiod_end, status, last_synced)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)''',
                    [
                        project['rentman_id'],
                        project['number'],
                        project['displayname'],
                        project['planperiod_start'],
                        project['planperiod_end'],
                        project['status'],
                        sync_id
                    ]
                )
                added += 1
            else:
                # Update existing project
                cursor.execute(
                    '''UPDATE Projects 
                    SET number = %s, displayname = %s, planperiod_start = %s,
                    planperiod_end = %s, status = %s, last_synced = %s
                    WHERE rentman_id = %s''',
                    [
                        project['number'],
                        project['displayname'],
                        project['planperiod_start'],
                        project['planperiod_end'],
                        project['status'],
                        sync_id,
                        project['rentman_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_projects()
    except Exception as e:
        print(f'Script failed: {str(e)}')
        exit(1)