BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE FUNCIONARIO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE SERVICO  CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE PRODUTO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE QUARTO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE DEPENDENTE CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE CLIENTE CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE RESERVA CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE BAR CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE FRIGOBAR CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ESTACIONAMENTO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE PASSEIOTURISTICO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE LAVANDERIA CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE RESTAURANTE CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE PRODUTOSVENDIDOS CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE TELEFONESCLIENTE CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE SERVICOSPRESTADOS CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE EQUIPAMENTOSQUARTO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE AVALIACAO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE HOSPEDAGEM CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
      NULL;
END;

/
CREATE TABLE FUNCIONARIO
  (
     nome               VARCHAR2(255) NOT NULL,
     cpf                VARCHAR2(11) NOT NULL PRIMARY KEY,
     salario            NUMBER(6, 2) NOT NULL,
     data_de_nascimento DATE NOT NULL,
     funcao             VARCHAR2(32) NOT NULL
  );

CREATE TABLE SERVICO
  (
     id    NUMBER(10) NOT NULL PRIMARY KEY,
     valor NUMBER(6, 2) NOT NULL
  );

CREATE TABLE PRODUTO
  (
     id         NUMBER(10) NOT NULL PRIMARY KEY,
     servico_id NUMBER(10) NOT NULL,
     nome       VARCHAR2(255) NOT NULL,
     descricao  VARCHAR2(255),
     valor      NUMBER(6, 2) NOT NULL,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE QUARTO
  (
     id     NUMBER(10) NOT NULL PRIMARY KEY,
     numero NUMBER(10) NOT NULL,
     tipo   VARCHAR2(64) NOT NULL,
     vista  VARCHAR2(64) NOT NULL,
     diaria NUMBER(6, 2) NOT NULL
  );

CREATE TABLE CLIENTE
  (
     nome               VARCHAR2(255) NOT NULL,
     cpf                VARCHAR2(11) NOT NULL PRIMARY KEY,
     data_de_nascimento DATE NOT NULL,
     sexo               VARCHAR2(16),
     rua                VARCHAR2(255),
     numero             NUMBER(10),
     bairro             VARCHAR2(32),
     cep                NUMBER(10)
  );

CREATE TABLE DEPENDENTE
  (
     nome               VARCHAR2(255) NOT NULL,
     cpf                VARCHAR2(11) NOT NULL,
     data_de_nascimento DATE NOT NULL,
     cliente_cpf        VARCHAR2(11) NOT NULL,
     FOREIGN KEY (cliente_cpf) REFERENCES CLIENTE(cpf) ON DELETE CASCADE,
     PRIMARY KEY(cpf, cliente_cpf)
  );

CREATE TABLE RESERVA
  (
     data_inicio DATE NOT NULL,
     data_fim    DATE NOT NULL,
     cliente_cpf VARCHAR2(11) NOT NULL,
     quarto_id   NUMBER(10) NOT NULL,
     FOREIGN KEY (cliente_cpf) REFERENCES CLIENTE(cpf) ON DELETE CASCADE,
     FOREIGN KEY (quarto_id) REFERENCES QUARTO(id) ON DELETE CASCADE,
     PRIMARY KEY(cliente_cpf, quarto_id)
  );

CREATE TABLE AVALIACAO
  (
     cliente_cpf VARCHAR2(11) NOT NULL,
     servico_id  NUMBER(10) NOT NULL,
     nota        NUMBER(2),
     comentario  VARCHAR2(140),
     FOREIGN KEY (cliente_cpf) REFERENCES CLIENTE(cpf) ON DELETE CASCADE,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE HOSPEDAGEM
  (
     quarto_id   NUMBER(10) NOT NULL,
     cliente_cpf VARCHAR2(11) NOT NULL,
     data_inicio DATE NOT NULL,
     data_fim    DATE NOT NULL,
     FOREIGN KEY (cliente_cpf) REFERENCES CLIENTE(cpf) ON DELETE CASCADE,
     FOREIGN KEY (quarto_id) REFERENCES QUARTO(id) ON DELETE CASCADE,
     PRIMARY KEY(cliente_cpf, quarto_id)
  );

CREATE TABLE TELEFONESCLIENTE
  (
     cliente_cpf VARCHAR2(11) NOT NULL,
     telefone    NUMBER(15) NOT NULL,
     FOREIGN KEY (cliente_cpf) REFERENCES CLIENTE(cpf) ON DELETE CASCADE,
     PRIMARY KEY (cliente_cpf, telefone)
  );

CREATE TABLE EQUIPAMENTOSQUARTO
  (
     quarto_id   NUMBER(10) NOT NULL,
     equipamento VARCHAR2(255),
     FOREIGN KEY (quarto_id) REFERENCES QUARTO(id) ON DELETE CASCADE,
     PRIMARY KEY (quarto_id, equipamento)
  );

CREATE TABLE SERVICOSPRESTADOS
  (
     funcionario_cpf VARCHAR2(11) NOT NULL,
     servico_id      NUMBER(10) NOT NULL,
     FOREIGN KEY (funcionario_cpf) REFERENCES FUNCIONARIO(cpf) ON DELETE CASCADE,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE,
     PRIMARY KEY (funcionario_cpf, servico_id)
  );

CREATE TABLE PASSEIOTURISTICO
  (
     servico_id NUMBER(10) NOT NULL PRIMARY KEY,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE LAVANDERIA
  (
     servico_id NUMBER(10) NOT NULL,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE,
     PRIMARY KEY (servico_id)
  );

CREATE TABLE FRIGOBAR
  (
     servico_id NUMBER(10) NOT NULL,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE,
     PRIMARY KEY (servico_id)
  );

CREATE TABLE BAR
  (
     servico_id NUMBER(10) NOT NULL PRIMARY KEY,
     FOREIGN KEY(servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE ESTACIONAMENTO
  (
     servico_id NUMBER(10) NOT NULL PRIMARY KEY,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE RESTAURANTE
  (
     servico_id NUMBER(10) NOT NULL PRIMARY KEY,
     FOREIGN KEY (servico_id) REFERENCES SERVICO(id) ON DELETE CASCADE
  );

CREATE TABLE PRODUTOSVENDIDOS
  (
     produto_id NUMBER(10) NOT NULL,
     quarto_id  NUMBER(10) NOT NULL,
     FOREIGN KEY (produto_id) REFERENCES PRODUTO(id) ON DELETE CASCADE,
     FOREIGN KEY (quarto_id) REFERENCES QUARTO(id) ON DELETE CASCADE,
     data DATE NOT NULL,
     quantidade NUMBER(4) NOT NULL,
     PRIMARY KEY (quarto_id, produto_id)
  );