File size: 4,639 Bytes
cd9bca9 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
"""
資料庫初始化腳本
用於在 Supabase 中建立必要的資料表
"""
from supabase import create_client
from backend.config import settings
import logging
logger = logging.getLogger(__name__)
# SQL 建表語句
CREATE_TABLES_SQL = {
"users": """
CREATE TABLE IF NOT EXISTS users (
user_id VARCHAR(255) PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
display_name VARCHAR(255),
picture_url TEXT,
status_message TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
""",
"products": """
CREATE TABLE IF NOT EXISTS products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INTEGER DEFAULT 0,
category VARCHAR(100),
image_url TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
""",
"orders": """
CREATE TABLE IF NOT EXISTS orders (
order_id VARCHAR(255) PRIMARY KEY,
user_id VARCHAR(255) REFERENCES users(user_id),
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'pending',
shipping_address TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
""",
"order_items": """
CREATE TABLE IF NOT EXISTS order_items (
id SERIAL PRIMARY KEY,
order_id VARCHAR(255) REFERENCES orders(order_id),
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
""",
"line_messages": """
CREATE TABLE IF NOT EXISTS line_messages (
id SERIAL PRIMARY KEY,
user_id VARCHAR(255),
message TEXT NOT NULL,
message_type VARCHAR(50) DEFAULT 'text',
timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
processed BOOLEAN DEFAULT FALSE
);
""",
"user_sessions": """
CREATE TABLE IF NOT EXISTS user_sessions (
id SERIAL PRIMARY KEY,
user_id VARCHAR(255) REFERENCES users(user_id),
session_data JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
"""
}
# 範例資料
SAMPLE_DATA = {
"products": [
{
"name": "iPhone 15 Pro",
"description": "最新款 iPhone，配備 A17 Pro 晶片",
"price": 35900,
"stock": 50,
"category": "手機"
},
{
"name": "MacBook Air M2",
"description": "輕薄筆記型電腦，搭載 M2 晶片",
"price": 37900,
"stock": 30,
"category": "筆電"
},
{
"name": "AirPods Pro",
"description": "主動降噪無線耳機",
"price": 7490,
"stock": 100,
"category": "耳機"
}
]
}
def init_database():
"""初始化資料庫"""
try:
supabase = create_client(settings.SUPABASE_URL, settings.SUPABASE_KEY)
logger.info("開始初始化資料庫...")
# 建立資料表
for table_name, sql in CREATE_TABLES_SQL.items():
try:
logger.info(f"建立資料表: {table_name}")
# 注意：Supabase 的 Python 客戶端可能不直接支援 SQL 執行
# 您需要在 Supabase Dashboard 的 SQL Editor 中執行這些 SQL
print(f"\n=== {table_name.upper()} TABLE SQL ===")
print(sql)
except Exception as e:
logger.error(f"建立資料表 {table_name} 失敗: {str(e)}")
# 插入範例資料
logger.info("插入範例資料...")
for table_name, data in SAMPLE_DATA.items():
try:
result = supabase.table(table_name).insert(data).execute()
logger.info(f"成功插入 {len(result.data)} 筆資料到 {table_name}")
except Exception as e:
logger.warning(f"插入範例資料到 {table_name} 失敗: {str(e)}")
logger.info("資料庫初始化完成！")
return True
except Exception as e:
logger.error(f"資料庫初始化失敗: {str(e)}")
return False
if __name__ == "__main__":
init_database() |