import pandas as pd
import os

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('docs/ERP_INTEGRATION_TIMELINE.xlsx', engine='openpyxl')

# Sheet 1: Integration Timeline
timeline_data = [
    ["Phase 1 - Discovery & Contract", "1.1", "Requirements alignment", "Workshop to align on ERP capabilities and requirements", "Joint", "", "None", "1w", "Week 1", "Week 1", "None", "Aligned spec", "Not Started"],
    ["Phase 1 - Discovery & Contract", "1.2", "Data Mapping", "Map existing system fields to ERP fields", "Joint", "", "1.1", "1w", "Week 2", "Week 2", "None", "Data Mapping Doc", "Not Started"],
    ["Phase 1 - Discovery & Contract", "1.3", "Source-of-truth agreement", "Finalize ownership of all data domains", "Joint", "", "1.2", "3d", "Week 2", "Week 2", "None", "SOT Matrix", "Not Started"],
    ["Phase 1 - Discovery & Contract", "1.4", "API & Security spec", "Finalize endpoint payloads and authentication", "Our Team", "ERP Team", "1.3", "1w", "Week 3", "Week 3", "None", "API Spec", "Not Started"],
    
    ["Phase 2 - Integration Foundation", "2.1", "ERP sandbox setup", "Provision testing environment for ERP", "ERP Team", "", "1.4", "1w", "Week 4", "Week 4", "Sandbox", "Sandbox URL", "Not Started"],
    ["Phase 2 - Integration Foundation", "2.2", "External IDs", "Add external_id and partner tables/columns", "Our Team", "", "1.4", "3d", "Week 4", "Week 4", "Local", "Migrations", "Not Started"],
    ["Phase 2 - Integration Foundation", "2.3", "Webhook Receiver", "Develop resilient webhook receiver", "Our Team", "", "2.2", "1w", "Week 5", "Week 5", "Local", "Receiver code", "Not Started"],
    ["Phase 2 - Integration Foundation", "2.4", "Queue/Retry Infra", "Implement integration event queue & logs", "Our Team", "", "2.3", "1w", "Week 6", "Week 6", "Local", "Queue code", "Not Started"],
    
    ["Phase 3 - ERP to Platform", "3.1", "Project & Plot Sync API", "ERP implements sync events", "ERP Team", "Our Team", "2.1", "2w", "Week 5", "Week 6", "Sandbox", "ERP Webhooks", "Not Started"],
    ["Phase 3 - ERP to Platform", "3.2", "Platform Project Sync", "Platform handles incoming projects", "Our Team", "", "3.1", "1.5w", "Week 7", "Week 8", "Local", "Project sync", "Not Started"],
    ["Phase 3 - ERP to Platform", "3.3", "Platform Plot Sync", "Platform handles incoming plots/availability", "Our Team", "", "3.1", "1.5w", "Week 8", "Week 9", "Local", "Plot sync", "Not Started"],
    
    ["Phase 4 - Platform to ERP", "4.1", "Reservation API", "ERP exposes reservation endpoint", "ERP Team", "", "1.4", "2w", "Week 5", "Week 6", "Sandbox", "ERP Endpoint", "Not Started"],
    ["Phase 4 - Platform to ERP", "4.2", "Platform Booking Flow", "Lock plot via ERP during booking", "Our Team", "ERP Team", "4.1", "2w", "Week 7", "Week 8", "Local", "Booking integration", "Not Started"],
    ["Phase 4 - Platform to ERP", "4.3", "Transaction Sync", "Send completed payment to ERP", "Our Team", "ERP Team", "4.1", "2w", "Week 9", "Week 10", "Local", "Transaction sync", "Not Started"],
    
    ["Phase 5 - Reliability", "5.1", "Reconciliation Jobs", "Nightly sync to resolve state differences", "Our Team", "ERP Team", "3.3,4.3", "2w", "Week 11", "Week 12", "Local", "Cron jobs", "Not Started"],
    ["Phase 5 - Reliability", "5.2", "Integration Logging", "Dashboards for integration errors", "Our Team", "", "2.4", "1w", "Week 11", "Week 11", "Local", "Dashboard", "Not Started"],
    
    ["Phase 6 - Testing", "6.1", "Integration Testing", "Sandbox end-to-end flow test", "Joint", "", "5.1", "2w", "Week 13", "Week 14", "Sandbox", "Test Report", "Not Started"],
    ["Phase 6 - Testing", "6.2", "UAT", "Business users review flow", "Joint", "", "6.1", "1w", "Week 15", "Week 15", "Sandbox", "Signoff", "Not Started"],
    
    ["Phase 7 - Deployment", "7.1", "Production Setup", "Exchange credentials, configure webhook", "Joint", "", "6.2", "1w", "Week 16", "Week 16", "Production", "Config", "Not Started"],
    ["Phase 7 - Deployment", "7.2", "Go-Live", "Enable integration", "Joint", "", "7.1", "1d", "Week 16", "Week 16", "Production", "Launch", "Not Started"]
]
df_timeline = pd.DataFrame(timeline_data, columns=["Phase", "Task ID", "Task", "Description", "Owner", "Supporting Team", "Dependency", "Estimated Duration", "Earliest Start", "Earliest Finish", "Environment", "Deliverable", "Status"])
df_timeline.to_excel(writer, sheet_name='Timeline', index=False)

# Sheet 2: Responsibility Matrix
raci_data = [
    ["Define business requirements", "R", "R", "C", "Joint workshop"],
    ["Define API Contract", "R", "C", "C", "Our team leads spec"],
    ["Provision ERP Sandbox", "I", "R", "C", "ERP team responsibility"],
    ["Develop Webhook Receiver (Platform)", "R", "I", "C", "Our team handles incoming webhooks"],
    ["Develop Webhook Sender (ERP)", "I", "R", "C", "ERP team pushes events to our receiver"],
    ["Implement Project/Plot logic (Platform)", "R", "C", "C", "Map incoming data to our DB"],
    ["Expose Reservation API (ERP)", "C", "R", "C", "Endpoint for plot locking"],
    ["Integrate Booking Flow (Platform)", "R", "I", "C", "Call ERP during checkout"],
    ["Transaction Reconciliation (Platform)", "R", "C", "C", "Nightly sync from our end"],
    ["UAT Testing", "R", "R", "A", "Both teams participate"],
    ["Production Go-Live", "R", "R", "A", "Coordinated launch"]
]
df_raci = pd.DataFrame(raci_data, columns=["Task", "Our Team", "ERP Team", "Joint", "Notes"])
df_raci.to_excel(writer, sheet_name='Responsibility Matrix', index=False)

# Sheet 3: API Delivery Matrix
api_data = [
    ["ERP.Project.Created", "ERP -> Platform", "ERP Team", "Our Team", "None", "HMAC", "Not Started", "Not Started", "Not Started", "Not Started"],
    ["ERP.Plot.Updated", "ERP -> Platform", "ERP Team", "Our Team", "None", "HMAC", "Not Started", "Not Started", "Not Started", "Not Started"],
    ["ERP.Plot.AvailabilityChanged", "ERP -> Platform", "ERP Team", "Our Team", "None", "HMAC", "Not Started", "Not Started", "Not Started", "Not Started"],
    ["Platform.Plot.Reserve", "Platform -> ERP", "ERP Team", "Our Team", "None", "OAuth2 / API Key", "Not Started", "Not Started", "Not Started", "Not Started"],
    ["Platform.Transaction.Completed", "Platform -> ERP", "ERP Team", "Our Team", "Platform.Plot.Reserve", "OAuth2 / API Key", "Not Started", "Not Started", "Not Started", "Not Started"]
]
df_api = pd.DataFrame(api_data, columns=["API/Event", "Direction", "Developed By", "Consumed By", "Dependency", "Authentication", "Development Status", "Testing Status", "UAT Status", "Production Status"])
df_api.to_excel(writer, sheet_name='API Delivery Matrix', index=False)

# Sheet 4: Data Mapping
mapping_data = [
    ["Project", "name", "properties", "project_name", "[ERP_PLACEHOLDER]", "string", "Yes", "ERP", "Direct", ""],
    ["Project", "address", "properties", "location", "[ERP_PLACEHOLDER]", "string", "Yes", "ERP", "Direct", ""],
    ["Plot", "plotnumber", "property_plots", "plot_number", "[ERP_PLACEHOLDER]", "string", "Yes", "ERP", "Direct", ""],
    ["Plot", "area_size", "property_plots", "size_sqm", "[ERP_PLACEHOLDER]", "string", "Yes", "ERP", "Direct", "Requires unit standardization"],
    ["Plot", "amount", "property_plots/pricing", "price", "[ERP_PLACEHOLDER]", "string/int", "Yes", "ERP", "Direct", "Must distinguish full vs installment base price"],
    ["Customer", "msisdn", "customers", "phone", "[ERP_PLACEHOLDER]", "string", "Yes", "Platform", "Direct", "E.164 format"],
    ["Customer", "email", "customers", "email", "[ERP_PLACEHOLDER]", "string", "No", "Platform", "Direct", ""],
    ["Customer", "ID_no", "customers", "id_number", "[ERP_PLACEHOLDER]", "string", "Yes", "Platform", "Direct", ""],
    ["Order", "referenceid", "orders", "order_reference", "[ERP_PLACEHOLDER]", "string", "Yes", "Platform", "Direct", ""],
    ["Order", "amount", "orders", "total_amount", "[ERP_PLACEHOLDER]", "int", "Yes", "Platform", "Direct", ""],
    ["Transaction", "receipt", "transactions", "receipt_number", "[ERP_PLACEHOLDER]", "string", "Yes", "Platform", "Direct", "MNO/Card receipt"],
    ["Transaction", "amount_paid", "transactions", "paid_amount", "[ERP_PLACEHOLDER]", "int", "Yes", "Platform", "Direct", ""]
]
df_mapping = pd.DataFrame(mapping_data, columns=["Domain", "Our Field", "Our Table", "ERP Field", "ERP Object/Table", "Data Type", "Required", "Source of Truth", "Transformation", "Notes"])
df_mapping.to_excel(writer, sheet_name='Data Mapping', index=False)

# Sheet 5: Risks & Dependencies
risks_data = [
    ["R01", "ERP API readiness delayed", "Schedule", "Medium", "High", "ERP Team", "Start with mocks; define contract early.", "Yes", "Active"],
    ["R02", "Conflicting plot availability (Race Condition)", "Technical", "High", "High", "Joint", "Implement pessimistic reservation flow at checkout (Platform locks plot in ERP before payment).", "Yes", "Active"],
    ["R03", "Missing ERP external IDs", "Data", "Medium", "High", "Our Team", "Introduce mapping table integration_partners to decouple IDs.", "No", "Active"],
    ["R04", "Webhook duplicates or out of order", "Technical", "High", "High", "Our Team", "Implement idempotency table (webhook_events) tracking event_id and processing timestamp.", "No", "Active"],
    ["R05", "KYC Privacy Concerns", "Compliance", "Low", "High", "Joint", "Apply data minimization. Send only essential info (e.g. ID No., Name, Phone).", "No", "Active"]
]
df_risks = pd.DataFrame(risks_data, columns=["ID", "Risk / Dependency", "Category", "Probability", "Impact", "Owner", "Mitigation", "Blocking", "Status"])
df_risks.to_excel(writer, sheet_name='Risks & Dependencies', index=False)

writer.close()
print("Excel generated successfully!")