-- 1) Tabela principal: reservation_details
CREATE TABLE reservation_details (
  idReservation           INT             NOT NULL,
  idEntityCompany         INT             NOT NULL,
  companyName             VARCHAR(255)    NULL,
  idRequesterCompany      INT             NULL,
  requesterCompanyName    VARCHAR(255)    NULL,
  groupName               VARCHAR(255)    NULL,
  contactName             VARCHAR(255)    NULL,
  contact1                VARCHAR(100)    NULL,
  contact2                VARCHAR(100)    NULL,
  dateAdd                 DATETIME(3)     NULL,
  dateUp                  DATETIME(3)     NULL,
  creditState             VARCHAR(50)     NULL,
  PRIMARY KEY (idReservation, idEntityCompany)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) reservation_rooms (filha de reservation_details)
CREATE TABLE reservation_rooms (
  idReservation           INT             NOT NULL,
  idEntityCompany         INT             NOT NULL,
  room_index              TINYINT UNSIGNED NOT NULL,
  idRoom                  INT             NULL,
  code                    VARCHAR(50)     NULL,
  checkIn                 DATETIME        NULL,
  checkOut                DATETIME        NULL,
  idRoomType              INT             NULL,
  roomTypeName            VARCHAR(255)    NULL,
  amount                  DECIMAL(12,2)   NULL,
  status                  TINYINT         NULL,
  pax                     TINYINT         NULL,
  chd1                    TINYINT         NULL,
  chd2                    TINYINT         NULL,
  chd3                    TINYINT         NULL,
  ratePlanId              INT             NULL,
  ratePlanName            VARCHAR(255)    NULL,
  mealPlanDesc            VARCHAR(255)    NULL,
  PRIMARY KEY (idReservation, idEntityCompany, room_index),
  INDEX idx_rooms_parent  (idReservation, idEntityCompany),
  FOREIGN KEY (idReservation, idEntityCompany)
    REFERENCES reservation_details (idReservation, idEntityCompany)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) reservation_guests (filha de reservation_details)
CREATE TABLE reservation_guests (
  idReservation             INT             NOT NULL,
  idEntityCompany           INT             NOT NULL,
  guest_index               TINYINT UNSIGNED NOT NULL,
  idEntity                  INT             NULL,
  name                      VARCHAR(255)    NULL,
  idRoom                    INT             NULL,
  contactMail               VARCHAR(255)    NULL,
  contactPhone              VARCHAR(50)     NULL,
  main                      TINYINT(1)      NULL,
  federalRegistrationNumber VARCHAR(50)     NULL,
  documentType              TINYINT         NULL,
  gender                    TINYINT         NULL,
  birthDate                 DATE            NULL,
  mainDocType               TINYINT         NULL,
  docCpfCnpjPassport        VARCHAR(100)    NULL,
  addressZipCode            VARCHAR(20)     NULL,
  addressCountry            VARCHAR(100)    NULL,
  addressStateCode          VARCHAR(10)     NULL,
  addressStateName          VARCHAR(100)    NULL,
  addressCity               VARCHAR(100)    NULL,
  addressNeighborhood       VARCHAR(100)    NULL,
  addressAddress            VARCHAR(255)    NULL,
  addressDetails            VARCHAR(255)    NULL,
  addressNumber             VARCHAR(20)     NULL,
  PRIMARY KEY (idReservation, idEntityCompany, guest_index),
  INDEX idx_guests_parent   (idReservation, idEntityCompany),
  FOREIGN KEY (idReservation, idEntityCompany)
    REFERENCES reservation_details (idReservation, idEntityCompany)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) reservation_commissions (filha de reservation_details)
CREATE TABLE reservation_commissions (
  idReservation           INT             NOT NULL,
  idEntityCompany         INT             NOT NULL,
  comm_index              TINYINT UNSIGNED NOT NULL,
  idEntity                INT             NULL,
  percentage              DECIMAL(5,2)    NULL,
  value                   DECIMAL(12,2)   NULL,
  PRIMARY KEY (idReservation, idEntityCompany, comm_index),
  INDEX idx_comms_parent  (idReservation, idEntityCompany),
  FOREIGN KEY (idReservation, idEntityCompany)
    REFERENCES reservation_details (idReservation, idEntityCompany)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) reservation_notes (filha de reservation_details)
CREATE TABLE reservation_notes (
  idReservation           INT             NOT NULL,
  idEntityCompany         INT             NOT NULL,
  note_index              TINYINT UNSIGNED NOT NULL,
  note                     TEXT           NULL,
  PRIMARY KEY (idReservation, idEntityCompany, note_index),
  INDEX idx_notes_parent   (idReservation, idEntityCompany),
  FOREIGN KEY (idReservation, idEntityCompany)
    REFERENCES reservation_details (idReservation, idEntityCompany)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6) reservation_revenue (filha de reservation_details)
CREATE TABLE reservation_revenue (
  idReservation           INT             NOT NULL,
  idEntityCompany         INT             NOT NULL,
  marketSegmentId         INT             NULL,
  marketSegmentName       VARCHAR(100)    NULL,
  idReservationChannel    VARCHAR(50)     NULL,
  nameChannel             VARCHAR(100)    NULL,
  purposeStayId           INT             NULL,
  purpose                 VARCHAR(100)    NULL,
  managerID               VARCHAR(50)     NULL,
  managerName             VARCHAR(100)    NULL,
  PRIMARY KEY (idReservation, idEntityCompany),
  INDEX idx_rev_parent     (idReservation, idEntityCompany),
  FOREIGN KEY (idReservation, idEntityCompany)
    REFERENCES reservation_details (idReservation, idEntityCompany)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7) reservation_room_requirements (filha de reservation_rooms)
CREATE TABLE reservation_room_requirements (
  idReservation     INT             NOT NULL,
  idEntityCompany   INT             NOT NULL,
  room_index        TINYINT UNSIGNED NOT NULL,
  req_index         TINYINT UNSIGNED NOT NULL,
  requirement       JSON            NULL,
  PRIMARY KEY (idReservation, idEntityCompany, room_index, req_index),
  INDEX idx_rrr_room (idReservation, idEntityCompany, room_index),
  FOREIGN KEY (idReservation, idEntityCompany, room_index)
    REFERENCES reservation_rooms (idReservation, idEntityCompany, room_index)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8) reservation_guest_notes (filha de reservation_guests)
CREATE TABLE reservation_guest_notes (
  idReservation     INT             NOT NULL,
  idEntityCompany   INT             NOT NULL,
  guest_index       TINYINT UNSIGNED NOT NULL,
  note_index        TINYINT UNSIGNED NOT NULL,
  note_text         TEXT            NULL,
  PRIMARY KEY (idReservation, idEntityCompany, guest_index, note_index),
  INDEX idx_ggn_guest (idReservation, idEntityCompany, guest_index),
  FOREIGN KEY (idReservation, idEntityCompany, guest_index)
    REFERENCES reservation_guests (idReservation, idEntityCompany, guest_index)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
