from sqlmodel import Field, SQLModel, Relationship, text, Column
import uuid
from typing import Optional, List
from datetime import datetime
from pydantic import EmailStr
from sqlalchemy import Enum as SAEnum
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import UUID
from etl.fileReader.reportType import ReportType
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy import Text, DateTime
from sqlalchemy.sql import func
from enum import Enum
from sqlalchemy import Column




class BaseModel(SQLModel):
    # Generate a fresh Column per table (no shared Column instance)
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)


class ReportType(str, Enum):
    QUIDOS_PRESITE_NOTE = "QUIDOS_PRESITE_NOTE"
    CHARTED_SURVEYOR_REPORT = "CHARTED_SURVEYOR_REPORT"
    ENERGY_PERFORMANCE_REPORT = "ENERGY_PERFORMANCE_REPORT"
    U_VALUE_CALCULATOR_REPORT = "U_VALUE_CALCULATOR_REPORT"
    OVERWRITING_U_VALUE_DECLARATION_FORM = "OVERWRITING_U_VALUE_DECLARATION_FORM"
    OSMOSIS_CONDITION_PAS_2035_REPORT = "OSMOSIS_CONDITION_PAS_2035_REPORT"
    DOMNA_CONDITION_PAS_2035_REPORT = "DOMNA_CONDITION_PAS_2035_REPORT"
    # Decent Homes Things
    DECENT_HOMES_RAW_DATA = "DECENT_HOMES_RAW_DATA"
    DECENT_HOMES_SUMMARY = "DECENT_HOMES_SUMMARY"
    DECENT_HOMES_PROPERTY_META = "DECENT_HOMES_PROPERTY_META"

class uploaded_files(BaseModel, table=True):
    __tablename__ = "uploaded_files"

    s3_json_uri: Optional[str] = Field(
        sa_column=Column(Text, nullable=True)
    )
    s3_file_uri: str = Field(index=True)

    doc_type: ReportType = Field(
        sa_column=Column(
            SAEnum(ReportType, name="reporttype", native_enum=True, create_type=False, validate_strings=True),
            nullable=False,
        )
    )

    s3_file_upload_timestamp: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("NOW() AT TIME ZONE 'utc'"),
            nullable=False,
        )
    )
    s3_json_upload_timestamp: Optional[datetime] = Field(
        sa_column=Column(DateTime(timezone=True), nullable=True)
    )

    uprn: str = Field(index=True)


class HubspotDealData(SQLModel, table=True):
    __tablename__ = "hubspot_deal_data"

    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)

    # HubSpot Deal identifiers
    deal_id: str = Field(index=True, nullable=False)
    dealname: Optional[str] = Field(default=None)
    dealstage: Optional[str] = Field(default=None)
    company_id: Optional[str] = Field(default=None)
    project_code: Optional[str] = Field(default=None)

    # HubSpot custom properties
    landlord_property_id: Optional[str] = Field(default=None)
    uprn: Optional[str] = Field(default=None)
    outcome: Optional[str] = Field(default=None)
    outcome_notes: Optional[str] = Field(default=None)

    major_condition_issue_description: Optional[str] = Field(default=None)
    major_condition_issue_photos: Optional[str] = Field(default=None)
    major_condition_issue_evidence_s3_url: Optional[str] = Field(default=None)

    coordination_status: Optional[str] = Field(default=None)
    design_status: Optional[str] = Field(default=None)

    created_at: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("NOW() AT TIME ZONE 'utc'"),
            nullable=False,
        )
    )

    updated_at: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("NOW() AT TIME ZONE 'utc'"),
            onupdate=func.now(),
            nullable=False,
        )
    )

class HubspotCommpanyData(SQLModel, table=True):
    __tablename__ = "hubspot_company_data"

    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)

    # HubSpot Deal identifiers
    company_id: str = Field(index=True, nullable=False)
    company_name: Optional[str] = Field(default=None)
    group_id: Optional[str] = Field(default=None)

    created_at: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("NOW() AT TIME ZONE 'utc'"),
            nullable=False,
        )
    )

    updated_at: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("NOW() AT TIME ZONE 'utc'"),
            onupdate=func.now(),
            nullable=False,
        )
    )