"""
DataFlow Server — Database Models (SQLAlchemy + SQLite)
"""
from datetime import datetime
from sqlalchemy import (
    Column, String, Float, Integer, Boolean,
    DateTime, Text, ForeignKey, Enum as SAEnum
)
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(String, primary_key=True)
    full_name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    position = Column(String, nullable=True)
    role = Column(String, default="developer")  # manager, developer, observer
    two_factor_enabled = Column(Boolean, default=False)
    biometric_enabled = Column(Boolean, default=False)
    language = Column(String, default="ru")
    timezone = Column(String, default="Asia/Almaty")
    currency = Column(String, default="KZT")
    avatar_url = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    tasks = relationship("Task", back_populates="assignee", foreign_keys="Task.assignee_id")


class Project(Base):
    __tablename__ = "projects"

    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(Text, default="")
    start_date = Column(DateTime, nullable=False)
    end_date = Column(DateTime, nullable=False)
    budget = Column(Float, default=0.0)
    spent = Column(Float, default=0.0)
    status = Column(String, default="active")  # active, paused, completed, archived
    progress = Column(Float, default=0.0)
    risk_level = Column(String, default="green")  # green, yellow, red
    owner_id = Column(String, ForeignKey("users.id"), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    tasks = relationship("Task", back_populates="project", cascade="all, delete-orphan")
    expenses = relationship("Expense", back_populates="project", cascade="all, delete-orphan")
    documents = relationship("Document", back_populates="project", cascade="all, delete-orphan")
    members = relationship("ProjectMember", back_populates="project", cascade="all, delete-orphan")
    forecast = relationship("AIForecast", back_populates="project", uselist=False, cascade="all, delete-orphan")


class ProjectMember(Base):
    __tablename__ = "project_members"

    id = Column(String, primary_key=True)
    project_id = Column(String, ForeignKey("projects.id"), nullable=False)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    role = Column(String, default="developer")

    project = relationship("Project", back_populates="members")


class Task(Base):
    __tablename__ = "tasks"

    id = Column(String, primary_key=True)
    title = Column(String, nullable=False)
    description = Column(Text, default="")
    project_id = Column(String, ForeignKey("projects.id"), nullable=False)
    status = Column(String, default="todo")  # todo, inProgress, review, done
    priority = Column(String, default="medium")  # low, medium, high, critical
    assignee_id = Column(String, ForeignKey("users.id"), nullable=True)
    assignee_name = Column(String, nullable=True)
    deadline = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    tags = Column(Text, default="")  # comma-separated

    project = relationship("Project", back_populates="tasks")
    assignee = relationship("User", back_populates="tasks", foreign_keys=[assignee_id])
    subtasks = relationship("SubTask", back_populates="task", cascade="all, delete-orphan")
    comments = relationship("TaskComment", back_populates="task", cascade="all, delete-orphan")


class SubTask(Base):
    __tablename__ = "subtasks"

    id = Column(String, primary_key=True)
    task_id = Column(String, ForeignKey("tasks.id"), nullable=False)
    title = Column(String, nullable=False)
    completed = Column(Boolean, default=False)

    task = relationship("Task", back_populates="subtasks")


class TaskComment(Base):
    __tablename__ = "task_comments"

    id = Column(String, primary_key=True)
    task_id = Column(String, ForeignKey("tasks.id"), nullable=False)
    author_name = Column(String, nullable=False)
    text = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    task = relationship("Task", back_populates="comments")


class Expense(Base):
    __tablename__ = "expenses"

    id = Column(String, primary_key=True)
    project_id = Column(String, ForeignKey("projects.id"), nullable=False)
    title = Column(String, nullable=False)
    amount = Column(Float, nullable=False)
    category = Column(String, nullable=False)  # personnel, infrastructure, software, ...
    date = Column(DateTime, default=datetime.utcnow)
    status = Column(String, default="pending")  # pending, approved, rejected
    receipt_url = Column(String, nullable=True)
    description = Column(Text, nullable=True)
    approved_by = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    project = relationship("Project", back_populates="expenses")


class Document(Base):
    __tablename__ = "documents"

    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    project_id = Column(String, ForeignKey("projects.id"), nullable=False)
    type = Column(String, nullable=False)  # pdf, docx, xlsx, image
    size_bytes = Column(Integer, default=0)
    file_path = Column(String, nullable=True)
    uploaded_by = Column(String, nullable=True)
    is_favorite = Column(Boolean, default=False)
    # AI OCR fields
    extracted_text = Column(Text, nullable=True)
    recognition_confidence = Column(Float, nullable=True)
    detected_type = Column(String, nullable=True)  # invoice, receipt, contract, spec
    extracted_amount = Column(Float, nullable=True)
    extracted_date = Column(DateTime, nullable=True)
    extracted_supplier = Column(String, nullable=True)
    uploaded_at = Column(DateTime, default=datetime.utcnow)

    project = relationship("Project", back_populates="documents")


class AIForecast(Base):
    __tablename__ = "ai_forecasts"

    id = Column(String, primary_key=True)
    project_id = Column(String, ForeignKey("projects.id"), unique=True, nullable=False)
    current_spent = Column(Float, default=0.0)
    planned_budget = Column(Float, default=0.0)
    risk_level = Column(String, default="green")
    overrun_probability = Column(Float, default=0.0)
    optimistic_forecast = Column(Float, default=0.0)
    realistic_forecast = Column(Float, default=0.0)
    pessimistic_forecast = Column(Float, default=0.0)
    recommendations = Column(Text, default="")  # JSON list stored as text
    generated_at = Column(DateTime, default=datetime.utcnow)

    project = relationship("Project", back_populates="forecast")


class Notification(Base):
    __tablename__ = "notifications"

    id = Column(String, primary_key=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=True)
    title = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    type = Column(String, nullable=False)  # task, expense, project, ai, document
    is_read = Column(Boolean, default=False)
    action_route = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)