import sys, os
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
from app.database import Base
from app.models.plant import Plant, PlantCategory, PlantVariety
from app.models.production import ProductionBatch, SeedLot, GerminationRecord, TransplantRecord
from app.models.branch import Branch
from app.config import get_settings
from datetime import date, timedelta
import uuid

settings = get_settings()
engine = create_engine(settings.database_url)

with Session(engine) as db:
    plants = db.query(Plant).limit(10).all()
    branches = db.query(Branch).limit(3).all()
    print(f"Found {len(plants)} plants, {len(branches)} branches")

    if not plants:
        print("No plants found. Run seed_farmer_data.py first.")
        sys.exit(1)

    batch_data = [
        {"plant_idx": 0, "batch_num": "BATCH-2025-000001", "sowing": "2025-01-15", "ready": "2025-04-15", "sown": 500, "current": 450, "status": "ready", "tray": "T-001", "location": "Greenhouse A", "notes": "Tomato batch - high germination rate"},
        {"plant_idx": 0, "batch_num": "BATCH-2025-000002", "sowing": "2025-02-10", "ready": "2025-05-10", "sown": 300, "current": 280, "status": "growing", "tray": "T-002", "location": "Greenhouse A", "notes": "Second tomato batch"},
        {"plant_idx": 1, "batch_num": "BATCH-2025-000003", "sowing": "2025-01-20", "ready": "2025-05-20", "sown": 400, "current": 380, "status": "ready", "tray": "T-003", "location": "Greenhouse B", "notes": "Chili peppers - excellent quality"},
        {"plant_idx": 1, "batch_num": "BATCH-2025-000004", "sowing": "2025-03-01", "ready": "2025-06-01", "sown": 250, "current": 230, "status": "growing", "tray": "T-004", "location": "Greenhouse B", "notes": "Chili batch 2"},
        {"plant_idx": 2, "batch_num": "BATCH-2025-000005", "sowing": "2025-02-01", "ready": "2025-05-01", "sown": 600, "current": 560, "status": "ready", "tray": "T-005", "location": "Nursery Row 1", "notes": "Marigold flowers"},
        {"plant_idx": 3, "batch_num": "BATCH-2025-000006", "sowing": "2025-02-15", "ready": "2025-06-15", "sown": 200, "current": 180, "status": "growing", "tray": "T-006", "location": "Nursery Row 2", "notes": "Jasmine seedlings"},
        {"plant_idx": 4, "batch_num": "BATCH-2025-000007", "sowing": "2025-03-10", "ready": "2025-06-10", "sown": 350, "current": 320, "status": "sowing", "tray": "T-007", "location": "Greenhouse C", "notes": "Eggplant batch"},
        {"plant_idx": 5, "batch_num": "BATCH-2025-000008", "sowing": "2025-01-25", "ready": "2025-04-25", "sown": 450, "current": 410, "status": "ready", "tray": "T-008", "location": "Greenhouse A", "notes": "Cucumber batch"},
        {"plant_idx": 6, "batch_num": "BATCH-2025-000009", "sowing": "2025-03-05", "ready": "2025-06-05", "sown": 150, "current": 140, "status": "growing", "tray": "T-009", "location": "Nursery Row 3", "notes": "Sunflower batch"},
        {"plant_idx": 7, "batch_num": "BATCH-2025-000010", "sowing": "2025-03-15", "ready": "2025-06-15", "sown": 280, "current": 260, "status": "sowing", "tray": "T-010", "location": "Greenhouse C", "notes": "Basil herbs"},
        {"plant_idx": 0, "batch_num": "BATCH-2025-000011", "sowing": "2025-04-01", "ready": "2025-07-01", "sown": 400, "current": 395, "status": "sowing", "tray": "T-011", "location": "Greenhouse A", "notes": "Early tomato - summer batch"},
        {"plant_idx": 2, "batch_num": "BATCH-2025-000012", "sowing": "2025-03-20", "ready": "2025-06-20", "sown": 500, "current": 470, "status": "growing", "tray": "T-012", "location": "Nursery Row 1", "notes": "Marigold batch 2 - growing well"},
    ]

    created = 0
    germination_count = 0
    transplant_count = 0

    for bd in batch_data:
        plant = plants[bd["plant_idx"] % len(plants)]
        branch = branches[0] if branches else None

        batch = ProductionBatch(
            id=str(uuid.uuid4()),
            batch_number=bd["batch_num"],
            plant_id=plant.id,
            sowing_date=date.fromisoformat(bd["sowing"]),
            expected_ready_date=date.fromisoformat(bd["ready"]),
            sown_quantity=bd["sown"],
            current_quantity=bd["current"],
            status=bd["status"],
            tray_number=bd["tray"],
            location=bd["location"],
            branch_id=branch.id if branch else None,
            notes=bd["notes"],
        )
        db.add(batch)
        db.flush()

        if bd["status"] in ("ready", "growing"):
            germ = GerminationRecord(
                id=str(uuid.uuid4()),
                batch_id=batch.id,
                date=date.fromisoformat(bd["sowing"]) + timedelta(days=14),
                germinated_quantity=int(bd["sown"] * 0.9),
                success_rate=90.0,
                notes="Standard germination",
            )
            db.add(germ)
            germination_count += 1

        if bd["status"] == "ready":
            tx = TransplantRecord(
                id=str(uuid.uuid4()),
                batch_id=batch.id,
                date=date.fromisoformat(bd["sowing"]) + timedelta(days=30),
                from_container="Seedling Tray",
                to_container="Growing Pot",
                quantity=bd["current"],
                notes="Transplanted to growing area",
            )
            db.add(tx)
            transplant_count += 1

        created += 1
        print(f"  Created {bd['batch_num']} - {plant.name} ({bd['status']})")

    db.commit()
    print(f"\nDone! Created {created} batches, {germination_count} germination records, {transplant_count} transplant records")