"""
SQLAlchemy database models for storing invoice data, sync metadata, auth, and config.
"""
from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, Text, Index, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()

# Many-to-many: roles <-> permissions
role_permissions = Table(
    "role_permissions",
    Base.metadata,
    Column("role_id", Integer, ForeignKey("roles.id", ondelete="CASCADE"), primary_key=True),
    Column("permission_id", Integer, ForeignKey("permissions.id", ondelete="CASCADE"), primary_key=True),
)

# Many-to-many: users <-> permissions (direct assignment per user)
user_permissions = Table(
    "user_permissions",
    Base.metadata,
    Column("user_id", Integer, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True),
    Column("permission_id", Integer, ForeignKey("permissions.id", ondelete="CASCADE"), primary_key=True),
)


class Permission(Base):
    """Granular permission key for controlling access to features."""
    __tablename__ = "permissions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String(80), nullable=False, unique=True, index=True)  # e.g. admin.access, branch.branch1
    name = Column(String(120), nullable=False)  # Human-readable label
    category = Column(String(40), nullable=False)  # Admin, Branches, Departments, Directors

    user_set = relationship("User", secondary=user_permissions, back_populates="direct_permissions")

    def __repr__(self):
        return f"<Permission(key={self.key})>"


class Role(Base):
    """Role with optional granular permissions. System roles: user, admin, director (is_system=True, slug set)."""
    __tablename__ = "roles"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(60), nullable=False)  # Display name
    slug = Column(String(20), nullable=True, unique=True, index=True)  # user, admin, director for system roles
    is_system = Column(Boolean, default=False, nullable=False)  # True = cannot delete, built-in permission set
    created_at = Column(DateTime, default=datetime.utcnow)

    permissions = relationship("Permission", secondary=role_permissions, back_populates="roles")
    users = relationship("User", back_populates="role_obj")

    def __repr__(self):
        return f"<Role(id={self.id}, name={self.name}, slug={self.slug})>"


Permission.roles = relationship("Role", secondary=role_permissions, back_populates="permissions")


class User(Base):
    """Dashboard user. Permissions are assigned directly per user (user_permissions). Role is optional legacy/display."""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(80), nullable=False, unique=True, index=True)
    password_hash = Column(String(255), nullable=False)
    role = Column(String(20), nullable=False, default="user")  # Legacy/display: user, admin, director
    role_id = Column(Integer, ForeignKey("roles.id", ondelete="SET NULL"), nullable=True, index=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    # Invite flow: set for invited users until they set password
    invite_token = Column(String(64), nullable=True, unique=True, index=True)
    invite_expires_at = Column(DateTime, nullable=True)
    # Password reset flow: set when user requests forgot password
    reset_token = Column(String(64), nullable=True, unique=True, index=True)
    reset_expires_at = Column(DateTime, nullable=True)

    role_obj = relationship("Role", back_populates="users")
    direct_permissions = relationship("Permission", secondary=user_permissions, back_populates="user_set")
    sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")

    @property
    def is_pending_invite(self) -> bool:
        return self.invite_token is not None

    def __repr__(self):
        return f"<User(id={self.id}, username={self.username}, role={self.role})>"


class Session(Base):
    """User session for cookie-based auth. OTP re-verification every 7 days."""
    __tablename__ = "sessions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    token = Column(String(64), nullable=False, unique=True, index=True)
    expires_at = Column(DateTime, nullable=False)
    remember_me = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    # Email OTP re-verification: required every OTP_INTERVAL_DAYS (default 7)
    otp_verified_at = Column(DateTime, nullable=True)  # when user last passed OTP check
    otp_code_hash = Column(String(255), nullable=True)  # hashed 6-digit code
    otp_expires_at = Column(DateTime, nullable=True)

    user = relationship("User", back_populates="sessions")

    __table_args__ = (Index("idx_sessions_token", "token"),)


class RevenueTarget(Base):
    """Revenue target and breakeven per branch/company (replaces targets_breakevens in JSON)."""
    __tablename__ = "revenue_targets"

    id = Column(Integer, primary_key=True, autoincrement=True)
    branch_id = Column(String(50), nullable=False)
    company_id = Column(Integer, nullable=False)
    target = Column(Integer, nullable=True)
    breakeven = Column(Integer, nullable=True)

    __table_args__ = (Index("idx_revenue_targets_branch_company", "branch_id", "company_id", unique=True),)


class GPThreshold(Base):
    """GP color thresholds per department type (replaces gp_thresholds in JSON)."""
    __tablename__ = "gp_thresholds"

    id = Column(Integer, primary_key=True, autoincrement=True)
    department_type = Column(String(50), nullable=False, unique=True)
    green_min = Column(Float, nullable=False)
    yellow_min = Column(Float, nullable=False)
    yellow_max = Column(Float, nullable=False)
    red_max = Column(Float, nullable=False)

    __table_args__ = (Index("idx_gp_thresholds_dept", "department_type", unique=True),)


class KPITargetOverride(Base):
    """KPI target overrides per branch/department/metric (replaces kpi_targets in JSON)."""
    __tablename__ = "kpi_target_overrides"

    id = Column(Integer, primary_key=True, autoincrement=True)
    branch = Column(String(80), nullable=False)
    department = Column(String(80), nullable=False)
    metric_key = Column(String(80), nullable=False)
    target = Column(Float, nullable=True)
    weight = Column(Float, nullable=True)

    __table_args__ = (Index("idx_kpi_overrides_branch_dept_metric", "branch", "department", "metric_key", unique=True),)


class CustomPageLayout(Base):
    """Org-wide custom dashboard page: URL slug + optional display title + layout JSON (v2 grid)."""
    __tablename__ = "custom_page_layouts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    slug = Column(String(64), nullable=False, unique=True, index=True)
    title = Column(String(200), nullable=True)
    # If set, /page/{slug} layout API requires this permission key (in addition to login).
    view_permission_key = Column(String(80), nullable=True, index=True)
    layout_json = Column(Text, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def __repr__(self):
        return f"<CustomPageLayout(slug={self.slug})>"


class Invoice(Base):
    """Invoice data from simPRO API."""
    __tablename__ = "invoices"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    invoice_id = Column(Integer, nullable=False, index=True)
    branch_id = Column(String(50), nullable=False, index=True)
    company_id = Column(Integer, nullable=False, index=True)
    company_name = Column(String(100), nullable=False)
    date_issued = Column(DateTime, nullable=False, index=True)
    total_ex_tax = Column(Float, nullable=False)
    is_paid = Column(Boolean, default=False)
    customer = Column(Text, nullable=True)  # JSON string
    jobs = Column(Text, nullable=True)  # JSON string
    stage = Column(String(100), nullable=True)
    status = Column(String(100), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Composite index for common queries and unique constraint
    __table_args__ = (
        Index('idx_branch_company_date', 'branch_id', 'company_id', 'date_issued'),
        Index('idx_unique_invoice', 'branch_id', 'company_id', 'invoice_id', unique=True),
    )
    
    def __repr__(self):
        return f"<Invoice(id={self.invoice_id}, branch={self.branch_id}, company={self.company_name}, amount={self.total_ex_tax})>"


class SyncMetadata(Base):
    """Metadata about sync operations for incremental updates."""
    __tablename__ = "sync_metadata"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    branch_id = Column(String(50), nullable=False)
    company_id = Column(Integer, nullable=False)
    last_sync_date = Column(DateTime, nullable=True)  # Latest invoice date we've synced
    last_sync_timestamp = Column(DateTime, nullable=False)  # When the sync ran
    invoice_count = Column(Integer, default=0)  # Number of invoices for this branch/company
    
    # Unique constraint on branch_id + company_id
    __table_args__ = (
        Index('idx_branch_company_sync', 'branch_id', 'company_id', unique=True),
    )
    
    def __repr__(self):
        return f"<SyncMetadata(branch={self.branch_id}, company={self.company_id}, last_sync={self.last_sync_timestamp})>"