#!/usr/bin/env python3
"""
Populate Categories Table with Australian Construction Trades
Based on WHS Act 2011 risk classifications
"""

from app import create_app
from app.extensions import db
from app.models import Category

def populate_categories():
    """Populate categories table with construction trades and risk levels"""
    
    print("🏗️  POPULATING CONSTRUCTION CATEGORIES...")
    print("=" * 50)
    
    app = create_app()
    
    with app.app_context():
        # Clear existing categories
        Category.query.delete()
        
        # Construction trades with WHS risk levels and requirements
        trades_data = [
            # LOW RISK TRADES
            {
                'name': 'Carpentry',
                'description': 'Timber framing, cabinetry, and general carpentry work',
                'whs_risk_level': 'low',
                'insurance_requirements': '$1M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Painting',
                'description': 'Interior and exterior painting, surface preparation',
                'whs_risk_level': 'low',
                'insurance_requirements': '$1M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Tiling',
                'description': 'Ceramic, stone, and tile installation',
                'whs_risk_level': 'low',
                'insurance_requirements': '$1M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Landscaping',
                'description': 'Garden design, planting, and outdoor construction',
                'whs_risk_level': 'low',
                'insurance_requirements': '$1M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'General Labour',
                'description': 'General construction support and manual labour',
                'whs_risk_level': 'low',
                'insurance_requirements': '$1M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            
            # MEDIUM RISK TRADES
            {
                'name': 'Plumbing',
                'description': 'Water, gas, and drainage system installation',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Bricklaying',
                'description': 'Brick and masonry construction work',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Concreting',
                'description': 'Concrete pouring, finishing, and formwork',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Plastering',
                'description': 'Wall and ceiling plastering and rendering',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Flooring',
                'description': 'Timber, vinyl, carpet, and hard flooring installation',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            {
                'name': 'Formwork Carpentry',
                'description': 'Temporary concrete formwork construction and structural support systems',
                'whs_risk_level': 'medium',
                'insurance_requirements': '$2M public liability minimum',
                'license_required': False,
                'white_card_required': True
            },
            
            # HIGH RISK TRADES
            {
                'name': 'Electrical',
                'description': 'Electrical installations and maintenance',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Roofing',
                'description': 'Roof installation, repair, and maintenance',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Scaffolding',
                'description': 'Scaffolding erection and dismantling',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Demolition',
                'description': 'Building and structure demolition work',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Excavation',
                'description': 'Earthmoving and excavation operations',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'HVAC',
                'description': 'Heating, ventilation, and air conditioning systems installation and maintenance',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Air Conditioning',
                'description': 'Split system, ducted, and commercial air conditioning installation and repair',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Steelfixing',
                'description': 'Reinforcement bar (rebar) placement and tying for concrete structures',
                'whs_risk_level': 'high',
                'insurance_requirements': '$5M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            
            # EXTREME RISK TRADES
            {
                'name': 'Crane Operation',
                'description': 'Mobile crane and tower crane operations',
                'whs_risk_level': 'extreme',
                'insurance_requirements': '$10M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'High Voltage Electrical',
                'description': 'High voltage electrical systems and substations',
                'whs_risk_level': 'extreme',
                'insurance_requirements': '$10M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Asbestos Removal',
                'description': 'Licensed asbestos removal and remediation',
                'whs_risk_level': 'extreme',
                'insurance_requirements': '$10M public liability minimum',
                'license_required': True,
                'white_card_required': True
            },
            {
                'name': 'Explosive Works',
                'description': 'Blasting and explosive demolition work',
                'whs_risk_level': 'extreme',
                'insurance_requirements': '$10M public liability minimum',
                'license_required': True,
                'white_card_required': True
            }
        ]
        
        # Create categories
        categories_created = 0
        for i, trade_data in enumerate(trades_data, 1):
            category = Category(
                name=trade_data['name'],
                description=trade_data['description'],
                whs_risk_level=trade_data['whs_risk_level'],
                insurance_requirements=trade_data['insurance_requirements'],
                license_required=trade_data['license_required'],
                white_card_required=trade_data['white_card_required'],
                is_active=True,
                sort_order=i
            )
            db.session.add(category)
            categories_created += 1
            
            # Show progress
            risk_icon = {
                'low': '🟢',
                'medium': '🟡', 
                'high': '🟠',
                'extreme': '🔴'
            }
            print(f"   {risk_icon[trade_data['whs_risk_level']]} {trade_data['name']} - {trade_data['whs_risk_level'].upper()} risk")
        
        # Commit all categories
        db.session.commit()
        
        print(f"\n✅ Successfully created {categories_created} construction categories!")
        
        # Verify categories in database
        total_categories = Category.query.count()
        print(f"✅ Total categories in database: {total_categories}")
        
        # Show risk distribution
        risk_counts = {}
        for level in ['low', 'medium', 'high', 'extreme']:
            count = Category.query.filter_by(whs_risk_level=level).count()
            risk_counts[level] = count
            print(f"   📊 {level.upper()} risk: {count} categories")
        
        print(f"\n🎉 Categories table populated successfully!")
        print(f"📋 Ready for job postings with proper WHS compliance!")

if __name__ == "__main__":
    populate_categories()