pods_rates_migration

New JSON schema

{
  "extraGuests": {
    "private": {
      "minGuests": 6,
      "maxGuests": 8,
      "rate": {
        "MEMBER": { "OFF_PEAK": 2.5, "REDUCED": 2.5, "PEAK": 5 },
        "NON_MEMBER": { "OFF_PEAK": 5, "REDUCED": 5, "PEAK": 10 }
      }
    },
    "coach": {
      "minGuests": 2,
      "maxGuests": 4,
      "rate": {
        "MEMBER": { "OFF_PEAK": 0, "PEAK": 0, "REDUCED": 0 },
        "NON_MEMBER": { "OFF_PEAK": 0, "PEAK": 0, "REDUCED": 0 }
      }
    }
  },
  "defaultRate": {
    "id": "OFF_PEAK",
    "displayName": "OFF PEAK",
    "rate": 20,
    "lockingFee": 10
  },
  "specialRates": [
    {
      "id": "PEAK",
      "displayName": "PEAK",
      "rate": 30,
      "lockingFee": 10,
      "ranges": [
        { "days": [1, 2, 3, 4, 5], "from": { "hour": 17 }, "to": { "hour": 24 } },
        { "days": [1, 2, 3, 4, 5, 6, 0], "from": { "hour": 0 }, "to": { "hour": 6 } },
        { "days": [6, 0], "from": { "hour": 10 }, "to": { "hour": 24 } }
      ]
    },
    {
      "id": "REDUCED",
      "displayName": "REDUCED",
      "rate": 30,
      "lockingFee": 10,
      "ranges": [
      ]
    }
  ],
  "membershipOverrides":{
      "default": {
        "defaultRate": {
          "id": "OFF_PEAK",
          "displayName": "OFF PEAK",
          "rate": 0,
          "lockingFee": 0
        },
        "specialRates": [
          {
            "id": "PEAK",
            "displayName": "PEAK",
            "rate": 30,
            "lockingFee": 10,
            "ranges": [
              { "days": [1, 2, 3, 4, 5], "from": { "hour": 17 }, "to": { "hour": 24 } },
              { "days": [1, 2, 3, 4, 5, 6, 0], "from": { "hour": 0 }, "to": { "hour": 6 } },
              { "days": [6, 0], "from": { "hour": 10 }, "to": { "hour": 24 } }
            ]
          },
          {
            "id": "REDUCED",
            "displayName": "REDUCED",
            "rate": 30,
            "lockingFee": 10,
            "ranges": [
            ]
          }
        ]
      },
      "HOME_ASTORIA": {
        "defaultRate": {
          "id": "OFF_PEAK",
          "displayName": "OFF PEAK",
          "rate": 0,
          "lockingFee": 10
        },
        "specialRates": [
          {
            "id": "PEAK",
            "displayName": "PEAK",
            "rate": 20,
            "lockingFee": 10,
            "ranges": [
              { "days": [1, 2, 3, 4, 5], "from": { "hour": 17 }, "to": { "hour": 24 } },
              { "days": [1, 2, 3, 4, 5, 6, 0], "from": { "hour": 0 }, "to": { "hour": 6 } },
              { "days": [6, 0], "from": { "hour": 10 }, "to": { "hour": 24 } }
            ]
          },
          {
            "id": "REDUCED",
            "displayName": "REDUCED",
            "rate": 30,
            "lockingFee": 10,
            "ranges": [
            ]
          }
        ]
    },
    "HOME_DOBRO": {
      "defaultRate": {
        "id": "OFF_PEAK",
        "displayName": "OFF PEAK",
        "rate": 0,
        "lockingFee": 10
      },
      "specialRates": [
        {
          "id": "PEAK",
          "displayName": "PEAK",
          "rate": 30,
          "lockingFee": 10,
          "ranges": [
            { "days": [1, 2, 3, 4, 5, 6, 0], "from": { "hour": 15.0}, "to": { "hour": 24 } }
          ]
        },
        {
          "id": "REDUCED",
          "displayName": "REDUCED",
          "rate": 30,
          "lockingFee": 10,
          "ranges": [
          ]
        }
      ]
    }
  }
}

Migration Script

This script generates update statements for each pod using a JSON export from the pods table including pods.id and pods.pricing

Example exported json:

[
  {
    "id": "e95-multi",
    "pricing": {
      "extraGuests": {
        "private": {
          "minGuests": 6,
          "maxGuests": 8,
          "rate": {
            "MEMBER": {
              "OFF_PEAK": 2.5,
              "REDUCED": 2.5,
              "PEAK": 2.5
            },
            "NON_MEMBER": {
              "OFF_PEAK": 5,
              "REDUCED": 5,
              "PEAK": 5
            }
          }
        },
        "coach": {
          "minGuests": 2,
          "maxGuests": 4,
          "rate": {
            "MEMBER": {
              "OFF_PEAK": 0,
              "PEAK": 0,
              "REDUCED": 0
            },
            "NON_MEMBER": {
              "OFF_PEAK": 0,
              "PEAK": 0,
              "REDUCED": 0
            }
          }
        }
      },
      "defaultRate": {
        "id": "OFF_PEAK",
        "displayName": "OFF PEAK",
        "MEMBER": 0,
        "MEMBER_PREM": 10,
        "NON_MEMBER": 20,
        "NON_MEMBER_PREM": 30
      },
      "specialRates": [
        {
          "id": "PEAK",
          "displayName": "PEAK",
          "MEMBER": 20,
          "MEMBER_PREM": 25,
          "NON_MEMBER": 40,
          "NON_MEMBER_PREM": 50,
          "ranges": [
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 17
              },
              "to": {
                "hour": 24
              }
            },
            {
              "days": [
                1,
                2,
                3,
                4,
                5,
                6,
                0
              ],
              "from": {
                "hour": 0
              },
              "to": {
                "hour": 6
              }
            },
            {
              "days": [
                6,
                0
              ],
              "from": {
                "hour": 10
              },
              "to": {
                "hour": 24
              }
            }
          ],
          "rangesMember": [
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 17
              },
              "to": {
                "hour": 22
              }
            },
            {
              "days": [
                6
              ],
              "from": {
                "hour": 10
              },
              "to": {
                "hour": 22
              }
            },
            {
              "days": [
                0
              ],
              "from": {
                "hour": 10
              },
              "to": {
                "hour": 18
              }
            }
          ]
        },
        {
          "id": "REDUCED",
          "displayName": "REDUCED",
          "MEMBER": 10,
          "MEMBER_PREM": 15,
          "NON_MEMBER": 20,
          "NON_MEMBER_PREM": 30,
          "ranges": [],
          "rangesMember": [
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 12
              },
              "to": {
                "hour": 17
              }
            },
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 22
              },
              "to": {
                "hour": 24
              }
            },
            {
              "days": [
                6
              ],
              "from": {
                "hour": 22
              },
              "to": {
                "hour": 24
              }
            }
          ]
        }
      ]
    }
  },
// more pods ...
]

Script

const pods = [... exported json...]
for (const pod of pods) {
  const pricing = {
    extraGuests: { ...pod.pricing.extraGuests },
    defaultRate: {
      id: pod.pricing.defaultRate.id,
      disPlayName: pod.pricing.defaultRate.displayName,
      rate: pod.pricing.defaultRate.NON_MEMBER,
      lockingFee: Math.max(pod.pricing.defaultRate.NON_MEMBER_PREM - pod.pricing.defaultRate.NON_MEMBER, 0)
    },
    ...pod.pricing.specialRates.length > 0
      ? {
        specialRates: [
          ...pod.pricing.specialRates.map(specialRate => {
            return {
              id: specialRate.id,
              disPlayName: specialRate.displayName,
              rate: specialRate.NON_MEMBER,
              lockingFee: Math.max(specialRate.NON_MEMBER_PREM - specialRate.NON_MEMBER, 0),
              ranges: [...specialRate.ranges]
            }
          })
        ]
      } : { specialRates: [] },
    membershipOverrides: {
      'default': {
        defaultRate: {
          id: pod.pricing.defaultRate.id,
          disPlayName: pod.pricing.defaultRate.displayName,
          rate: pod.pricing.defaultRate.MEMBER,
          lockingFee: Math.max(pod.pricing.defaultRate.MEMBER_PREM - pod.pricing.defaultRate.MEMBER, 0)
        },
        ...pod.pricing.specialRates.length > 0
          ? {
            specialRates: [
              ...pod.pricing.specialRates.map(specialRate => {
                return {
                  id: specialRate.id,
                  disPlayName: specialRate.displayName,
                  rate: specialRate.MEMBER,
                  lockingFee: Math.max(specialRate.MEMBER_PREM - specialRate.MEMBER, 0),
                  ranges: [...specialRate.rangesMember]
                }
              })
            ]
          } : { specialRates: [] }
      }
    }
  }
  console.log(`UPDATE pods SET rates = '${JSON.stringify(pricing, null, 2)}' WHERE id = '${pod.id}';`)
}

Usage:

# node migration.ts > pods_update.sql

Example output

UPDATE pods SET rates = '{
  "extraGuests": {
    "private": {
      "minGuests": 6,
      "maxGuests": 8,
      "rate": {
        "MEMBER": {
          "OFF_PEAK": 2.5,
          "REDUCED": 2.5,
          "PEAK": 2.5
        },
        "NON_MEMBER": {
          "OFF_PEAK": 5,
          "REDUCED": 5,
          "PEAK": 5
        }
      }
    },
    "coach": {
      "minGuests": 2,
      "maxGuests": 4,
      "rate": {
        "MEMBER": {
          "OFF_PEAK": 0,
          "PEAK": 0,
          "REDUCED": 0
        },
        "NON_MEMBER": {
          "OFF_PEAK": 0,
          "PEAK": 0,
          "REDUCED": 0
        }
      }
    }
  },
  "defaultRate": {
    "id": "OFF_PEAK",
    "disPlayName": "OFF PEAK",
    "rate": 20,
    "lockingFee": 10
  },
  "specialRates": [
    {
      "id": "PEAK",
      "disPlayName": "PEAK",
      "rate": 40,
      "lockingFee": 10,
      "ranges": [
        {
          "days": [
            1,
            2,
            3,
            4,
            5
          ],
          "from": {
            "hour": 17
          },
          "to": {
            "hour": 24
          }
        },
        {
          "days": [
            1,
            2,
            3,
            4,
            5,
            6,
            0
          ],
          "from": {
            "hour": 0
          },
          "to": {
            "hour": 6
          }
        },
        {
          "days": [
            6,
            0
          ],
          "from": {
            "hour": 10
          },
          "to": {
            "hour": 24
          }
        }
      ]
    },
    {
      "id": "REDUCED",
      "disPlayName": "REDUCED",
      "rate": 20,
      "lockingFee": 10,
      "ranges": []
    }
  ],
  "membershipOverrides": {
    "default": {
      "defaultRate": {
        "id": "OFF_PEAK",
        "disPlayName": "OFF PEAK",
        "rate": 0,
        "lockingFee": 10
      },
      "specialRates": [
        {
          "id": "PEAK",
          "disPlayName": "PEAK",
          "rate": 20,
          "lockingFee": 5,
          "ranges": [
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 17
              },
              "to": {
                "hour": 22
              }
            },
            {
              "days": [
                6
              ],
              "from": {
                "hour": 10
              },
              "to": {
                "hour": 22
              }
            },
            {
              "days": [
                0
              ],
              "from": {
                "hour": 10
              },
              "to": {
                "hour": 18
              }
            }
          ]
        },
        {
          "id": "REDUCED",
          "disPlayName": "REDUCED",
          "rate": 10,
          "lockingFee": 5,
          "ranges": [
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 12
              },
              "to": {
                "hour": 17
              }
            },
            {
              "days": [
                1,
                2,
                3,
                4,
                5
              ],
              "from": {
                "hour": 22
              },
              "to": {
                "hour": 24
              }
            },
            {
              "days": [
                6
              ],
              "from": {
                "hour": 22
              },
              "to": {
                "hour": 24
              }
            }
          ]
        }
      ]
    }
  }
}' WHERE id = 'e95-multi';