from fastapi import FastAPI,Cookie, Depends, Query, status,File, UploadFile,Request,Response,HTTPException
from fastapi.templating import Jinja2Templates
from fastapi.responses import HTMLResponse, RedirectResponse, JSONResponse
from typing import List, Optional
from os.path import isfile, isdir, join
import threading
import os 
import requests
from bs4 import BeautifulSoup
import asyncio
import urllib.request
from fastapi.responses import FileResponse
from fastapi.middleware.cors import CORSMiddleware
import dataset
from datetime import datetime, timedelta
from fastapi.staticfiles import StaticFiles
import shutil
import io
import pymysql
pymysql.install_as_MySQLdb()

app = FastAPI()


app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

'''
資料表: agent_form
欄位:
cellphone (手機)
customer_note (備註)
'''

# 取得資料庫連線
def get_db_connection():
    # 測試機 DB
    # db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/stage?charset=utf8mb4')

    # 正式機 DB
    db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4')

    return db

# 檢查已登錄過的客戶手機號碼
@app.get("/check")
async def checkCellphone(cellphone: str = ''):
    # 呼叫傳入參數
    temp_list = cellphone.split("\n")

    # 手機號碼清單
    cellphone_list = []

    # 避免換行空白問題
    for t in temp_list:
        if t:
            cellphone_list.append(t.strip().replace("-", ""))
        
    cellphone_list = "','".join(cellphone_list)
   
    # 取得資料庫連線
    db = get_db_connection()

    # 取得手機號碼資料
    q = "SELECT * \
        FROM agent_form \
        WHERE REPLACE(cellphone, '-', '') IN ('" + cellphone_list + "')"

    count = len(list(db.query(q)))
    
    if count == 0: # 無手機號碼資料
        print('無手機號碼資料')
        return {"count": 0, "msg": "無手機號碼資料"}
    else:
        # 有手機號碼資料
        cellphone_list = []
        
        for r1 in db.query(q):
            print('手機號碼: ' + r1['cellphone'] + ' 資料已存在')
            cellphone_list.append(r1['cellphone'])
        
        return {"count": count, "cellphone": cellphone_list}