CREATE TABLE test_drive_packages (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR UNIQUE NOT NULL,
  package_type VARCHAR NOT NULL,
  description VARCHAR,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE test_drive_package_teachers (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id VARCHAR(64) REFERENCES users(id) ON DELETE CASCADE,
  test_drive_package_id VARCHAR(64) REFERENCES test_drive_packages(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE lesson_bookings ADD COLUMN test_drive_package_id VARCHAR(64) REFERENCES test_drive_packages(id);

CREATE TABLE test_drive_package_choices (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  test_drive_package_id VARCHAR(64) REFERENCES test_drive_packages(id) ON DELETE CASCADE,
  user_id VARCHAR(64) REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE test_drive_package_choice_teachers (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  test_drive_package_choice_id VARCHAR(64) REFERENCES test_drive_package_choices(id) ON DELETE CASCADE,
  teacher_id VARCHAR(64) REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE teachers ADD COLUMN short_bio VARCHAR;
ALTER TABLE test_drive_package_teachers ADD COLUMN short_bio VARCHAR;