"""add installation system tables

Revision ID: 0014
Revises: 0013
Create Date: 2026-02-22 16:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '0014'
down_revision = '0013'
branch_labels = None
depends_on = None


def upgrade():
    """建立初始化系統相關資料表"""

    # 1. installation_sessions (安裝會話)
    # 注意：tenant_id 不設外鍵，因為初始化時 tenants 表可能還不存在
    op.create_table(
        'installation_sessions',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=True),  # 允許 NULL，初始化時可能還沒有 tenant
        sa.Column('session_name', sa.String(200), nullable=True),
        sa.Column('environment', sa.String(20), nullable=True),

        # 狀態追蹤
        sa.Column('started_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('completed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('status', sa.String(20), server_default='in_progress'),

        # 進度統計
        sa.Column('total_checklist_items', sa.Integer(), nullable=True),
        sa.Column('passed_checklist_items', sa.Integer(), server_default='0'),
        sa.Column('failed_checklist_items', sa.Integer(), server_default='0'),
        sa.Column('total_steps', sa.Integer(), nullable=True),
        sa.Column('completed_steps', sa.Integer(), server_default='0'),
        sa.Column('failed_steps', sa.Integer(), server_default='0'),

        sa.Column('executed_by', sa.String(100), nullable=True),

        # 存取控制
        sa.Column('is_locked', sa.Boolean(), server_default='false'),
        sa.Column('locked_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('locked_by', sa.String(100), nullable=True),
        sa.Column('lock_reason', sa.String(200), nullable=True),

        sa.Column('is_unlocked', sa.Boolean(), server_default='false'),
        sa.Column('unlocked_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('unlocked_by', sa.String(100), nullable=True),
        sa.Column('unlock_reason', sa.String(200), nullable=True),
        sa.Column('unlock_expires_at', sa.TIMESTAMP(), nullable=True),

        sa.Column('last_viewed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('last_viewed_by', sa.String(100), nullable=True),
        sa.Column('view_count', sa.Integer(), server_default='0'),

        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id')
        # 移除外鍵約束，因為初始化時 tenants 表可能還不存在
    )
    op.create_index('idx_installation_sessions_tenant', 'installation_sessions', ['tenant_id'])
    op.create_index('idx_installation_sessions_status', 'installation_sessions', ['status'])

    # 2. installation_checklist_items (檢查項目定義)
    op.create_table(
        'installation_checklist_items',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('category', sa.String(50), nullable=False),
        sa.Column('item_code', sa.String(100), unique=True, nullable=False),
        sa.Column('item_name', sa.String(200), nullable=False),
        sa.Column('check_type', sa.String(50), nullable=False),
        sa.Column('check_command', sa.Text(), nullable=True),
        sa.Column('expected_value', sa.Text(), nullable=True),
        sa.Column('min_requirement', sa.Text(), nullable=True),
        sa.Column('recommended_value', sa.Text(), nullable=True),
        sa.Column('is_required', sa.Boolean(), server_default='true'),
        sa.Column('sequence_order', sa.Integer(), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_checklist_items_category', 'installation_checklist_items', ['category'])
    op.create_index('idx_checklist_items_order', 'installation_checklist_items', ['sequence_order'])

    # 3. installation_checklist_results (檢查結果)
    # 注意：tenant_id 不設外鍵，因為初始化時 tenants 表可能還不存在
    op.create_table(
        'installation_checklist_results',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=True),  # 允許 NULL
        sa.Column('session_id', sa.Integer(), nullable=True),
        sa.Column('checklist_item_id', sa.Integer(), nullable=False),
        sa.Column('status', sa.String(20), nullable=False),
        sa.Column('actual_value', sa.Text(), nullable=True),
        sa.Column('checked_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('checked_by', sa.String(100), nullable=True),
        sa.Column('auto_checked', sa.Boolean(), server_default='false'),
        sa.Column('remarks', sa.Text(), nullable=True),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id'),
        # 移除 tenant_id 外鍵約束
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['checklist_item_id'], ['installation_checklist_items.id'], ondelete='CASCADE')
    )
    op.create_index('idx_checklist_results_session', 'installation_checklist_results', ['session_id'])
    op.create_index('idx_checklist_results_status', 'installation_checklist_results', ['status'])

    # 4. installation_steps (安裝步驟定義)
    op.create_table(
        'installation_steps',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('step_code', sa.String(50), unique=True, nullable=False),
        sa.Column('step_name', sa.String(200), nullable=False),
        sa.Column('phase', sa.String(20), nullable=False),
        sa.Column('sequence_order', sa.Integer(), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('execution_type', sa.String(50), nullable=True),
        sa.Column('execution_script', sa.Text(), nullable=True),
        sa.Column('depends_on_steps', postgresql.ARRAY(sa.String()), nullable=True),
        sa.Column('is_required', sa.Boolean(), server_default='true'),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_installation_steps_phase', 'installation_steps', ['phase'])
    op.create_index('idx_installation_steps_order', 'installation_steps', ['sequence_order'])

    # 5. installation_logs (安裝執行記錄)
    # 注意：tenant_id 不設外鍵，因為初始化時 tenants 表可能還不存在
    op.create_table(
        'installation_logs',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=True),  # 允許 NULL
        sa.Column('step_id', sa.Integer(), nullable=False),
        sa.Column('session_id', sa.Integer(), nullable=True),
        sa.Column('status', sa.String(20), nullable=False),
        sa.Column('started_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('completed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('executed_by', sa.String(100), nullable=True),
        sa.Column('execution_method', sa.String(50), nullable=True),
        sa.Column('result_data', postgresql.JSONB(), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('retry_count', sa.Integer(), server_default='0'),
        sa.Column('remarks', sa.Text(), nullable=True),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id'),
        # 移除 tenant_id 外鍵約束
        sa.ForeignKeyConstraint(['step_id'], ['installation_steps.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='CASCADE')
    )
    op.create_index('idx_installation_logs_session', 'installation_logs', ['session_id'])
    op.create_index('idx_installation_logs_status', 'installation_logs', ['status'])

    # 6. installation_tenant_info (租戶初始化資訊)
    # 注意：tenant_id 不設外鍵，因為初始化時 tenants 表可能還不存在
    op.create_table(
        'installation_tenant_info',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), unique=True, nullable=True),  # 允許 NULL
        sa.Column('session_id', sa.Integer(), nullable=True),

        # 公司基本資訊
        sa.Column('company_name', sa.String(200), nullable=True),
        sa.Column('company_name_en', sa.String(200), nullable=True),
        sa.Column('tax_id', sa.String(50), nullable=True),
        sa.Column('industry', sa.String(100), nullable=True),
        sa.Column('company_size', sa.String(20), nullable=True),

        # 聯絡資訊
        sa.Column('phone', sa.String(50), nullable=True),
        sa.Column('fax', sa.String(50), nullable=True),
        sa.Column('email', sa.String(200), nullable=True),
        sa.Column('website', sa.String(200), nullable=True),
        sa.Column('address', sa.Text(), nullable=True),
        sa.Column('address_en', sa.Text(), nullable=True),

        # 負責人資訊
        sa.Column('representative_name', sa.String(100), nullable=True),
        sa.Column('representative_title', sa.String(100), nullable=True),
        sa.Column('representative_email', sa.String(200), nullable=True),
        sa.Column('representative_phone', sa.String(50), nullable=True),

        # 系統管理員資訊
        sa.Column('admin_employee_id', sa.String(50), nullable=True),
        sa.Column('admin_username', sa.String(100), nullable=True),
        sa.Column('admin_legal_name', sa.String(100), nullable=True),
        sa.Column('admin_english_name', sa.String(100), nullable=True),
        sa.Column('admin_email', sa.String(200), nullable=True),
        sa.Column('admin_phone', sa.String(50), nullable=True),

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

        # 狀態追蹤
        sa.Column('is_completed', sa.Boolean(), server_default='false'),
        sa.Column('completed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('completed_by', sa.String(100), nullable=True),

        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id'),
        # 移除 tenant_id 外鍵約束
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='SET NULL')
    )

    # 7. installation_department_setup (部門架構設定)
    # 注意：tenant_id 不設外鍵，因為初始化時 tenants 表可能還不存在
    op.create_table(
        'installation_department_setup',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=True),  # 允許 NULL
        sa.Column('session_id', sa.Integer(), nullable=True),
        sa.Column('department_code', sa.String(50), nullable=False),
        sa.Column('department_name', sa.String(200), nullable=False),
        sa.Column('department_name_en', sa.String(200), nullable=True),
        sa.Column('email_domain', sa.String(100), nullable=True),
        sa.Column('parent_code', sa.String(50), nullable=True),
        sa.Column('depth', sa.Integer(), server_default='0'),
        sa.Column('manager_name', sa.String(100), nullable=True),
        sa.Column('is_created', sa.Boolean(), server_default='false'),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id'),
        # 移除 tenant_id 外鍵約束
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='CASCADE')
    )
    op.create_index('idx_dept_setup_session', 'installation_department_setup', ['session_id'])

    # 8. temporary_passwords (臨時密碼)
    # 注意：tenant_id 和 employee_id 不設外鍵，因為初始化時這些表可能還不存在
    op.create_table(
        'temporary_passwords',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=True),  # 允許 NULL
        sa.Column('employee_id', sa.Integer(), nullable=True),  # 允許 NULL，不設外鍵
        sa.Column('username', sa.String(100), nullable=False),
        sa.Column('session_id', sa.Integer(), nullable=True),

        # 密碼資訊
        sa.Column('password_hash', sa.String(255), nullable=False),
        sa.Column('plain_password', sa.String(100), nullable=True),
        sa.Column('password_method', sa.String(20), nullable=True),
        sa.Column('is_temporary', sa.Boolean(), server_default='true'),
        sa.Column('must_change_on_login', sa.Boolean(), server_default='true'),

        # 有效期限
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('expires_at', sa.TIMESTAMP(), nullable=True),

        # 使用狀態
        sa.Column('is_used', sa.Boolean(), server_default='false'),
        sa.Column('used_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('first_login_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('password_changed_at', sa.TIMESTAMP(), nullable=True),

        # 查看控制
        sa.Column('is_viewable', sa.Boolean(), server_default='true'),
        sa.Column('viewable_until', sa.TIMESTAMP(), nullable=True),
        sa.Column('view_count', sa.Integer(), server_default='0'),
        sa.Column('last_viewed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('first_viewed_at', sa.TIMESTAMP(), nullable=True),

        # 明文密碼清除記錄
        sa.Column('plain_password_cleared_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('cleared_reason', sa.String(100), nullable=True),

        sa.PrimaryKeyConstraint('id'),
        # 移除 tenant_id 和 employee_id 的外鍵約束
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='SET NULL')
    )
    op.create_index('idx_temp_pwd_username', 'temporary_passwords', ['username'])
    op.create_index('idx_temp_pwd_session', 'temporary_passwords', ['session_id'])

    # 9. installation_access_logs (存取審計日誌)
    op.create_table(
        'installation_access_logs',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('session_id', sa.Integer(), nullable=False),
        sa.Column('action', sa.String(50), nullable=False),
        sa.Column('action_by', sa.String(100), nullable=True),
        sa.Column('action_method', sa.String(50), nullable=True),
        sa.Column('ip_address', sa.String(50), nullable=True),
        sa.Column('user_agent', sa.Text(), nullable=True),
        sa.Column('access_granted', sa.Boolean(), nullable=True),
        sa.Column('deny_reason', sa.String(200), nullable=True),
        sa.Column('sensitive_data_accessed', postgresql.ARRAY(sa.String()), nullable=True),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP')),

        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['session_id'], ['installation_sessions.id'], ondelete='CASCADE')
    )
    op.create_index('idx_access_logs_session', 'installation_access_logs', ['session_id'])
    op.create_index('idx_access_logs_action', 'installation_access_logs', ['action'])
    op.create_index('idx_access_logs_created', 'installation_access_logs', ['created_at'])


def downgrade():
    """移除初始化系統相關資料表"""

    op.drop_index('idx_access_logs_created', 'installation_access_logs')
    op.drop_index('idx_access_logs_action', 'installation_access_logs')
    op.drop_index('idx_access_logs_session', 'installation_access_logs')
    op.drop_table('installation_access_logs')

    op.drop_index('idx_temp_pwd_session', 'temporary_passwords')
    op.drop_index('idx_temp_pwd_username', 'temporary_passwords')
    op.drop_table('temporary_passwords')

    op.drop_index('idx_dept_setup_session', 'installation_department_setup')
    op.drop_table('installation_department_setup')

    op.drop_table('installation_tenant_info')

    op.drop_index('idx_installation_logs_status', 'installation_logs')
    op.drop_index('idx_installation_logs_session', 'installation_logs')
    op.drop_table('installation_logs')

    op.drop_index('idx_installation_steps_order', 'installation_steps')
    op.drop_index('idx_installation_steps_phase', 'installation_steps')
    op.drop_table('installation_steps')

    op.drop_index('idx_checklist_results_status', 'installation_checklist_results')
    op.drop_index('idx_checklist_results_session', 'installation_checklist_results')
    op.drop_table('installation_checklist_results')

    op.drop_index('idx_checklist_items_order', 'installation_checklist_items')
    op.drop_index('idx_checklist_items_category', 'installation_checklist_items')
    op.drop_table('installation_checklist_items')

    op.drop_index('idx_installation_sessions_status', 'installation_sessions')
    op.drop_index('idx_installation_sessions_tenant', 'installation_sessions')
    op.drop_table('installation_sessions')
