DROP TABLE cn_firma CASCADE CONSTRAINTS
 ;

/**********************************/
/* Table Name: cn_firma */
/**********************************/
CREATE TABLE cn_firma (
 id_firma NUMBER NOT NULL,
 id_cia NUMBER NOT NULL,
 nombre VARCHAR2(128) NOT NULL,
 documento NUMBER NOT NULL,
 cargo VARCHAR2(128) NOT NULL,
 numero NUMBER NULL,
 anexo VARCHAR2(128) NOT NULL,
 activo NUMBER(1) DEFAULT 1 NOT NULL,
 orden NUMBER NULL
)

/*comentarios de los campos*/
COMMENT ON TABLE cn_firma is 'tabla cn_firma de representantes'
 ;
COMMENT ON COLUMN cn_firma.id_cia is 'id_cia que corresponde a la compañia'
 ;
COMMENT ON COLUMN cn_firma.id_firma is 'consecutivo de id_firma'
 ;
COMMENT ON COLUMN cn_firma.nombre is 'nombre de representante'
 ;
COMMENT ON COLUMN cn_firma.documento is 'documento del respresentante'
 ;
COMMENT ON COLUMN cn_firma.cargo is 'cargo del representante'
 ;
COMMENT ON COLUMN cn_firma.numero is 'numero de la tarjeta de profesional'
 ;
COMMENT ON COLUMN cn_firma.anexo is 'descripcion padjunto de archivos'
 ;
COMMENT ON COLUMN cn_firma.activo is 'estado que determina si aun esta vigente'
 ;
COMMENT ON COLUMN cn_firma.orden is 'orden o consecutivo para las firmas'
 ;


ALTER TABLE cn_firma ADD CONSTRAINT cn_firma_PK PRIMARY KEY (id_firma,id_cia)
 ;
ALTER TABLE cn_firma ADD CONSTRAINT cn_firma_FK0 FOREIGN KEY (id_cia) REFERENCES cn_cia (id_cia)
 ;

/*Asegura que los valores sean de un bit 0 o 1*/

ALTER TABLE CN_FIRMA
ADD CONSTRAINT cn_firma_act_bit CHECK (ACTIVO IN ('0','1'))
;

/************************************************************/
/*tabla relacion firmas e informes*/

DROP TABLE cn_informe_firma CASCADE CONSTRAINTS
 ;

/**********************************/
/* Table Name: cn_informe_firma */
/**********************************/
CREATE TABLE cn_informe_firma (
 id_informe_firma NUMBER NOT NULL,
 id_cia NUMBER NOT NULL,
 comando_informe VARCHAR2(128) NOT NULL,
 id_firma NUMBER NOT NULL
)
 ;
/*comentarios de los campos*/
COMMENT ON TABLE cn_informe_firma is 'tabla relacion firmas e informes'
 ;
COMMENT ON COLUMN cn_informe_firma.id_cia is 'id_cia que corresponde a la compañia'
 ;
COMMENT ON COLUMN cn_informe_firma.id_informe_firma is 'consecutivo para la relacion de informes y firmas'
 ;
COMMENT ON COLUMN cn_informe_firma.comando_informe is 'nombre de identificacion del informe'
 ;
COMMENT ON COLUMN cn_informe_firma.id_firma is 'id del representante firma'
 ;


/*llave primaria*/
ALTER TABLE cn_informe_firma ADD CONSTRAINT cn_informe_firma_PK PRIMARY KEY (id_cia,comando_informe,id_firma)
 ;

ALTER TABLE cn_informe_firma ADD CONSTRAINT cn_informe_firma_FK0 FOREIGN KEY (id_cia) REFERENCES cn_cia (id_cia)
 ;

ALTER TABLE cn_informe_firma ADD CONSTRAINT cn_informe_firma_FK1 FOREIGN KEY (id_firma,id_cia) REFERENCES cn_firma (id_firma,id_cia)
 ;

/*prueba secuencia*/
DROP SEQUENCE cn_informe_firma_SEQ
 ;
CREATE SEQUENCE cn_informe_firma_SEQ NOMAXVALUE NOCACHE NOORDER NOCYCLE
 ;
DROP TRIGGER cn_informe_firma_TRG
 ;
CREATE TRIGGER cn_informe_firma_TRG
BEFORE INSERT ON cn_informe_firma
FOR EACH ROW
BEGIN
SELECT cn_informe_firma_SEQ.NEXTVAL INTO :NEW.id_informe_firma FROM DUAL;
END;
 ;

