from reportlab.lib.pagesizes import letter
from reportlab.lib import colors
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.platypus import (
    SimpleDocTemplate,
    Table,
    TableStyle,
    Paragraph,
    HRFlowable,
    Spacer,
    Image,
)
import os
from bidi.algorithm import get_display
import arabic_reshaper
import sqlite3

# Create the PDF document
pdf_file = "arabic_invoice.pdf"
document = SimpleDocTemplate(pdf_file, pagesize=letter)

# Setup Arabic Font
font_path = r"C:\Users\pc\Desktop\repo_poject\font\arial.ttf"  # Replace with the actual path to your Arial font
pdfmetrics.registerFont(TTFont("ArialArabic", font_path))


# Helper function to reshape and reorder Arabic text
def arabic_text(text):
    reshaped_text = arabic_reshaper.reshape(str(text))
    return get_display(reshaped_text)


# Helper function to round numbers to 2 decimal places
def round_number(value):
    try:
        return round(float(value), 2)
    except ValueError:
        return value


# Connect to the SQLite database
conn = sqlite3.connect(
    r"C:\Users\pc\Desktop\repo_poject\bukab.db"
)  # Replace with the actual path to your database
cursor = conn.cursor()

# Fetch data from the "خالد" table
cursor.execute("SELECT * FROM خالد")
rows = cursor.fetchall()

# Define column names
columns = [
    "رقم المعاملة",
    "التاريخ",
    "الاسم",
    "البلد",
    "المدينة",
    "القيمة",
    "السعر",
    "نوع العملية",
    "مدين",
    "مدان",
    "المجموع",
    "العمولة",
]

# Close the database connection
conn.close()

# Add elements to the PDF document
elements = []

# Define styles
styles = getSampleStyleSheet()
style_normal = styles["Normal"]
style_normal.fontName = "ArialArabic"
style_normal.alignment = 2  # RIGHT alignment for Arabic
style_heading = styles["Heading1"]
style_heading.fontName = "ArialArabic"
style_heading.alignment = 2  # RIGHT alignment for Arabic
elements.append(
    HRFlowable(
        width="100%",
        thickness=1,
        lineCap="round",
        color=colors.black,
        hAlign="LEFT",
        spaceBefore=0,
        spaceAfter=0,
    )
)
# Header
header_data = [
    [arabic_text("اسم المكتب"), "", arabic_text("فاتورة")],
    # [arabic_text("شعار الشركة"), "", ""],
    [
        Image("logo.jpg", width=2 * inch, height=2 * inch),
        "",
        "",
    ],  # Replace with the actual path to your logo
    [arabic_text("عنوان الشارع"), "", arabic_text("التاريخ: ٢٠ نوفمبر ٢٠٢٣")],
    [arabic_text("المدينة، الرمز البريدي"), "", arabic_text("رقم الفاتورة: ٥")],
    [
        arabic_text("هاتف: ١٢٣٤.١٢٣.١١١ فاكس: ١٢٣٤.١٢٣.١١١"),
        "",
        arabic_text("من أجل: وصف المشروع أو الخدمة"),
    ],
]

# Create the header table
header_table = Table(header_data, colWidths=[3.5 * inch, 0.5 * inch, 3.0 * inch])
header_table.setStyle(
    TableStyle(
        [
            ("SPAN", (0, 0), (0, 1)),
            ("SPAN", (2, 0), (2, 1)),
            ("SPAN", (0, 2), (0, 4)),
            ("SPAN", (2, 2), (2, 3)),
            ("ALIGN", (0, 0), (-1, -1), "CENTER"),  # Align all cells to the right
            ("FONTNAME", (0, 0), (-1, -1), "ArialArabic"),
            ("FONTSIZE", (0, 0), (-1, -1), 10),
            ("BOTTOMPADDING", (0, 0), (-1, -1), 12),
            ("TOPPADDING", (0, 0), (-1, -1), 0),
        ]
    )
)
elements.append(header_table)

# Add Spacer for empty space above the line

# Add the separating line
elements.append(
    HRFlowable(
        width="100%",
        thickness=1,
        lineCap="round",
        color=colors.black,
        hAlign="LEFT",
        spaceBefore=0,
        spaceAfter=0,
    )
)
elements.append(Spacer(1, 0.2 * inch))  # Adjust the height as needed

# Description Section
description_data = [
    [arabic_text(col) for col in columns]
]  # Use the column names as headers
for row in rows:
    rounded_row = list(row)
    # Round مدين, مدان, and المجموع to 2 decimal places
    rounded_row[8] = round_number(row[8])  # مدين
    rounded_row[9] = round_number(row[9])  # مدان
    rounded_row[10] = round_number(row[10])  # المجموع
    description_data.append([arabic_text(item) for item in rounded_row])

# Create the description table
col_widths = [0.6 * inch] * len(columns)  # Adjust as needed
description_table = Table(description_data, colWidths=col_widths)
description_table.setStyle(
    TableStyle(
        [
            ("BACKGROUND", (0, 0), (-1, 0), colors.lightgrey),
            ("LINEBELOW", (0, 0), (-1, 0), 1, colors.black),
            ("LINEBELOW", (0, -1), (-1, -1), 1, colors.black),
            ("ALIGN", (0, 0), (-1, -1), "CENTER"),
            ("FONTNAME", (0, 0), (-1, -1), "ArialArabic"),
            ("FONTSIZE", (0, 0), (-1, -1), 8),  # Reduced font size to fit all columns
            ("BOTTOMPADDING", (0, 0), (-1, -1), 6),
        ]
    )
)
elements.append(description_table)

# Add final text
elements.append(Paragraph(arabic_text("!شكرا لعملك"), style_normal))

# Build the PDF
document.build(elements)

print(f"PDF report generated: {pdf_file}")


add more customstion as you can