53 lines
2.4 KiB
MySQL
53 lines
2.4 KiB
MySQL
|
-- Add column to toggle accommodations module
|
||
|
|
ALTER TABLE public.families
|
||
|
|
ADD enable_accommodations boolean NOT NULL DEFAULT false;
|
||
|
|
COMMENT
|
||
|
|
ON COLUMN public.families.enable_accommodations IS 'Specify whether accommodations feature is enabled for the family';
|
||
|
|
|
||
|
|
|
||
|
|
-- Create tables
|
||
|
|
CREATE TABLE IF NOT EXISTS accommodations_list
|
||
|
|
(
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
family_id integer NOT NULL REFERENCES families,
|
||
|
|
time_create BIGINT NOT NULL,
|
||
|
|
time_update BIGINT NOT NULL,
|
||
|
|
name VARCHAR(50) NOT NULL,
|
||
|
|
need_validation BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
description text NULL,
|
||
|
|
color VARCHAR(6) NULL,
|
||
|
|
open_to_reservations BOOLEAN NOT NULL DEFAULT false
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMENT ON COLUMN accommodations_list.need_validation is 'true if family admin review is required for validation. False otherwise';
|
||
|
|
COMMENT ON COLUMN accommodations_list.open_to_reservations is 'true if reservations can be created / updated. False otherwise';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS accommodations_reservations
|
||
|
|
(
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
family_id integer NOT NULL REFERENCES families ON DELETE CASCADE,
|
||
|
|
accommodation_id integer NOT NULL REFERENCES accommodations_list ON DELETE CASCADE,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
|
||
|
|
time_create BIGINT NOT NULL,
|
||
|
|
time_update BIGINT NOT NULL,
|
||
|
|
reservation_start BIGINT NOT NULL,
|
||
|
|
reservation_end BIGINT NOT NULL,
|
||
|
|
validated BOOLEAN NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMENT ON COLUMN accommodations_reservations.validated is 'null if not reviewed yet. true if reservation is accepted. false if reservation is rejected';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS accommodations_reservations_cals_urls
|
||
|
|
(
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
family_id integer NOT NULL REFERENCES families ON DELETE CASCADE,
|
||
|
|
accommodation_id integer NULL REFERENCES accommodations_list ON DELETE CASCADE,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
|
||
|
|
name VARCHAR(50) NOT NULL,
|
||
|
|
token VARCHAR(50) NOT NULL,
|
||
|
|
time_create BIGINT NOT NULL,
|
||
|
|
time_used BIGINT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMENT ON COLUMN accommodations_reservations_cals_urls.accommodation_id is 'null to get reservations of all accommodations. otherwise get the reservations of the specified accommodation only';
|