#!/usr/bin/env python3
"""
Seed Australian Construction Trade Categories

Populates the categories table with Australian construction trades
including WHS risk levels, insurance requirements, and licensing info.
"""

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


# Australian Construction Trade Categories
TRADE_CATEGORIES = [
    {
        "name": "General Labourer",
        "description": "General construction labour, site cleanup, material handling, and basic tasks",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. White Card (General Construction Induction) required.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 1
    },
    {
        "name": "Carpenter / Joiner",
        "description": "Structural carpentry, formwork, framing, roofing, decking, and joinery work",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Carpentry trade qualification or extensive experience required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 2
    },
    {
        "name": "Electrician",
        "description": "Electrical installations, wiring, lighting, power systems, and electrical maintenance",
        "whs_risk_level": "extreme",
        "insurance_requirements": "Public liability insurance $10M minimum. Licensed Electrician (A Grade) required. Must be registered with relevant state authority.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 3
    },
    {
        "name": "Plumber",
        "description": "Plumbing installations, drainage, gas fitting, water systems, and sanitary plumbing",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Licensed Plumber required. Gas fitting license if working with gas.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 4
    },
    {
        "name": "Bricklayer / Blocklayer",
        "description": "Brickwork, blockwork, stone masonry, retaining walls, and masonry construction",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Trade qualification or demonstrated competency required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 5
    },
    {
        "name": "Plasterer",
        "description": "Wall and ceiling plastering, rendering, solid plastering, and finishing work",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Trade certificate preferred.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 6
    },
    {
        "name": "Painter / Decorator",
        "description": "Interior and exterior painting, decorating, surface preparation, and finishing",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Working at heights certification if required.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 7
    },
    {
        "name": "Tiler",
        "description": "Floor and wall tiling, waterproofing, screeding, and tile installation",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Waterproofing license for wet areas.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 8
    },
    {
        "name": "Roofer",
        "description": "Roof installation, repairs, guttering, flashing, and roof maintenance",
        "whs_risk_level": "extreme",
        "insurance_requirements": "Public liability insurance $10M minimum. Working at heights certification mandatory. Roof plumber license for metal roofing.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 9
    },
    {
        "name": "HVAC Technician",
        "description": "Heating, ventilation, air conditioning installation, service, and refrigeration",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. ARCtick license for refrigerant handling. Electrical license may be required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 10
    },
    {
        "name": "Scaffolder",
        "description": "Scaffolding erection, dismantling, inspection, and height safety systems",
        "whs_risk_level": "extreme",
        "insurance_requirements": "Public liability insurance $10M minimum. High Risk Work License for scaffolding over 4m. Advanced scaffold ticket required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 11
    },
    {
        "name": "Concreter",
        "description": "Concrete pouring, finishing, formwork, reinforcement, and concrete structures",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Concrete placement certification recommended.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 12
    },
    {
        "name": "Demolition Worker",
        "description": "Building demolition, structural removal, site clearing, and hazardous material handling",
        "whs_risk_level": "extreme",
        "insurance_requirements": "Public liability insurance $10M minimum. Demolition license required. Asbestos awareness training mandatory.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 13
    },
    {
        "name": "Welder / Fabricator",
        "description": "Metal welding, fabrication, structural steel work, and metal joining",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Welding certification to AS/NZS standards. Confined space ticket if required.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 14
    },
    {
        "name": "Landscaper",
        "description": "Landscaping, garden construction, paving, retaining walls, and outdoor features",
        "whs_risk_level": "low",
        "insurance_requirements": "Public liability insurance $1M minimum. Structural landscaping may require engineering approval.",
        "license_required": False,
        "white_card_required": False,
        "sort_order": 15
    },
    {
        "name": "Excavator Operator",
        "description": "Excavation, earthmoving, trenching, grading, and heavy machinery operation",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. High Risk Work License for excavator operation. Dial Before You Dig compliance.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 16
    },
    {
        "name": "Drywall / Gyprock Installer",
        "description": "Plasterboard installation, steel framing, suspended ceilings, and partition walls",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Working at heights certification for ceiling work.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 17
    },
    {
        "name": "Flooring Specialist",
        "description": "Timber flooring, vinyl installation, carpet laying, floor preparation, and finishing",
        "whs_risk_level": "low",
        "insurance_requirements": "Public liability insurance $1M minimum. Floor sanding certification if applicable.",
        "license_required": False,
        "white_card_required": False,
        "sort_order": 18
    },
    {
        "name": "Glass / Glazier",
        "description": "Glass installation, window fitting, glazing, mirrors, and shower screens",
        "whs_risk_level": "high",
        "insurance_requirements": "Public liability insurance $5M minimum. Glass handling and installation certification. Working at heights if required.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 19
    },
    {
        "name": "Insulation Installer",
        "description": "Thermal and acoustic insulation, ceiling insulation, wall insulation, and underfloor",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Insulation installation certification. Asbestos awareness if working in older buildings.",
        "license_required": False,
        "white_card_required": True,
        "sort_order": 20
    },
    {
        "name": "Site Supervisor / Foreman",
        "description": "Site management, coordination, safety oversight, and construction supervision",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $5M minimum. Builder's license or supervisor qualifications. WHS White Card mandatory.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 21
    },
    {
        "name": "Safety Officer / WHS",
        "description": "Workplace health and safety management, compliance, inspections, and risk assessment",
        "whs_risk_level": "low",
        "insurance_requirements": "Professional indemnity insurance $2M minimum. WHS qualification (Cert IV or Diploma). White Card required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 22
    },
    {
        "name": "Building Designer / Draftsperson",
        "description": "Building design, drafting, plans, specifications, and design documentation",
        "whs_risk_level": "low",
        "insurance_requirements": "Professional indemnity insurance $2M minimum. Registered building designer or architectural qualifications.",
        "license_required": True,
        "white_card_required": False,
        "sort_order": 23
    },
    {
        "name": "Engineer (Structural/Civil)",
        "description": "Structural engineering, civil engineering, design certification, and site inspections",
        "whs_risk_level": "low",
        "insurance_requirements": "Professional indemnity insurance $10M minimum. Registered Professional Engineer Queensland (RPEQ) or equivalent state registration.",
        "license_required": True,
        "white_card_required": False,
        "sort_order": 24
    },
    {
        "name": "Waterproofing Specialist",
        "description": "Waterproofing membranes, wet areas, balconies, basements, and moisture protection",
        "whs_risk_level": "medium",
        "insurance_requirements": "Public liability insurance $2M minimum. Waterproofing license (QBCC or state equivalent) required.",
        "license_required": True,
        "white_card_required": True,
        "sort_order": 25
    }
]


def seed_categories():
    """Seed the database with Australian trade categories"""
    app = create_app()
    
    with app.app_context():
        print("\n" + "="*60)
        print("  🏗️  Seeding Australian Trade Categories")
        print("="*60 + "\n")
        
        # Check if categories already exist
        existing_count = Category.query.count()
        if existing_count > 0:
            print(f"⚠️  Database already has {existing_count} categories")
            response = input("\n   Clear existing and reseed? (y/N): ")
            if response.lower() != 'y':
                print("\n❌ Cancelled by user")
                return
            
            # Clear existing categories
            Category.query.delete()
            db.session.commit()
            print("✅ Cleared existing categories\n")
        
        # Add all trade categories
        added = 0
        for cat_data in TRADE_CATEGORIES:
            try:
                category = Category(**cat_data)
                db.session.add(category)
                added += 1
                print(f"✅ Added: {cat_data['name']:<30} ({cat_data['whs_risk_level']} risk)")
            except Exception as e:
                print(f"❌ Error adding {cat_data['name']}: {e}")
        
        # Commit all changes
        try:
            db.session.commit()
            print("\n" + "="*60)
            print(f"  ✅ Successfully seeded {added} trade categories")
            print("="*60)
            
            # Show summary by risk level
            print("\n📊 Summary by Risk Level:")
            for risk in ['low', 'medium', 'high', 'extreme']:
                count = Category.query.filter_by(whs_risk_level=risk).count()
                print(f"   {risk.capitalize():<10} : {count} categories")
            
            print("\n💡 Categories are now available in job posting dropdown!")
            print("\n")
            
        except Exception as e:
            db.session.rollback()
            print(f"\n❌ Error committing to database: {e}")


if __name__ == "__main__":
    seed_categories()