"""
Installation System Models
初始化系統資料模型
"""
from datetime import datetime
from sqlalchemy import (
    Column, Integer, String, Boolean, Text, TIMESTAMP, ForeignKey, ARRAY
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import relationship
from app.db.base import Base


class InstallationSession(Base):
    """安裝會話"""
    __tablename__ = "installation_sessions"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    session_name = Column(String(200))
    environment = Column(String(20))  # development/testing/production

    # 狀態追蹤
    started_at = Column(TIMESTAMP, default=datetime.now)
    completed_at = Column(TIMESTAMP)
    status = Column(String(20), default='in_progress')  # in_progress/completed/failed/paused

    # 進度統計
    total_checklist_items = Column(Integer)
    passed_checklist_items = Column(Integer, default=0)
    failed_checklist_items = Column(Integer, default=0)
    total_steps = Column(Integer)
    completed_steps = Column(Integer, default=0)
    failed_steps = Column(Integer, default=0)

    executed_by = Column(String(100))

    # 存取控制
    is_locked = Column(Boolean, default=False)
    locked_at = Column(TIMESTAMP)
    locked_by = Column(String(100))
    lock_reason = Column(String(200))

    is_unlocked = Column(Boolean, default=False)
    unlocked_at = Column(TIMESTAMP)
    unlocked_by = Column(String(100))
    unlock_reason = Column(String(200))
    unlock_expires_at = Column(TIMESTAMP)

    last_viewed_at = Column(TIMESTAMP)
    last_viewed_by = Column(String(100))
    view_count = Column(Integer, default=0)

    created_at = Column(TIMESTAMP, default=datetime.now)

    # Relationships
    # 不定義 tenant relationship，因為沒有 FK constraint
    tenant_info = relationship("InstallationTenantInfo", back_populates="session", uselist=False)
    department_setups = relationship("InstallationDepartmentSetup", back_populates="session")
    temporary_passwords = relationship("TemporaryPassword", back_populates="session")
    access_logs = relationship("InstallationAccessLog", back_populates="session")
    checklist_results = relationship("InstallationChecklistResult", back_populates="session")
    installation_logs = relationship("InstallationLog", back_populates="session")


class InstallationChecklistItem(Base):
    """檢查項目定義（系統級）"""
    __tablename__ = "installation_checklist_items"

    id = Column(Integer, primary_key=True, index=True)
    category = Column(String(50), nullable=False)  # hardware/network/software/container/security
    item_code = Column(String(100), unique=True, nullable=False)
    item_name = Column(String(200), nullable=False)
    check_type = Column(String(50), nullable=False)  # command/api/config/manual
    check_command = Column(Text)  # 自動檢查命令
    expected_value = Column(Text)
    min_requirement = Column(Text)
    recommended_value = Column(Text)
    is_required = Column(Boolean, default=True)
    sequence_order = Column(Integer, nullable=False)
    description = Column(Text)
    created_at = Column(TIMESTAMP, default=datetime.now)

    # Relationships
    results = relationship("InstallationChecklistResult", back_populates="checklist_item")


class InstallationChecklistResult(Base):
    """檢查結果（租戶級）"""
    __tablename__ = "installation_checklist_results"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
    checklist_item_id = Column(Integer, ForeignKey("installation_checklist_items.id", ondelete="CASCADE"), nullable=False)
    status = Column(String(20), nullable=False)  # pass/fail/warning/pending/skip
    actual_value = Column(Text)
    checked_at = Column(TIMESTAMP)
    checked_by = Column(String(100))
    auto_checked = Column(Boolean, default=False)
    remarks = Column(Text)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

    # Relationships
    # 不定義 tenant relationship，因為沒有 FK constraint
    session = relationship("InstallationSession", back_populates="checklist_results")
    checklist_item = relationship("InstallationChecklistItem", back_populates="results")


class InstallationStep(Base):
    """安裝步驟定義（系統級）"""
    __tablename__ = "installation_steps"

    id = Column(Integer, primary_key=True, index=True)
    step_code = Column(String(50), unique=True, nullable=False)
    step_name = Column(String(200), nullable=False)
    phase = Column(String(20), nullable=False)  # phase1/phase2/...
    sequence_order = Column(Integer, nullable=False)
    description = Column(Text)
    execution_type = Column(String(50))  # auto/manual/script
    execution_script = Column(Text)
    depends_on_steps = Column(ARRAY(String))  # 依賴的步驟代碼
    is_required = Column(Boolean, default=True)
    created_at = Column(TIMESTAMP, default=datetime.now)

    # Relationships
    logs = relationship("InstallationLog", back_populates="step")


class InstallationLog(Base):
    """安裝執行記錄（租戶級）"""
    __tablename__ = "installation_logs"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    step_id = Column(Integer, ForeignKey("installation_steps.id", ondelete="CASCADE"), nullable=False)
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
    status = Column(String(20), nullable=False)  # pending/running/success/failed/skipped
    started_at = Column(TIMESTAMP)
    completed_at = Column(TIMESTAMP)
    executed_by = Column(String(100))
    execution_method = Column(String(50))  # manual/auto/api/script
    result_data = Column(JSONB)
    error_message = Column(Text)
    retry_count = Column(Integer, default=0)
    remarks = Column(Text)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

    # Relationships
    # 不定義 tenant relationship，因為沒有 FK constraint
    step = relationship("InstallationStep", back_populates="logs")
    session = relationship("InstallationSession", back_populates="installation_logs")


class InstallationTenantInfo(Base):
    """租戶初始化資訊"""
    __tablename__ = "installation_tenant_info"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True, unique=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))

    # 公司基本資訊
    company_name = Column(String(200))
    company_name_en = Column(String(200))
    tenant_code = Column(String(50))  # 租戶代碼 = Keycloak Realm
    tenant_prefix = Column(String(10))  # 員工編號前綴
    tax_id = Column(String(50))
    industry = Column(String(100))
    company_size = Column(String(20))  # small/medium/large

    # 聯絡資訊
    tel = Column(String(20))  # 公司電話（對應 tenants.tel）
    phone = Column(String(50))
    fax = Column(String(50))
    email = Column(String(200))
    website = Column(String(200))
    add = Column(Text)  # 公司地址（對應 tenants.add）
    address = Column(Text)
    address_en = Column(Text)

    # 郵件網域設定
    domain_set = Column(Integer, default=2)  # 1=組織網域, 2=部門網域
    domain = Column(String(100))  # 組織網域（domain_set=1 時使用）

    # 負責人資訊
    representative_name = Column(String(100))
    representative_title = Column(String(100))
    representative_email = Column(String(200))
    representative_phone = Column(String(50))

    # 系統管理員資訊
    admin_employee_id = Column(String(50))
    admin_username = Column(String(100))
    admin_legal_name = Column(String(100))
    admin_english_name = Column(String(100))
    admin_email = Column(String(200))
    admin_phone = Column(String(50))

    # 初始設定
    default_language = Column(String(10), default='zh-TW')
    timezone = Column(String(50), default='Asia/Taipei')
    date_format = Column(String(20), default='YYYY-MM-DD')
    currency = Column(String(10), default='TWD')

    # 狀態追蹤
    is_completed = Column(Boolean, default=False)
    completed_at = Column(TIMESTAMP)
    completed_by = Column(String(100))

    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

    # Relationships
    # 不定義 tenant relationship，因為沒有 FK constraint
    session = relationship("InstallationSession", back_populates="tenant_info")


class InstallationDepartmentSetup(Base):
    """部門架構設定"""
    __tablename__ = "installation_department_setup"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
    department_code = Column(String(50), nullable=False)
    department_name = Column(String(200), nullable=False)
    department_name_en = Column(String(200))
    email_domain = Column(String(100))
    parent_code = Column(String(50))
    depth = Column(Integer, default=0)
    manager_name = Column(String(100))
    is_created = Column(Boolean, default=False)
    created_at = Column(TIMESTAMP, default=datetime.now)

    # Relationships
    # 不定義 tenant relationship，因為沒有 FK constraint
    session = relationship("InstallationSession", back_populates="department_setups")


class TemporaryPassword(Base):
    """臨時密碼"""
    __tablename__ = "temporary_passwords"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 tenants 表可能不存在
    employee_id = Column(Integer, nullable=True)  # 不設外鍵，初始化時 employees 表可能不存在
    username = Column(String(100), nullable=False)
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))

    # 密碼資訊
    password_hash = Column(String(255), nullable=False)
    plain_password = Column(String(100))  # 明文密碼（僅初始化階段）
    password_method = Column(String(20))  # auto/manual
    is_temporary = Column(Boolean, default=True)
    must_change_on_login = Column(Boolean, default=True)

    # 有效期限
    created_at = Column(TIMESTAMP, default=datetime.now)
    expires_at = Column(TIMESTAMP)

    # 使用狀態
    is_used = Column(Boolean, default=False)
    used_at = Column(TIMESTAMP)
    first_login_at = Column(TIMESTAMP)
    password_changed_at = Column(TIMESTAMP)

    # 查看控制
    is_viewable = Column(Boolean, default=True)
    viewable_until = Column(TIMESTAMP)
    view_count = Column(Integer, default=0)
    last_viewed_at = Column(TIMESTAMP)
    first_viewed_at = Column(TIMESTAMP)

    # 明文密碼清除記錄
    plain_password_cleared_at = Column(TIMESTAMP)
    cleared_reason = Column(String(100))

    # Relationships
    # 不定義 tenant 和 employee relationship，因為沒有 FK constraint
    session = relationship("InstallationSession", back_populates="temporary_passwords")


class InstallationAccessLog(Base):
    """存取審計日誌"""
    __tablename__ = "installation_access_logs"

    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"), nullable=False)
    action = Column(String(50), nullable=False)  # lock/unlock/view/download_pdf
    action_by = Column(String(100))
    action_method = Column(String(50))  # database/api/system
    ip_address = Column(String(50))
    user_agent = Column(Text)
    access_granted = Column(Boolean)
    deny_reason = Column(String(200))
    sensitive_data_accessed = Column(ARRAY(String))
    created_at = Column(TIMESTAMP, default=datetime.now)

    # Relationships
    session = relationship("InstallationSession", back_populates="access_logs")


class InstallationEnvironmentConfig(Base):
    """環境配置記錄"""
    __tablename__ = "installation_environment_config"

    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))
    config_key = Column(String(100), unique=True, nullable=False, index=True)
    config_value = Column(Text)
    config_category = Column(String(50), nullable=False, index=True)  # redis/database/keycloak/mailserver/nextcloud/traefik
    is_sensitive = Column(Boolean, default=False)  # 是否為敏感資訊（密碼等）
    is_configured = Column(Boolean, default=False)
    configured_at = Column(TIMESTAMP)
    configured_by = Column(String(100))
    description = Column(Text)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

    # Relationships
    session = relationship("InstallationSession")


class InstallationSystemStatus(Base):
    """系統狀態記錄（三階段：Initialization/Operational/Transition）"""
    __tablename__ = "installation_system_status"

    id = Column(Integer, primary_key=True, index=True)
    current_phase = Column(String(20), nullable=False, index=True)  # initialization/operational/transition
    previous_phase = Column(String(20))
    phase_changed_at = Column(TIMESTAMP)
    phase_changed_by = Column(String(100))
    phase_change_reason = Column(Text)

    # Initialization 階段資訊
    initialized_at = Column(TIMESTAMP)
    initialized_by = Column(String(100))
    initialization_completed = Column(Boolean, default=False)

    # Operational 階段資訊
    last_health_check_at = Column(TIMESTAMP)
    health_check_status = Column(String(20))  # healthy/degraded/unhealthy
    operational_since = Column(TIMESTAMP)

    # Transition 階段資訊
    transition_started_at = Column(TIMESTAMP)
    transition_approved_by = Column(String(100))
    env_db_consistent = Column(Boolean)
    consistency_checked_at = Column(TIMESTAMP)
    inconsistencies = Column(Text)  # JSON 格式

    # 系統鎖定
    is_locked = Column(Boolean, default=False)
    locked_at = Column(TIMESTAMP)
    locked_by = Column(String(100))
    lock_reason = Column(String(200))

    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)