set search_path to "20200608esDWvendite";
drop table if exists macrocategorie cascade;
create table macrocategorie(
	codice char(5) primary key,
	descrizione char(20)
);
drop table if exists categorie cascade;
create table categorie(
	codice char(5) primary key,
	descrizione char(20),
	macrocategoria char(5) references macrocategorie(codice)
);
drop table if exists prodotti cascade;
create table prodotti(
	codiceprodotto char(5) primary key,
	descrizione char(20),
	prezzo numeric(6,2),
	categoria char(5) references categorie(codice)
);
drop table if exists vendite cascade;
create table vendite(
	numeroscontrino integer primary key,
	ora time
);
drop table if exists dettagliovendite cascade;
create table dettagliovendite(
	numeroscontrino integer references vendite(numeroscontrino),
	codiceprodotto char(5) references prodotti(codiceprodotto),
	quantita integer,
	primary key(numeroscontrino,codiceprodotto)
);
insert into macrocategorie values ('C', 'Prodotti per casa');
insert into macrocategorie values ('A', 'Alimentari');
insert into categorie values ('B', 'Biscotti', 'A');
insert into categorie values ('M', 'Marmellate', 'A');
insert into categorie values ('S', 'Saponi', 'C');
insert into categorie values ('D', 'Detersivi', 'C');
insert into prodotti values ('P001','XXXX',12.50,'D');
insert into prodotti values ('P002','XYX',2.50,'M');
insert into prodotti values ('P003','XYZ',31.00,'B');
insert into prodotti values ('P004','ZZZ',4.50,'M');
insert into prodotti values ('P005','KKK',3.50,'M');
insert into prodotti values ('P006','YYYY',5.50,'D');
insert into vendite values (1001,'09:05');
insert into vendite values (1002,'09:24');
insert into vendite values (1003,'10:15');
insert into vendite values (1004,'12:05');
insert into vendite values (1005,'14:05');
insert into vendite values (1006,'14:05');
insert into dettagliovendite values (1001,'P001',2);
insert into dettagliovendite values (1001,'P002',3);
insert into dettagliovendite values (1001,'P003',1);
insert into dettagliovendite values (1002,'P003',1);
insert into dettagliovendite values (1002,'P006',2);
insert into dettagliovendite values (1003,'P003',2);
insert into dettagliovendite values (1003,'P004',1);
insert into dettagliovendite values (1004,'P001',3);
insert into dettagliovendite values (1004,'P003',1);
insert into dettagliovendite values (1004,'P005',1);
insert into dettagliovendite values (1005,'P004',1);
insert into dettagliovendite values (1006,'P001',1);
insert into dettagliovendite values (1006,'P003',1);


--- Dettagli per le due dimensioni temporali
drop table if exists DimOre;
create table DimOre (
	KOre integer primary key,
	OraInizio integer, 
	Fascia1ora char(5),
	Fascia2ore char(5),
	MattPom char(10)
);
insert into DimOre values ( 1, 9, ' 9-10', ' 9-11','Mattina');
insert into DimOre values ( 2,10, '10-11', ' 9-11','Mattina');
insert into DimOre values ( 3,11, '11-12', '11-13','Mattina');
insert into DimOre values ( 4,12, '12-13', '11-13','Mattina');
insert into DimOre values ( 5,13, '13-14', '13-15','Pomeriggio');
insert into DimOre values ( 6,14, '14-15', '13-15','Pomeriggio');
insert into DimOre values ( 7,15, '15-16', '15-17','Pomeriggio');
insert into DimOre values ( 8,16, '16-17', '15-17','Pomeriggio');
insert into DimOre values ( 9,17, '17-18', '17-19','Pomeriggio');
insert into DimOre values (10,18, '18-19', '17-19','Pomeriggio');
--- per la data solo alcuni campi descrittivi
drop table if exists DimDate;
create table DimDate (
	KDate integer primary key,
	Data date, 
	GiornoSettimanaNum integer,
	GiornoSettimana char(3),
	GiornoMese integer, 
	GiornoAnno integer
);
--- per la data solo alcune ennuple
insert into DimDate values (1, '08-06-2020', 1, 'Lun', 8, 160);
insert into DimDate values (2, '09-06-2020', 2, 'Mar', 9, 161);
insert into DimDate values (3, '10-06-2020', 3, 'Mer',10, 162);
insert into DimDate values (4, '11-06-2020', 3, 'Gio',11, 163);

create table DimProdotti(
	KProdotto serial primary key,
	codiceprodotto char(5) ,
	descrizioneprodotto char(20),
	codicecategoria char(5) ,
	descrizionecategoria char(20),
	codicemacrocategoria char(5) ,
	descrizioneMacroCategoria char(20)
);

insert into DimProdotti (codiceprodotto, descrizioneprodotto,
	codicecategoria, descrizionecategoria,
    codicemacrocategoria, descrizioneMacroCategoria)
select CodiceProdotto, p.Descrizione as DescrizioneProdotto,
       c.Codice as CodiceCategoria, c.Descrizione as DescrizioneCategoria,
	   m.Codice as CodiceMacroCategoria, m.Descrizione as DescrizioneMacroCategoria
from (prodotti p join categorie c on categoria = c.codice)
         join macrocategorie m on macrocategoria = m.codice
where CodiceProdotto not in (select CodiceProdotto from DimProdotti);

create table DimNegozi(
	KNegozio serial primary key,
	codicenegozio char(5) ,
	descrizionenegozio char(20),
	citta char(20) 
);
insert into DimNegozi (codicenegozio) values ('Neg01');

create table FattiVendite(
	KDate integer,
	KOre integer,
	KProdotto integer,
	KNegozio integer,
	Quantita integer, 
	Importo numeric(10,2)
);

create table FattiVenditeConChiaviProduzione(
	Data date,
	Ora integer,
	CodiceProdotto char(5),
	CodiceNegozio char(5),
	Quantita integer, 
	Importo numeric(10,2)
);

insert into FattiVenditeConChiaviProduzione 
	select current_date as data, extract(hour from ora) as ora, 
	       codiceprodotto, 'Neg01' as codicenegozio, 
	       sum(quantita) as quantita, sum(quantita*prezzo) as importo
    from vendite join dettagliovendite using (numeroscontrino)
             join prodotti using (codiceprodotto)
    group by codiceprodotto, extract(hour from ora)

insert into FattiVendite
select KDate, KOre, KProdotto, KNegozio, Quantita, Importo
from FattiVenditeConChiaviProduzione join DimDate using (Data)
                  join DimOre on (ora = oraInizio)
                  join DimProdotti using (CodiceProdotto)
                  join DimNegozi using (CodiceNegozio)


