excel.go

func importExcelData(db *gorm.DB) {
	dataDir := os.Getenv("DIGITAL_EQUITY_WORKDIR")
	f, err := excelize.OpenFile(dataDir + "/DigitalEquity.xlsx")
	if err != nil {
		logrus.Errorf("Failed to open Excel file: %s\n", err)
		return
	}

	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	if err != nil {
		logrus.Errorf("Failed to read rows from sheet: %s\n", err)
		return
	}
	if len(rows) < 1 {
		logrus.Println("Excel sheet is empty")
		return
	}

	header := rows[0]
	colIndex := make(map[string]int)
	for idx, colName := range header {
		colIndex[colName] = idx
	}

	if err := db.Exec("DELETE FROM states").Error; err != nil {
		logrus.Printf("Failed to clear existing data in states table: %s\n", err)
		return
	}
	var data []models.State

	for _, row := range rows[1:] {
		stateName := getCellValue(row, colIndex, "STATE")
		if stateName == "" {
			continue
		}
		stateLetters, ok := STATE_ABBREVIATIONS[stateName]
		if !ok {
			logrus.Printf("State abbreviation not found for %s\n", stateName)
			continue
		}
		state := models.State{
			Name:                   stateName,
			StateLetters:           stateLetters,
			LinkToPlan:             getCellValue(row, colIndex, "LINK TO PLAN"),
			MoneyAwarded:           getCellValue(row, colIndex, "TOTAL MONEY AWARDED"),
			NotesFromPlan:          getCellValue(row, colIndex, "NOTES FROM PLAN"),
			Strategies:             getCellValue(row, colIndex, "State Strategies Regarding Incarcerated Individuals"),
			DatePlanApproved:       getCellValue(row, colIndex, "DATE PLAN APPROVED"),
			StateOpportunityGrants: getCellValue(row, colIndex, "State Opportunity Grants; 9/22/2024"),
		}
		data = append(data, state)
	}

	if err := db.Create(&data).Error; err != nil {
		logrus.Printf("An error occurred during data import: %s\n", err)
		return
	}
	logrus.Println("Data import completed successfully")
}

func populateStateLinks(db *gorm.DB) {
	var states []models.State
	if err := db.Find(&states).Error; err != nil {
		logrus.Printf("Failed to query states: %s\n", err)
		return
	}
	var dataToInsert []models.StateLink
	for _, state := range states {
		if state.LinkToPlan != "" {
			links := strings.Split(state.LinkToPlan, ";")
			for _, link := range links {
				link = strings.TrimSpace(link)
				if link != "" {
					dataToInsert = append(dataToInsert, models.StateLink{
						StateID:    state.ID,
						LinkToPlan: link,
					})
				}
			}
		}
	}

	if len(dataToInsert) > 0 {
		if err := db.Create(&dataToInsert).Error; err != nil {
			logrus.Printf("An error occurred during link import: %s\n", err)
		} else {
			logrus.Println("Links imported into state_links table successfully")
		}
	} else {
		logrus.Println("No links to import into state_links table")
	}
}


func getCellValue(row []string, colIndex map[string]int, colName string) string {
	if idx, ok := colIndex[colName]; ok && idx < len(row) {
		return row[idx]
	}
	return ""
}