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

# Load .env
load_dotenv()
REPO_ID = os.getenv("REPO_ID", "your-username/worker-management")
ORIGINAL_REPO_ID = os.getenv("ORIGINAL_REPO_ID", None)

# Register fonts
try:
    pdfmetrics.registerFont(TTFont('THSarabun', 'THSarabunNew.ttf'))
    pdfmetrics.registerFont(TTFont('THSarabunBold', 'THSarabunNew-Bold.ttf'))
except Exception as e:
    raise Exception(f"Error loading fonts: {e}")

# Convert timestamp to Thai format
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}"

# Initialize database
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()

# Import JSON data
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}")

# Backup database
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}")

# Generate next request number
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}"

# Create PDF with background
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)
    
    # Add background image (from index1.html)
    try:
        # Try original Space first
        if ORIGINAL_REPO_ID:
            bg_path = hf_hub_download(repo_id=ORIGINAL_REPO_ID, filename="assets/bg.png", repo_type="space")
        else:
            bg_path = hf_hub_download(repo_id=REPO_ID, filename="assets/bg.png", repo_type="space")
        c.drawImage(bg_path, 0, 0, width=A4[0], height=A4[1], preserveAspectRatio=True)
    except Exception as e:
        print(f"Error loading background image (bg.png): {e}")
    
    # Apply styles from main (5).css
    c.setFont("THSarabunBold", 52.284 / 2.83465)  # fs0
    c.drawString(2*cm, 28*cm, "กระทรวงแรงงาน")
    c.setFont("THSarabun", 35.212 / 2.83465)  # fs3
    c.drawString(2*cm, 27.5*cm, "ใบเสร็จรับเงิน (ต้นฉบับ)")
    c.drawString(2*cm, 26*cm, f"เลขที่: {receipt_number}")
    c.drawString(2*cm, 25.5*cm, "ที่ทำการ: สำนักบริหารแรงงานต่างด้าว")
    c.drawString(2*cm, 25*cm, "วันที่: 01 เมษายน 2568")
    c.drawString(2*cm, 24.5*cm, f"เลขที่ใบชำระเงิน: {payment_number}")
    c.drawString(2*cm, 24*cm, f"เลขรับคำขอที่: {request_number}")
    c.drawString(2*cm, 23.5*cm, f"ชื่อผู้ชำระเงิน: {english_name}")
    c.drawString(2*cm, 23*cm, f"สัญชาติ: {nationality}")
    c.drawString(2*cm, 22.5*cm, f"เลขอ้างอิงคนต่างด้าว: {foreign_reference_number}")
    c.drawString(2*cm, 22*cm, f"หมายเลขประจำตัวคนต่างด้าว: {id_number}")
    c.drawString(2*cm, 21.5*cm, "ชื่อนายจ้าง / สถานประกอบการ: บริษัท บาน กง เอ็นจิเนียริ่ง จำกัด")
    c.drawString(2*cm, 21*cm, "เลขประจำตัวนายจ้าง: 0415567000061")
    c.setFont("THSarabunBold", 35.212 / 2.83465)
    c.drawString(2*cm, 20*cm, "รายการ")
    c.setFont("THSarabun", 35.212 / 2.83465)
    c.drawString(2*cm, 19.5*cm, "1. ค่าธรรมเนียมในการยื่นคำขอ ฉบับละ 100 บาท: 100.00")
    c.drawString(2*cm, 19*cm, "2. ค่าธรรมเนียม: 900.00")
    c.drawString(2*cm, 18.5*cm, "รวมเป็นเงินทั้งสิ้น (บาท): (หนึ่งพันบาทถ้วน) 1,000.00")
    c.drawString(2*cm, 18*cm, "ได้รับเงินไว้เป็นการถูกต้องแล้ว")
    c.drawString(2*cm, 17.5*cm, "(ลงชื่อ) นางสาวอารีวรรณ โพธิ์นิ่มแดง (ผู้รับเงิน)")
    c.drawString(2*cm, 17*cm, "ตำแหน่ง: นักวิชาการแรงงานชำนาญการ")
    
    # QR Code
    pdf_url = f"[invalid url, do not cite]
    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)
    
    # Add print timestamp
    c.setFont("THSarabun", 20 / 2.83465)
    print_time = thai_date_time(time.time())
    c.drawString(2*cm, 2*cm, f"พิมพ์เมื่อ: {print_time}")
    
    c.showPage()
    c.save()
    
    # Upload PDF
    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}")
    
    # Upload to original Space (if specified)
    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}")
    
    # Save PDF info
    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

# Validate input
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

# Add worker
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()
    
    # Save data
    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
    ))
    
    # Handle attachments
    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"[invalid url, do not cite]
            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()
    
    # Create PDF
    pdf_path = create_receipt_pdf(request_number, english_name, foreign_reference_number, id_number, nationality, receipt_number, payment_number)
    
    # Backup database
    backup_db()
    
    return f"เพิ่มข้อมูลพนักงานสำเร็จ! เลขคำขอ: {request_number}\nPDF: {pdf_path}\nไฟล์แนบ: {', '.join(attachment_urls) if attachment_urls else 'ไม่มีไฟล์แนบ'}"

# Download all PDFs
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"

# Initialize database and import JSON
init_db()
import_json_to_db()

# Gradio interface
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()