-- ===========================================================================
-- 
--  Copyright 2014  E. URBAH
--                  at LAL, Univ Paris-Sud, IN2P3/CNRS, Orsay, France
--  License GPL v3
-- 
--  XtremWeb-HEP Billing :  SQL script creating following views :
-- 
--  View_CustomerContracts
--  View_CustomerDetails
--  View_Metrics
--  View_MetricTaxes
--  View_TariffDetails
--  View_TariffPlans
--  View_TaxDetails
-- 
-- ===========================================================================
drop view if exists  View_CustomerContracts;
drop view if exists  View_CustomerDetails;
drop view if exists  View_Metrics;
drop view if exists  View_MetricTaxes;
drop view if exists  View_TariffDetails;
drop view if exists  View_TariffPlans;
drop view if exists  View_TaxDetails;

-- -----------------------------------------------------
create view View_CustomerContracts as
-- -----------------------------------------------------
select CustomerAccount, CustomerBalance, TariffPlanName, CustomerContractStartDate,
       CustomerContractEndDate, CustomerContractDescription, CustomerDescription
from   CustomerContracts inner join Customers   on CustomerContracts.CustomerId   = Customers.CustomerId
                         inner join TariffPlans on CustomerContracts.TariffPlanId = TariffPlans.TariffPlanId;
show warnings;

-- -----------------------------------------------------
create view View_CustomerDetails as
-- -----------------------------------------------------
select CustomerAccount, CustomerBalance, CustomerDetailsStartDate, CustomerName,
       CustomerBillingAddress, CustomerVATnumber, CustomerComment, CustomerDescription
from   CustomerDetails inner join Customers on CustomerDetails.CustomerId = Customers.CustomerId;
show warnings;

-- -----------------------------------------------------
create view View_Metrics as
-- -----------------------------------------------------
select MetricName, MetricDimension, MetricTable, MetricColumn, MetricDescription
from   Metrics;
show warnings;

-- -----------------------------------------------------
create view View_MetricTaxes as
-- -----------------------------------------------------
select MetricName, TaxName, MetricTaxesComment
from   MetricTaxes inner join Metrics on MetricTaxes.MetricId = Metrics.MetricId
                   inner join Taxes   on MetricTaxes.TaxId    = Taxes.TaxId;
show warnings;

-- -----------------------------------------------------
create view View_TariffDetails as
-- -----------------------------------------------------
select     TariffPlanName, MetricName, TariffDetailStartDate, TariffPackageThreshold,
           TariffPackageFare, TariffUsageRate, TariffComment
from       TariffDetails
inner join TariffPlans on TariffDetails.TariffPlanId = TariffPlans.TariffPlanId
inner join Metrics     on TariffDetails.MetricId     = Metrics.MetricId;
show warnings;

-- -----------------------------------------------------
create view View_TariffPlans as
-- -----------------------------------------------------
select     TariffPlanName, TariffPlanMaxSimultaneousTasks, TariffPlanDescription
from       TariffPlans;
show warnings;

-- -----------------------------------------------------
create view View_TaxDetails as
-- -----------------------------------------------------
select TaxName, TaxDetailStartDate, TaxLevel, TaxRate, TaxComment
from   TaxDetails inner join Taxes on TaxDetails.TaxId = Taxes.TaxId;
show warnings;
