"""
建立 tenant_role_rights 表
"""
import psycopg2

conn = psycopg2.connect(
    host="10.1.0.20",
    port=5433,
    database="hr_portal",
    user="admin",
    password="DC1qaz2wsx"
)
conn.autocommit = False
cur = conn.cursor()

try:
    print("Creating tenant_role_rights table...")

    cur.execute("""
        CREATE TABLE IF NOT EXISTS tenant_role_rights (
            id SERIAL PRIMARY KEY,
            role_id INTEGER NOT NULL REFERENCES tenant_user_roles(id) ON DELETE CASCADE,
            function_id INTEGER NOT NULL REFERENCES system_functions_cache(id) ON DELETE CASCADE,
            can_read BOOLEAN NOT NULL DEFAULT FALSE,
            can_create BOOLEAN NOT NULL DEFAULT FALSE,
            can_update BOOLEAN NOT NULL DEFAULT FALSE,
            can_delete BOOLEAN NOT NULL DEFAULT FALSE,
            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            edit_by VARCHAR(100),
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT uq_role_function UNIQUE (role_id, function_id)
        );
    """)

    # 建立索引
    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_role_rights_role
        ON tenant_role_rights(role_id);
    """)

    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_role_rights_function
        ON tenant_role_rights(function_id);
    """)

    conn.commit()
    print("SUCCESS: tenant_role_rights table created")

    # 驗證
    cur.execute("""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_name = 'tenant_role_rights';
    """)
    result = cur.fetchone()
    if result:
        print(f"Verified: {result[0]} table exists")

        # 查看表結構
        cur.execute("""
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_name = 'tenant_role_rights'
            ORDER BY ordinal_position;
        """)
        columns = cur.fetchall()
        print("\nTable structure:")
        for col in columns:
            print(f"  {col[0]:<20} {col[1]}")
    else:
        print("ERROR: Table not found after creation")

except Exception as e:
    conn.rollback()
    print(f"ERROR: {e}")
    import traceback
    traceback.print_exc()
finally:
    cur.close()
    conn.close()