"""create_system_functions_table

Revision ID: a1b2c3d4e5f6
Revises: 5e95bf5ff0af
Create Date: 2026-02-23 10:40:00.000000

"""
from typing import Sequence, Union

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

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


def upgrade() -> None:
    # 建立 system_functions 資料表
    op.create_table(
        'system_functions',
        sa.Column('id', sa.Integer(), nullable=False, comment='資料編號'),
        sa.Column('code', sa.String(length=200), nullable=False, comment='系統功能代碼/功能英文名稱'),
        sa.Column('upper_function_id', sa.Integer(), nullable=False, server_default='0', comment='上層功能代碼 (0為初始層)'),
        sa.Column('name', sa.String(length=200), nullable=False, comment='系統功能中文名稱'),
        sa.Column('function_type', sa.Integer(), nullable=False, comment='系統功能類型 (1:node, 2:function)'),
        sa.Column('order', sa.Integer(), nullable=False, comment='系統功能次序'),
        sa.Column('function_icon', sa.String(length=200), nullable=False, server_default='', comment='功能圖示'),
        sa.Column('module_code', sa.String(length=200), nullable=True, comment='功能模組名稱 (function_type=2 必填)'),
        sa.Column('module_functions', postgresql.JSON(), nullable=False, server_default='[]', comment='模組項目 (View,Create,Read,Update,Delete,Print,File)'),
        sa.Column('description', sa.Text(), nullable=False, server_default='', comment='說明 (富文本格式)'),
        sa.Column('is_mana', sa.Boolean(), nullable=False, server_default='true', comment='系統管理'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true', comment='啟用'),
        sa.Column('edit_by', sa.Integer(), nullable=False, comment='資料建立者'),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.func.now(), comment='資料最新建立時間'),
        sa.Column('updated_at', sa.DateTime(), nullable=True, onupdate=sa.func.now(), comment='資料最新修改時間'),
        sa.PrimaryKeyConstraint('id')
    )

    # 建立索引
    op.create_index('ix_system_functions_code', 'system_functions', ['code'])
    op.create_index('ix_system_functions_upper_function_id', 'system_functions', ['upper_function_id'])
    op.create_index('ix_system_functions_function_type', 'system_functions', ['function_type'])
    op.create_index('ix_system_functions_is_active', 'system_functions', ['is_active'])

    # 建立自動編號序列 (從 10 開始)
    op.execute("""
        CREATE SEQUENCE system_functions_id_seq
        START WITH 10
        INCREMENT BY 1
        MINVALUE 10
        NO MAXVALUE
        CACHE 1;
    """)

    # 設定 id 欄位使用序列
    op.execute("""
        ALTER TABLE system_functions
        ALTER COLUMN id SET DEFAULT nextval('system_functions_id_seq');
    """)

    # 將序列所有權給予資料表
    op.execute("""
        ALTER SEQUENCE system_functions_id_seq
        OWNED BY system_functions.id;
    """)


def downgrade() -> None:
    op.drop_index('ix_system_functions_is_active', table_name='system_functions')
    op.drop_index('ix_system_functions_function_type', table_name='system_functions')
    op.drop_index('ix_system_functions_upper_function_id', table_name='system_functions')
    op.drop_index('ix_system_functions_code', table_name='system_functions')
    op.execute('DROP SEQUENCE IF EXISTS system_functions_id_seq CASCADE')
    op.drop_table('system_functions')
