#!/usr/bin/env python3
"""
初始化 MOPS 公告資料庫
"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
from pathlib import Path

def init_database():
    """建立並初始化資料庫"""
    db_dir = Path(__file__).parent
    db_path = db_dir / 'mops_announcements.db'
    schema_path = db_dir / 'schema.sql'

    # 讀取 schema
    with open(schema_path, 'r', encoding='utf-8') as f:
        schema_sql = f.read()

    # 建立資料庫
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    try:
        # 執行 schema
        cursor.executescript(schema_sql)
        conn.commit()
        print(f'[OK] 資料庫已建立: {db_path}')

        # 驗證
        cursor.execute('SELECT * FROM announcement_types')
        types = cursor.fetchall()
        print(f'\n公告類型參照表 ({len(types)} 筆):')
        print('-' * 80)
        for t in types:
            print(f'  {t[0]}. {t[1]:15} | {t[3]:15} | {t[4]:4} | {t[5] or "-":>3} 欄位')

        # 檢查視圖
        cursor.execute("SELECT name FROM sqlite_master WHERE type='view'")
        views = cursor.fetchall()
        print(f'\n已建立視圖 ({len(views)} 個):')
        for v in views:
            print(f'  - {v[0]}')

    except Exception as e:
        print(f'[ERROR] 初始化失敗: {e}')
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == '__main__':
    init_database()