53 lines
2.0 KiB
SQL
53 lines
2.0 KiB
SQL
CREATE TYPE "HolidayCalendarScope" AS ENUM ('COUNTRY', 'STATE', 'CITY');
|
|
|
|
CREATE TABLE "holiday_calendars" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"scopeType" "HolidayCalendarScope" NOT NULL,
|
|
"countryId" TEXT NOT NULL,
|
|
"stateCode" TEXT,
|
|
"metroCityId" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"priority" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "holiday_calendars_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "holiday_calendar_entries" (
|
|
"id" TEXT NOT NULL,
|
|
"holidayCalendarId" TEXT NOT NULL,
|
|
"date" DATE NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"isRecurringAnnual" BOOLEAN NOT NULL DEFAULT false,
|
|
"source" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "holiday_calendar_entries_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "holiday_calendars_countryId_scopeType_idx" ON "holiday_calendars"("countryId", "scopeType");
|
|
CREATE INDEX "holiday_calendars_countryId_stateCode_idx" ON "holiday_calendars"("countryId", "stateCode");
|
|
CREATE INDEX "holiday_calendars_metroCityId_idx" ON "holiday_calendars"("metroCityId");
|
|
CREATE INDEX "holiday_calendar_entries_date_idx" ON "holiday_calendar_entries"("date");
|
|
|
|
CREATE UNIQUE INDEX "holiday_calendar_entries_holidayCalendarId_date_name_key"
|
|
ON "holiday_calendar_entries"("holidayCalendarId", "date", "name");
|
|
|
|
ALTER TABLE "holiday_calendars"
|
|
ADD CONSTRAINT "holiday_calendars_countryId_fkey"
|
|
FOREIGN KEY ("countryId") REFERENCES "countries"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "holiday_calendars"
|
|
ADD CONSTRAINT "holiday_calendars_metroCityId_fkey"
|
|
FOREIGN KEY ("metroCityId") REFERENCES "metro_cities"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "holiday_calendar_entries"
|
|
ADD CONSTRAINT "holiday_calendar_entries_holidayCalendarId_fkey"
|
|
FOREIGN KEY ("holidayCalendarId") REFERENCES "holiday_calendars"("id")
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|