#!/usr/bin/env python3
from docx import Document
from docx.shared import Pt, Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT

doc = Document()

# Title
title = doc.add_heading('Excel Live Coding Interview Guide', 0)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER
subtitle = doc.add_paragraph('Essential Functions & VBA for Mid-Level Data Analysts')
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER

# ============ TABLE OF CONTENTS ============
doc.add_heading('Contents', level=1)
toc_items = [
    '1. Lookup & Reference Functions',
    '2. Logical Functions',
    '3. Text Functions',
    '4. Date & Time Functions',
    '5. Statistical & Math Functions',
    '6. Array Functions (Dynamic Arrays)',
    '7. Pivot Tables',
    '8. Data Validation & Conditional Formatting',
    '9. VBA Fundamentals',
    '10. Common Interview Scenarios',
]
for item in toc_items:
    doc.add_paragraph(item, style='List Bullet')

# ============ 1. LOOKUP & REFERENCE ============
doc.add_page_break()
doc.add_heading('1. Lookup & Reference Functions', level=1)

# VLOOKUP
doc.add_heading('VLOOKUP', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Look up a value in the first column of a range and return a value from another column.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=VLOOKUP(A2, Products!$A$2:$D$100, 3, FALSE)', style='List Bullet')
doc.add_paragraph('Looks up value in A2, searches Products table, returns 3rd column, exact match', style='List Bullet')

p = doc.add_paragraph()
p.add_run('Key Points: ').bold = True
doc.add_paragraph('FALSE = exact match (most common), TRUE = approximate match', style='List Bullet')
doc.add_paragraph('Can only look LEFT to RIGHT (limitation)', style='List Bullet')
doc.add_paragraph('Use $ signs to lock the table range when copying formula', style='List Bullet')

# HLOOKUP
doc.add_heading('HLOOKUP', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Same as VLOOKUP but searches horizontally (in rows instead of columns).')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])')

# INDEX/MATCH
doc.add_heading('INDEX/MATCH (Superior to VLOOKUP)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('More flexible lookup - can look in any direction, less prone to errors.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=INDEX(C2:C100, MATCH(A2, B2:B100, 0))', style='List Bullet')
doc.add_paragraph('Find A2 in column B, return corresponding value from column C', style='List Bullet')

p = doc.add_paragraph()
p.add_run('Why INDEX/MATCH > VLOOKUP: ').bold = True
doc.add_paragraph('Can look LEFT (VLOOKUP cannot)', style='List Bullet')
doc.add_paragraph('Inserting columns won\'t break the formula', style='List Bullet')
doc.add_paragraph('Faster on large datasets', style='List Bullet')
doc.add_paragraph('More flexible - lookup and return ranges are independent', style='List Bullet')

# Two-way lookup
doc.add_heading('Two-Way Lookup (INDEX/MATCH/MATCH)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Look up value at intersection of row and column.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=INDEX(B2:M13, MATCH("March", A2:A13, 0), MATCH("Sales", B1:M1, 0))', style='List Bullet')

# XLOOKUP
doc.add_heading('XLOOKUP (Excel 365/2021+)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Modern replacement for VLOOKUP/HLOOKUP/INDEX-MATCH.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=XLOOKUP(A2, Products!A:A, Products!C:C, "Not Found")', style='List Bullet')

p = doc.add_paragraph()
p.add_run('Advantages: ').bold = True
doc.add_paragraph('Default is exact match (no FALSE needed)', style='List Bullet')
doc.add_paragraph('Can return multiple columns', style='List Bullet')
doc.add_paragraph('Built-in error handling (if_not_found)', style='List Bullet')
doc.add_paragraph('Can search from bottom up', style='List Bullet')

# ============ 2. LOGICAL FUNCTIONS ============
doc.add_page_break()
doc.add_heading('2. Logical Functions', level=1)

# IF
doc.add_heading('IF', level=2)
p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=IF(logical_test, value_if_true, value_if_false)')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F")))', style='List Bullet')

# IFS
doc.add_heading('IFS (Excel 2019+)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Multiple conditions without nesting.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=IFS(condition1, value1, condition2, value2, ...)')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")', style='List Bullet')

# AND/OR/NOT
doc.add_heading('AND / OR / NOT', level=2)
doc.add_paragraph('=AND(condition1, condition2, ...) - TRUE if ALL conditions are true', style='List Bullet')
doc.add_paragraph('=OR(condition1, condition2, ...) - TRUE if ANY condition is true', style='List Bullet')
doc.add_paragraph('=NOT(condition) - Reverses TRUE/FALSE', style='List Bullet')

p = doc.add_paragraph()
p.add_run('Combined Example: ').bold = True
doc.add_paragraph('=IF(AND(B2="Sales", C2>10000), "Bonus", "No Bonus")', style='List Bullet')

# IFERROR / IFNA
doc.add_heading('IFERROR / IFNA', level=2)
p = doc.add_paragraph()
p.add_run('IFERROR: ').bold = True
p.add_run('Catches any error and returns alternative value.')
doc.add_paragraph('=IFERROR(VLOOKUP(A2, Data, 2, FALSE), "Not Found")', style='List Bullet')

p = doc.add_paragraph()
p.add_run('IFNA: ').bold = True
p.add_run('Catches only #N/A errors (more specific).')
doc.add_paragraph('=IFNA(VLOOKUP(A2, Data, 2, FALSE), 0)', style='List Bullet')

# SWITCH
doc.add_heading('SWITCH (Excel 2019+)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Compare one value against multiple values.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=SWITCH(expression, value1, result1, value2, result2, ..., [default])')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=SWITCH(A2, 1, "Jan", 2, "Feb", 3, "Mar", "Unknown")', style='List Bullet')

# ============ 3. TEXT FUNCTIONS ============
doc.add_page_break()
doc.add_heading('3. Text Functions', level=1)

text_funcs = [
    ('LEFT(text, num_chars)', 'Extract characters from left', '=LEFT("Hello", 2) → "He"'),
    ('RIGHT(text, num_chars)', 'Extract characters from right', '=RIGHT("Hello", 2) → "lo"'),
    ('MID(text, start, num_chars)', 'Extract from middle', '=MID("Hello", 2, 3) → "ell"'),
    ('LEN(text)', 'Count characters', '=LEN("Hello") → 5'),
    ('TRIM(text)', 'Remove extra spaces', '=TRIM("  Hi  ") → "Hi"'),
    ('CLEAN(text)', 'Remove non-printable chars', '=CLEAN(A1)'),
    ('UPPER(text) / LOWER(text)', 'Change case', '=UPPER("hello") → "HELLO"'),
    ('PROPER(text)', 'Capitalize first letters', '=PROPER("john doe") → "John Doe"'),
    ('CONCAT(text1, text2, ...)', 'Join text (Excel 2016+)', '=CONCAT(A1, " ", B1)'),
    ('TEXTJOIN(delimiter, ignore_empty, range)', 'Join with delimiter', '=TEXTJOIN(", ", TRUE, A1:A5)'),
    ('SUBSTITUTE(text, old, new)', 'Replace text', '=SUBSTITUTE("Mr.", "Mr", "Ms") → "Ms."'),
    ('FIND(find_text, within_text)', 'Find position (case-sensitive)', '=FIND("o", "Hello") → 5'),
    ('SEARCH(find_text, within_text)', 'Find position (case-insensitive)', '=SEARCH("O", "Hello") → 5'),
]

table = doc.add_table(rows=len(text_funcs)+1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0].text = 'Function'
table.rows[0].cells[1].text = 'Purpose'
table.rows[0].cells[2].text = 'Example'
for cell in table.rows[0].cells:
    cell.paragraphs[0].runs[0].bold = True

for i, (func, purpose, example) in enumerate(text_funcs, 1):
    table.rows[i].cells[0].text = func
    table.rows[i].cells[1].text = purpose
    table.rows[i].cells[2].text = example

# Text to Columns equivalent
doc.add_heading('Splitting Text (Common Interview Task)', level=2)
p = doc.add_paragraph()
p.add_run('Split "John Doe" into First and Last Name: ').bold = True
doc.add_paragraph('First Name: =LEFT(A1, FIND(" ", A1)-1)', style='List Bullet')
doc.add_paragraph('Last Name: =RIGHT(A1, LEN(A1)-FIND(" ", A1))', style='List Bullet')

p = doc.add_paragraph()
p.add_run('Extract domain from email: ').bold = True
doc.add_paragraph('=RIGHT(A1, LEN(A1)-FIND("@", A1))', style='List Bullet')

# ============ 4. DATE & TIME FUNCTIONS ============
doc.add_page_break()
doc.add_heading('4. Date & Time Functions', level=1)

date_funcs = [
    ('TODAY()', 'Current date', '=TODAY() → 12/5/2024'),
    ('NOW()', 'Current date & time', '=NOW() → 12/5/2024 10:30'),
    ('YEAR(date)', 'Extract year', '=YEAR("3/15/2024") → 2024'),
    ('MONTH(date)', 'Extract month (1-12)', '=MONTH("3/15/2024") → 3'),
    ('DAY(date)', 'Extract day', '=DAY("3/15/2024") → 15'),
    ('WEEKDAY(date, [type])', 'Day of week (1-7)', '=WEEKDAY("3/15/2024") → 6 (Friday)'),
    ('WEEKNUM(date)', 'Week number of year', '=WEEKNUM("3/15/2024") → 11'),
    ('EOMONTH(start_date, months)', 'End of month', '=EOMONTH("3/15/2024", 0) → 3/31/2024'),
    ('EDATE(start_date, months)', 'Add/subtract months', '=EDATE("3/15/2024", 2) → 5/15/2024'),
    ('DATEDIF(start, end, unit)', 'Difference between dates', '=DATEDIF(A1, B1, "M") → months'),
    ('NETWORKDAYS(start, end)', 'Working days between', '=NETWORKDAYS(A1, B1) excludes weekends'),
    ('TEXT(value, format)', 'Format date as text', '=TEXT(A1, "MMMM YYYY") → "March 2024"'),
]

table = doc.add_table(rows=len(date_funcs)+1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0].text = 'Function'
table.rows[0].cells[1].text = 'Purpose'
table.rows[0].cells[2].text = 'Example'
for cell in table.rows[0].cells:
    cell.paragraphs[0].runs[0].bold = True

for i, (func, purpose, example) in enumerate(date_funcs, 1):
    table.rows[i].cells[0].text = func
    table.rows[i].cells[1].text = purpose
    table.rows[i].cells[2].text = example

# Date calculations
doc.add_heading('Common Date Calculations', level=2)
doc.add_paragraph('Age from birthdate: =DATEDIF(A1, TODAY(), "Y")', style='List Bullet')
doc.add_paragraph('First day of month: =EOMONTH(A1, -1)+1', style='List Bullet')
doc.add_paragraph('Last day of month: =EOMONTH(A1, 0)', style='List Bullet')
doc.add_paragraph('Quarter: =ROUNDUP(MONTH(A1)/3, 0)', style='List Bullet')
doc.add_paragraph('Fiscal year (starts July): =IF(MONTH(A1)>=7, YEAR(A1)+1, YEAR(A1))', style='List Bullet')

# ============ 5. STATISTICAL & MATH ============
doc.add_page_break()
doc.add_heading('5. Statistical & Math Functions', level=1)

# Basic
doc.add_heading('Basic Aggregations', level=2)
basic_funcs = [
    ('SUM(range)', 'Add all values'),
    ('AVERAGE(range)', 'Mean of values'),
    ('COUNT(range)', 'Count numbers only'),
    ('COUNTA(range)', 'Count non-empty cells'),
    ('COUNTBLANK(range)', 'Count empty cells'),
    ('MAX(range) / MIN(range)', 'Largest/smallest value'),
    ('MEDIAN(range)', 'Middle value'),
    ('MODE(range)', 'Most frequent value'),
    ('STDEV(range)', 'Standard deviation (sample)'),
    ('VAR(range)', 'Variance'),
]

for func, purpose in basic_funcs:
    p = doc.add_paragraph(style='List Bullet')
    p.add_run(func + ': ').bold = True
    p.add_run(purpose)

# Conditional
doc.add_heading('Conditional Aggregations (CRITICAL for Interviews)', level=2)

p = doc.add_paragraph()
p.add_run('SUMIF / COUNTIF / AVERAGEIF (Single Condition)').bold = True

doc.add_paragraph('=SUMIF(range, criteria, [sum_range])', style='List Bullet')
doc.add_paragraph('=SUMIF(A:A, "Sales", B:B)  → Sum column B where column A = "Sales"', style='List Bullet')
doc.add_paragraph('=COUNTIF(A:A, ">100")  → Count cells > 100', style='List Bullet')
doc.add_paragraph('=COUNTIF(A:A, "*apple*")  → Count cells containing "apple"', style='List Bullet')

p = doc.add_paragraph()
p.add_run('SUMIFS / COUNTIFS / AVERAGEIFS (Multiple Conditions)').bold = True

doc.add_paragraph('=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)', style='List Bullet')
doc.add_paragraph('=SUMIFS(C:C, A:A, "Sales", B:B, ">2024-01-01")  → Sum C where A="Sales" AND B>date', style='List Bullet')
doc.add_paragraph('=COUNTIFS(A:A, "Sales", B:B, ">=100", B:B, "<500")  → Between 100 and 500', style='List Bullet')

# LARGE/SMALL
doc.add_heading('Ranking Functions', level=2)
doc.add_paragraph('=LARGE(range, k) → k-th largest value. =LARGE(A:A, 3) → 3rd largest', style='List Bullet')
doc.add_paragraph('=SMALL(range, k) → k-th smallest value', style='List Bullet')
doc.add_paragraph('=RANK(number, range, [order]) → Rank of a number. 0=descending, 1=ascending', style='List Bullet')
doc.add_paragraph('=PERCENTILE(range, k) → Value at k-th percentile. =PERCENTILE(A:A, 0.9) → 90th percentile', style='List Bullet')

# Rounding
doc.add_heading('Rounding Functions', level=2)
doc.add_paragraph('=ROUND(number, decimals) → Standard rounding', style='List Bullet')
doc.add_paragraph('=ROUNDUP(number, decimals) → Always round up', style='List Bullet')
doc.add_paragraph('=ROUNDDOWN(number, decimals) → Always round down', style='List Bullet')
doc.add_paragraph('=CEILING(number, significance) → Round up to nearest multiple', style='List Bullet')
doc.add_paragraph('=FLOOR(number, significance) → Round down to nearest multiple', style='List Bullet')

# ============ 6. ARRAY FUNCTIONS ============
doc.add_page_break()
doc.add_heading('6. Array Functions (Dynamic Arrays - Excel 365)', level=1)

# FILTER
doc.add_heading('FILTER', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Filter a range based on criteria (spills results).')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=FILTER(array, include, [if_empty])')

p = doc.add_paragraph()
p.add_run('Examples: ').bold = True
doc.add_paragraph('=FILTER(A2:D100, C2:C100="Sales")  → All rows where col C = "Sales"', style='List Bullet')
doc.add_paragraph('=FILTER(A2:D100, (B2:B100>100)*(C2:C100="Sales"))  → Multiple conditions (AND)', style='List Bullet')
doc.add_paragraph('=FILTER(A2:D100, (B2:B100>100)+(C2:C100="Sales"))  → Multiple conditions (OR)', style='List Bullet')

# SORT
doc.add_heading('SORT', level=2)
p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=SORT(array, [sort_index], [sort_order], [by_col])')

doc.add_paragraph('=SORT(A2:C100, 2, -1)  → Sort by 2nd column, descending', style='List Bullet')
doc.add_paragraph('=SORT(FILTER(A2:C100, B2:B100>100), 3, 1)  → Filter then sort', style='List Bullet')

# UNIQUE
doc.add_heading('UNIQUE', level=2)
p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=UNIQUE(array, [by_col], [exactly_once])')

doc.add_paragraph('=UNIQUE(A2:A100)  → Distinct values in column A', style='List Bullet')
doc.add_paragraph('=UNIQUE(A2:C100)  → Unique rows across columns A-C', style='List Bullet')

# SORTBY
doc.add_heading('SORTBY', level=2)
doc.add_paragraph('=SORTBY(array, by_array1, [order1], ...)  → Sort by external column', style='List Bullet')
doc.add_paragraph('=SORTBY(A2:B100, C2:C100, -1)  → Sort A:B by column C descending', style='List Bullet')

# SEQUENCE
doc.add_heading('SEQUENCE', level=2)
doc.add_paragraph('=SEQUENCE(rows, [cols], [start], [step])  → Generate number series', style='List Bullet')
doc.add_paragraph('=SEQUENCE(12, 1, 1, 1)  → 1 to 12 vertically', style='List Bullet')

# LET
doc.add_heading('LET (Define Variables)', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Create named variables within a formula for reuse and readability.')

p = doc.add_paragraph()
p.add_run('Syntax: ').bold = True
p.add_run('=LET(name1, value1, [name2, value2], ..., calculation)')

p = doc.add_paragraph()
p.add_run('Example: ').bold = True
doc.add_paragraph('=LET(sales, SUMIF(A:A,"Sales",B:B), costs, SUMIF(A:A,"Costs",B:B), sales-costs)', style='List Bullet')

# ============ 7. PIVOT TABLES ============
doc.add_page_break()
doc.add_heading('7. Pivot Tables', level=1)

doc.add_heading('Creating a Pivot Table', level=2)
steps = [
    'Select your data (include headers)',
    'Insert → PivotTable',
    'Choose New Worksheet or Existing Worksheet',
    'Drag fields to: Rows, Columns, Values, Filters',
]
for i, step in enumerate(steps, 1):
    doc.add_paragraph(f'{i}. {step}')

doc.add_heading('Pivot Table Components', level=2)
components = [
    ('Rows', 'Categories displayed as rows (e.g., Product, Region)'),
    ('Columns', 'Categories displayed as columns (e.g., Month, Year)'),
    ('Values', 'Numbers to aggregate (Sum, Count, Average, etc.)'),
    ('Filters', 'Dropdown filters for the entire pivot'),
]
for comp, desc in components:
    p = doc.add_paragraph(style='List Bullet')
    p.add_run(comp + ': ').bold = True
    p.add_run(desc)

doc.add_heading('Key Pivot Table Skills for Interviews', level=2)
skills = [
    'Grouping dates (by Month, Quarter, Year)',
    'Calculated fields: Insert → Fields, Items & Sets → Calculated Field',
    'Show values as: % of Grand Total, % of Column, Running Total, Rank',
    'Slicers: Visual filters (Insert → Slicer)',
    'Refresh data: Right-click → Refresh',
    'Drill down: Double-click a value to see underlying data',
    'Sorting and filtering within pivot',
    'Multiple value fields (e.g., Sum and Count)',
]
for skill in skills:
    doc.add_paragraph(skill, style='List Bullet')

doc.add_heading('GETPIVOTDATA Function', level=2)
p = doc.add_paragraph()
p.add_run('Purpose: ').bold = True
p.add_run('Extract specific values from a pivot table.')

doc.add_paragraph('=GETPIVOTDATA("Sales", $A$3, "Region", "East", "Year", 2024)', style='List Bullet')

# ============ 8. DATA VALIDATION & CONDITIONAL FORMATTING ============
doc.add_page_break()
doc.add_heading('8. Data Validation & Conditional Formatting', level=1)

doc.add_heading('Data Validation', level=2)
p = doc.add_paragraph()
p.add_run('Location: ').bold = True
p.add_run('Data → Data Validation')

validation_types = [
    ('Whole Number / Decimal', 'Restrict to numbers within range'),
    ('List', 'Dropdown from range or comma-separated values'),
    ('Date / Time', 'Restrict to date/time range'),
    ('Text Length', 'Limit character count'),
    ('Custom', 'Formula-based validation'),
]

for val_type, desc in validation_types:
    p = doc.add_paragraph(style='List Bullet')
    p.add_run(val_type + ': ').bold = True
    p.add_run(desc)

p = doc.add_paragraph()
p.add_run('Dependent Dropdown Example: ').bold = True
doc.add_paragraph('List source: =INDIRECT(A1) where A1 contains a named range', style='List Bullet')

doc.add_heading('Conditional Formatting', level=2)
p = doc.add_paragraph()
p.add_run('Location: ').bold = True
p.add_run('Home → Conditional Formatting')

cf_types = [
    ('Highlight Cell Rules', 'Greater than, Less than, Equal to, Text Contains'),
    ('Top/Bottom Rules', 'Top 10, Bottom 10%, Above/Below Average'),
    ('Data Bars', 'Horizontal bars showing relative values'),
    ('Color Scales', '2-color or 3-color gradient based on value'),
    ('Icon Sets', 'Arrows, flags, shapes based on value'),
    ('New Rule → Formula', 'Custom formula: =A1>B1 or =$A1="Complete"'),
]

for cf_type, desc in cf_types:
    p = doc.add_paragraph(style='List Bullet')
    p.add_run(cf_type + ': ').bold = True
    p.add_run(desc)

p = doc.add_paragraph()
p.add_run('Interview Tip: ').bold = True
p.add_run('When using formulas in conditional formatting, use mixed references (e.g., $A1) to apply formatting across rows/columns correctly.')

# ============ 9. VBA FUNDAMENTALS ============
doc.add_page_break()
doc.add_heading('9. VBA Fundamentals', level=1)

doc.add_heading('Getting Started', level=2)
doc.add_paragraph('Open VBA Editor: Alt + F11', style='List Bullet')
doc.add_paragraph('Insert Module: Insert → Module', style='List Bullet')
doc.add_paragraph('Run Macro: F5 or Alt + F8', style='List Bullet')
doc.add_paragraph('Record Macro: Developer → Record Macro', style='List Bullet')

doc.add_heading('Basic Structure', level=2)
code1 = '''Sub MacroName()
    ' This is a comment
    Dim variableName As DataType

    ' Your code here

End Sub'''
p = doc.add_paragraph()
run = p.add_run(code1)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_heading('Variable Types', level=2)
var_types = [
    ('String', 'Text', 'Dim name As String'),
    ('Integer', 'Whole numbers (-32,768 to 32,767)', 'Dim count As Integer'),
    ('Long', 'Large whole numbers', 'Dim bigNum As Long'),
    ('Double', 'Decimal numbers', 'Dim price As Double'),
    ('Boolean', 'True/False', 'Dim isValid As Boolean'),
    ('Date', 'Dates', 'Dim startDate As Date'),
    ('Variant', 'Any type (flexible but slower)', 'Dim anything As Variant'),
    ('Range', 'Cell range object', 'Dim rng As Range'),
    ('Worksheet', 'Worksheet object', 'Dim ws As Worksheet'),
    ('Workbook', 'Workbook object', 'Dim wb As Workbook'),
]

table = doc.add_table(rows=len(var_types)+1, cols=3)
table.style = 'Table Grid'
table.rows[0].cells[0].text = 'Type'
table.rows[0].cells[1].text = 'Use For'
table.rows[0].cells[2].text = 'Declaration'
for cell in table.rows[0].cells:
    cell.paragraphs[0].runs[0].bold = True

for i, (vtype, use, decl) in enumerate(var_types, 1):
    table.rows[i].cells[0].text = vtype
    table.rows[i].cells[1].text = use
    table.rows[i].cells[2].text = decl

# Common VBA Operations
doc.add_heading('Common Operations', level=2)

doc.add_paragraph().add_run('Working with Cells:').bold = True
code2 = '''' Read a cell value
value = Range("A1").Value
value = Cells(1, 1).Value   ' Row 1, Column 1

' Write to a cell
Range("A1").Value = "Hello"
Cells(1, 1).Value = 100

' Select a range
Range("A1:B10").Select

' Copy and Paste
Range("A1:A10").Copy Destination:=Range("C1")

' Clear contents
Range("A1:A10").ClearContents'''
p = doc.add_paragraph()
run = p.add_run(code2)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Loops:').bold = True
code3 = '''' For Loop
For i = 1 To 10
    Cells(i, 1).Value = i * 2
Next i

' For Each (loop through range)
Dim cell As Range
For Each cell In Range("A1:A10")
    If cell.Value > 100 Then
        cell.Interior.Color = vbYellow
    End If
Next cell

' Do While Loop
i = 1
Do While Cells(i, 1).Value <> ""
    ' Process row i
    i = i + 1
Loop'''
p = doc.add_paragraph()
run = p.add_run(code3)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Conditionals:').bold = True
code4 = '''' If Statement
If score >= 90 Then
    grade = "A"
ElseIf score >= 80 Then
    grade = "B"
Else
    grade = "C"
End If

' Select Case
Select Case department
    Case "Sales"
        bonus = salary * 0.1
    Case "Marketing"
        bonus = salary * 0.08
    Case Else
        bonus = salary * 0.05
End Select'''
p = doc.add_paragraph()
run = p.add_run(code4)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Working with Worksheets:').bold = True
code5 = '''' Reference worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

' Add new worksheet
Worksheets.Add.Name = "NewSheet"

' Loop through all worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

' Find last row with data
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Find last column with data
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column'''
p = doc.add_paragraph()
run = p.add_run(code5)
run.font.name = 'Courier New'
run.font.size = Pt(9)

# Practical VBA Examples
doc.add_page_break()
doc.add_heading('Practical VBA Examples for Interviews', level=2)

doc.add_paragraph().add_run('Example 1: Highlight Duplicates').bold = True
code6 = '''Sub HighlightDuplicates()
    Dim rng As Range
    Dim cell As Range
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

    For Each cell In rng
        If dict.exists(cell.Value) Then
            cell.Interior.Color = vbYellow
            dict(cell.Value).Interior.Color = vbYellow
        Else
            dict.Add cell.Value, cell
        End If
    Next cell
End Sub'''
p = doc.add_paragraph()
run = p.add_run(code6)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Example 2: Create Summary Report').bold = True
code7 = '''Sub CreateSummary()
    Dim wsData As Worksheet, wsSummary As Worksheet
    Dim lastRow As Long, i As Long
    Dim category As String
    Dim dict As Object

    Set wsData = Worksheets("Data")
    Set dict = CreateObject("Scripting.Dictionary")

    lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

    ' Sum by category
    For i = 2 To lastRow
        category = wsData.Cells(i, 1).Value
        If dict.exists(category) Then
            dict(category) = dict(category) + wsData.Cells(i, 2).Value
        Else
            dict.Add category, wsData.Cells(i, 2).Value
        End If
    Next i

    ' Create summary sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Summary").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsSummary = Worksheets.Add
    wsSummary.Name = "Summary"

    ' Output results
    wsSummary.Range("A1").Value = "Category"
    wsSummary.Range("B1").Value = "Total"

    i = 2
    For Each key In dict.Keys
        wsSummary.Cells(i, 1).Value = key
        wsSummary.Cells(i, 2).Value = dict(key)
        i = i + 1
    Next key
End Sub'''
p = doc.add_paragraph()
run = p.add_run(code7)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Example 3: Export Each Sheet to CSV').bold = True
code8 = '''Sub ExportSheetsToCSV()
    Dim ws As Worksheet
    Dim savePath As String

    savePath = ThisWorkbook.Path & "\\"

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs savePath & ws.Name & ".csv", xlCSV
        ActiveWorkbook.Close False
    Next ws

    MsgBox "Export complete!"
End Sub'''
p = doc.add_paragraph()
run = p.add_run(code8)
run.font.name = 'Courier New'
run.font.size = Pt(9)

doc.add_paragraph().add_run('Example 4: Autofilter and Copy Results').bold = True
code9 = '''Sub FilterAndCopy()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long

    Set wsSource = Worksheets("Data")
    Set wsDest = Worksheets("Filtered")

    ' Clear destination
    wsDest.Cells.Clear

    lastRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row

    ' Apply filter
    wsSource.Range("A1:D" & lastRow).AutoFilter Field:=2, Criteria1:=">1000"

    ' Copy visible cells
    wsSource.Range("A1:D" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=wsDest.Range("A1")

    ' Remove filter
    wsSource.AutoFilterMode = False
End Sub'''
p = doc.add_paragraph()
run = p.add_run(code9)
run.font.name = 'Courier New'
run.font.size = Pt(9)

# ============ 10. COMMON INTERVIEW SCENARIOS ============
doc.add_page_break()
doc.add_heading('10. Common Interview Scenarios', level=1)

scenarios = [
    {
        'title': 'Scenario 1: Clean and Transform Data',
        'task': 'You have a column with names in "LastName, FirstName" format. Split into two columns and clean extra spaces.',
        'formula': '''First Name: =TRIM(MID(A1, FIND(",", A1)+1, 100))
Last Name: =TRIM(LEFT(A1, FIND(",", A1)-1))'''
    },
    {
        'title': 'Scenario 2: Calculate Running Total',
        'task': 'Create a running total of sales by date.',
        'formula': '=SUMIF($A$2:A2, "<="&A2, $B$2:B2)\nOr: =SUM($B$2:B2)'
    },
    {
        'title': 'Scenario 3: Find Duplicates',
        'task': 'Flag duplicate entries in column A.',
        'formula': '=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique")\nOr: =IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique")'
    },
    {
        'title': 'Scenario 4: Rank Within Groups',
        'task': 'Rank salespeople within each region by sales amount.',
        'formula': '=SUMPRODUCT((A2=$A$2:$A$100)*(C2<$C$2:$C$100))+1\nWhere A=Region, C=Sales'
    },
    {
        'title': 'Scenario 5: Dynamic Data Validation',
        'task': 'Create dropdown that shows products based on selected category.',
        'formula': '''1. Create named ranges for each category
2. Data Validation → List → =INDIRECT(CategoryCell)'''
    },
    {
        'title': 'Scenario 6: Conditional Summing Across Sheets',
        'task': 'Sum a cell across multiple sheets.',
        'formula': "=SUM(Sheet1:Sheet12!A1)\nOr: =SUMPRODUCT(SUMIF(INDIRECT(\"'\"&{\"Sheet1\",\"Sheet2\",\"Sheet3\"}&\"'!A:A\"),\"Criteria\",INDIRECT(\"'\"&{\"Sheet1\",\"Sheet2\",\"Sheet3\"}&\"'!B:B\")))"
    },
    {
        'title': 'Scenario 7: Year-over-Year Comparison',
        'task': 'Calculate YoY growth percentage.',
        'formula': 'YoY Growth: =(CurrentYear - PriorYear) / PriorYear\n=IFERROR((B2-C2)/C2, 0)'
    },
    {
        'title': 'Scenario 8: Find Nth Occurrence',
        'task': 'Find the 3rd occurrence of "Error" in a column.',
        'formula': '=SMALL(IF(A:A="Error", ROW(A:A)), 3)\n(Enter with Ctrl+Shift+Enter if not Excel 365)'
    },
]

for scenario in scenarios:
    doc.add_heading(scenario['title'], level=2)
    p = doc.add_paragraph()
    p.add_run('Task: ').bold = True
    p.add_run(scenario['task'])

    p = doc.add_paragraph()
    p.add_run('Solution: ').bold = True

    p = doc.add_paragraph()
    run = p.add_run(scenario['formula'])
    run.font.name = 'Courier New'
    run.font.size = Pt(9)

# ============ INTERVIEW TIPS ============
doc.add_page_break()
doc.add_heading('Interview Tips Summary', level=1)

tips_do = [
    'Think out loud - explain your approach',
    'Ask clarifying questions about the data',
    'Start simple, then optimize',
    'Use keyboard shortcuts (Ctrl+Shift+End, Ctrl+Down)',
    'Know INDEX/MATCH over VLOOKUP',
    'Demonstrate pivot table skills',
    'Show you can handle errors (IFERROR)',
    'Mention performance (avoid volatile functions on large data)',
]

tips_dont = [
    "Don't start coding without understanding the problem",
    "Don't hardcode values - use cell references",
    "Don't forget to handle edge cases (blanks, errors)",
    "Don't use entire column (A:A) in SUMIFS on large data",
    "Don't panic if you forget exact syntax - explain the concept",
]

doc.add_heading('DO:', level=2)
for tip in tips_do:
    doc.add_paragraph(tip, style='List Bullet')

doc.add_heading("DON'T:", level=2)
for tip in tips_dont:
    doc.add_paragraph(tip, style='List Bullet')

doc.add_heading('Essential Keyboard Shortcuts', level=2)
shortcuts = [
    ('Ctrl+Shift+End', 'Select to last used cell'),
    ('Ctrl+Down/Up/Left/Right', 'Jump to edge of data'),
    ('Ctrl+Shift+L', 'Toggle AutoFilter'),
    ('Alt+=', 'AutoSum'),
    ('Ctrl+;', 'Insert current date'),
    ('Ctrl+Shift+;', 'Insert current time'),
    ('F4', 'Toggle absolute reference ($)'),
    ('Ctrl+`', 'Show/hide formulas'),
    ('Alt+Enter', 'New line within cell'),
    ('Ctrl+D', 'Fill down'),
    ('Ctrl+R', 'Fill right'),
]

table = doc.add_table(rows=len(shortcuts)+1, cols=2)
table.style = 'Table Grid'
table.rows[0].cells[0].text = 'Shortcut'
table.rows[0].cells[1].text = 'Action'
for cell in table.rows[0].cells:
    cell.paragraphs[0].runs[0].bold = True

for i, (shortcut, action) in enumerate(shortcuts, 1):
    table.rows[i].cells[0].text = shortcut
    table.rows[i].cells[1].text = action

# Save
doc.save('/home/rosy/public/Excel_Interview_Guide.docx')
print("Excel Interview Guide created successfully!")
