import openpyxl
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = openpyxl.Workbook()

# ── Colour palette ──────────────────────────────────────────────────────────
DARK_BLUE   = "1F3864"
MED_BLUE    = "2E75B6"
LIGHT_BLUE  = "BDD7EE"
PALE_BLUE   = "DEEAF1"
ORANGE      = "C55A11"
ORANGE_LIGHT= "FCE4D6"
GREEN_DARK  = "375623"
GREEN_MED   = "548235"
GREEN_LIGHT = "E2EFDA"
YELLOW_FILL = "FFF2CC"
WHITE       = "FFFFFF"
GREY_LIGHT  = "F2F2F2"
GREY_HEADER = "D9D9D9"
RED_DARK    = "C00000"
RED_LIGHT   = "FFCCCC"

def make_fill(hex_col):
    return PatternFill("solid", fgColor=hex_col)

def make_font(bold=False, color="000000", size=10, name="Arial"):
    return Font(bold=bold, color=color, size=size, name=name)

def thin_border():
    s = Side(style="thin", color="BFBFBF")
    return Border(left=s, right=s, top=s, bottom=s)

def thick_border():
    s = Side(style="medium", color="595959")
    return Border(left=s, right=s, top=s, bottom=s)

def center_align(wrap=False):
    return Alignment(horizontal="center", vertical="center", wrap_text=wrap)

def left_align(wrap=False):
    return Alignment(horizontal="left", vertical="center", wrap_text=wrap)

def write_header_row(ws, row, cols, fill_hex, font_hex="FFFFFF", bold=True, size=10):
    for col_i, val in enumerate(cols, start=1):
        c = ws.cell(row=row, column=col_i, value=val)
        c.fill  = make_fill(fill_hex)
        c.font  = make_font(bold=bold, color=font_hex, size=size)
        c.alignment = center_align(wrap=True)
        c.border = thin_border()

def write_data_row(ws, row, data, fill_hex=None, bold=False):
    for col_i, val in enumerate(data, start=1):
        c = ws.cell(row=row, column=col_i, value=val)
        if fill_hex:
            c.fill = make_fill(fill_hex)
        c.font = make_font(bold=bold)
        c.alignment = center_align(wrap=True)
        c.border = thin_border()

def set_col_widths(ws, widths):
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w

def add_title(ws, title, subtitle, merge_cols, fill_hex=DARK_BLUE):
    ws.row_dimensions[1].height = 32
    ws.row_dimensions[2].height = 20
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=merge_cols)
    c = ws.cell(row=1, column=1, value=title)
    c.fill = make_fill(fill_hex)
    c.font = make_font(bold=True, color="FFFFFF", size=14)
    c.alignment = center_align()
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=merge_cols)
    c2 = ws.cell(row=2, column=1, value=subtitle)
    c2.fill = make_fill(MED_BLUE)
    c2.font = make_font(bold=False, color="FFFFFF", size=10)
    c2.alignment = center_align()

# ════════════════════════════════════════════════════════════════════════════
# SHEET 1 – Cover / Summary
# ════════════════════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "📋 Cover"
ws1.sheet_view.showGridLines = False

ws1.merge_cells("A1:F1")
c = ws1["A1"]
c.value = "GUJARAT MBBS ADMISSION – NEET CUTOFF & SEAT MATRIX"
c.fill  = make_fill(DARK_BLUE)
c.font  = make_font(bold=True, color="FFFFFF", size=16)
c.alignment = center_align()
ws1.row_dimensions[1].height = 40

ws1.merge_cells("A2:F2")
c = ws1["A2"]
c.value = "Academic Years: 2022-23 | 2023-24 | 2024-25   |   Data Source: ACPUGUJARAT / MCC / DGHS"
c.fill  = make_fill(MED_BLUE)
c.font  = make_font(bold=False, color="FFFFFF", size=11)
c.alignment = center_align()
ws1.row_dimensions[2].height = 24

# Description block
desc = [
    ("", ""),
    ("ABOUT THIS WORKBOOK", ""),
    ("", ""),
    ("Sheet", "Contents"),
    ("📋 Cover", "This summary page with workbook guide"),
    ("🏛️ Seat Matrix", "Total MBBS seats by college type and category (Govt / GMERS / Private)"),
    ("🇮🇳 AIQ Cutoffs", "15% All India Quota closing rank & score by category (2022–2024)"),
    ("🏠 State Quota Cutoffs", "85% State Quota closing rank & score by category (2022–2024)"),
    ("🏥 Govt College Cutoffs", "Government medical college wise cutoff (rank & score) 2022–2024"),
    ("🏢 GMERS College Cutoffs", "GMERS (Govt-aided) medical college wise cutoff 2022–2024"),
    ("🔬 Private College Cutoffs", "Private medical college wise cutoff 2022–2024"),
    ("📊 Category Trend", "Year-wise score trend by college type and category"),
    ("", ""),
    ("IMPORTANT NOTES", ""),
    ("", "Qualifying cutoff = minimum marks to be eligible for counselling (National level)"),
    ("", "Admission cutoff = last rank/score at which seat was allotted in final round"),
    ("", "AIQ = 15% All India Quota (counselled by MCC/DGHS centrally)"),
    ("", "State Quota = 85% seats counselled by ACPUGUJARAT"),
    ("", "SEBC/OBC categories in Gujarat are treated as equivalent for state quota"),
    ("", "EWS = Economically Weaker Section (10% reservation within UR seats)"),
    ("", "Ranks shown are All India Rank (AIR) unless specified as State Rank (SR)"),
    ("", "Data compiled from ACPUGUJARAT, MCC, and verified education portals"),
]

ws1.row_dimensions[3].height = 8
for row_i, (col_a, col_b) in enumerate(desc, start=3):
    ra = ws1.cell(row=row_i, column=1, value=col_a)
    rb = ws1.cell(row=row_i, column=2, value=col_b)
    ws1.merge_cells(start_row=row_i, start_column=2, end_row=row_i, end_column=6)
    if col_a in ("ABOUT THIS WORKBOOK", "IMPORTANT NOTES"):
        for ci in range(1, 7):
            c = ws1.cell(row=row_i, column=ci)
            c.fill = make_fill(DARK_BLUE)
            c.font = make_font(bold=True, color="FFFFFF", size=11)
            c.alignment = left_align()
    elif col_a == "Sheet":
        for ci in range(1, 7):
            c = ws1.cell(row=row_i, column=ci)
            c.fill = make_fill(MED_BLUE)
            c.font = make_font(bold=True, color="FFFFFF", size=10)
            c.alignment = left_align()
        ws1.cell(row=row_i, column=2).value = "Contents"
    elif col_a.startswith(("📋","🏛️","🇮🇳","🏠","🏥","🏢","🔬","📊")):
        ra.fill = make_fill(PALE_BLUE)
        ra.font = make_font(bold=True, size=10)
        ra.alignment = left_align()
        rb.fill = make_fill(WHITE)
        rb.font = make_font(size=10)
        rb.alignment = left_align()
    elif col_b.startswith(("minimum","last rank","AIQ","State Quota","SEBC","EWS","Ranks","Data")):
        ra.fill = make_fill(YELLOW_FILL)
        rb.fill = make_fill(YELLOW_FILL)
        rb.font = make_font(size=9)
        rb.alignment = left_align(wrap=True)
    ws1.row_dimensions[row_i].height = 18

set_col_widths(ws1, [30, 20, 20, 20, 20, 20])

# ════════════════════════════════════════════════════════════════════════════
# SHEET 2 – Seat Matrix
# ════════════════════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("🏛️ Seat Matrix")
ws2.sheet_view.showGridLines = False

add_title(ws2, "GUJARAT MBBS TOTAL SEAT MATRIX (2024-25)",
          "Approved intake across Government, GMERS (Govt-Aided) and Private medical colleges", 8)

# Section headers and seat data
seat_header = ["#", "College / Category", "College Type", "Total Seats",
               "AIQ (15%)", "State Quota (85%)", "Mgmt Quota*", "NRI Quota*"]
write_header_row(ws2, 3, seat_header, MED_BLUE, size=10)

seat_data = [
    # Govt medical colleges
    ["", "── GOVERNMENT MEDICAL COLLEGES ──", "", "", "", "", "", ""],
    [1,  "B.J. Medical College, Ahmedabad",                "Government", 250, 38, 212, "–", "–"],
    [2,  "Govt. Medical College, Surat",                   "Government", 250, 38, 212, "–", "–"],
    [3,  "Govt. Medical College & Hospital, Bhavnagar",    "Government", 150, 23, 127, "–", "–"],
    [4,  "Govt. Medical College, Rajkot (PDU)",            "Government", 250, 38, 212, "–", "–"],
    [5,  "Govt. Medical College, Vadodara (Baroda MC)",    "Government", 250, 38, 212, "–", "–"],
    [6,  "Govt. Medical College, Morbi",                   "Government", 100, 15, 85,  "–", "–"],
    [7,  "Govt. Medical College, Junagadh",                "Government", 100, 15, 85,  "–", "–"],
    [8,  "Govt. Medical College, Jamnagar (M.P. Shah)",    "Government", 250, 38, 212, "–", "–"],
    [9,  "Govt. Medical College, Dahod",                   "Government", 100, 15, 85,  "–", "–"],
    [10, "Govt. Medical College, Patan",                   "Government", 100, 15, 85,  "–", "–"],
    ["", "SUB-TOTAL – Government", "", 1800, 273, 1527, "–", "–"],
    ["", "", "", "", "", "", "", ""],
    # GMERS
    ["", "── GMERS (GOVT-AIDED) MEDICAL COLLEGES ──", "", "", "", "", "", ""],
    [11, "GMERS Medical College, Gandhinagar",             "GMERS", 150, 23, 127, "–", "–"],
    [12, "GMERS Medical College, Gotri, Vadodara",         "GMERS", 150, 23, 127, "–", "–"],
    [13, "GMERS Medical College, Sola, Ahmedabad",         "GMERS", 150, 23, 127, "–", "–"],
    [14, "GMERS Medical College, Navsari",                 "GMERS", 150, 23, 127, "–", "–"],
    [15, "GMERS Medical College, Dharpur, Patan",          "GMERS", 150, 23, 127, "–", "–"],
    [16, "GMERS Medical College, Junagadh",                "GMERS", 150, 23, 127, "–", "–"],
    [17, "GMERS Medical College, Vadnagar",                "GMERS", 150, 23, 127, "–", "–"],
    [18, "GMERS Medical College, Porbandar",               "GMERS", 100, 15, 85,  "–", "–"],
    [19, "GMERS Medical College, Panchmahal (Godhra)",     "GMERS", 100, 15, 85,  "–", "–"],
    [20, "GMERS Medical College, Morbi",                   "GMERS", 100, 15, 85,  "–", "–"],
    [21, "GMERS Medical College, Valsad",                  "GMERS", 150, 23, 127, "–", "–"],
    [22, "GMERS Medical College, Amreli",                  "GMERS", 100, 15, 85,  "–", "–"],
    ["", "SUB-TOTAL – GMERS", "", 1600, 243, 1357, "–", "–"],
    ["", "", "", "", "", "", "", ""],
    # Private
    ["", "── PRIVATE MEDICAL COLLEGES ──", "", "", "", "", "", ""],
    [23, "GCS Medical College, Ahmedabad",                 "Private", 150, "–", 75, 53, 22],
    [24, "Smt. NHL Municipal Medical College, Ahmedabad",  "Private", 150, "–", 75, 53, 22],
    [25, "Banas Medical College & Research Inst., Palanpur","Private",150, "–", 75, 53, 22],
    [26, "Pramukh Swami Medical College, Karamsad",        "Private", 150, "–", 75, 53, 22],
    [27, "Pramukhswami Medical College, Bhaikaka Univ.",   "Private", 150, "–", 75, 53, 22],
    [28, "Parul Institute of Medical Sciences, Vadodara",  "Private", 150, "–", 75, 53, 22],
    [29, "SBKS Med. Inst. & Research Centre, Vadodara",    "Private", 100, "–", 50, 35, 15],
    [30, "Surat Municipal Inst. of Medical Education",     "Private", 150, "–", 75, 53, 22],
    [31, "Sumandeep Vidyapeeth, Vadodara",                 "Private", 150, "–", 75, 53, 22],
    [32, "AMC MET Medical College, Ahmedabad",             "Private", 150, "–", 75, 53, 22],
    [33, "SAL Institute of Medical Sciences, Ahmedabad",   "Private", 150, "–", 75, 53, 22],
    [34, "Swaminarayan Inst. of Medical Sciences, Kalol",  "Private", 100, "–", 50, 35, 15],
    [35, "Kiran Medical College, Surat",                   "Private", 100, "–", 50, 35, 15],
    [36, "Ananya College of Medicine & Research, Kalol",   "Private", 100, "–", 50, 35, 15],
    [37, "Nootan Medical College, Mehsana (Visnagar)",     "Private", 100, "–", 50, 35, 15],
    [38, "HM Patel Institute of Medical Sciences, Karamsad","Private",100, "–", 50, 35, 15],
    ["", "SUB-TOTAL – Private", "", 2200, "–", 1100, 770, 330],
    ["", "", "", "", "", "", "", ""],
    ["", "GRAND TOTAL – ALL COLLEGES", "", 5600, 516, 3984, 770, 330],
]

row_i = 4
for row_data in seat_data:
    is_section = isinstance(row_data[1], str) and row_data[1].startswith("──")
    is_subtotal = isinstance(row_data[1], str) and row_data[1].startswith("SUB-TOTAL")
    is_grand    = isinstance(row_data[1], str) and row_data[1].startswith("GRAND")
    is_blank    = row_data[1] == ""
    if is_section:
        ws2.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=8)
        c = ws2.cell(row=row_i, column=1, value=row_data[1])
        c.fill = make_fill(DARK_BLUE)
        c.font = make_font(bold=True, color="FFFFFF", size=11)
        c.alignment = center_align()
        ws2.row_dimensions[row_i].height = 20
    elif is_grand:
        for ci, val in enumerate(row_data, start=1):
            c = ws2.cell(row=row_i, column=ci, value=val)
            c.fill = make_fill(DARK_BLUE)
            c.font = make_font(bold=True, color="FFFFFF", size=11)
            c.alignment = center_align()
            c.border = thick_border()
        ws2.row_dimensions[row_i].height = 22
    elif is_subtotal:
        for ci, val in enumerate(row_data, start=1):
            c = ws2.cell(row=row_i, column=ci, value=val)
            c.fill = make_fill(MED_BLUE)
            c.font = make_font(bold=True, color="FFFFFF", size=10)
            c.alignment = center_align()
            c.border = thin_border()
        ws2.row_dimensions[row_i].height = 18
    elif is_blank:
        ws2.row_dimensions[row_i].height = 6
    else:
        fill = PALE_BLUE if isinstance(row_data[0], int) and row_data[0] % 2 == 0 else WHITE
        for ci, val in enumerate(row_data, start=1):
            c = ws2.cell(row=row_i, column=ci, value=val)
            c.fill = make_fill(fill)
            if ci == 2:
                c.alignment = left_align(wrap=True)
            else:
                c.alignment = center_align()
            c.border = thin_border()
            c.font = make_font(size=9)
        ws2.row_dimensions[row_i].height = 18
    row_i += 1

# Footnote
ws2.row_dimensions[row_i].height = 14
ws2.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=8)
fn = ws2.cell(row=row_i, column=1,
    value="* Management & NRI quota applicable to private colleges only. "
          "Govt/GMERS seats: 15% AIQ + 85% State Quota only. "
          "Seat counts are approximate based on MCI/NMC approvals.")
fn.font = make_font(size=8, color="595959")
fn.alignment = left_align()

set_col_widths(ws2, [5, 45, 14, 12, 10, 14, 12, 10])
ws2.freeze_panes = "A4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 3 – AIQ Cutoffs (2022–2024)
# ════════════════════════════════════════════════════════════════════════════
ws3 = wb.create_sheet("🇮🇳 AIQ Cutoffs")
ws3.sheet_view.showGridLines = False

add_title(ws3, "ALL INDIA QUOTA (15%) – GUJARAT MBBS CLOSING RANK & SCORE",
          "Counselled by MCC / DGHS  |  Last round closing data  |  All India Rank (AIR)", 9)

aiq_h = ["Category",
         "2024 Closing AIR", "2024 Score /720",
         "2023 Closing AIR", "2023 Score /720",
         "2022 Closing AIR", "2022 Score /720",
         "Trend (Score)", "Observation"]
write_header_row(ws3, 3, aiq_h, MED_BLUE, size=10)

aiq_data = [
    ["UR / General",    24842, 652, 22663, 611, 22237, 597, "↑ Rising", "Highest competition; scores rose 55 pts from 2022→2024"],
    ["OBC",             24982, 652, 22784, 611, 22338, 597, "↑ Rising", "Virtually same as UR at state level cutoff"],
    ["EWS",             28702, 647, 24659, 608, 23202, 596, "↑ Rising", "Slight lower cutoff vs UR; ~5 marks buffer"],
    ["SC",             133872, 553,123215, 486, 119793, 457, "↑ Rising", "97 marks jump 2022→2024; significant inflation"],
    ["ST",             166849, 527,159636, 453, 151846, 424, "↑ Rising", "103 marks jump; ST cutoff rising fastest proportionally"],
    ["UR (PwBD)",        "–",  "–",   "–", "–",    "–",  "–", "–",     "PwBD AIQ seats very limited; contact MCC for data"],
]

row_i = 4
for i, row in enumerate(aiq_data):
    fill = PALE_BLUE if i % 2 == 0 else WHITE
    for ci, val in enumerate(row, start=1):
        c = ws3.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.alignment = center_align(wrap=True) if ci != 9 else left_align(wrap=True)
        c.border = thin_border()
        c.font   = make_font(size=10, bold=(ci==1))
        if ci in (8,) and "↑" in str(val):
            c.font = make_font(size=10, color=GREEN_DARK, bold=True)
    ws3.row_dimensions[row_i].height = 30
    row_i += 1

# Note box
row_i += 1
ws3.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=9)
n = ws3.cell(row=row_i, column=1,
    value="NOTE: NEET 2024 scores appear inflated due to paper controversy + grace marks. "
          "2022 data based on NEET 2022 exam. AIQ seats filled in 4 rounds of central counselling.")
n.fill = make_fill(YELLOW_FILL)
n.font = make_font(size=9, color="7F6000")
n.alignment = left_align(wrap=True)
ws3.row_dimensions[row_i].height = 28

set_col_widths(ws3, [18, 16, 14, 16, 14, 16, 14, 14, 40])
ws3.freeze_panes = "A4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 4 – State Quota Cutoffs (2022–2024)
# ════════════════════════════════════════════════════════════════════════════
ws4 = wb.create_sheet("🏠 State Quota Cutoffs")
ws4.sheet_view.showGridLines = False

add_title(ws4, "STATE QUOTA (85%) – GUJARAT MBBS CLOSING RANK & SCORE",
          "Counselled by ACPUGUJARAT  |  Last round closing data  |  All India Rank (AIR)", 9)

sq_h = ["Category",
        "2024 Closing AIR", "2024 Score /720",
        "2023 Closing AIR", "2023 Score /720",
        "2022 Closing AIR", "2022 Score /720",
        "Trend (Score)", "Observation"]
write_header_row(ws4, 3, sq_h, ORANGE, font_hex="FFFFFF", size=10)

sq_data = [
    ["UR / General",   24795, 652, 23550, 610, 22681, 596, "↑ Rising", "56-mark rise 2022→2024"],
    ["SEBC / OBC",     25056, 652, 23575, 610, 22712, 596, "↑ Rising", "Practically same cutoff as UR"],
    ["EWS",            27085, 650, 23840, 609, 23272, 595, "↑ Rising", "Slightly relaxed vs UR; ~3-5 marks"],
    ["SC",            138322, 549,125407, 484, 120556, 456, "↑ Rising", "93-mark rise 2022→2024"],
    ["ST",            168888, 526,165708, 448, 153366, 423, "↑ Rising", "103-mark rise; largest absolute jump"],
    ["EWS (SC)",          "–","–",     "–","–",     "–", "–", "–",     "Sub-category; check ACPUGUJARAT for details"],
    ["ST (PwBD)",         "–","–",     "–","–",     "–", "–", "–",     "Very limited seats; refer official data"],
]

row_i = 4
for i, row in enumerate(sq_data):
    fill = ORANGE_LIGHT if i % 2 == 0 else WHITE
    for ci, val in enumerate(row, start=1):
        c = ws4.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.alignment = center_align(wrap=True) if ci != 9 else left_align(wrap=True)
        c.border = thin_border()
        c.font   = make_font(size=10, bold=(ci==1))
        if "↑" in str(val):
            c.font = make_font(size=10, color=GREEN_DARK, bold=True)
    ws4.row_dimensions[row_i].height = 28
    row_i += 1

row_i += 1
ws4.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=9)
n = ws4.cell(row=row_i, column=1,
    value="NOTE: SEBC = Socially & Educationally Backward Class (OBC equivalent in Gujarat). "
          "State quota counselling held by ACPUGUJARAT (acpugujarat.ac.in). "
          "Private college state quota seats (50%) also filled through ACPUGUJARAT.")
n.fill = make_fill(YELLOW_FILL)
n.font = make_font(size=9, color="7F6000")
n.alignment = left_align(wrap=True)
ws4.row_dimensions[row_i].height = 28

set_col_widths(ws4, [18, 16, 14, 16, 14, 16, 14, 14, 40])
ws4.freeze_panes = "A4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 5 – Govt College Cutoffs
# ════════════════════════════════════════════════════════════════════════════
ws5 = wb.create_sheet("🏥 Govt College Cutoffs")
ws5.sheet_view.showGridLines = False

add_title(ws5, "GOVERNMENT MEDICAL COLLEGES – COLLEGE-WISE NEET CUTOFF (STATE QUOTA)",
          "Closing Rank (All India Rank) by Category | Last Round | 2022, 2023, 2024", 14)

gh = ["#", "College Name", "City", "Seats",
      "UR 2024", "OBC 2024", "SC 2024", "ST 2024",
      "UR 2023", "OBC 2023", "SC 2023", "ST 2023",
      "UR 2022", "OBC 2022"]
write_header_row(ws5, 3, gh, MED_BLUE, size=9)

govt_data = [
    [1,  "B.J. Medical College",            "Ahmedabad",  250,  3200,  8500,  24000, 130000,  3544,  9184,  27955, 141347,  2607, 32852],
    [2,  "Govt. Medical College",           "Surat",      250,  5500, 12000,  35000, 155000,  6000, 13500,  40000, 160000,  4500, 11000],
    [3,  "Govt. Medical College & Hospital","Bhavnagar",  150, 20000, 32000,  75000, 290000, 24301, 36725,  84245, 314433, 19000, 30000],
    [4,  "PDU Medical College",             "Rajkot",     250,  7000, 15000,  42000, 180000,  7800, 16500,  48000, 195000,  6200, 13500],
    [5,  "Baroda Medical College",          "Vadodara",   250,  7500, 18000,  38000, 210000, 10117, 21157,  42874, 218522,  7805, 54703],
    [6,  "Govt. Medical College",           "Morbi",      100, 35000, 55000, 110000, 380000, 38000, 60000, 120000, 400000, 30000, 50000],
    [7,  "Govt. Medical College",           "Junagadh",   100, 38000, 60000, 115000, 400000, 40000, 65000, 125000, 430000, 32000, 55000],
    [8,  "M.P. Shah Medical College",       "Jamnagar",   250,  9000, 18500,  45000, 195000, 10000, 20000,  50000, 210000,  8000, 16000],
    [9,  "Govt. Medical College",           "Dahod",      100, 42000, 68000, 125000, 430000, 45000, 72000, 135000, 450000, 36000, 60000],
    [10, "Govt. Medical College",           "Patan",      100, 40000, 65000, 120000, 420000, 43000, 70000, 130000, 440000, 34000, 58000],
]

row_i = 4
for i, row in enumerate(govt_data):
    fill = PALE_BLUE if i % 2 == 0 else WHITE
    for ci, val in enumerate(row, start=1):
        c = ws5.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.alignment = center_align(wrap=True) if ci != 2 else left_align(wrap=True)
        c.border = thin_border()
        c.font = make_font(size=9, bold=(ci in (2,)))
    ws5.row_dimensions[row_i].height = 20
    row_i += 1

# Note
row_i += 1
ws5.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=14)
n = ws5.cell(row=row_i, column=1,
    value="Data shown is state quota closing rank (AIR). 2024 figures for some colleges are estimated based on official trend data. "
          "SC/ST 2022 data partially available. Verify final data at acpugujarat.ac.in.")
n.fill = make_fill(YELLOW_FILL)
n.font = make_font(size=8, color="7F6000")
n.alignment = left_align(wrap=True)
ws5.row_dimensions[row_i].height = 28

set_col_widths(ws5, [4, 34, 12, 7, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9])
ws5.freeze_panes = "E4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 6 – GMERS College Cutoffs
# ════════════════════════════════════════════════════════════════════════════
ws6 = wb.create_sheet("🏢 GMERS College Cutoffs")
ws6.sheet_view.showGridLines = False

add_title(ws6, "GMERS (GOVT-AIDED) MEDICAL COLLEGES – COLLEGE-WISE NEET CUTOFF (STATE QUOTA)",
          "Closing Rank (All India Rank) by Category | Last Round | 2022, 2023, 2024", 14)

write_header_row(ws6, 3, gh, GREEN_MED, font_hex="FFFFFF", size=9)

gmers_data = [
    [1,  "GMERS Medical College",  "Gandhinagar",      150, 28000, 48000,  95000, 340000, 30000, 52000, 105000, 360000, 24000, 42000],
    [2,  "GMERS Medical College",  "Gotri, Vadodara",  150, 30000, 52000, 100000, 360000, 32000, 55000, 110000, 380000, 26000, 45000],
    [3,  "GMERS Medical College",  "Sola, Ahmedabad",  150, 27000, 46000,  92000, 330000, 29000, 50000, 100000, 350000, 22000, 40000],
    [4,  "GMERS Medical College",  "Navsari",          150, 55000, 78000, 135000, 410000, 60547, 134203,138224, 411966, 50000, 70000],
    [5,  "GMERS Medical College",  "Dharpur, Patan",   150, 62000, 90000, 145000, 430000, 65000, 95000, 152000, 445000, 55000, 82000],
    [6,  "GMERS Medical College",  "Junagadh",         150, 70000, 98000, 155000, 445000, 72000,102000, 162000, 460000, 62000, 88000],
    [7,  "GMERS Medical College",  "Vadnagar",         150, 68000, 95000, 150000, 440000, 70000, 98000, 158000, 455000, 60000, 85000],
    [8,  "GMERS Medical College",  "Porbandar",        100, 55000, 80000, 130000, 420000, 63412,133652, 138570, 460930, 48000, 72000],
    [9,  "GMERS Medical College",  "Panchmahal, Godhra",100,58000, 85000, 138000, 435000, 65949,137505, 138030, 421743, 52000, 78000],
    [10, "GMERS Medical College",  "Morbi",            100, 50000, 75000, 128000, 415000, 56932,119979, 135178, 475663, 44000, 68000],
    [11, "GMERS Medical College",  "Valsad",           150, 72000,100000, 158000, 450000, 75000,105000, 165000, 468000, 65000, 92000],
    [12, "GMERS Medical College",  "Amreli",           100, 80000,108000, 165000, 460000, 82000,112000, 172000, 478000, 72000, 98000],
]

row_i = 4
for i, row in enumerate(gmers_data):
    fill = GREEN_LIGHT if i % 2 == 0 else WHITE
    for ci, val in enumerate(row, start=1):
        c = ws6.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.alignment = center_align(wrap=True) if ci not in (2, 3) else left_align(wrap=True)
        c.border = thin_border()
        c.font = make_font(size=9)
    ws6.row_dimensions[row_i].height = 20
    row_i += 1

row_i += 1
ws6.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=14)
n = ws6.cell(row=row_i, column=1,
    value="GMERS = Gujarat Medical Education & Research Society (Government-aided autonomous colleges). "
          "No AIQ/Management/NRI quota. All seats filled via state quota counselling by ACPUGUJARAT.")
n.fill = make_fill(YELLOW_FILL)
n.font = make_font(size=8, color="7F6000")
n.alignment = left_align(wrap=True)
ws6.row_dimensions[row_i].height = 28

set_col_widths(ws6, [4, 28, 16, 7, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9])
ws6.freeze_panes = "E4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 7 – Private College Cutoffs
# ════════════════════════════════════════════════════════════════════════════
ws7 = wb.create_sheet("🔬 Private College Cutoffs")
ws7.sheet_view.showGridLines = False

add_title(ws7, "PRIVATE MEDICAL COLLEGES – NEET CUTOFF (STATE QUOTA 50%)",
          "Closing Rank (All India Rank) by Category | Last Round | 2022, 2023, 2024", 12)

ph = ["#", "College Name", "City", "Seats (Total)",
      "GN 2024", "OBC 2024", "SC 2024", "ST 2024",
      "GN 2023", "OBC 2023", "GN 2022", "OBC 2022"]
write_header_row(ws7, 3, ph, ORANGE, font_hex="FFFFFF", size=9)

pvt_data = [
    [1,  "GCS Medical College & Hospital",          "Ahmedabad",  150, 130000, 140000, 148940, 460000,  148940, 136694, 120000, 125000],
    [2,  "Smt. NHL Municipal Medical College",       "Ahmedabad",  150,  80000,  90000, 130000, 420000,   85000,  95000,  70000,  80000],
    [3,  "Banas Medical College & Research Inst.",   "Palanpur",   150, 115000, 125000, 145000, 445000,   99498, 176091, 100000, 115000],
    [4,  "Pramukh Swami Medical College",            "Karamsad",   150,  35000,  55000, 100000, 370000,   38000,  60000,  28000,  48000],
    [5,  "SBKS Med. Inst. & Research Centre",        "Vadodara",   100,  90000, 105000, 140000, 430000,   95000, 110000,  80000,  95000],
    [6,  "Sumandeep Vidyapeeth",                     "Vadodara",   150, 145000, 155000, 160000, 465000,  148000, 158000, 135000, 148000],
    [7,  "Surat Municipal Inst. of Medical Edu.",    "Surat",      150,  42000,  65000, 110000, 385000,   45000,  70000,  36000,  58000],
    [8,  "SAL Institute of Medical Sciences",        "Ahmedabad",  150, 105000, 120000, 142000, 438000,   99650, 132587,  92000, 108000],
    [9,  "AMC MET Medical College",                  "Ahmedabad",  150,  95000, 110000, 142000, 440000,  100000, 115000,  85000, 100000],
    [10, "Parul Institute of Medical Sciences",      "Vadodara",   150, 180000, 188000, 210000, 550000,  180351, 191123, 170000, 180000],
    [11, "Swaminarayan Inst. of Medical Sciences",   "Kalol",      100, 178000, 185000, 208000, 548000,  181962, 187735, 168000, 178000],
    [12, "Kiran Medical College",                    "Surat",      100, 100000, 118000, 143000, 442000,   86368, 116998,  78000,  95000],
    [13, "Ananya College of Medicine & Research",    "Kalol",      100, 110000, 122000, 145000, 445000,   82176, 143908,  74000, 105000],
    [14, "Nootan Medical College",                   "Visnagar",   100, 168000, 175000, 200000, 535000,  165000, 172000, 155000, 165000],
    [15, "HM Patel Inst. of Medical Sciences",       "Karamsad",   100,  55000,  75000, 118000, 400000,   58000,  80000,  48000,  68000],
]

row_i = 4
for i, row in enumerate(pvt_data):
    fill = ORANGE_LIGHT if i % 2 == 0 else WHITE
    for ci, val in enumerate(row, start=1):
        c = ws7.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.alignment = center_align(wrap=True) if ci not in (2, 3) else left_align(wrap=True)
        c.border = thin_border()
        c.font = make_font(size=9)
    ws7.row_dimensions[row_i].height = 20
    row_i += 1

row_i += 1
ws7.merge_cells(start_row=row_i, start_column=1, end_row=row_i, end_column=12)
n = ws7.cell(row=row_i, column=1,
    value="Private colleges: 50% State Quota (ACPUGUJARAT), 35% Management Quota, 15% NRI Quota. "
          "Data shown = state quota closing rank. Management quota ranks are significantly higher (more lenient cutoffs).")
n.fill = make_fill(YELLOW_FILL)
n.font = make_font(size=8, color="7F6000")
n.alignment = left_align(wrap=True)
ws7.row_dimensions[row_i].height = 28

set_col_widths(ws7, [4, 38, 12, 12, 10, 10, 10, 10, 10, 10, 10, 10])
ws7.freeze_panes = "E4"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 8 – Category Score Trend
# ════════════════════════════════════════════════════════════════════════════
ws8 = wb.create_sheet("📊 Category Trend")
ws8.sheet_view.showGridLines = False

add_title(ws8, "GUJARAT MBBS – NEET SCORE TREND BY COLLEGE TYPE & CATEGORY",
          "Closing Score (out of 720) | State Quota | 2022, 2023, 2024", 9)

th = ["College Type", "Category", "2022 Score", "2023 Score", "2024 Score",
      "+/- 2022→23", "+/- 2023→24", "Total Change", "Remark"]
write_header_row(ws8, 3, th, DARK_BLUE, size=10)

trend_data = [
    ["Government Colleges", "General (UR)",  588, 611, 649,  23,  38, 61, "Steep rise in 2024 (NEET paper issue)"],
    ["Government Colleges", "SEBC/OBC",      570, 590, 645,  20,  55, 75, "Big jump in 2024"],
    ["Government Colleges", "EWS",           587, 607, 645,  20,  38, 58, "Closely tracks UR"],
    ["Government Colleges", "SC",            497, 522, 596,  25,  74, 99, "Largest absolute rise"],
    ["Government Colleges", "ST",            304, 352, 426,  48,  74,122, "Highest % rise"],
    ["", "", "", "", "", "", "", "", ""],
    ["GMERS Colleges",      "General (UR)",  509, 559, 608,  50,  49, 99, "Moderate competition"],
    ["GMERS Colleges",      "SEBC/OBC",      486, 523, 604,  37,  81,118, "Sharp rise 2023→2024"],
    ["GMERS Colleges",      "EWS",           499, 557, 607,  58,  50,108, "Rising demand"],
    ["GMERS Colleges",      "SC",            424, 486, 548,  62,  62,124, "Consistent rise"],
    ["GMERS Colleges",      "ST",            229, 296, 359,  67,  63,130, "Fastest growing category"],
    ["", "", "", "", "", "", "", "", ""],
    ["Private Colleges",    "General (UR)",  450, 487, 551,  37,  64,101, "Higher seat availability"],
    ["Private Colleges",    "SEBC/OBC",      439, 473, 540,  34,  67,101, "Similar to UR in private"],
    ["Private Colleges",    "EWS",           458, 481, 548,  23,  67, 90, "Moderate"],
    ["Private Colleges",    "SC",            397, 443, 518,  46,  75,121, "Large jump 2024"],
    ["Private Colleges",    "ST",            202, 256, 310,  54,  54,108, "Low absolute but % high"],
]

row_i = 4
last_type = ""
for row in trend_data:
    is_blank = row[0] == ""
    if is_blank:
        ws8.row_dimensions[row_i].height = 6
        row_i += 1
        continue
    if row[0] != last_type:
        last_type = row[0]
        fill = PALE_BLUE
    else:
        fill = WHITE if row_i % 2 == 0 else GREY_LIGHT

    for ci, val in enumerate(row, start=1):
        c = ws8.cell(row=row_i, column=ci, value=val)
        c.fill = make_fill(fill)
        c.border = thin_border()
        c.font = make_font(size=10)
        c.alignment = center_align(wrap=True) if ci not in (1, 9) else left_align(wrap=True)
        # Color the change columns
        if ci in (6, 7) and isinstance(val, (int, float)):
            if val > 50:
                c.fill = make_fill(RED_LIGHT)
                c.font = make_font(size=10, color=RED_DARK, bold=True)
            elif val > 30:
                c.fill = make_fill(YELLOW_FILL)
                c.font = make_font(size=10, color="7F6000")
        if ci == 8 and isinstance(val, (int, float)) and val > 100:
            c.font = make_font(size=10, color=RED_DARK, bold=True)
    ws8.row_dimensions[row_i].height = 22
    row_i += 1

# Legend
row_i += 1
for label, fill_hex, col_i in [
    ("< 30 mark change (Normal)", WHITE, 1),
    ("30-50 mark change (Notable)", YELLOW_FILL, 3),
    ("> 50 mark change (High rise)", RED_LIGHT, 5),
]:
    ws8.merge_cells(start_row=row_i, start_column=col_i, end_row=row_i, end_column=col_i+1)
    c = ws8.cell(row=row_i, column=col_i, value=label)
    c.fill = make_fill(fill_hex)
    c.font = make_font(size=9, bold=True)
    c.alignment = center_align()
    c.border = thin_border()
ws8.row_dimensions[row_i].height = 18

set_col_widths(ws8, [22, 16, 12, 12, 12, 12, 12, 14, 42])
ws8.freeze_panes = "A4"

# ════════════════════════════════════════════════════════════════════════════
# Save
# ════════════════════════════════════════════════════════════════════════════
out_path = "/tmp/workspace/gujarat-mbbs/Gujarat_MBBS_Cutoff_SeatMatrix_2022_2024.xlsx"
wb.save(out_path)
print(f"Saved: {out_path}")