CREATE TABLE REMESAS_CLIENTE (
    ID                  TIPO_ID NOT NULL,
    ID_EMPRESA          TIPO_ID,
    REFERENCIA          VARCHAR(255),
    FECHA_REMESA        DATE,
    IMPORTE_TOTAL       TIPO_IMPORTE,
    TITULAR             VARCHAR(255),
    ENTIDAD             VARCHAR(15),
    SUCURSAL            VARCHAR(15),
    DC                  VARCHAR(15),
    CUENTA              VARCHAR(15),
    DESCRIPCION         VARCHAR(255),
    FECHA_ALTA          DATE,
    FECHA_MODIFICACION  DATE,
    USUARIO             TIPO_USUARIO
);
ALTER TABLE REMESAS_CLIENTE ADD CONSTRAINT PK_REMESAS_CLIENTE PRIMARY KEY (ID);
ALTER TABLE REMESAS_CLIENTE ADD CONSTRAINT FK_REMESAS_CLIENTE FOREIGN KEY (ID_EMPRESA) REFERENCES EMPRESAS (ID);

/* Trigger: TRI_INSERT_REMESAS_CLIENTE */
CREATE TRIGGER TRI_INSERT_REMESAS_CLIENTE FOR REMESAS_CLIENTE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  NEW.FECHA_ALTA = current_timestamp;
  NEW.FECHA_MODIFICACION = Null;
end;

/* Trigger: TRI_UPDATE_REMESAS_CLI */
CREATE TRIGGER TRI_UPDATE_REMESAS_CLI FOR REMESAS_CLIENTE
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  NEW.FECHA_MODIFICACION = current_timestamp;
end;


CREATE GENERATOR GEN_REMESAS_CLIENTE_ID;
SET GENERATOR GEN_REMESAS_CLIENTE_ID TO 1;


CREATE TABLE RECIBOS_CLIENTE (
    ID                  TIPO_ID NOT NULL,
    ID_FACTURA          TIPO_ID,
    REFERENCIA          VARCHAR(255),
    FECHA_VENCIMIENTO   DATE,
    DESCRIPCION         VARCHAR(255),
    OBSERVACIONES       VARCHAR(255),
    IMPORTE             TIPO_IMPORTE,
    OTROS_GASTOS        TIPO_IMPORTE,
    FECHA_ALTA          DATE,
    FECHA_MODIFICACION  DATE,
    USUARIO             TIPO_USUARIO,
    ID_REMESA           TIPO_ID
);
ALTER TABLE RECIBOS_CLIENTE ADD CONSTRAINT PK_RECIBOS_CLIENTE PRIMARY KEY (ID);
ALTER TABLE RECIBOS_CLIENTE ADD CONSTRAINT FK_RECIBOS_CLIENTE FOREIGN KEY (ID_FACTURA) REFERENCES FACTURAS_CLIENTE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE RECIBOS_CLIENTE ADD CONSTRAINT FK_RECIBOS_CLIENTE2 FOREIGN KEY (ID_REMESA) REFERENCES REMESAS_CLIENTE (ID) ON DELETE SET NULL ON UPDATE SET NULL;


/* Trigger: TRI_INSERT_RECIBOS_CLIENTE */
CREATE TRIGGER TRI_INSERT_RECIBOS_CLIENTE FOR RECIBOS_CLIENTE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  NEW.FECHA_ALTA = current_timestamp;
  NEW.FECHA_MODIFICACION = Null;
end;

/* Trigger: TRI_UPDATE_RECIBOS_CLIENTE */
CREATE TRIGGER TRI_UPDATE_RECIBOS_CLIENTE FOR RECIBOS_CLIENTE
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  NEW.FECHA_MODIFICACION = current_timestamp;
end;

CREATE GENERATOR GEN_RECIBOS_CLIENTE_ID;
SET GENERATOR GEN_RECIBOS_CLIENTE_ID TO 1;



CREATE TABLE PAGOS_CLIENTE (
    ID                  TIPO_ID NOT NULL,
    ID_RECIBO           TIPO_ID,
    TIPO                VARCHAR(255),
    FECHA_PAGO          DATE,
    TITULAR             VARCHAR(255),
    ENTIDAD             VARCHAR(15),
    SUCURSAL            VARCHAR(15),
    DC                  VARCHAR(15),
    CUENTA              VARCHAR(15),
    FECHA_ALTA          DATE,
    FECHA_MODIFICACION  DATE,
    USUARIO             TIPO_USUARIO
);
ALTER TABLE PAGOS_CLIENTE ADD CONSTRAINT PK_PAGOS_CLIENTE PRIMARY KEY (ID);
ALTER TABLE PAGOS_CLIENTE ADD CONSTRAINT FK_PAGOS_CLIENTE FOREIGN KEY (ID_RECIBO) REFERENCES RECIBOS_CLIENTE (ID);

/* Trigger: TRI_INSERT_PAGOS_CLIENTE */
CREATE TRIGGER TRI_INSERT_PAGOS_CLIENTE FOR PAGOS_CLIENTE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  NEW.FECHA_ALTA = current_timestamp;
  NEW.FECHA_MODIFICACION = Null;
end;

/* Trigger: TRI_UPDATE_PAGOS_CLIENTE */
CREATE TRIGGER TRI_UPDATE_PAGOS_CLIENTE FOR PAGOS_CLIENTE
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  NEW.FECHA_MODIFICACION = current_timestamp;
end;

CREATE GENERATOR GEN_PAGOS_CLIENTE_ID;
SET GENERATOR GEN_PAGOS_CLIENTE_ID TO 1;




/*VISTAS*/

DECLARE EXTERNAL FUNCTION MOD
    INTEGER,
    INTEGER
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_mod' MODULE_NAME 'ib_udf';


DROP VIEW V_FACTURAS_CLIENTE;
/*
DROP VIEW V_FAC_CLI_SITUACION;
DROP VIEW V_REC_FAC_CLI_COBRADOS;
DROP VIEW V_REC_FAC_CLI;
DROP VIEW  V_RECIBOS_CLIENTE;
DROP VIEW V_REC_CLI_SITUACION;
*/

CREATE VIEW V_REC_CLI_SITUACION(
    ID_RECIBO,
    SITUACION)
AS
SELECT RECIBOS_CLIENTE.ID,
CASE WHEN (COUNT(PAGOS_CLIENTE.ID_RECIBO)=0) THEN 'PENDIENTE'
     WHEN (MOD(COUNT(PAGOS_CLIENTE.ID_RECIBO), 2)=0) THEN 'DEVUELTO'
     ELSE 'COBRADO'
END
FROM RECIBOS_CLIENTE
LEFT JOIN PAGOS_CLIENTE
ON (PAGOS_CLIENTE.ID_RECIBO = RECIBOS_CLIENTE.ID)
GROUP BY RECIBOS_CLIENTE.ID;

CREATE VIEW V_RECIBOS_CLIENTE(
    ID,
    REFERENCIA,
    SITUACION,
    ID_FACTURA,
    ID_REMESA,
    REFERENCIA_REMESA,
    FECHA_VENCIMIENTO,
    DESCRIPCION,
    OBSERVACIONES,
    IMPORTE,
    OTROS_GASTOS,
    FECHA_FACTURA,
    FORMA_PAGO_FACTURA,
    IMPORTE_FACTURA,
    ID_EMPRESA,
    NOMBRE_CLIENTE,
    NIF_CIF_CLIENTE,
    ENTIDAD_CLIENTE,
    SUCURSAL_CLIENTE,
    DC_CLIENTE,
    CUENTA_CLIENTE,
    FECHA_ALTA,
    FECHA_MODIFICACION,
    USUARIO)
AS
SELECT RECIBOS_CLIENTE.ID, RECIBOS_CLIENTE.REFERENCIA,
       V_REC_CLI_SITUACION.SITUACION,
       RECIBOS_CLIENTE.ID_FACTURA,
       RECIBOS_CLIENTE.ID_REMESA, REMESAS_CLIENTE.REFERENCIA as REFERENCIA_REMESA,
       RECIBOS_CLIENTE.FECHA_VENCIMIENTO, RECIBOS_CLIENTE.DESCRIPCION,
       RECIBOS_CLIENTE.OBSERVACIONES, RECIBOS_CLIENTE.IMPORTE, RECIBOS_CLIENTE.OTROS_GASTOS,

       FACTURAS_CLIENTE.FECHA_FACTURA, FORMAS_PAGO.DESCRIPCION, FACTURAS_CLIENTE.IMPORTE_TOTAL,
       FACTURAS_CLIENTE.ID_EMPRESA,

       CONTACTOS.NOMBRE, CONTACTOS.NIF_CIF, CONTACTOS_DATOS_BANCO.ENTIDAD,
       CONTACTOS_DATOS_BANCO.SUCURSAL, CONTACTOS_DATOS_BANCO.DC, CONTACTOS_DATOS_BANCO.CUENTA,

       RECIBOS_CLIENTE.FECHA_ALTA,
       RECIBOS_CLIENTE.FECHA_MODIFICACION,
       RECIBOS_CLIENTE.USUARIO

FROM V_REC_CLI_SITUACION
LEFT JOIN RECIBOS_CLIENTE
ON (RECIBOS_CLIENTE.ID = V_REC_CLI_SITUACION.ID_RECIBO)
LEFT JOIN FACTURAS_CLIENTE
ON (FACTURAS_CLIENTE.ID = RECIBOS_CLIENTE.ID_FACTURA)
LEFT JOIN FORMAS_PAGO
ON (FORMAS_PAGO.ID = FACTURAS_CLIENTE.ID_FORMA_PAGO)
LEFT JOIN CONTACTOS
ON (CONTACTOS.ID = FACTURAS_CLIENTE.ID_CLIENTE)
LEFT JOIN CONTACTOS_DATOS_BANCO
ON (CONTACTOS_DATOS_BANCO.ID_CONTACTO = CONTACTOS.ID)
LEFT JOIN REMESAS_CLIENTE
ON (REMESAS_CLIENTE.ID = RECIBOS_CLIENTE.ID_REMESA);


CREATE VIEW V_REC_FAC_CLI(
    ID_FACTURA,
    NUM_RECIBOS)
AS
SELECT FACTURAS_CLIENTE.ID, COUNT(RECIBOS_CLIENTE.ID)
FROM FACTURAS_CLIENTE
LEFT JOIN RECIBOS_CLIENTE
ON (RECIBOS_CLIENTE.ID_FACTURA = FACTURAS_CLIENTE.ID)
GROUP BY FACTURAS_CLIENTE.ID;

CREATE VIEW V_REC_FAC_CLI_COBRADOS(
    ID_FACTURA,
    NUM_REC_COBRADOS)
AS
SELECT V_RECIBOS_CLIENTE.ID_FACTURA, COUNT(V_RECIBOS_CLIENTE.ID)
FROM V_RECIBOS_CLIENTE
WHERE (V_RECIBOS_CLIENTE.SITUACION = 'COBRADO')
GROUP BY V_RECIBOS_CLIENTE.ID_FACTURA;


CREATE VIEW V_FAC_CLI_SITUACION(
    ID_FACTURA,
    SITUACION)
AS
SELECT V_REC_FAC_CLI.ID_FACTURA,
CASE
    WHEN ((V_REC_FAC_CLI.NUM_RECIBOS = 0)
         OR (COALESCE (V_REC_FAC_CLI_COBRADOS.NUM_REC_COBRADOS,0) = 0)) THEN 'PENDIENTE'
    WHEN (V_REC_FAC_CLI.NUM_RECIBOS = COALESCE (V_REC_FAC_CLI_COBRADOS.NUM_REC_COBRADOS, 0)) THEN 'PAGADA'
    ELSE 'PARCIALMENTE PAGADA'
END
FROM V_REC_FAC_CLI
LEFT JOIN V_REC_FAC_CLI_COBRADOS
ON (V_REC_FAC_CLI_COBRADOS.ID_FACTURA = V_REC_FAC_CLI.ID_FACTURA);


CREATE VIEW V_FACTURAS_CLIENTE(
    ID,
    ID_EMPRESA,
    REFERENCIA,
    FECHA_FACTURA,
    SITUACION,
    BASE_IMPONIBLE,
    DESCUENTO,
    IMPORTE_DESCUENTO,
    IVA,
    IMPORTE_IVA,
    RE,
    IMPORTE_RE,
    IMPORTE_TOTAL,
    OBSERVACIONES,
    ID_CLIENTE,
    NIF_CIF,
    NOMBRE,
    CALLE,
    POBLACION,
    PROVINCIA,
    CODIGO_POSTAL,
    FECHA_ALTA,
    FECHA_MODIFICACION,
    USUARIO,
    ID_FORMA_PAGO,
    ID_TIPO_IVA,
    IMPORTE_NETO,
    IMPORTE_PORTE)
AS
SELECT FACTURAS_CLIENTE.ID,
  FACTURAS_CLIENTE.ID_EMPRESA,
  FACTURAS_CLIENTE.REFERENCIA,
  FACTURAS_CLIENTE.FECHA_FACTURA,
  V_FAC_CLI_SITUACION.SITUACION,
  FACTURAS_CLIENTE.BASE_IMPONIBLE,
  FACTURAS_CLIENTE.DESCUENTO,
  FACTURAS_CLIENTE.IMPORTE_DESCUENTO,
  FACTURAS_CLIENTE.IVA,
  FACTURAS_CLIENTE.IMPORTE_IVA,
  FACTURAS_CLIENTE.RE,
  FACTURAS_CLIENTE.IMPORTE_RE,
  FACTURAS_CLIENTE.IMPORTE_TOTAL,
  FACTURAS_CLIENTE.OBSERVACIONES,
  FACTURAS_CLIENTE.ID_CLIENTE,
  FACTURAS_CLIENTE.NIF_CIF,
  FACTURAS_CLIENTE.NOMBRE,
  FACTURAS_CLIENTE.CALLE,
  FACTURAS_CLIENTE.POBLACION,
  FACTURAS_CLIENTE.PROVINCIA,
  FACTURAS_CLIENTE.CODIGO_POSTAL,
  FACTURAS_CLIENTE.FECHA_ALTA,
  FACTURAS_CLIENTE.FECHA_MODIFICACION,
  FACTURAS_CLIENTE.USUARIO,
  FACTURAS_CLIENTE.ID_FORMA_PAGO,
  FACTURAS_CLIENTE.ID_TIPO_IVA,
  FACTURAS_CLIENTE.IMPORTE_NETO,
  FACTURAS_CLIENTE.IMPORTE_PORTE
  FROM V_FAC_CLI_SITUACION
  LEFT JOIN FACTURAS_CLIENTE
  ON (FACTURAS_CLIENTE.ID = V_FAC_CLI_SITUACION.ID_FACTURA);