/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     6/7/2017 21:32:10                            */
/*==============================================================*/


ALTER TABLE ALQUILER
   DROP CONSTRAINT FK_ALQUILER_FK_ALQUIL_EMBARCAC;

ALTER TABLE ALQUILER
   DROP CONSTRAINT FK_ALQUILER_FK_CLIENT_CLIENTE;

ALTER TABLE EMBARCACION
   DROP CONSTRAINT FK_EMBARCAC_FK_REPARA_REPARACI;

ALTER TABLE EMBARCACION
   DROP CONSTRAINT FK_EMBARCAC_FK_TRIPUL_TRIPULAC;

ALTER TABLE FACTURA
   DROP CONSTRAINT FK_FACTURA_FK_ALQUIL_ALQUILER;

ALTER TABLE MANTENIMIENTO
   DROP CONSTRAINT FK_MANTENIM_FK_MAN_EM_EMBARCAC;

ALTER TABLE PASAJEROS
   DROP CONSTRAINT FK_PASAJERO_FK_TRIPUL_TRIPULAC;

ALTER TABLE TRIPULACION
   DROP CONSTRAINT FK_TRIPULAC_FK_TRIPUL_EMBARCAC;

DROP INDEX FK_ALQUILER_FACTURA_FK;

DROP INDEX FK_ALQUILER_EMBARCACION_FK;

DROP INDEX FK_CLIENTE_ALQUILER_FK;

DROP TABLE ALQUILER CASCADE CONSTRAINTS;

DROP INDEX FK_CLIENTE_FACTURA_FK;

DROP TABLE CLIENTE CASCADE CONSTRAINTS;

DROP INDEX FK_TRIPULACION_EMBARCACION_FK;

DROP INDEX FK_REPARACION_EMBARCACION_FK;

DROP TABLE EMBARCACION CASCADE CONSTRAINTS;

DROP INDEX FK_ALQUILER_FACTURA2_FK;

DROP TABLE FACTURA CASCADE CONSTRAINTS;

DROP INDEX FK_MAN_EMBARCA_FK;

DROP TABLE MANTENIMIENTO CASCADE CONSTRAINTS;

DROP INDEX FK_TRIPULACION_PASAJERO_FK;

DROP TABLE PASAJEROS CASCADE CONSTRAINTS;

DROP TABLE REPARACION CASCADE CONSTRAINTS;

DROP INDEX FK_TRIPULACION_EMBARCACION2_FK;

DROP TABLE TRIPULACION CASCADE CONSTRAINTS;

/*==============================================================*/
/* Table: ALQUILER                                              */
/*==============================================================*/
CREATE TABLE ALQUILER  (
   ID_ALQUILER          INTEGER                         NOT NULL,
   ID_FACTURA           INTEGER,
   ID_EMBARCACION       INTEGER,
   ID_CLIENTE           INTEGER,
   VALOR_INICIAL_ALQUILER NUMBER(10,2),
   VALOR_FINAL_ALQUILER NUMBER(10,2),
   FECHA_ALQUILER       DATE,
   FECHA_ENTREGA_EMBARCACION DATE,
   FECHA_SALIDA_EMBARCACION DATE,
   OBSERVACION_ALQUILER VARCHAR2(200),
   CONSTRAINT PK_ALQUILER PRIMARY KEY (ID_ALQUILER)
);

/*==============================================================*/
/* Index: FK_CLIENTE_ALQUILER_FK                                */
/*==============================================================*/
CREATE INDEX FK_CLIENTE_ALQUILER_FK ON ALQUILER (
   ID_CLIENTE ASC
);

/*==============================================================*/
/* Index: FK_ALQUILER_EMBARCACION_FK                            */
/*==============================================================*/
CREATE INDEX FK_ALQUILER_EMBARCACION_FK ON ALQUILER (
   ID_EMBARCACION ASC
);

/*==============================================================*/
/* Index: FK_ALQUILER_FACTURA_FK                                */
/*==============================================================*/
CREATE INDEX FK_ALQUILER_FACTURA_FK ON ALQUILER (
   ID_FACTURA ASC
);

/*==============================================================*/
/* Table: CLIENTE                                               */
/*==============================================================*/
CREATE TABLE CLIENTE  (
   ID_CLIENTE           INTEGER                         NOT NULL,
   ID_FACTURA           INTEGER,
   NOMBRE_CLIENTE       VARCHAR2(50),
   DIRECCION_CLIENTE    VARCHAR2(70),
   TELEFONO             VARCHAR2(14),
   REINCIDENCIA_ALQUILER INTEGER,
   CEDULA               VARCHAR2(11),
   OBSERVACION_CLIENTE  VARCHAR2(200),
   CONSTRAINT PK_CLIENTE PRIMARY KEY (ID_CLIENTE)
);

/*==============================================================*/
/* Index: FK_CLIENTE_FACTURA_FK                                 */
/*==============================================================*/
CREATE INDEX FK_CLIENTE_FACTURA_FK ON CLIENTE (
   ID_FACTURA ASC
);

/*==============================================================*/
/* Table: EMBARCACION                                           */
/*==============================================================*/
CREATE TABLE EMBARCACION  (
   ID_EMBARCACION       INTEGER                         NOT NULL,
   ID_REPARACION        INTEGER,
   ID_TRIPULACION       INTEGER,
   MATRICULA            VARCHAR2(50),
   TIPO_EMBARCACION     VARCHAR2(50),
   NOMBRE               VARCHAR2(50),
   MAX_PASAJEROS        INTEGER,
   ESTADO               VARCHAR2(50),
   DISPONIBLE           SMALLINT,
   CONSTRAINT PK_EMBARCACION PRIMARY KEY (ID_EMBARCACION)
);

/*==============================================================*/
/* Index: FK_REPARACION_EMBARCACION_FK                          */
/*==============================================================*/
CREATE INDEX FK_REPARACION_EMBARCACION_FK ON EMBARCACION (
   ID_REPARACION ASC
);

/*==============================================================*/
/* Index: FK_TRIPULACION_EMBARCACION_FK                         */
/*==============================================================*/
CREATE INDEX FK_TRIPULACION_EMBARCACION_FK ON EMBARCACION (
   ID_TRIPULACION ASC
);

/*==============================================================*/
/* Table: FACTURA                                               */
/*==============================================================*/
CREATE TABLE FACTURA  (
   ID_FACTURA           INTEGER                         NOT NULL,
   ID_ALQUILER          INTEGER,
   ID_CLIENTE           INTEGER,
   PAGO_INICIAL         NUMBER(10,2),
   MULTA                NUMBER(10,2),
   IVA                  NUMBER(10,2),
   PAGO_TOTAL           NUMBER(10,2),
   FECHA_FACTURA        DATE,
   CONSTRAINT PK_FACTURA PRIMARY KEY (ID_FACTURA)
);

/*==============================================================*/
/* Index: FK_ALQUILER_FACTURA2_FK                               */
/*==============================================================*/
CREATE INDEX FK_ALQUILER_FACTURA2_FK ON FACTURA (
   ID_ALQUILER ASC
);

/*==============================================================*/
/* Table: MANTENIMIENTO                                         */
/*==============================================================*/
CREATE TABLE MANTENIMIENTO  (
   ID_MANTENIMIENTO     INTEGER                         NOT NULL,
   ID_EMBARCACION       INTEGER,
   FECHA_MANTENIMIENTO  DATE,
   HORA_MANTENIMIENTO   DATE,
   FECHA_ENTREGA_MANTENIMIENTO DATE,
   HORA_ENTREGA_MANTENIMIENTO DATE,
   VALOR_MANTENIMIENTO  NUMBER(10,2),
   OBSERVACION_MANTENIMIENTO VARCHAR2(200),
   CONSTRAINT PK_MANTENIMIENTO PRIMARY KEY (ID_MANTENIMIENTO)
);

/*==============================================================*/
/* Index: FK_MAN_EMBARCA_FK                                     */
/*==============================================================*/
CREATE INDEX FK_MAN_EMBARCA_FK ON MANTENIMIENTO (
   ID_EMBARCACION ASC
);

/*==============================================================*/
/* Table: PASAJEROS                                             */
/*==============================================================*/
CREATE TABLE PASAJEROS  (
   ID_PASAJERO          INTEGER                         NOT NULL,
   ID_TRIPULACION       INTEGER,
   EDAD                 INTEGER,
   NOMBRE_PASAJERO      VARCHAR2(50),
   SEXO                 VARCHAR2(50),
   CONSTRAINT PK_PASAJEROS PRIMARY KEY (ID_PASAJERO)
);

/*==============================================================*/
/* Index: FK_TRIPULACION_PASAJERO_FK                            */
/*==============================================================*/
CREATE INDEX FK_TRIPULACION_PASAJERO_FK ON PASAJEROS (
   ID_TRIPULACION ASC
);

/*==============================================================*/
/* Table: REPARACION                                            */
/*==============================================================*/
CREATE TABLE REPARACION  (
   ID_REPARACION        INTEGER                         NOT NULL,
   FECHA_REPARACION     DATE,
   VALOR_REPARACION     NUMBER(10,2),
   CONSTRAINT PK_REPARACION PRIMARY KEY (ID_REPARACION)
);

/*==============================================================*/
/* Table: TRIPULACION                                           */
/*==============================================================*/
CREATE TABLE TRIPULACION  (
   ID_TRIPULACION       INTEGER                         NOT NULL,
   ID_EMBARCACION       INTEGER,
   PUESTOS_DISPONIBLE   INTEGER,
   PUESTOS_OCUPADOS     INTEGER,
   CONSTRAINT PK_TRIPULACION PRIMARY KEY (ID_TRIPULACION)
);

/*==============================================================*/
/* Index: FK_TRIPULACION_EMBARCACION2_FK                        */
/*==============================================================*/
CREATE INDEX FK_TRIPULACION_EMBARCACION2_FK ON TRIPULACION (
   ID_EMBARCACION ASC
);

ALTER TABLE ALQUILER
   ADD CONSTRAINT FK_ALQUILER_FK_ALQUIL_EMBARCAC foreign KEY (ID_EMBARCACION)
      references EMBARCACION (ID_EMBARCACION);

ALTER TABLE ALQUILER
   ADD CONSTRAINT FK_ALQUILER_FK_CLIENT_CLIENTE foreign KEY (ID_CLIENTE)
      references CLIENTE (ID_CLIENTE);

ALTER TABLE CLIENTE
   ADD CONSTRAINT FK_CLIENTE_FK_CLIENT_FACTURA foreign KEY (ID_FACTURA)
      references FACTURA (ID_FACTURA);

ALTER TABLE EMBARCACION
   ADD CONSTRAINT FK_EMBARCAC_FK_REPARA_REPARACI foreign KEY (ID_REPARACION)
      references REPARACION (ID_REPARACION);

ALTER TABLE EMBARCACION
   ADD CONSTRAINT FK_EMBARCAC_FK_TRIPUL_TRIPULAC foreign KEY (ID_TRIPULACION)
      references TRIPULACION (ID_TRIPULACION);

ALTER TABLE FACTURA
   ADD CONSTRAINT FK_FACTURA_FK_ALQUIL_ALQUILER foreign KEY (ID_ALQUILER)
      references ALQUILER (ID_ALQUILER);

ALTER TABLE MANTENIMIENTO
   ADD CONSTRAINT FK_MANTENIM_FK_MAN_EM_EMBARCAC foreign KEY (ID_EMBARCACION)
      references EMBARCACION (ID_EMBARCACION);

ALTER TABLE PASAJEROS
   ADD CONSTRAINT FK_PASAJERO_FK_TRIPUL_TRIPULAC foreign KEY (ID_TRIPULACION)
      references TRIPULACION (ID_TRIPULACION);

ALTER TABLE TRIPULACION
   ADD CONSTRAINT FK_TRIPULAC_FK_TRIPUL_EMBARCAC foreign KEY (ID_EMBARCACION)
      references EMBARCACION (ID_EMBARCACION);

CREATE OR REPLACE TRIGGER tr_b_i_calculo_multa
before
INSERT OR UPDATE ON FACTURA
FOR EACH ROW
DECLARE
dias NUMBER :=0;
fecha_inicial DATE;
valor_inicial NUMBER :=0;
mensaje VARCHAR2(200) :='';
BEGIN
    SELECT fecha_entrega_embarcacion,valor_inicial_alquiler INTO fecha_inicial,valor_inicial FROM alquiler WHERE id_alquiler = :NEW.id_alquiler;
    dias := :NEW.fecha_factura - fecha_inicial;
    :NEW.pago_inicial := valor_inicial;
    :NEW.iva := valor_inicial*(12/100);
    :NEW.multa := 0;
    DBMS_OUTPUT.put_line(dias);
    IF dias>=1 THEN
        :NEW.multa := valor_inicial*(15/100);
        mensaje := 'Excedió el limite de horas de alquiler '||:NEW.multa;
    END IF;
    :NEW.pago_total := :NEW.iva+:NEW.multa+:NEW.pago_inicial;
    UPDATE cliente SET observacion_cliente = mensaje WHERE id_cliente = :NEW.id_cliente;
    UPDATE alquiler SET observacion_alquiler = mensaje,valor_final_alquiler = :NEW.pago_total
    WHERE id_alquiler = :NEW.id_alquiler;
END;

CREATE OR REPLACE TRIGGER tr_b_i_calculo_reicidencia
after
INSERT OR UPDATE ON ALQUILER
FOR EACH ROW
DECLARE
reincidencia INTEGER :=0;
BEGIN
    SELECT reincidencia_alquiler INTO reincidencia FROM cliente WHERE id_cliente = :NEW.id_cliente;
    IF reincidencia IS NULL THEN reincidencia := 0; END IF;
    reincidencia := reincidencia +1;
    UPDATE cliente SET REINCIDENCIA_ALQUILER = reincidencia WHERE id_cliente = :NEW.id_cliente;
END;

CREATE OR REPLACE TRIGGER tr_b_i_descuento
before
INSERT OR UPDATE ON ALQUILER
FOR EACH ROW
DECLARE
reincidencia INTEGER :=0;
BEGIN
    SELECT reincidencia_alquiler INTO reincidencia FROM cliente WHERE id_cliente = :NEW.id_cliente;
    IF reincidencia >=7 THEN
        :NEW.valor_inicial_alquiler := :NEW.valor_inicial_alquiler - (:NEW.valor_inicial_alquiler * (40/100));
        UPDATE cliente SET reincidencia_alquiler = 0 WHERE id_cliente = :NEW.id_cliente;
    END IF;
END;

CREATE OR REPLACE TRIGGER tr_b_i_eliminar_tripulacion
before
INSERT OR UPDATE ON FACTURA
FOR EACH ROW
DECLARE
embarcacion INTEGER :=0;
tripulacion INTEGER :=0;
BEGIN
    SELECT id_embarcacion INTO embarcacion FROM alquiler WHERE id_alquiler = :NEW.id_alquiler;
    SELECT id_tripulacion INTO tripulacion FROM embarcacion WHERE id_embarcacion = embarcacion;
    DELETE FROM pasajeros WHERE id_tripulacion = tripulacion;
    DELETE FROM tripulacion WHERE id_embarcacion = embarcacion;
END;