86 lines
3.2 KiB
SQL
86 lines
3.2 KiB
SQL
-- +goose Up
|
|
CREATE TABLE IF NOT EXISTS crews (
|
|
id BINARY(16) PRIMARY KEY,
|
|
crew_type VARCHAR(32) NOT NULL,
|
|
user_id BINARY(16) NOT NULL,
|
|
start_date DATETIME NOT NULL,
|
|
end_date DATETIME NOT NULL,
|
|
created_at DATETIME NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
CONSTRAINT chk_crews_crew_type CHECK (crew_type IN ('main', 'additional')),
|
|
CONSTRAINT chk_crews_date_range CHECK (start_date <= end_date),
|
|
CONSTRAINT fk_crews_user FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE INDEX idx_crews_user_id ON crews(user_id);
|
|
CREATE INDEX idx_crews_crew_type ON crews(crew_type);
|
|
CREATE INDEX idx_crews_start_date ON crews(start_date);
|
|
CREATE INDEX idx_crews_end_date ON crews(end_date);
|
|
|
|
CREATE TABLE IF NOT EXISTS roster_crews (
|
|
id BINARY(16) PRIMARY KEY,
|
|
roster_id BINARY(16) NOT NULL,
|
|
crew_id BINARY(16) NOT NULL,
|
|
confirm_at DATETIME NULL,
|
|
role VARCHAR(32) NOT NULL,
|
|
created_at DATETIME NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
CONSTRAINT chk_roster_crews_role CHECK (role IN ('Pilot', 'Copilot', 'Doctor', 'AirRescuer')),
|
|
CONSTRAINT fk_roster_crews_roster FOREIGN KEY (roster_id) REFERENCES hems_duty_rosters(id),
|
|
CONSTRAINT fk_roster_crews_crew FOREIGN KEY (crew_id) REFERENCES crews(id)
|
|
);
|
|
|
|
CREATE INDEX idx_roster_crews_roster_id ON roster_crews(roster_id);
|
|
CREATE INDEX idx_roster_crews_role ON roster_crews(role);
|
|
CREATE UNIQUE INDEX idx_roster_crews_roster_crew ON roster_crews(roster_id, crew_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS duty_roster_other_persons (
|
|
id BINARY(16) PRIMARY KEY,
|
|
roster_id BINARY(16) NOT NULL,
|
|
name VARCHAR(191) NOT NULL,
|
|
mobile_phone VARCHAR(64) NULL,
|
|
email VARCHAR(191) NULL,
|
|
created_at DATETIME NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
CONSTRAINT fk_duty_roster_other_persons_roster FOREIGN KEY (roster_id) REFERENCES hems_duty_rosters(id)
|
|
);
|
|
|
|
CREATE INDEX idx_duty_roster_other_persons_roster_id ON duty_roster_other_persons(roster_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS duty_roster_crew_pilot_additionals (
|
|
id BINARY(16) PRIMARY KEY,
|
|
crew_id BINARY(16) NOT NULL,
|
|
flight_instructor TINYINT(1) NOT NULL DEFAULT 0,
|
|
line_checker TINYINT(1) NOT NULL DEFAULT 0,
|
|
supervisor TINYINT(1) NOT NULL DEFAULT 0,
|
|
examiner TINYINT(1) NOT NULL DEFAULT 0,
|
|
copilot TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NULL,
|
|
created_by BINARY(16) NULL,
|
|
updated_at DATETIME NULL,
|
|
updated_by BINARY(16) NULL,
|
|
deleted_at DATETIME NULL,
|
|
deleted_by BINARY(16) NULL,
|
|
CONSTRAINT fk_duty_roster_crew_pilot_additionals_crew FOREIGN KEY (crew_id) REFERENCES crews(id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_duty_roster_crew_pilot_additionals_crew_id ON duty_roster_crew_pilot_additionals(crew_id);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS duty_roster_crew_pilot_additionals;
|
|
DROP TABLE IF EXISTS duty_roster_other_persons;
|
|
DROP TABLE IF EXISTS roster_crews;
|
|
DROP TABLE IF EXISTS crews;
|