app.py

import gradio as gr
import sqlite3
import json
from huggingface_hub import hf_hub_download, hf_hub_upload
import os
from pathlib import Path
from reportlab.lib.pagesizes import A4
from reportlab.lib.units import cm
from reportlab.pdfgen import canvas
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
import qrcode
import zipfile
import io
from dotenv import load_dotenv
import time

# อ่าน .env (ถ้ามี)
load_dotenv()
REPO_ID = os.getenv("REPO_ID", "your-username/worker-management")
ORIGINAL_REPO_ID = os.getenv("ORIGINAL_REPO_ID", None)

# ตั้งค่าฟอนต์
try:
    pdfmetrics.registerFont(TTFont('THSarabun', 'THSarabunNew.ttf'))
    pdfmetrics.registerFont(TTFont('THSarabunBold', 'THSarabunNew-Bold.ttf'))
except Exception as e:
    print(f"Warning: Could not load fonts: {e}")
    # ใช้ฟอนต์เริ่มต้นแทน
    pass

# ฟังก์ชันแปลงวันที่เป็นภาษาไทย
def thai_date_time(timestamp):
    months = {
        1: "มกราคม", 2: "กุมภาพันธ์", 3: "มีนาคม", 4: "เมษายน",
        5: "พฤษภาคม", 6: "มิถุนายน", 7: "กรกฎาคม", 8: "สิงหาคม",
        9: "กันยายน", 10: "ตุลาคม", 11: "พฤศจิกายน", 12: "ธันวาคม"
    }
    dt = time.localtime(timestamp)
    day = dt.tm_mday
    month = months[dt.tm_mon]
    year = dt.tm_year + 543
    hour = dt.tm_hour
    minute = dt.tm_min
    period = "น"
    return f"{day:02d} {month} {year} {hour:02d}:{minute:02d} {period}"

# เริ่มต้นฐานข้อมูล
def init_db():
    conn = sqlite3.connect("workers.db")
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS workers (
        request_number TEXT PRIMARY KEY,
        english_name TEXT,
        foreign_reference_number TEXT,
        id_number TEXT,
        nationality TEXT,
        receipt_number TEXT,
        payment_number TEXT
    )''')
    c.execute('''CREATE TABLE IF NOT EXISTS attachments (
        request_number TEXT,
        file_url TEXT,
        FOREIGN KEY(request_number) REFERENCES workers(request_number)
    )''')
    c.execute('''CREATE TABLE IF NOT EXISTS pdfs (
        request_number TEXT,
        pdf_url TEXT,
        FOREIGN KEY(request_number) REFERENCES workers(request_number)
    )''')
    conn.commit()
    conn.close()

# นำเข้า JSON
def import_json_to_db():
    try:
        json_path = hf_hub_download(repo_id=REPO_ID, filename="worker-database.json", repo_type="space")
        with open(json_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
            
        conn = sqlite3.connect("workers.db")
        c = conn.cursor()
        for record in data:
            c.execute('''INSERT OR IGNORE INTO workers (
                request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number
            ) VALUES (?, ?, ?, ?, ?, ?, ?)''', (
                record['เลขคำขอ'],
                record['ชื่อภาษาอังกฤษ'],
                record['หมายเลขอ้างอิงคนต่างด้าว'],
                record['หมายเลขประจำตัว'],
                record['สัญชาติ'],
                record['เลขที่บนขวาใบเสร็จ'],
                record['หมายเลขชำระเงิน']
            ))
        conn.commit()
        conn.close()
    except Exception as e:
        print(f"Error importing JSON: {e}")

# สำรองฐานข้อมูล
def backup_db():
    try:
        hf_hub_upload(repo_id=REPO_ID, path_in_repo="workers.db", path_or_fileobj="workers.db", repo_type="space")
    except Exception as e:
        print(f"Error backing up database: {e}")

# ดึงเลขคำขอถัดไป
def get_next_request_number():
    conn = sqlite3.connect("workers.db")
    c = conn.cursor()
    c.execute("SELECT request_number FROM workers")
    request_numbers = c.fetchall()
    conn.close()
    
    if not request_numbers:
        return "WP-68-366-150"
    
    max_num = max([int(num[0].split('-')[-1]) for num in request_numbers])
    return f"WP-68-366-{max_num + 1:03d}"

# สร้าง PDF
def create_receipt_pdf(request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number):
    filename = f"pdfs/receipt_{request_number}.pdf"
    local_path = f"temp_{request_number}.pdf"
    c = canvas.Canvas(local_path, pagesize=A4)
    
    # ใช้ฟอนต์ที่มี หรือ Helvetica ถ้าไม่มี
    try:
        c.setFont("THSarabunBold", 18)
    except:
        c.setFont("Helvetica-Bold", 18)
    
    c.drawString(2*cm, 28*cm, "Ministry of Labour")
    
    try:
        c.setFont("THSarabun", 12)
    except:
        c.setFont("Helvetica", 12)
        
    c.drawString(2*cm, 27.5*cm, "Receipt (Original)")
    c.drawString(2*cm, 26*cm, f"Number: {receipt_number}")
    c.drawString(2*cm, 25.5*cm, "Office: Bureau of Foreign Workers Administration")
    c.drawString(2*cm, 25*cm, "Date: 01 April 2025")
    c.drawString(2*cm, 24.5*cm, f"Payment Number: {payment_number}")
    c.drawString(2*cm, 24*cm, f"Request Number: {request_number}")
    c.drawString(2*cm, 23.5*cm, f"Payer Name: {english_name}")
    c.drawString(2*cm, 23*cm, f"Nationality: {nationality}")
    c.drawString(2*cm, 22.5*cm, f"Foreign Reference Number: {foreign_reference_number}")
    c.drawString(2*cm, 22*cm, f"Foreign Worker ID: {id_number}")
    c.drawString(2*cm, 21.5*cm, "Employer: Ban Kong Engineering Co., Ltd.")
    c.drawString(2*cm, 21*cm, "Employer ID: 0415567000061")
    
    try:
        c.setFont("THSarabunBold", 12)
    except:
        c.setFont("Helvetica-Bold", 12)
        
    c.drawString(2*cm, 20*cm, "Items")
    
    try:
        c.setFont("THSarabun", 12)
    except:
        c.setFont("Helvetica", 12)
        
    c.drawString(2*cm, 19.5*cm, "1. Application fee 100 Baht each: 100.00")
    c.drawString(2*cm, 19*cm, "2. Service fee: 900.00")
    c.drawString(2*cm, 18.5*cm, "Total amount (Baht): (One thousand Baht) 1,000.00")
    c.drawString(2*cm, 18*cm, "Payment received correctly")
    c.drawString(2*cm, 17.5*cm, "(Signature) Ms. Areewan Pho Nim Daeng (Receiver)")
    c.drawString(2*cm, 17*cm, "Position: Senior Labor Academic")
    
    # QR Code
    pdf_url = f"https://huggingface.co/spaces/{REPO_ID}/raw/main/{filename}"
    try:
        qr = qrcode.make(pdf_url)
        qr.save("temp_qr.png")
        c.drawImage("temp_qr.png", 15*cm, 15*cm, width=3*cm, height=3*cm)
    except Exception as e:
        print(f"Error creating QR code: {e}")
    
    # เพิ่ม print timestamp ที่ด้านล่าง
    try:
        c.setFont("THSarabun", 8)
    except:
        c.setFont("Helvetica", 8)
        
    print_time = thai_date_time(time.time())
    c.drawString(2*cm, 2*cm, f"Printed: {print_time}")
    
    c.showPage()
    c.save()
    
    # อัพโหลด PDF ไปยัง Space ใหม่
    try:
        hf_hub_upload(repo_id=REPO_ID, path_in_repo=filename, path_or_fileobj=local_path, repo_type="space")
    except Exception as e:
        print(f"Error uploading PDF to Space: {e}")
    
    # อัพโหลดไปยัง Space เดิม (ถ้ามี)
    if ORIGINAL_REPO_ID:
        try:
            hf_hub_upload(repo_id=ORIGINAL_REPO_ID, path_in_repo=filename, path_or_fileobj=local_path, repo_type="space")
        except Exception as e:
            print(f"Error uploading to original Space: {e}")
    
    # บันทึกข้อมูล PDF
    conn = sqlite3.connect("workers.db")
    c = conn.cursor()
    c.execute("INSERT OR REPLACE INTO pdfs (request_number, pdf_url) VALUES (?, ?)",
              (request_number, pdf_url))
    conn.commit()
    conn.close()
    
    return filename

# ตรวจสอบข้อมูล
def validate_input(english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number):
    if not id_number or not id_number.isdigit() or len(id_number) != 13:
        return "หมายเลขประจำตัวต้องเป็นตัวเลข 13 หลัก"
    if not receipt_number or not payment_number:
        return "กรุณากรอกเลขที่ใบเสร็จและหมายเลขชำระเงิน"
    return None

# เพิ่มข้อมูลพนักงาน
def add_worker(english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number, attachments):
    error = validate_input(english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number)
    if error:
        return error
    
    request_number = get_next_request_number()
    
    # บันทึกข้อมูล
    conn = sqlite3.connect("workers.db")
    c = conn.cursor()
    c.execute('''INSERT INTO workers (
        request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number
    ) VALUES (?, ?, ?, ?, ?, ?, ?)''', (
        request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number
    ))
    
    # จัดการไฟล์แนบ
    attachment_urls = []
    for i, file_data in enumerate(attachments or []):
        filename = f"attachments/file_{request_number}_{i}_{os.path.basename(file_data.name)}"
        try:
            hf_hub_upload(repo_id=REPO_ID, path_in_repo=filename, path_or_fileobj=file_data, repo_type="space")
            url = f"https://huggingface.co/spaces/{REPO_ID}/raw/main/{filename}"
            c.execute("INSERT INTO attachments (request_number, file_url) VALUES (?, ?)", (request_number, url))
            attachment_urls.append(url)
        except Exception as e:
            print(f"Error uploading attachment: {e}")
    
    conn.commit()
    conn.close()
    
    # สร้าง PDF
    pdf_path = create_receipt_pdf(request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number)
    
    # สำรองฐานข้อมูล
    backup_db()
    
    return f"เพิ่มข้อมูลพนักงานสำเร็จ! เลขคำขอ: {request_number}\nPDF: {pdf_path}\nไฟล์แนบ: {', '.join(attachment_urls) if attachment_urls else 'ไม่มีไฟล์แนบ'}"

# ดาวน์โหลด PDF ทั้งหมด
def download_all_pdfs():
    conn = sqlite3.connect("workers.db")
    c = conn.cursor()
    c.execute("SELECT request_number, pdf_url FROM pdfs")
    pdfs = c.fetchall()
    conn.close()
    
    zip_buffer = io.BytesIO()
    with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
        for request_number, pdf_url in pdfs:
            try:
                local_path = hf_hub_download(repo_id=REPO_ID, filename=f"pdfs/receipt_{request_number}.pdf", repo_type="space")
                zip_file.write(local_path, f"receipt_{request_number}.pdf")
            except:
                continue
    
    zip_path = "all_receipts.zip"
    with open(zip_path, 'wb') as f:
        f.write(zip_buffer.getvalue())
    
    try:
        hf_hub_upload(repo_id=REPO_ID, path_in_repo="all_receipts.zip", path_or_fileobj=zip_path, repo_type="space")
    except Exception as e:
        print(f"Error uploading ZIP: {e}")
    
    return f"pdfs/all_receipts.zip"

# เริ่มต้นฐานข้อมูลและนำเข้า JSON
init_db()
import_json_to_db()

# อินเทอร์เฟซ Gradio
with gr.Blocks(theme=gr.themes.Soft()) as demo:
    gr.Markdown("## เพิ่มข้อมูลพนักงานใหม่")
    with gr.Row():
        english_name = gr.Textbox(label="ชื่อภาษาอังกฤษ", placeholder="เช่น MR. AUNG KYAW", lines=1)
        foreign_reference_number = gr.Textbox(label="หมายเลขอ้างอิงคนต่างด้าว", placeholder="เช่น 2492102076212", lines=1)
    with gr.Row():
        id_number = gr.Textbox(label="หมายเลขประจำตัว (13 หลัก)", placeholder="เช่น 6685490000472", lines=1)
        nationality = gr.Textbox(label="สัญชาติ", value="เมียนมา", lines=1)
    with gr.Row():
        receipt_number = gr.Textbox(label="เลขที่บนขวาใบเสร็จ", placeholder="เช่น 2100680001130", lines=1)
        payment_number = gr.Textbox(label="หมายเลขชำระเงิน", placeholder="เช่น IV680210/002350", lines=1)
    attachments = gr.File(label="แนบไฟล์ (สูงสุด 5MB)", file_count="multiple", file_types=[".jpg", ".png", ".pdf"], max_file_size="5MB")
    submit = gr.Button("บันทึกข้อมูล")
    output = gr.Textbox(label="ผลลัพธ์")
    
    gr.Markdown("## ดาวน์โหลด PDF ทั้งหมด")
    download_all = gr.Button("ดาวน์โหลด PDF ทั้งหมด")
    download_output = gr.Textbox(label="ลิงก์ดาวน์โหลด")
    
    submit.click(fn=add_worker, inputs=[english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number, attachments], outputs=output)
    download_all.click(fn=download_all_pdfs, outputs=download_output)

demo.launch()