"""
Script to import Preferred Sectors and Job Categories for Skilled Labour from Excel files
Place the Excel files in the project root directory:
- 'Prefered sectors for skilled labours.xlsx'
- 'Preferred Job Category for skilled Labour.xlsx'

Run: python import_skilled_labour_excel.py
"""

import os
import sys
import django

# Setup Django environment
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from candidates.models import PreferredSectorForSkilledLabour, PreferredJobCategoryForSkilledLabour

def import_from_excel():
    try:
        import openpyxl
    except ImportError:
        print("Error: openpyxl is not installed. Install it using: pip install openpyxl")
        return
    
    base_dir = os.path.dirname(os.path.abspath(__file__))
    
    # Import Sectors
    sectors_file = os.path.join(base_dir, 'Prefered sectors for skilled labours.xlsx')
    if os.path.exists(sectors_file):
        print(f"Importing sectors from: {sectors_file}")
        wb = openpyxl.load_workbook(sectors_file)
        ws = wb.active
        
        count = 0
        for row in ws.iter_rows(min_row=2, values_only=True):
            if row and len(row) > 1 and row[1]:  # Data is in second column
                sector_name = str(row[1]).strip()
                if sector_name:
                    sector, created = PreferredSectorForSkilledLabour.objects.get_or_create(name=sector_name)
                    if created:
                        count += 1
                        print(f"Created: {sector_name}")
                    else:
                        print(f"  Already exists: {sector_name}")
        
        print(f"\nSectors imported: {count}")
    else:
        print(f"Warning: File not found - {sectors_file}")
    
    # Import Job Categories
    categories_file = os.path.join(base_dir, 'Preferred Job Category for skilled Labour.xlsx')
    if os.path.exists(categories_file):
        print(f"\nImporting job categories from: {categories_file}")
        wb = openpyxl.load_workbook(categories_file)
        ws = wb.active
        
        count = 0
        for row in ws.iter_rows(min_row=2, values_only=True):
            if row and len(row) > 1 and row[1]:  # Data is in second column
                category_name = str(row[1]).strip()
                if category_name:
                    category, created = PreferredJobCategoryForSkilledLabour.objects.get_or_create(name=category_name)
                    if created:
                        count += 1
                        print(f"Created: {category_name}")
                    else:
                        print(f"  Already exists: {category_name}")
        
        print(f"\nJob Categories imported: {count}")
    else:
        print(f"Warning: File not found - {categories_file}")
    
    print("\n" + "="*50)
    print("Import Summary:")
    print(f"Total Sectors: {PreferredSectorForSkilledLabour.objects.count()}")
    print(f"Total Job Categories: {PreferredJobCategoryForSkilledLabour.objects.count()}")
    print("="*50)

if __name__ == '__main__':
    import_from_excel()