"""
PROJECT Models - Maps to projects, project_regions, project_roles, project_subcontractors tables
"""
from sqlalchemy import Column, String, Boolean, Integer, Text, Date, DateTime, Numeric, ForeignKey, CheckConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB, ENUM
from sqlalchemy.orm import relationship
from datetime import datetime
from typing import Optional, List, Dict, Any
from app.models.base import BaseModel
from app.models.enums import ProjectStatus, ProjectType, ServiceType, CompensationType


class Project(BaseModel):
    """
    Project model - Central collaboration entity between Client and Contractor
    Maps to 'projects' table in docs/schema/schema.sql
    
    A project represents a specific agreement/work being executed between:
    - Client (telecom operator who needs field work done)
    - Contractor (company that executes the field work)
    """
    __tablename__ = "projects"
    
    # Organization Links - Core Relationship
    client_id = Column(UUID(as_uuid=True), ForeignKey('clients.id', ondelete='RESTRICT'), nullable=False)
    contractor_id = Column(UUID(as_uuid=True), ForeignKey('contractors.id', ondelete='RESTRICT'), nullable=False)
    
    # Project Identity
    title = Column(Text, nullable=False)
    description = Column(Text, nullable=True)
    project_type = Column(
        ENUM(ProjectType, name='projecttype', create_type=False, values_callable=lambda x: [e.value for e in x]),
        nullable=False
    )
    service_type = Column(String(50), nullable=True)  # ServiceType enum in DB
    
    # Project Management
    primary_manager_id = Column(UUID(as_uuid=True), ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    
    # Project Lifecycle
    status = Column(String(50), default='planning', nullable=False)  # ProjectStatus enum in DB
    planned_start_date = Column(Date, nullable=True)
    planned_end_date = Column(Date, nullable=True)
    actual_start_date = Column(Date, nullable=True)
    actual_end_date = Column(Date, nullable=True)
    is_closed = Column(Boolean, default=False, nullable=False)
    closed_at = Column(DateTime, nullable=True)
    closed_by_user_id = Column(UUID(as_uuid=True), ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    
    # Platform Billing (SaaS billing tracking)
    platform_billing_plan = Column(Text, nullable=True)
    is_billable = Column(Boolean, default=True, nullable=False)
    
    # Financial Tracking (JSONB)
    # Example: {"payroll": 500000, "equipment": 300000, "transport": 100000, "materials": 150000}
    budget = Column(JSONB, default={}, nullable=False)
    
    # Inventory Requirements (JSONB Dictionary) - Defines allowed inventory types for this project
    # Key: inventory code, Value: inventory requirement object
    # Example: {
    #   "ONT-ZTE-F670L": {
    #     "code": "ONT-ZTE-F670L",
    #     "name": "ZTE F670L ONT Device",
    #     "description": "Fiber optic network terminal for FTTH installations",
    #     "usage_type": "installed",
    #     "unit": "pieces",
    #     "requires_serial_number": true,
    #     "category": "Equipment",
    #     "include_in_completion": true,
    #     "completion_field_label": "ONT Serial Number",
    #     "completion_required": true
    #   },
    #   "CABLE-FIBER-SM": {
    #     "code": "CABLE-FIBER-SM",
    #     "name": "Single Mode Fiber Cable",
    #     "description": "Outdoor fiber optic cable",
    #     "usage_type": "consumed",
    #     "unit": "meters",
    #     "requires_serial_number": false,
    #     "category": "Cable"
    #   }
    # }
    inventory_requirements = Column(JSONB, default={}, nullable=False)
    
    # Photo Requirements (JSONB Array)
    # Example: [{"type": "before_installation", "required": true, "min": 1, "max": 3}, ...]
    photo_requirements = Column(JSONB, default=[], nullable=False)
    
    # Activation Requirements (JSONB Array) - Dynamic form fields for subscription activation
    # Example: [{"field": "ont_serial_number", "label": "ONT Serial Number", "type": "text", "required": true}, ...]
    activation_requirements = Column(JSONB, default=[], nullable=False)
    
    # Invoice Pricing Rules (JSONB) - Flexible pricing for automatic invoice calculation
    # Example: {"pricing_model": "tiered", "tiers": [...], "tax_rate": 16}
    invoice_pricing_rules = Column(JSONB, default=None, nullable=True)
    
    # Total Tickets Invoiced (for cumulative pricing calculations)
    total_tickets_invoiced = Column(Integer, default=0, nullable=False)
    
    # Additional Metadata (flexible JSONB for future needs)
    additional_metadata = Column(JSONB, default={}, nullable=False)
    
    # Relationships
    client = relationship("Client", foreign_keys=[client_id], lazy='joined')
    contractor = relationship("Contractor", foreign_keys=[contractor_id], lazy='joined')
    primary_manager = relationship("User", foreign_keys=[primary_manager_id])
    closed_by_user = relationship("User", foreign_keys=[closed_by_user_id])
    
    # Child relationships (cascade delete)
    regions = relationship("ProjectRegion", back_populates="project", cascade="all, delete-orphan")
    roles = relationship("ProjectRole", back_populates="project", cascade="all, delete-orphan")
    team = relationship("ProjectTeam", back_populates="project", cascade="all, delete-orphan")
    subcontractors = relationship("ProjectSubcontractor", back_populates="project", cascade="all, delete-orphan")
    timesheets = relationship("Timesheet", back_populates="project", lazy="dynamic")
    payrolls = relationship("UserPayroll", back_populates="project", lazy="dynamic")  # Worker payroll records
    tasks = relationship("Task", back_populates="project", lazy="dynamic")  # For infrastructure projects
    inventory = relationship("ProjectInventory", back_populates="project", lazy="dynamic")  # Equipment & materials
    finance_transactions = relationship("ProjectFinance", back_populates="project", lazy="dynamic")  # Financial transactions
    sales_orders = relationship("SalesOrder", back_populates="project", lazy="dynamic")  # Sales orders for installations
    tickets = relationship("Ticket", back_populates="project", lazy="dynamic")  # Work orders from sales/incidents/tasks
    
    # Table constraints
    __table_args__ = (
        CheckConstraint(
            '(planned_end_date IS NULL OR planned_start_date IS NULL OR planned_end_date >= planned_start_date)',
            name='chk_project_planned_dates'
        ),
        CheckConstraint(
            '(actual_end_date IS NULL OR actual_start_date IS NULL OR actual_end_date >= actual_start_date)',
            name='chk_project_actual_dates'
        ),
    )
    
    def __repr__(self):
        return f"<Project(title='{self.title}', status='{self.status}')>"
    
    # Validation Methods
    def can_activate(self) -> bool:
        """Check if project has minimum requirements to activate"""
        return (
            self.status == ProjectStatus.PLANNING.value and
            self.client_id is not None and
            self.contractor_id is not None and
            self.title is not None
        )
    
    def can_close(self) -> bool:
        """Check if project can be closed (business rules)"""
        return (
            self.status in [ProjectStatus.ACTIVE.value, ProjectStatus.ON_HOLD.value, ProjectStatus.COMPLETED.value] and
            not self.is_closed
        )
    
    def is_active_period(self) -> bool:
        """Check if current date is within project period"""
        from datetime import date
        today = date.today()
        
        if self.actual_start_date and self.actual_end_date:
            return self.actual_start_date <= today <= self.actual_end_date
        elif self.planned_start_date and self.planned_end_date:
            return self.planned_start_date <= today <= self.planned_end_date
        
        return False
    
    @property
    def is_overdue(self) -> bool:
        """Check if project is overdue"""
        from datetime import date
        today = date.today()
        
        if self.is_closed:
            return False
        
        end_date = self.actual_end_date or self.planned_end_date
        return end_date is not None and end_date < today
    
    @property
    def duration_days(self) -> Optional[int]:
        """Calculate project duration in days"""
        start = self.actual_start_date or self.planned_start_date
        end = self.actual_end_date or self.planned_end_date
        
        if start and end:
            return (end - start).days
        return None


class ProjectRegion(BaseModel):
    """
    ProjectRegion model - Geographic organization within a project
    Maps to 'project_regions' table in docs/schema/schema.sql
    
    Purpose: Inventory distribution hubs and team organization
    Flow: Main office → Regional hubs → Field agents collect → Customer sites
    Examples: "Nairobi West", "Mombasa Central", "Kisumu North"
    """
    __tablename__ = "project_regions"
    
    # Relationships
    project_id = Column(UUID(as_uuid=True), ForeignKey('projects.id', ondelete='CASCADE'), nullable=False)
    
    # Region Details
    region_name = Column(Text, nullable=False)  # e.g., "Nairobi West Hub", "Kimbo"
    region_code = Column(Text, nullable=True)  # Short code: "NRB-W"
    description = Column(Text, nullable=True)
    
    # Location Hierarchy (Country → Region/County → City/Sub-county → Address → Coordinates)
    country = Column(Text, default='Kenya', nullable=False)
    region = Column(Text, nullable=True)  # Administrative region/county (e.g., "Nairobi", "Kiambu")
    city = Column(Text, nullable=True)  # Sub-county or city (e.g., "Embakasi East", "Ruiru")
    address_line1 = Column(Text, nullable=True)  # Physical address of regional hub
    address_line2 = Column(Text, nullable=True)
    maps_link = Column(Text, nullable=True)  # Google Maps share link
    latitude = Column(Numeric(precision=10, scale=7), nullable=True)
    longitude = Column(Numeric(precision=10, scale=7), nullable=True)
    
    # Regional Manager
    manager_id = Column(UUID(as_uuid=True), ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    
    # Hub Contact Persons (for public hub tracking access)
    # Array of authorized persons who can view hub inventory without user accounts
    # Structure: [{"name": "John", "phone": "+254...", "email": "...", "role": "Hub Coordinator"}]
    hub_contact_persons = Column(JSONB, default=[], nullable=False)
    
    # Status
    is_active = Column(Boolean, default=True, nullable=False)
    
    # Metadata
    notes = Column(Text, nullable=True)
    additional_metadata = Column(JSONB, default={}, nullable=False)
    
    # Relationships
    project = relationship("Project", back_populates="regions")
    manager = relationship("User", foreign_keys=[manager_id])
    inventory_distributions = relationship("ProjectInventoryDistribution", back_populates="region", lazy="dynamic")
    
    def __repr__(self):
        return f"<ProjectRegion(name='{self.region_name}', project_id='{self.project_id}')>"


class ProjectRole(BaseModel):
    """
    ProjectRole model - Defines roles and compensation within a project

    Simplified global compensation structure with 4 types:
    - FIXED_RATE: Time-based pay (hourly, daily, weekly, monthly)
    - PER_UNIT: Work-based pay (per-ticket, per-job)
    - COMMISSION: Percentage-based pay (sales commission)
    - FIXED_PLUS_COMMISSION: Hybrid (base + commission)

    Examples:
    - Kenya daily worker: FIXED_RATE, base_rate=1000, rate_period=DAY
    - USA hourly worker: FIXED_RATE, base_rate=25, rate_period=HOUR
    - Per-ticket tech: PER_UNIT, per_unit_rate=500
    - Sales agent: COMMISSION, commission_percentage=10
    - Hybrid: FIXED_PLUS_COMMISSION, base_rate=500, rate_period=DAY, commission_percentage=5
    """
    __tablename__ = "project_roles"

    # Relationships
    project_id = Column(UUID(as_uuid=True), ForeignKey('projects.id', ondelete='CASCADE'), nullable=False)

    # Role Definition
    role_name = Column(Text, nullable=False)  # e.g., 'Technician', 'Supervisor'
    description = Column(Text, nullable=True)

    # Compensation Structure
    compensation_type = Column(String(50), nullable=False)  # CompensationType enum in DB

    # For FIXED_RATE and FIXED_PLUS_COMMISSION
    base_rate = Column(Numeric(precision=12, scale=2), nullable=True)  # Base rate amount
    rate_period = Column(String(10), nullable=True)  # RatePeriod enum: HOUR, DAY, WEEK, MONTH

    # For PER_UNIT
    per_unit_rate = Column(Numeric(precision=12, scale=2), nullable=True)  # Per-ticket/job rate

    # For COMMISSION and FIXED_PLUS_COMMISSION
    commission_percentage = Column(Numeric(precision=5, scale=2), nullable=True)  # Commission %

    # Status
    is_active = Column(Boolean, default=True, nullable=False)

    # Relationships
    project = relationship("Project", back_populates="roles")

    # Table constraints
    __table_args__ = (
        # FIXED_RATE requires base_rate and rate_period
        CheckConstraint(
            "compensation_type != 'FIXED_RATE' OR (base_rate IS NOT NULL AND base_rate >= 0 AND rate_period IS NOT NULL)",
            name='chk_fixed_rate_fields'
        ),
        # PER_UNIT requires per_unit_rate
        CheckConstraint(
            "compensation_type != 'PER_UNIT' OR (per_unit_rate IS NOT NULL AND per_unit_rate >= 0)",
            name='chk_per_unit_fields'
        ),
        # COMMISSION requires commission_percentage
        CheckConstraint(
            "compensation_type != 'COMMISSION' OR (commission_percentage IS NOT NULL AND commission_percentage >= 0 AND commission_percentage <= 100)",
            name='chk_commission_fields'
        ),
        # FIXED_PLUS_COMMISSION requires base_rate, rate_period, and commission_percentage
        CheckConstraint(
            "compensation_type != 'FIXED_PLUS_COMMISSION' OR (base_rate IS NOT NULL AND base_rate >= 0 AND rate_period IS NOT NULL AND commission_percentage IS NOT NULL AND commission_percentage >= 0 AND commission_percentage <= 100)",
            name='chk_fixed_plus_commission_fields'
        ),
    )

    def __repr__(self):
        return f"<ProjectRole(name='{self.role_name}', type='{self.compensation_type}')>"


class ProjectSubcontractor(BaseModel):
    """
    ProjectSubcontractor model - Subcontracting relationships within a project
    Maps to 'project_subcontractors' table in docs/schema/schema.sql
    
    Purpose: Track additional contractors hired by main contractor
    A project has one main contractor (projects.contractor_id) and can have multiple subcontractors
    """
    __tablename__ = "project_subcontractors"
    
    # Relationships
    project_id = Column(UUID(as_uuid=True), ForeignKey('projects.id', ondelete='CASCADE'), nullable=False)
    subcontractor_id = Column(UUID(as_uuid=True), ForeignKey('contractors.id', ondelete='RESTRICT'), nullable=False)
    
    # Scope of Work
    scope_description = Column(Text, nullable=True)  # What work is this subcontractor responsible for?
    
    # Regional Assignment (optional - NULL means project-wide)
    project_region_id = Column(UUID(as_uuid=True), ForeignKey('project_regions.id', ondelete='SET NULL'), nullable=True)
    
    # Contract Details
    contract_start_date = Column(Date, nullable=True)
    contract_end_date = Column(Date, nullable=True)
    contract_value = Column(Numeric(precision=12, scale=2), nullable=True)
    currency = Column(Text, default='KES', nullable=False)
    
    # Status
    is_active = Column(Boolean, default=True, nullable=False)
    activated_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    deactivated_at = Column(DateTime, nullable=True)
    deactivated_by_user_id = Column(UUID(as_uuid=True), ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    deactivation_reason = Column(Text, nullable=True)
    
    # Metadata
    notes = Column(Text, nullable=True)
    additional_metadata = Column(JSONB, default={}, nullable=False)
    
    # Relationships
    project = relationship("Project", back_populates="subcontractors")
    subcontractor = relationship("Contractor", foreign_keys=[subcontractor_id])
    region = relationship("ProjectRegion", foreign_keys=[project_region_id])
    deactivated_by_user = relationship("User", foreign_keys=[deactivated_by_user_id])
    
    def __repr__(self):
        return f"<ProjectSubcontractor(project_id='{self.project_id}', subcontractor_id='{self.subcontractor_id}')>"