require "csv"

# rubocop:disable Metrics/BlockLength
namespace :import do
  desc "Imports COVID-19 shielding links from MHCLG's Google Sheet"
  task covid19_mhclg: :environment do
    SERVICE_MAPPINGS = {
      "Single Vulnerable People URL" => { lgsl: 1287, lgil: 8 },
      # "volunteering" => { lgsl: 1113, lgil: 8 },
    }.freeze

    DOC_ID = "10U5nHFusxMPAh93aleuPYpE-l8ZOJGOjClQemoeIx5A".freeze
    SHEET_ID = "543314380".freeze
    CSV_URL = URI.parse("https://docs.google.com/spreadsheets/d/#{DOC_ID}/export?gid=#{SHEET_ID}&format=csv&id=#{DOC_ID}")

    response = Net::HTTP.get_response(CSV_URL)
    unless response.code_type == Net::HTTPOK
      raise "Error downloading CSV in #{self.class}"
    end

    csv = CSV.parse(response.body, headers: true)

    # Sanity check
    raise "Missing 'GSS' column" if csv["GSS"].compact.empty?

    SERVICE_MAPPINGS.keys.each do |k|
      raise "Missing '#{csv[k]}' column" if csv[k].compact.empty?
    end

    SERVICE_MAPPINGS.each do |type, codes|
      service_interaction = ServiceInteraction.find_or_create_by(
        service: Service.find_by!(lgsl_code: codes[:lgsl]),
        interaction: Interaction.find_by!(lgil_code: codes[:lgil]),
      )

      csv.each do |row|
        url = row[type]&.strip
        next if url.blank?

        begin
          URI.parse(url)
        rescue URI::InvalidURIError
          puts "Invalid URL '#{url}' for area #{row['GSS']}, skipping"
          next
        end

        local_authority = LocalAuthority.find_by(gss: row["GSS"])
        unless local_authority
          puts "Could not find local authority GSS=#{row['GSS']}"
          next
        end

        link = Link.find_or_initialize_by(
          local_authority: local_authority,
          service_interaction: service_interaction,
        )
        link.url = url
        link.save!
      end
      puts "Done"
    end
  end
end
# rubocop:enable Metrics/BlockLength

Add script to import COVID urls from MHCLG
Add script to import COVID urls from MHCLG

MHCLG have been collecting local URLs for supporting vulnerable people
from local council websites in a [Google Sheet][]. This script imports
them into Local Links Manager for LGSL 1287 and LGIL 8, based on the
rationale in [this PR][624].

The script fetches the CSV directly from Google Docs, which means that
the document first needs to be made publicly downloadable by anyone with
the link. First it performs a sanity check to ensure the sheet is in the
correct format. It next finds any existing `Link`s for each
`LocalAuthority` for the `ServiceInteraction`, overwriting the URL if a
`Link` already exists, or creating it if it doesn't.

[Google Sheet]:
https://docs.google.com/spreadsheets/d/10U5nHFusxMPAh93aleuPYpE-l8ZOJGOjClQemoeIx5A/edit?ts=5eaadb03#gid=2060364237
[624]: https://github.com/alphagov/local-links-manager/pull/624
