"""add system status table

Revision ID: ba655ef20407
Revises: ddbf7bb95812
Create Date: 2026-02-22 20:47:37.691492

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'ba655ef20407'
down_revision: Union[str, None] = 'ddbf7bb95812'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """建立系統狀態記錄表"""

    # installation_system_status (系統狀態記錄)
    # 用途：記錄系統當前所處的階段 (Initialization/Operational/Transition)
    op.create_table(
        'installation_system_status',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('current_phase', sa.String(20), nullable=False),  # initialization/operational/transition
        sa.Column('previous_phase', sa.String(20), nullable=True),
        sa.Column('phase_changed_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('phase_changed_by', sa.String(100), nullable=True),
        sa.Column('phase_change_reason', sa.Text(), nullable=True),

        # Initialization 階段資訊
        sa.Column('initialized_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('initialized_by', sa.String(100), nullable=True),
        sa.Column('initialization_completed', sa.Boolean(), server_default='false'),

        # Operational 階段資訊
        sa.Column('last_health_check_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('health_check_status', sa.String(20), nullable=True),  # healthy/degraded/unhealthy
        sa.Column('operational_since', sa.TIMESTAMP(), nullable=True),

        # Transition 階段資訊
        sa.Column('transition_started_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('transition_approved_by', sa.String(100), nullable=True),
        sa.Column('env_db_consistent', sa.Boolean(), nullable=True),
        sa.Column('consistency_checked_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('inconsistencies', sa.Text(), nullable=True),  # JSON 格式記錄不一致項目

        # 系統鎖定（防止誤操作）
        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('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')
    )

    op.create_index('idx_system_status_phase', 'installation_system_status', ['current_phase'])

    # 插入預設記錄（初始階段）
    op.execute("""
        INSERT INTO installation_system_status
        (current_phase, initialization_completed, is_locked)
        VALUES ('initialization', false, false)
    """)


def downgrade() -> None:
    """移除系統狀態記錄表"""

    op.drop_index('idx_system_status_phase', 'installation_system_status')
    op.drop_table('installation_system_status')
