/*
-- crie o banco de dados
CREATE DATABASE archlintdb ENCODING 'UTF8';
-- no banco de dados criado, execute o script abaixo
*/

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET default_tablespace = '';
SET default_with_oids = false;

SET search_path = public, pg_catalog;

DROP TABLE IF EXISTS modification CASCADE;
DROP TABLE IF EXISTS artifact CASCADE;
DROP TABLE IF EXISTS dependancyclass CASCADE;
DROP TABLE IF EXISTS revisionclass CASCADE;
DROP TABLE IF EXISTS buildedclass CASCADE;
DROP TABLE IF EXISTS classpackage CASCADE;
DROP TABLE IF EXISTS layer CASCADE;
DROP TABLE IF EXISTS revisionlist CASCADE;
DROP TABLE IF EXISTS revision CASCADE;
DROP TABLE IF EXISTS project CASCADE;
DROP TABLE IF EXISTS repository CASCADE;
DROP TABLE IF EXISTS repositorytype CASCADE;
DROP TABLE IF EXISTS role CASCADE;

CREATE TABLE artifact (
    id bigserial CONSTRAINT artifact_pkey PRIMARY KEY, 
    filepath text,
	CONSTRAINT unq_artifact UNIQUE(filepath)
);

CREATE TABLE buildedclass (
    id bigserial CONSTRAINT buildedclass_pkey PRIMARY KEY,
    name text NOT NULL,
    classpackage_id bigint,
	CONSTRAINT unq_buildedclass_0 UNIQUE (name)
);

CREATE TABLE classpackage (
    id bigserial CONSTRAINT classpackage_pkey PRIMARY KEY,
    name text NOT NULL,
	CONSTRAINT unq_classpackage_0 UNIQUE (name)
);

CREATE TABLE dependancyclass (
    id bigserial CONSTRAINT dependancyclass_pkey PRIMARY KEY,
    buildedclass_id bigint NOT NULL,
    revisionclass_id bigint NOT NULL,
	CONSTRAINT unq_dependancyclass_0 UNIQUE (revisionclass_id, buildedclass_id)
);

CREATE TABLE layer (
    id bigserial CONSTRAINT layer_pkey PRIMARY KEY,
    name text,
	CONSTRAINT unq_layer_0 UNIQUE (name)
);

CREATE TABLE modification (
    id bigserial CONSTRAINT modification_pkey PRIMARY KEY,
    artifact_id bigint,
    revision_id bigint,
    actiontype character varying(2)
);

CREATE TABLE project (
    id bigserial CONSTRAINT project_pkey PRIMARY KEY,
    additionalcomments text,
    name character varying(100) NOT NULL,
    summary text,
    urlsite character varying(255),
    repository_id bigint,
	CONSTRAINT unq_project_0 UNIQUE (name)
);

CREATE TABLE repository (
    id bigserial CONSTRAINT repository_pkey PRIMARY KEY,
    urladdress character varying(255) NOT NULL,
    repositorytype_id integer,
	CONSTRAINT unq_repository_0 UNIQUE (urladdress)
);

CREATE TABLE repositorytype (
    id integer CONSTRAINT repositorytype_pkey PRIMARY KEY,
    name character varying(50) NOT NULL,
	CONSTRAINT unq_repositorytype_0 UNIQUE (name)
);

CREATE TABLE revision (
    id bigserial CONSTRAINT revision_pkey PRIMARY KEY,
    author character varying(255) NOT NULL,
    datecommit character varying(50) NOT NULL,
    message text,
    revisionorder integer NOT NULL,
    project_id bigint NOT NULL,
	CONSTRAINT unq_revision_0 UNIQUE (project_id, revisionorder)
);

CREATE TABLE revisionclass (
    id bigserial CONSTRAINT revisionclass_pkey PRIMARY KEY,
    buildedclass_id bigint,
    revision_id bigint,
	CONSTRAINT unq_revisionclass_0 UNIQUE (revision_id, buildedclass_id)
);

ALTER TABLE ONLY buildedclass
    ADD CONSTRAINT fk_buildedclass_classpackage_id FOREIGN KEY (classpackage_id) REFERENCES classpackage(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY dependancyclass
    ADD CONSTRAINT fk_dependancyclass_buildedclass_id FOREIGN KEY (buildedclass_id) REFERENCES buildedclass(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY dependancyclass
    ADD CONSTRAINT fk_dependancyclass_revisionclass_id FOREIGN KEY (revisionclass_id) REFERENCES revisionclass(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY modification
    ADD CONSTRAINT fk_modification_artifact_id FOREIGN KEY (artifact_id) REFERENCES artifact(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY modification
    ADD CONSTRAINT fk_modification_revision_id FOREIGN KEY (revision_id) REFERENCES revision(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_project_repository_id FOREIGN KEY (repository_id) REFERENCES repository(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY repository
    ADD CONSTRAINT fk_repository_repositorytype_id FOREIGN KEY (repositorytype_id) REFERENCES repositorytype(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY revision
    ADD CONSTRAINT fk_revision_project_id FOREIGN KEY (project_id) REFERENCES project(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY revisionclass
    ADD CONSTRAINT fk_revisionclass_buildedclass_id FOREIGN KEY (buildedclass_id) REFERENCES buildedclass(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY revisionclass
    ADD CONSTRAINT fk_revisionclass_revision_id FOREIGN KEY (revision_id) REFERENCES revision(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO repositorytype VALUES (1, 'Subversion');
INSERT INTO repositorytype VALUES (2, 'Mercurial');
INSERT INTO repositorytype VALUES (3, 'Git');
INSERT INTO repositorytype VALUES (4, 'CVS');

-- adicionado em 24/04/2012 - 19:18 por Cristiano Maffort
ALTER TABLE repository ADD COLUMN username character varying(50);

ALTER TABLE repository ADD COLUMN passwd character varying(50);

ALTER TABLE revision ALTER COLUMN revisionorder TYPE bigint;

ALTER TABLE revisionclass ADD COLUMN isstub boolean;

ALTER TABLE revisionclass ALTER COLUMN isstub SET DEFAULT true;

-- adicionado em 15/05/2012 - 14:00 por Cristiano Maffort
CREATE TABLE role (
    id bigserial CONSTRAINT role_pkey PRIMARY KEY,
    project_id bigint NOT NULL,
    layer_id bigint NOT NULL,
    classpackage_id bigint
);

ALTER TABLE ONLY role 
   ADD CONSTRAINT fk_role_project_id FOREIGN KEY (project_id)
      REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY role 
   ADD CONSTRAINT fk_role_layer_id FOREIGN KEY (layer_id)
      REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY role 
   ADD CONSTRAINT fk_role_classpackage_id FOREIGN KEY (classpackage_id)
      REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- CRIA SCHEMA metricas	  

DROP SCHEMA IF EXISTS metricas CASCADE;

CREATE SCHEMA metricas;

DROP TABLE IF EXISTS metricas.headrevision CASCADE;

CREATE TABLE metricas.headrevision (
    id bigserial CONSTRAINT headrevision_pkey PRIMARY KEY,
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    CONSTRAINT unq_headrevision_0 UNIQUE (revision_id),
    CONSTRAINT unq_headrevision_1 UNIQUE (project_id)
);

ALTER TABLE ONLY metricas.headrevision 
    ADD CONSTRAINT fk_headrevision_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.headrevision 
    ADD CONSTRAINT fk_headrevision_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


DROP TABLE IF EXISTS metricas.filters CASCADE;

CREATE TABLE metricas.threshold (
    id bigserial CONSTRAINT threshold_pkey PRIMARY KEY,
    project_id bigint NOT NULL,
    a_sca numeric,
    a_ins numeric,
    d_h1_sca numeric,
    d_h1_del numeric,
    d_h1_hvy numeric,
    d_h2_sca numeric,
    d_h2_del numeric,
    d_h3_dir numeric
);
  
ALTER TABLE ONLY metricas.threshold 
    ADD CONSTRAINT fk_threshold_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

-- TABELAS PARA ARMAZENAR MÉTRICAS SEGUNDO ARTIGO SBQS 

DROP TABLE IF EXISTS metricas.dmc CASCADE;

-- D(m,c): número de classes de m que dependem de c 
CREATE TABLE metricas.dmc (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    -- classe da qual o módulo <module_id> depende
    buildedclass_target bigint NOT NULL,
    -- número de classes do módulo <classpackage_base> que dependem de <buildedclass_target> na revisão
    -- <revisionorder> do sistema <project_id>
    dmc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.dmc 
    ADD CONSTRAINT fk_dmc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dmc 
    ADD CONSTRAINT fk_dmc_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dmc 
    ADD CONSTRAINT fk_dmc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dmc 
    ADD CONSTRAINT fk_dmc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- -------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS metricas.cm CASCADE;

-- C(m): número de classes de m 
CREATE TABLE metricas.cm (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    -- número de classes do módulo <classpackage_base> na revisão <revisionorder> do sistema <project_id>
    cm_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.cm 
    ADD CONSTRAINT fk_cm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cm 
    ADD CONSTRAINT fk_cm_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cm
    ADD CONSTRAINT fk_cm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.drumc CASCADE;

-- DRU(m,c): percentual de classes de m que dependem de c
CREATE TABLE metricas.drumc (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    -- classe da qual o módulo <classpackage_base> depende
    buildedclass_target bigint NOT NULL,
    -- número de classes de m que utilizam a classe c	
    dmc_count bigint NOT NULL,
    -- número de classes de m
    cm_count bigint NOT NULL,
    -- percentual de classes do módulo <classpackage_base> que dependem de <buildedclass_target> na revisão
    -- <revisionorder> do sistema <project_id>
    drumc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.drumc 
    ADD CONSTRAINT fk_drumc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drumc
    ADD CONSTRAINT fk_drumc_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drumc 
    ADD CONSTRAINT fk_drumc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drumc 
    ADD CONSTRAINT fk_drumc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.dcm CASCADE;

-- DC(m): classes das quais classes do módulo m depende
-- "o módulo m depende de quais classes?"
CREATE TABLE metricas.dcm (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    -- classe da qual o módulo <classpackage_base> depende
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.dcm 
    ADD CONSTRAINT fk_dcm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcm
    ADD CONSTRAINT fk_dcm_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcm 
    ADD CONSTRAINT fk_dcm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcm 
    ADD CONSTRAINT fk_dcm_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;      

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.amvm CASCADE;

-- CLASSES DE M QUE NÃO UTILIZAM C QUANDO (100*<drumc_rate>)% DAS CLASSES DE M USAM C 
CREATE TABLE metricas.amvm (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    -- módulo base
    classpackage_base bigint NOT NULL,
    -- classe pertencente a <classpackage_base>
    buildedclass_base bigint NOT NULL,
    -- classe NÃO é utilizada por <buildedclass_base>
    buildedclass_target bigint NOT NULL,
    -- número de classes de m que utilizam a classe c	
    dmc_count bigint NOT NULL,
    -- número de classes de m
    cm_count bigint NOT NULL,    
    -- percentual de classes de <classpackage_base> que utilizam <buildedclass_target>
    drumc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.amvm 
    ADD CONSTRAINT fk_amvm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amvm
    ADD CONSTRAINT fk_amvm_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amvm 
    ADD CONSTRAINT fk_amvm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amvm 
    ADD CONSTRAINT fk_amvm_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;      

ALTER TABLE ONLY metricas.amvm 
    ADD CONSTRAINT fk_amvm_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE; 

-- -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 

DROP TABLE IF EXISTS metricas.dlc CASCADE;

-- D(l,c): número de classes de l que dependem de c 
CREATE TABLE metricas.dlc (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    -- classe da qual o componente <layer_base> depende
    buildedclass_target bigint NOT NULL,
    -- número de classes do componente <layer_base> que dependem de <buildedclass_target> na revisão
    -- <revisionorder> do sistema <project_id>
    dlc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.dlc 
    ADD CONSTRAINT fk_dlc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dlc 
    ADD CONSTRAINT fk_dlc_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dlc 
    ADD CONSTRAINT fk_dlc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dlc 
    ADD CONSTRAINT fk_dlc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.cl CASCADE;

-- C(l): número de classes de l
CREATE TABLE metricas.cl (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    -- número de classes do componente <layer_base> na revisão <revisionorder> do sistema <project_id>
    cl_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.cl 
    ADD CONSTRAINT fk_cl_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cl 
    ADD CONSTRAINT fk_cl_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cl
    ADD CONSTRAINT fk_cl_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.drulc CASCADE;

-- DRU(l,c): percentual de classes de l que dependem de c
CREATE TABLE metricas.drulc (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    -- classe da qual o componente <layer_base> depende
    buildedclass_target bigint NOT NULL,
    -- número de classes de l que utilizam a classe c	
    dlc_count bigint NOT NULL,
    -- número de classes de l
    cl_count bigint NOT NULL,
    -- percentual de classes do componente <layer_base> que dependem de <buildedclass_target> na revisão
    -- <revisionorder> do sistema <project_id>
    drulc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.drulc 
    ADD CONSTRAINT fk_drulc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drulc
    ADD CONSTRAINT fk_drulc_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drulc 
    ADD CONSTRAINT fk_drulc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drulc 
    ADD CONSTRAINT fk_drulc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.dcl CASCADE;

-- DC(l): classes das quais classes do componente l depende
-- "o componente l depende de quais classes?"
CREATE TABLE metricas.dcl (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    -- classe da qual o componente <layer_base> depende
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.dcl 
    ADD CONSTRAINT fk_dcl_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcl
    ADD CONSTRAINT fk_dcl_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcl 
    ADD CONSTRAINT fk_dcl_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dcl 
    ADD CONSTRAINT fk_dcl_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;      

-- ------------------------------------------------------------------------------------------------- 

DROP TABLE IF EXISTS metricas.alvl CASCADE;

-- CLASSES DE L QUE NÃO UTILIZAM C QUANDO (100*<drulc_rate>)% DAS CLASSES DE L USAM C 
CREATE TABLE metricas.alvl (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    -- classe pertencente a <layer_base>
    buildedclass_base bigint NOT NULL,
    -- classe NÃO é utilizada por <buildedclass_base>
    buildedclass_target bigint NOT NULL,
    -- número de classes de l que utilizam a classe c	
    dlc_count bigint NOT NULL,
    -- número de classes de l
    cl_count bigint NOT NULL,    
    -- percentual de classes de <layer_base> que utilizam <buildedclass_target>
    drulc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.alvl
    ADD CONSTRAINT fk_alvl_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alvl
    ADD CONSTRAINT fk_alvl_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alvl 
    ADD CONSTRAINT fk_alvl_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alvl 
    ADD CONSTRAINT fk_alvl_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;      

ALTER TABLE ONLY metricas.alvl 
    ADD CONSTRAINT fk_alvl_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

-- PRIMEIRA VERSAO DA CLASSE NO REPOSITÓRIO
CREATE TABLE metricas.bornclass (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    buildedclass_id bigint NOT NULL,
    CONSTRAINT unq_bornclass_0 UNIQUE (project_id, revisionorder, buildedclass_id)
);

ALTER TABLE ONLY metricas.bornclass 
    ADD CONSTRAINT fk_bornclass_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.bornclass
    ADD CONSTRAINT fk_bornclass_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;     

ALTER TABLE ONLY metricas.bornclass 
    ADD CONSTRAINT fk_bornclass_buildedclass_id FOREIGN KEY (buildedclass_id) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- ------------------------------------------------------------------------------------------------- 

-- RELAÇÕES DE DEPENDÊNCIA ESTABELECIDAS QUANDO A CLASSE "NASCE"

DROP TABLE IF EXISTS metricas.first;

CREATE TABLE metricas.first (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL, 
    -- primeira revisão da classe
    revisionclass_base bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,   
    -- classe da qual a classe <buildedclass_base> depende
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.first
    ADD CONSTRAINT fk_first_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;     

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_revisionclass_base FOREIGN KEY (revisionclass_base) REFERENCES revisionclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.first 
    ADD CONSTRAINT fk_first_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- RELAÇÕES DE DEPENDÊNCIA EXISTENSTE NA VERSÃO ATUAL DO SISTEMA

DROP TABLE IF EXISTS metricas.head;

CREATE TABLE metricas.head (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,     
    -- revisao atual da classe
    revisionclass_base bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_base bigint NOT NULL,
--    layer_target bigint NOT NULL,
--    classpackage_target bigint NOT NULL,    
    -- classe da qual a classe <buildedclass_base> depende
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.head
    ADD CONSTRAINT fk_head_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;     

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_revisionclass_base FOREIGN KEY (revisionclass_base) REFERENCES revisionclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.head 
    ADD CONSTRAINT fk_head_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- CLASSES QUE NASCERAM SEM DEPENDER DE CLASSES QUE M DEPENDE

DROP TABLE IF EXISTS metricas.cwdm;

CREATE TABLE metricas.cwdm (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.cwdm  
    ADD CONSTRAINT fk_cwdm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdm  
    ADD CONSTRAINT fk_cwdm_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdm  
    ADD CONSTRAINT fk_cwdm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdm 
    ADD CONSTRAINT fk_cwdm_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdm  
    ADD CONSTRAINT fk_cwdm_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- NÚMERO DE CLASSES DE M QUE NASCERAM SEM DEPENDER DE CLASSES QUE M DEPENDE

DROP TABLE IF EXISTS metricas.cwdmc;

CREATE TABLE metricas.cwdmc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    cwdmc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.cwdmc  
    ADD CONSTRAINT fk_cwdmc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdmc  
    ADD CONSTRAINT fk_cwdmc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdmc  
    ADD CONSTRAINT fk_cwdmc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdmc  
    ADD CONSTRAINT fk_cwdmc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- CLASSES QUE ADICIONARAM RELAÇÃO DE DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.adlm;

CREATE TABLE metricas.adlm (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.adlm  
    ADD CONSTRAINT fk_adlm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlm  
    ADD CONSTRAINT fk_adlm_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlm  
    ADD CONSTRAINT fk_adlm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlm  
    ADD CONSTRAINT fk_adlm_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlm  
    ADD CONSTRAINT fk_adlm_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- NÚMERO DE CLASSES DE M QUE ADICIONARAM RELAÇÃO COM C

DROP TABLE IF EXISTS metricas.adlmc;

CREATE TABLE metricas.adlmc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    adlmc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.adlmc  
    ADD CONSTRAINT fk_adlmc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlmc  
    ADD CONSTRAINT fk_adlmc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlmc  
    ADD CONSTRAINT fk_adlmc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adlmc  
    ADD CONSTRAINT fk_adlmc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- PERCENTUAL DE CLASSES DE M QUE NASCERAM SEM RELA;ÁO COM C MAS QUE ADICIONARAM DEPOIS

DROP TABLE IF EXISTS metricas.dhrmc;

CREATE TABLE metricas.dhrmc (
    project_id bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    cwdmc_count bigint NOT NULL,
    adlmc_count bigint NOT NULL,
    dhrmc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.dhrmc  
    ADD CONSTRAINT fk_dhrmc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dhrmc  
    ADD CONSTRAINT fk_dhrmc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dhrmc  
    ADD CONSTRAINT fk_dhrmc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- CLASSES DE M QUE NASCERAM SEM DEPENDËNCIA COM C QUANDO UM PERCENTUAL DE CLASSES DE M ADICIONARAM DEPENDËNCIA COM C

DROP TABLE IF EXISTS metricas.amhvm;

CREATE TABLE metricas.amhvm (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,    
    buildedclass_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    cwdmc_count bigint NOT NULL,
    adlmc_count bigint NOT NULL,
    dhrmc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.amhvm  
    ADD CONSTRAINT fk_amhvm_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
-- CLASSES QUE NASCERAM SEM DEPENDER DE CLASSES QUE L DEPENDE

DROP TABLE IF EXISTS metricas.cwdl;

CREATE TABLE metricas.cwdl (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.cwdl  
    ADD CONSTRAINT fk_cwdl_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdl  
    ADD CONSTRAINT fk_cwdl_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdl 
    ADD CONSTRAINT fk_cwdl_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdl  
    ADD CONSTRAINT fk_cwdl_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- NÚMERO DE CLASSES DE L QUE NASCERAM SEM DEPENDER DE CLASSES QUE L DEPENDE

DROP TABLE IF EXISTS metricas.cwdlc;

CREATE TABLE metricas.cwdlc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL,
    cwdlc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.cwdlc  
    ADD CONSTRAINT fk_cwdlc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdlc  
    ADD CONSTRAINT fk_cwdlc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.cwdlc  
    ADD CONSTRAINT fk_cwdlc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- CLASSES DE L QUE ADICIONARAM RELAÇÃO DE DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.adll;

CREATE TABLE metricas.adll (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.adll
    ADD CONSTRAINT fk_adll_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adll  
    ADD CONSTRAINT fk_adll_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adll  
    ADD CONSTRAINT fk_adll_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adll  
    ADD CONSTRAINT fk_adll_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- NÚMERO DE CLASSES DE L QUE ADICIONARAM RELAÇÃO COM C

DROP TABLE IF EXISTS metricas.adllc;

CREATE TABLE metricas.adllc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL,
    adllc_count bigint NOT NULL
);

ALTER TABLE ONLY metricas.adllc  
    ADD CONSTRAINT fk_adllc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adllc  
    ADD CONSTRAINT fk_adllc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.adllc  
    ADD CONSTRAINT fk_adllc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- PERCENTUAL DE CLASSES DE L QUE NASCERAM SEM RELAÇÃO COM C MAS QUE ADICIONARAM DEPOIS

DROP TABLE IF EXISTS metricas.dhrlc;

CREATE TABLE metricas.dhrlc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    cwdlc_count bigint NOT NULL,
    adllc_count bigint NOT NULL,
    dhrlc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.dhrlc  
    ADD CONSTRAINT fk_dhrlc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dhrlc  
    ADD CONSTRAINT fk_dhrlc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dhrlc  
    ADD CONSTRAINT fk_dhrlc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- CLASSES DE L QUE NASCERAM SEM DEPENDËNCIA COM C QUANDO UM PERCENTUAL DE CLASSES DE L ADICIONARAM DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.alhvl;

CREATE TABLE metricas.alhvl (
    project_id bigint NOT NULL,
    revision_id bigint NOT NULL,
    revisionorder bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,    
    buildedclass_target bigint NOT NULL,
    cwdlc_count bigint NOT NULL,
    adllc_count bigint NOT NULL,
    dhrlc_rate numeric NOT NULL
);

ALTER TABLE ONLY metricas.alhvl  
    ADD CONSTRAINT fk_alhvl_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alhvl  
    ADD CONSTRAINT fk_alhvl_revision_id FOREIGN KEY (revision_id) REFERENCES revision (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;
      
ALTER TABLE ONLY metricas.alhvl  
    ADD CONSTRAINT fk_alhvl_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alhvl  
    ADD CONSTRAINT fk_alhvl_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.alhvl  
    ADD CONSTRAINT fk_alhvl_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

-- CLASSES QUE EM ALGUM MOMENTO PASSADO ESTABELECERAM RELAÇÃO DE DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.dac CASCADE;

CREATE TABLE metricas.dac (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.dac 
    ADD CONSTRAINT fk_dac_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dac 
    ADD CONSTRAINT fk_dac_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dac 
    ADD CONSTRAINT fk_dac_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dac 
    ADD CONSTRAINT fk_dac_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.dac 
    ADD CONSTRAINT fk_dac_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

-- CLASSES QUE REMOVERAM RELAÇÃO DE DEPENDÊNCIA COM C
-- DE MODO QUE NÃO POSSUEM MAIS ESSA DEPENDÊNCIA NA VERSÃO ATUAL DO SISTEMA

DROP TABLE IF EXISTS metricas.drc CASCADE;

CREATE TABLE metricas.drc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    buildedclass_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL
);

ALTER TABLE ONLY metricas.drc 
    ADD CONSTRAINT fk_drc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drc 
    ADD CONSTRAINT fk_drc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drc 
    ADD CONSTRAINT fk_drc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drc 
    ADD CONSTRAINT fk_drc_buildedclass_base FOREIGN KEY (buildedclass_base) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drc 
    ADD CONSTRAINT fk_drc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- PERCENTUAL DO NÚMERO DE CLASSES DE M QUE REMOVERAM RELAÇÃO DE DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.drrmc CASCADE;

CREATE TABLE metricas.drrmc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    classpackage_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL,
    dacm_count bigint,
    drcm_count bigint,
    drrmc_rate numeric
);

ALTER TABLE ONLY metricas.drrmc  
    ADD CONSTRAINT fk_drrmc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drrmc  
    ADD CONSTRAINT fk_drrmc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drrmc  
    ADD CONSTRAINT fk_drrmc_classpackage_base FOREIGN KEY (classpackage_base) REFERENCES classpackage (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;      

ALTER TABLE ONLY metricas.drrmc
    ADD CONSTRAINT fk_drrmc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;


-- PERCENTUAL DO NÚMERO DE CLASSES DE L QUE REMOVERAM RELAÇÃO DE DEPENDÊNCIA COM C

DROP TABLE IF EXISTS metricas.drrlc CASCADE;

CREATE TABLE metricas.drrlc (
    project_id bigint NOT NULL,
    layer_base bigint NOT NULL,
    buildedclass_target bigint NOT NULL,
    dacl_count bigint,
    drcl_count bigint,
    drrlc_rate numeric
);

ALTER TABLE ONLY metricas.drrlc  
    ADD CONSTRAINT fk_drrlc_project_id FOREIGN KEY (project_id) REFERENCES project (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drrlc  
    ADD CONSTRAINT fk_drrlc_layer_base FOREIGN KEY (layer_base) REFERENCES layer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY metricas.drrlc
    ADD CONSTRAINT fk_drrlc_buildedclass_target FOREIGN KEY (buildedclass_target) REFERENCES buildedclass (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;	  