"""
Shifa Online Consultation - Patient Record Excel Workbook Builder
Creates a professional .xlsm workbook with:
  - Sheet 1: Data Entry Form (UI)
  - Sheet 2: Patient Database
  - VBA macro for Submit / Clear / Print
"""

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, GradientFill
)
from openpyxl.styles.numbers import FORMAT_DATE_DDMMYY
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image as XLImage
from openpyxl.worksheet.page import PageMargins
import os

# ─── colour palette ──────────────────────────────────────────────────────────
TEAL_DARK   = "006B6B"   # deep teal – main header bg
TEAL_MID    = "009999"   # section header bg
TEAL_LIGHT  = "CCF2F2"   # alternating row / label bg
TEAL_XLIGHT = "E6FAFA"   # form field bg
WHITE       = "FFFFFF"
GOLD        = "D4AF37"   # accent border / logo text
DARK_TEXT   = "1A1A2E"   # near-black body text
GREY_BORDER = "AAAAAA"

# ─── border helpers ──────────────────────────────────────────────────────────
thin  = Side(style="thin",   color=GREY_BORDER)
med   = Side(style="medium", color=TEAL_DARK)
thick = Side(style="thick",  color=TEAL_DARK)

def thin_border():
    return Border(left=thin, right=thin, top=thin, bottom=thin)

def med_border():
    return Border(left=med, right=med, top=med, bottom=med)

def thick_bottom():
    return Border(bottom=thick)

# ─── helpers ─────────────────────────────────────────────────────────────────
def merge_fill(ws, cell_range, fill_color, text="", font_color=WHITE,
               font_size=11, bold=False, italic=False, h_align="center",
               v_align="center", wrap=False):
    ws.merge_cells(cell_range)
    top_left = ws[cell_range.split(":")[0]]
    top_left.value = text
    top_left.fill  = PatternFill("solid", start_color=fill_color)
    top_left.font  = Font(name="Arial", size=font_size, bold=bold,
                          italic=italic, color=font_color)
    top_left.alignment = Alignment(horizontal=h_align, vertical=v_align,
                                   wrap_text=wrap)
    return top_left

def label_cell(ws, ref, text):
    c = ws[ref]
    c.value = text
    c.font  = Font(name="Arial", size=10, bold=True, color=DARK_TEXT)
    c.fill  = PatternFill("solid", start_color=TEAL_LIGHT)
    c.alignment = Alignment(horizontal="right", vertical="center")
    c.border = thin_border()

def input_cell(ws, ref, value="", fmt=None, locked=False):
    c = ws[ref]
    c.value = value
    c.font  = Font(name="Arial", size=10, color=DARK_TEXT)
    c.fill  = PatternFill("solid", start_color=TEAL_XLIGHT)
    c.alignment = Alignment(horizontal="left", vertical="center",
                            wrap_text=True)
    c.border = Border(left=thin, right=thin,
                      top=Side(style="thin", color=TEAL_MID),
                      bottom=Side(style="medium", color=TEAL_MID))
    if fmt:
        c.number_format = fmt
    return c

# ─── build workbook ──────────────────────────────────────────────────────────
wb = Workbook()

# ════════════════════════════════════════════════════════════════════════════
#  SHEET 1 – DATA ENTRY FORM
# ════════════════════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "DATA ENTRY FORM"
ws1.sheet_view.showGridLines = False

# set column widths
col_widths = {"A": 3, "B": 18, "C": 30, "D": 3, "E": 18, "F": 30, "G": 3}
for col, w in col_widths.items():
    ws1.column_dimensions[col].width = w

# row heights (set generous heights for form rows)
ws1.row_dimensions[1].height  = 8    # top margin
ws1.row_dimensions[2].height  = 14
ws1.row_dimensions[3].height  = 50   # logo / title band
ws1.row_dimensions[4].height  = 22   # subtitle
ws1.row_dimensions[5].height  = 10   # spacer
ws1.row_dimensions[6].height  = 20   # section header
for r in range(7, 28):
    ws1.row_dimensions[r].height = 28
ws1.row_dimensions[28].height = 20   # section header 2
for r in range(29, 38):
    ws1.row_dimensions[r].height = 28
ws1.row_dimensions[38].height = 16   # spacer
ws1.row_dimensions[39].height = 36   # buttons row
ws1.row_dimensions[40].height = 10   # bottom margin

# ── top accent bar ────────────────────────────────────────────────────────
merge_fill(ws1, "A1:G2",  TEAL_DARK)
merge_fill(ws1, "A40:G40", TEAL_DARK)

# ── LOGO / TITLE BAND ─────────────────────────────────────────────────────
logo_cell = merge_fill(ws1, "A3:G3", TEAL_DARK,
    text="🏥  SHIFA ONLINE CONSULTATION",
    font_color=WHITE, font_size=22, bold=True, italic=False)

# subtitle
merge_fill(ws1, "A4:G4", TEAL_MID,
    text="Patient Consultation Record System  |  New Patient Entry",
    font_color=WHITE, font_size=11, italic=True)

# spacer
merge_fill(ws1, "A5:G5", WHITE)

# ── SECTION 1: PATIENT INFORMATION ───────────────────────────────────────
merge_fill(ws1, "A6:G6", TEAL_MID,
    text="▌  SECTION 1 — PATIENT INFORMATION",
    font_color=WHITE, font_size=10, bold=True, h_align="left")

# row 7 – Serial Number (auto) + Patient Name
label_cell(ws1, "B7", "Serial No.")
# serial is auto-generated by formula referencing DB sheet
c = input_cell(ws1, "C7")
c.value = '=IF(Database!A2="","AUTO",MAX(Database!A:A)+1)'
c.font = Font(name="Arial", size=10, bold=True, color=TEAL_DARK)
c.number_format = "0"

label_cell(ws1, "E7", "Patient Name *")
input_cell(ws1, "F7")

# row 8 – Father/Husband + Age
label_cell(ws1, "B8", "Father / Husband")
input_cell(ws1, "C8")
label_cell(ws1, "E8", "Age *")
input_cell(ws1, "F8")

# row 9 – Gender + Contact
label_cell(ws1, "B9", "Gender *")
input_cell(ws1, "C9")
label_cell(ws1, "E9", "Contact No. *")
input_cell(ws1, "F9")

# row 10 – City
label_cell(ws1, "B10", "City / Location")
input_cell(ws1, "C10")
label_cell(ws1, "E10", "Doctor Name *")
input_cell(ws1, "F10")

# row 11 – Disease/Complaint
label_cell(ws1, "B11", "Disease / Complaint *")
input_cell(ws1, "C11")
label_cell(ws1, "E11", "Consultation Date *")
input_cell(ws1, "F11", fmt="DD/MM/YYYY")

# row 12 – Appointment Time + Fee
label_cell(ws1, "B12", "Appointment Time")
input_cell(ws1, "C12", fmt="HH:MM AM/PM")
label_cell(ws1, "E12", "Consultation Fee (Rs.)")
input_cell(ws1, "F12", fmt="#,##0")

# spacer row 13
merge_fill(ws1, "A13:G13", WHITE)

# ── SECTION 2: CLINICAL NOTES ─────────────────────────────────────────────
merge_fill(ws1, "A14:G14", TEAL_MID,
    text="▌  SECTION 2 — CLINICAL NOTES",
    font_color=WHITE, font_size=10, bold=True, h_align="left")

# Reports / Notes (tall row)
label_cell(ws1, "B15", "Reports / Notes")
ws1.row_dimensions[15].height = 55
ws1.merge_cells("C15:F16")
c = ws1["C15"]
c.value = ""
c.font  = Font(name="Arial", size=10, color=DARK_TEXT)
c.fill  = PatternFill("solid", start_color=TEAL_XLIGHT)
c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
c.border = Border(left=thin, right=thin,
                  top=Side(style="thin", color=TEAL_MID),
                  bottom=Side(style="medium", color=TEAL_MID))

# Prescription Notes (tall row)
label_cell(ws1, "B17", "Prescription Notes")
ws1.row_dimensions[17].height = 55
ws1.merge_cells("C17:F18")
c = ws1["C17"]
c.value = ""
c.font  = Font(name="Arial", size=10, color=DARK_TEXT)
c.fill  = PatternFill("solid", start_color=TEAL_XLIGHT)
c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
c.border = Border(left=thin, right=thin,
                  top=Side(style="thin", color=TEAL_MID),
                  bottom=Side(style="medium", color=TEAL_MID))

# row 19 spacer
merge_fill(ws1, "A19:G19", WHITE)

# ── SECTION 3: FOLLOW-UP & STATUS ─────────────────────────────────────────
merge_fill(ws1, "A20:G20", TEAL_MID,
    text="▌  SECTION 3 — FOLLOW-UP & STATUS",
    font_color=WHITE, font_size=10, bold=True, h_align="left")

label_cell(ws1, "B21", "Follow-up Date")
input_cell(ws1, "C21", fmt="DD/MM/YYYY")
label_cell(ws1, "E21", "Status *")
input_cell(ws1, "F21")

# row 22 spacer
merge_fill(ws1, "A22:G22", WHITE)

# ── BUTTONS ROW ───────────────────────────────────────────────────────────
# Submit button (styled cell – macro attached by VBA)
merge_fill(ws1, "B23:C23", TEAL_DARK,
    text="✔  SUBMIT RECORD",
    font_color=WHITE, font_size=11, bold=True)
ws1["B23"].border = Border(
    left=Side(style="thick", color=GOLD),
    right=Side(style="thick", color=GOLD),
    top=Side(style="thick", color=GOLD),
    bottom=Side(style="thick", color=GOLD))

merge_fill(ws1, "E23:F23", "CC0000",
    text="✖  CLEAR FORM",
    font_color=WHITE, font_size=11, bold=True)
ws1["E23"].border = Border(
    left=Side(style="thick", color=GOLD),
    right=Side(style="thick", color=GOLD),
    top=Side(style="thick", color=GOLD),
    bottom=Side(style="thick", color=GOLD))

# row 24 spacer
merge_fill(ws1, "A24:G24", WHITE)

# ── footer note ───────────────────────────────────────────────────────────
merge_fill(ws1, "A25:G25", TEAL_LIGHT,
    text="  ★ Fields marked with * are mandatory.  Serial No. is auto-generated.  "
         "Click SUBMIT to save the record to the database.",
    font_color=TEAL_DARK, font_size=9, italic=True, h_align="left")

# ─── DATA VALIDATIONS ─────────────────────────────────────────────────────
# Gender dropdown
dv_gender = DataValidation(
    type="list",
    formula1='"Male,Female,Other"',
    allow_blank=True,
    showDropDown=False,
    showErrorMessage=True,
    errorTitle="Invalid Input",
    error="Please select: Male, Female, or Other"
)
dv_gender.sqref = "C9"
ws1.add_data_validation(dv_gender)

# Status dropdown
dv_status = DataValidation(
    type="list",
    formula1='"Pending,Completed,Follow-up Required,Cancelled"',
    allow_blank=True,
    showDropDown=False,
    showErrorMessage=True,
    errorTitle="Invalid Input",
    error="Please select a valid status."
)
dv_status.sqref = "F21"
ws1.add_data_validation(dv_status)

# Doctor Name dropdown
dv_doctor = DataValidation(
    type="list",
    formula1='"Dr. Ahmed Ali,Dr. Sara Khan,Dr. Bilal Raza,Dr. Fatima Noor,Dr. Usman Tariq"',
    allow_blank=True,
    showDropDown=False,
    showErrorMessage=True,
    errorTitle="Invalid Input",
    error="Please select a doctor from the list."
)
dv_doctor.sqref = "F10"
ws1.add_data_validation(dv_doctor)

# Age validation (1-120)
dv_age = DataValidation(
    type="whole",
    operator="between",
    formula1="1",
    formula2="120",
    allow_blank=True,
    showErrorMessage=True,
    errorTitle="Invalid Age",
    error="Age must be a number between 1 and 120."
)
dv_age.sqref = "F8"
ws1.add_data_validation(dv_age)

# Date validations
dv_date = DataValidation(
    type="date",
    allow_blank=True,
    showErrorMessage=True,
    errorTitle="Invalid Date",
    error="Please enter a valid date (DD/MM/YYYY)."
)
dv_date.sqref = "F11 C21"
ws1.add_data_validation(dv_date)

# Fee validation (numeric >= 0)
dv_fee = DataValidation(
    type="decimal",
    operator="greaterThanOrEqual",
    formula1="0",
    allow_blank=True,
    showErrorMessage=True,
    errorTitle="Invalid Fee",
    error="Consultation fee must be a positive number."
)
dv_fee.sqref = "F12"
ws1.add_data_validation(dv_fee)

# sheet protection (only input cells unlocked via VBA)
# We do NOT lock via openpyxl as it complicates macro usage

# ════════════════════════════════════════════════════════════════════════════
#  SHEET 2 – DATABASE
# ════════════════════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("Database")
ws2.sheet_view.showGridLines = False

# column definitions: (header, width)
DB_COLS = [
    ("Serial No.",         8),
    ("Patient Name",       20),
    ("Father/Husband",     20),
    ("Age",                 7),
    ("Gender",             10),
    ("Contact No.",        15),
    ("City / Location",    18),
    ("Disease / Complaint",25),
    ("Doctor Name",        20),
    ("Consultation Date",  16),
    ("Appointment Time",   16),
    ("Fee (Rs.)",          12),
    ("Reports / Notes",    30),
    ("Prescription",       30),
    ("Follow-up Date",     16),
    ("Status",             15),
    ("Entry Timestamp",    20),
]

# set column widths
for i, (hdr, w) in enumerate(DB_COLS, start=1):
    ws2.column_dimensions[get_column_letter(i)].width = w

ws2.row_dimensions[1].height = 8   # top margin row
ws2.row_dimensions[2].height = 50  # logo/title
ws2.row_dimensions[3].height = 22  # subtitle
ws2.row_dimensions[4].height = 10  # spacer
ws2.row_dimensions[5].height = 28  # table header
ws2.row_dimensions[6].height = 24  # pre-filled sample (first data row sentinel)

last_col_letter = get_column_letter(len(DB_COLS))

# top accent bar
merge_fill(ws2, f"A1:{last_col_letter}1", TEAL_DARK)

# Logo / Title
merge_fill(ws2, f"A2:{last_col_letter}2", TEAL_DARK,
    text="🏥  SHIFA ONLINE CONSULTATION  —  Patient Database",
    font_color=WHITE, font_size=18, bold=True)

# subtitle
merge_fill(ws2, f"A3:{last_col_letter}3", TEAL_MID,
    text="Patient Consultation Record System  |  All Records",
    font_color=WHITE, font_size=10, italic=True)

# spacer
merge_fill(ws2, f"A4:{last_col_letter}4", WHITE)

# ── TABLE HEADER ROW (row 5) ─────────────────────────────────────────────
for i, (hdr, _) in enumerate(DB_COLS, start=1):
    c = ws2.cell(row=5, column=i)
    c.value = hdr
    c.font  = Font(name="Arial", size=10, bold=True, color=WHITE)
    c.fill  = PatternFill("solid", start_color=TEAL_DARK)
    c.alignment = Alignment(horizontal="center", vertical="center",
                            wrap_text=True)
    c.border = Border(
        left=Side(style="thin", color=WHITE),
        right=Side(style="thin", color=WHITE),
        bottom=Side(style="medium", color=GOLD)
    )

# freeze panes below header
ws2.freeze_panes = "A6"

# auto-filter on header row
ws2.auto_filter.ref = f"A5:{last_col_letter}5"

# ── PLACEHOLDER / SAMPLE ROW (row 6, greyed out) ─────────────────────────
sample = [
    1, "Muhammad Ali", "Hassan Ali", 35, "Male",
    "03001234567", "Lahore", "Fever & Cough",
    "Dr. Ahmed Ali", "15/06/2026", "10:00 AM",
    500, "CBC Normal", "Tab. Paracetamol 500mg BD x 5 days",
    "22/06/2026", "Completed", "15/06/2026 03:42 PM"
]
for i, val in enumerate(sample, start=1):
    c = ws2.cell(row=6, column=i, value=val)
    c.font  = Font(name="Arial", size=9, color="888888", italic=True)
    c.fill  = PatternFill("solid", start_color="F5F5F5")
    c.alignment = Alignment(horizontal="center", vertical="center",
                            wrap_text=True)
    c.border = thin_border()
    if i == 10 or i == 15:
        c.number_format = "DD/MM/YYYY"
    if i == 12:
        c.number_format = "#,##0"

# ── ALTERNATE ROW SHADING formula (conditional formatting) ───────────────
from openpyxl.formatting.rule import FormulaRule

alt_fill = PatternFill("solid", start_color=TEAL_XLIGHT)
ws2.conditional_formatting.add(
    f"A7:{last_col_letter}1048576",
    FormulaRule(formula=["MOD(ROW(),2)=1"], fill=alt_fill)
)

# ── PRINT SETTINGS for Sheet 2 ───────────────────────────────────────────
ws2.page_setup.orientation = "landscape"
ws2.page_setup.paperSize   = ws2.PAPERSIZE_A4
ws2.page_setup.fitToPage   = True
ws2.page_setup.fitToWidth  = 1
ws2.page_setup.fitToHeight = 0
ws2.print_title_rows = "1:5"
ws2.page_margins = PageMargins(
    left=0.5, right=0.5, top=0.75, bottom=0.75,
    header=0.3, footer=0.3
)
# Header/Footer
ws2.oddHeader.center.text  = "Shifa Online Consultation – Patient Records"
ws2.oddHeader.center.font  = "Arial,Bold"
ws2.oddFooter.left.text    = "Confidential – For Internal Use Only"
ws2.oddFooter.right.text   = "Page &P of &N"

# ── PRINT SETTINGS for Sheet 1 ───────────────────────────────────────────
ws1.page_setup.orientation = "portrait"
ws1.page_setup.paperSize   = ws1.PAPERSIZE_A4
ws1.page_setup.fitToPage   = True
ws1.page_setup.fitToWidth  = 1
ws1.page_setup.fitToHeight = 1
ws1.page_margins = PageMargins(
    left=0.5, right=0.5, top=0.75, bottom=0.75,
    header=0.3, footer=0.3
)
ws1.oddHeader.center.text = "Shifa Online Consultation – New Patient Entry"
ws1.oddFooter.right.text  = "Page &P of &N"

# ════════════════════════════════════════════════════════════════════════════
#  SAVE AS .xlsm  (macro-enabled workbook)
# ════════════════════════════════════════════════════════════════════════════
out_dir  = "/home/daytona/workspace/shifa-consultation"
out_xlsx = os.path.join(out_dir, "Shifa_Patient_Records.xlsx")

wb.save(out_xlsx)
print(f"Saved: {out_xlsx}")
print("Done (xlsx base file).")

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/shifa-consultation/build_shifa.py'

"""
Converts Shifa_Patient_Records.xlsx → Shifa_Patient_Records.xlsm
by injecting a VBA project (vbaProject.bin) built from a macro stub.

Since we cannot compile a real .bin from scratch without COM/xlwings,
we instead:
  1. Create a minimal vbaProject.bin via the 'oletools' / openpyxl trick
  2. If that's unavailable, produce an .xlsm wrapper that contains
     the VBA code as a module stub readable by Excel on open.

Strategy used here: embed pre-built vbaProject.bin bytes that encode
three public macros:
  - SubmitRecord  → copies form data to Database sheet & clears form
  - ClearForm     → wipes all input cells
  - PrintDatabase → prints Sheet 2

We craft the vbaProject.bin using the 'cfb' (Compound File Binary)
structure with just enough content to satisfy Excel.
Because building a valid .bin from scratch requires exact CFB magic bytes,
we use a reliable alternative: write the VBA source as a companion .bas
file for the user, and produce the .xlsm from the xlsx by renaming +
patching content types so Excel accepts it and prompts to enable macros.

The user can then open the file → open VBA editor → import the .bas file.
Alternatively we document the VBA in the sheet itself.
"""

import zipfile, shutil, io, os, re

SRC = "/home/daytona/workspace/shifa-consultation/Shifa_Patient_Records.xlsx"
DST = "/home/daytona/workspace/shifa-consultation/Shifa_Patient_Records.xlsm"

# ── VBA source code ──────────────────────────────────────────────────────────
VBA_CODE = r'''
Attribute VB_Name = "ShifaMacros"

'===========================================================================
'  SHIFA ONLINE CONSULTATION – Patient Record Macros
'  Author : Generated automatically
'  Sheet1 : "DATA ENTRY FORM"   (entry cells mapped below)
'  Sheet2 : "Database"
'===========================================================================

' ─── Cell map (DATA ENTRY FORM) ─────────────────────────────────────────
'  C7  = Serial No (formula, read-only)
'  F7  = Patient Name
'  C8  = Father/Husband
'  F8  = Age
'  C9  = Gender
'  F9  = Contact No.
'  C10 = City
'  F10 = Doctor Name
'  C11 = Disease/Complaint
'  F11 = Consultation Date
'  C12 = Appointment Time
'  F12 = Consultation Fee
'  C15 = Reports/Notes        (merged C15:F16)
'  C17 = Prescription Notes   (merged C17:F18)
'  C21 = Follow-up Date
'  F21 = Status

Sub SubmitRecord()
    Dim wsForm As Worksheet
    Dim wsDB   As Worksheet
    Dim nextRow As Long
    Dim serialNo As Long

    wsForm = ThisWorkbook.Sheets("DATA ENTRY FORM")
    wsDB   = ThisWorkbook.Sheets("Database")

    ' ── Validate mandatory fields ────────────────────────────────────────
    If Trim(wsForm.Range("F7").Value) = "" Then
        MsgBox "Patient Name is required!", vbExclamation, "Validation Error"
        wsForm.Range("F7").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("F8").Value) = "" Then
        MsgBox "Age is required!", vbExclamation, "Validation Error"
        wsForm.Range("F8").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("C9").Value) = "" Then
        MsgBox "Gender is required!", vbExclamation, "Validation Error"
        wsForm.Range("C9").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("F9").Value) = "" Then
        MsgBox "Contact Number is required!", vbExclamation, "Validation Error"
        wsForm.Range("F9").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("F10").Value) = "" Then
        MsgBox "Doctor Name is required!", vbExclamation, "Validation Error"
        wsForm.Range("F10").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("C11").Value) = "" Then
        MsgBox "Disease / Complaint is required!", vbExclamation, "Validation Error"
        wsForm.Range("C11").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("F11").Value) = "" Then
        MsgBox "Consultation Date is required!", vbExclamation, "Validation Error"
        wsForm.Range("F11").Select
        Exit Sub
    End If
    If Trim(wsForm.Range("F21").Value) = "" Then
        MsgBox "Status is required!", vbExclamation, "Validation Error"
        wsForm.Range("F21").Select
        Exit Sub
    End If

    ' ── Find next empty row in Database (data starts at row 6) ──────────
    nextRow = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row + 1
    If nextRow < 7 Then nextRow = 7   ' keep row 6 as sample

    ' ── Auto serial number ───────────────────────────────────────────────
    If nextRow = 7 Then
        serialNo = 1
    Else
        serialNo = wsDB.Cells(nextRow - 1, 1).Value + 1
    End If

    ' ── Write data to Database ───────────────────────────────────────────
    wsDB.Cells(nextRow,  1).Value = serialNo
    wsDB.Cells(nextRow,  2).Value = Trim(wsForm.Range("F7").Value)
    wsDB.Cells(nextRow,  3).Value = Trim(wsForm.Range("C8").Value)
    wsDB.Cells(nextRow,  4).Value = wsForm.Range("F8").Value
    wsDB.Cells(nextRow,  5).Value = wsForm.Range("C9").Value
    wsDB.Cells(nextRow,  6).Value = Trim(wsForm.Range("F9").Value)
    wsDB.Cells(nextRow,  7).Value = Trim(wsForm.Range("C10").Value)
    wsDB.Cells(nextRow,  8).Value = Trim(wsForm.Range("C11").Value)
    wsDB.Cells(nextRow,  9).Value = wsForm.Range("F10").Value
    wsDB.Cells(nextRow, 10).Value = wsForm.Range("F11").Value
    wsDB.Cells(nextRow, 11).Value = wsForm.Range("C12").Value
    wsDB.Cells(nextRow, 12).Value = wsForm.Range("F12").Value
    wsDB.Cells(nextRow, 13).Value = wsForm.Range("C15").Value
    wsDB.Cells(nextRow, 14).Value = wsForm.Range("C17").Value
    wsDB.Cells(nextRow, 15).Value = wsForm.Range("C21").Value
    wsDB.Cells(nextRow, 16).Value = wsForm.Range("F21").Value
    wsDB.Cells(nextRow, 17).Value = Now()   ' timestamp

    ' ── Format date & number cells ───────────────────────────────────────
    wsDB.Cells(nextRow, 10).NumberFormat = "DD/MM/YYYY"
    wsDB.Cells(nextRow, 15).NumberFormat = "DD/MM/YYYY"
    wsDB.Cells(nextRow, 17).NumberFormat = "DD/MM/YYYY HH:MM AM/PM"
    wsDB.Cells(nextRow, 12).NumberFormat = "#,##0"

    ' ── Apply row formatting ─────────────────────────────────────────────
    Dim dbRange As Range
    dbRange = wsDB.Range(wsDB.Cells(nextRow, 1), wsDB.Cells(nextRow, 17))
    dbRange.Font.Name = "Arial"
    dbRange.Font.Size = 9
    dbRange.Borders.LineStyle = xlContinuous
    dbRange.Borders.Color = RGB(170, 170, 170)
    If (nextRow Mod 2) = 0 Then
        dbRange.Interior.Color = RGB(230, 250, 250)  ' light teal
    Else
        dbRange.Interior.Color = RGB(255, 255, 255)  ' white
    End If
    dbRange.Columns(1).HorizontalAlignment = xlCenter   ' serial no.
    dbRange.Columns(4).HorizontalAlignment = xlCenter   ' age
    dbRange.Columns(5).HorizontalAlignment = xlCenter   ' gender
    dbRange.Columns(12).HorizontalAlignment = xlRight   ' fee
    dbRange.Columns(16).HorizontalAlignment = xlCenter  ' status

    ' ── Colour-code status ───────────────────────────────────────────────
    Select Case wsDB.Cells(nextRow, 16).Value
        Case "Completed"
            wsDB.Cells(nextRow, 16).Font.Color = RGB(0, 128, 0)
            wsDB.Cells(nextRow, 16).Font.Bold = True
        Case "Pending"
            wsDB.Cells(nextRow, 16).Font.Color = RGB(180, 100, 0)
            wsDB.Cells(nextRow, 16).Font.Bold = True
        Case "Follow-up Required"
            wsDB.Cells(nextRow, 16).Font.Color = RGB(0, 80, 160)
            wsDB.Cells(nextRow, 16).Font.Bold = True
        Case "Cancelled"
            wsDB.Cells(nextRow, 16).Font.Color = RGB(200, 0, 0)
            wsDB.Cells(nextRow, 16).Font.Bold = True
    End Select

    ' ── Clear the form ───────────────────────────────────────────────────
    Call ClearForm

    MsgBox "Record saved successfully! Serial No: " & serialNo, _
           vbInformation, "Shifa - Record Submitted"

    ' ── Switch to Database to confirm ────────────────────────────────────
    ' (optional - comment out if you want to stay on form)
    ' wsDB.Activate

End Sub

' ─────────────────────────────────────────────────────────────────────────────
Sub ClearForm()
    Dim wsForm As Worksheet
    wsForm = ThisWorkbook.Sheets("DATA ENTRY FORM")

    ' clear all input cells (leave labels and formulas intact)
    wsForm.Range("F7").ClearContents   ' Patient Name
    wsForm.Range("C8").ClearContents   ' Father/Husband
    wsForm.Range("F8").ClearContents   ' Age
    wsForm.Range("C9").ClearContents   ' Gender
    wsForm.Range("F9").ClearContents   ' Contact
    wsForm.Range("C10").ClearContents  ' City
    wsForm.Range("F10").ClearContents  ' Doctor
    wsForm.Range("C11").ClearContents  ' Disease
    wsForm.Range("F11").ClearContents  ' Date
    wsForm.Range("C12").ClearContents  ' Time
    wsForm.Range("F12").ClearContents  ' Fee
    wsForm.Range("C15").ClearContents  ' Reports
    wsForm.Range("C17").ClearContents  ' Prescription
    wsForm.Range("C21").ClearContents  ' Follow-up Date
    wsForm.Range("F21").ClearContents  ' Status

    wsForm.Range("F7").Select
End Sub

' ─────────────────────────────────────────────────────────────────────────────
Sub PrintDatabase()
    Dim wsDB As Worksheet
    wsDB = ThisWorkbook.Sheets("Database")
    wsDB.PrintOut Copies:=1, Collate:=True
End Sub

' ─────────────────────────────────────────────────────────────────────────────
Sub PrintCurrentForm()
    Dim wsForm As Worksheet
    wsForm = ThisWorkbook.Sheets("DATA ENTRY FORM")
    wsForm.PrintOut Copies:=1, Collate:=True
End Sub
'''

# ── Write .bas companion file ────────────────────────────────────────────────
bas_path = "/home/daytona/workspace/shifa-consultation/ShifaMacros.bas"
with open(bas_path, "w", encoding="utf-8") as f:
    f.write(VBA_CODE)
print(f"VBA source saved to: {bas_path}")

# ── Copy xlsx → xlsm and patch content types ────────────────────────────────
shutil.copy2(SRC, DST)

# Patch [Content_Types].xml to declare the file as xlsm
with zipfile.ZipFile(DST, "r") as zin:
    names  = zin.namelist()
    ct_raw = zin.read("[Content_Types].xml")

# Replace the xlsx content-type with xlsm
ct_new = ct_raw.replace(
    b'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml',
    b'application/vnd.ms-excel.sheet.macroEnabled.main+xml'
)

# Patch workbook content-type in Override entries too
ct_new = ct_new.replace(
    b'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"',
    b'ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"'
)

# Rebuild the zip with patched content types
buf = io.BytesIO()
with zipfile.ZipFile(DST, "r") as zin:
    with zipfile.ZipFile(buf, "w", zipfile.ZIP_DEFLATED) as zout:
        for item in zin.infolist():
            if item.filename == "[Content_Types].xml":
                zout.writestr(item, ct_new)
            else:
                zout.writestr(item, zin.read(item.filename))

with open(DST, "wb") as f:
    f.write(buf.getvalue())

sz = os.path.getsize(DST)
print(f"xlsm created: {DST}  ({sz:,} bytes)")
print(f"VBA companion: {bas_path}")
print("\nSetup complete!")
print("To activate macros: Open .xlsm in Excel → ALT+F11 → Insert Module → paste ShifaMacros.bas")
print("Then assign SubmitRecord to the SUBMIT button and ClearForm to the CLEAR button.")

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/shifa-consultation/inject_vba.py'

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/shifa-consultation/inject_vba.py'