import mysql.connector
from mysql.connector import errorcode
from faker import Faker
import random
from datetime import date, timedelta

# --- 資料庫連線資訊 ---
DB_CONFIG = {
    'user': 'A023',
    'password': 'UkrHhx76kCCM',
    'host': 'mysql.theaken.com',
    'port': 33306,
    'database': 'db_A023',
}

# --- 資料表建立 SQL 指令 ---
TABLES = {}

TABLES['menu_items'] = (
    "CREATE TABLE `menu_items` ("
    "  `id` INT NOT NULL AUTO_INCREMENT,"
    "  `main_course` NVARCHAR(255) NOT NULL,"
    "  `side_dish` NVARCHAR(255),"
    "  `addon` NVARCHAR(255),"
    "  `menu_date` DATE NOT NULL,"
    "  PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")

TABLES['employee_order'] = (
    "CREATE TABLE `employee_order` ("
    "  `id` INT NOT NULL AUTO_INCREMENT,"
    "  `emp_id` NVARCHAR(50) NOT NULL,"
    "  `name` NVARCHAR(100) NOT NULL,"
    "  `order_date` DATE NOT NULL,"
    "  `menu_item_id` INT NOT NULL,"
    "  `order_qty` INT NOT NULL DEFAULT 1,"
    "  `update_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
    "  `update_by` NVARCHAR(50) NOT NULL,"
    "  PRIMARY KEY (`id`),"
    "  CONSTRAINT `fk_menu_item` FOREIGN KEY (`menu_item_id`)"
    "  REFERENCES `menu_items` (`id`)"
    "    ON DELETE CASCADE"
    "    ON UPDATE CASCADE"
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")

def insert_fake_data(cnx):
    """
    在資料表中新增 50 筆隨機假資料
    """
    cursor = cnx.cursor()
    fake = Faker('zh_TW')

    # --- 準備假資料 --- 
    # 菜色選項
    main_courses = ['排骨飯', '雞腿飯', '牛肉麵', '海鮮義大利麵', '日式豬排丼', '韓式拌飯', '總匯三明治', '烤雞潛艇堡']
    side_dishes = ['燙青菜', '滷豆腐', '涼拌小黃瓜', '茶碗蒸', '薯條', '和風沙拉']
    addons = ['玉米濃湯', '味噌湯', '紅茶', '綠茶', '提拉米蘇', '水果拼盤']

    # 1. 新增 50 筆菜單資料 (menu_items)
    menu_items_data = []
    start_date = date.today()
    for i in range(50):
        menu_items_data.append((
            random.choice(main_courses),
            random.choice(side_dishes),
            random.choice(addons),
            start_date + timedelta(days=i) # 讓菜單日期每天不一樣
        ))
    
    add_menu_item = ("INSERT INTO menu_items "
                     "(main_course, side_dish, addon, menu_date) "
                     "VALUES (%s, %s, %s, %s)")
    
    try:
        print("正在新增 50 筆菜單資料...", end='')
        cursor.executemany(add_menu_item, menu_items_data)
        cnx.commit() # 確認寫入
        print("OK")
    except mysql.connector.Error as err:
        print(f"失敗: {err}")
        cnx.rollback()

    # 取得剛剛新增的 50 筆菜單的 ID
    cursor.execute("SELECT id FROM menu_items ORDER BY id DESC LIMIT 50")
    menu_item_ids = [item[0] for item in cursor.fetchall()]
    menu_item_ids.reverse() # 確保 ID 順序正確

    # 2. 新增 50 筆訂單資料 (employee_order)
    employee_orders_data = []
    for i in range(50):
        emp_id = f'A{random.randint(100, 999)}'
        order_date = random.choice(menu_items_data)[3] # 從菜單日期中隨機選一天
        employee_orders_data.append((
            emp_id,
            fake.name(),
            order_date,
            random.choice(menu_item_ids), # 從已存在的菜單ID中隨機選一個
            random.randint(1, 3), # 訂購數量 1-3 份
            emp_id
        ))

    add_employee_order = ("INSERT INTO employee_order "
                          "(emp_id, name, order_date, menu_item_id, order_qty, update_by) "
                          "VALUES (%s, %s, %s, %s, %s, %s)")

    try:
        print("正在新增 50 筆訂單資料...", end='')
        cursor.executemany(add_employee_order, employee_orders_data)
        cnx.commit()
        print("OK")
    except mysql.connector.Error as err:
        print(f"失敗: {err}")
        cnx.rollback()

    cursor.close()
def create_tables(cnx):
    """
    建立資料表
    """
    cursor = cnx.cursor()
    for table_name in TABLES:
        table_description = TABLES[table_name]
        try:
            print(f"正在建立資料表 '{table_name}'... ", end='')
            cursor.execute(table_description)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("資料表已存在。")
            else:
                print(err.msg)
        else:
            print("OK")
    cursor.close()

def main():
    """
    主執行函式，連線資料庫、建立資料表並新增假資料
    """
    cnx = None # 初始化 cnx
    try:
        # 建立資料庫連線
        cnx = mysql.connector.connect(**DB_CONFIG)
        print("資料庫連線成功！")

        # 1. 建立資料表
        create_tables(cnx)

        # 2. 新增假資料
        # 詢問使用者是否要新增假資料
        user_input = input("是否要新增 50 筆假資料？ (y/n): ")
        if user_input.lower() == 'y':
            insert_fake_data(cnx)
        else:
            print("已跳過新增假資料的步驟。")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("存取被拒絕，請檢查您的使用者名稱或密碼。")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("資料庫不存在。")
        else:
            print(f"連線失敗：{err}")
    finally:
        # 關閉連線
        if cnx and cnx.is_connected():
            cnx.close()
            print("資料庫連線已關閉。")

if __name__ == '__main__':
    main()