DROP TABLE OrderProducts;
DROP TABLE OrderBranches;
DROP TABLE Orders;
DROP TABLE OrderTemplateProducts;
DROP TABLE OrderTemplates;

DROP TABLE ClientBranches;
DROP TABLE ClientBrands;
DROP TABLE ClientTaxPayers;
DROP TABLE Clients;

DROP TABLE ProductTaxes;
DROP TABLE Products;
DROP TABLE Units;

CREATE TABLE Units (
    Id SERIAL PRIMARY KEY,
    Name varchar(255),
	Key varchar(10),
    HasDecimalPlaces bool
);

INSERT INTO Units (Name, Key, HasDecimalPlaces) VALUES ('Piezas', 'U', false);
INSERT INTO Units (Name, Key, HasDecimalPlaces) VALUES ('KG', 'KG', True);
INSERT INTO Units (Name, Key, HasDecimalPlaces) VALUES ('Litros', 'L', True);

CREATE TABLE Products (

    Id SERIAL PRIMARY KEY,
    Code varchar(255) NOT NULL,
    Name varchar(255) NOT NULL,
    Category varchar(255) NOT NULL,
    SalePriceRate decimal(12,2) NULL,
    WholesalePrice decimal(12,2) NULL,
    WholesaleQty decimal(12,3) NULL,
    UnitId int REFERENCES Units(id) NOT NULL,
    SupplierCost decimal(12,2) NOT NULL,
    SalePrice decimal(12,2) NOT NULL,
    Buyer varchar(255) NULL,
    Supplier varchar(255) NULL,
    PurchaseZone varchar(255) NULL,
    MinQty decimal(12,3) NULL,
    Qty decimal(12,3) NULL,
    ClaveProductoSat varchar(10) NULL, 
    ClaveUnidadSat varchar(3) NULL
);

CREATE TABLE ProductTaxes (
    Id SERIAL PRIMARY KEY,
    Name varchar(255) NOT NULL,
    Pc decimal(12,2) NOT NULL,
    ProductId int REFERENCES Products(id) ON DELETE CASCADE
);


CREATE TABLE Clients (

    Id SERIAL PRIMARY KEY,
    Name varchar(255),
    Contact varchar(255),
    Phone varchar(255),
    Email varchar(255)
);

CREATE TABLE ClientBrands (

    Id SERIAL PRIMARY KEY,
    ClientId int REFERENCES Clients(id) ON DELETE CASCADE,
    Name varchar(255),
    Contact varchar(255),
    ContactEmail varchar(255),
    ContactPhone varchar(255),
    SendInvoice bool
);

CREATE TABLE ClientTaxPayers (

    Id SERIAL PRIMARY KEY,
    ClientId int REFERENCES Clients(id) ON DELETE CASCADE,
    TaxPayerIdNo varchar(255),
    LegalName varchar(255),
    Street varchar(255),
    IntNumber varchar(255),
    ExtNumber varchar(255),
    Neighborhood varchar(255),
    Municipality varchar(255),
    State varchar(255),
    ZipCode varchar(255),
    Email varchar(255)
);

CREATE TABLE ClientBranches (

    Id SERIAL PRIMARY KEY,
    ClientId int REFERENCES Clients(id) ON DELETE CASCADE,
    Name varchar(255),
    Manager varchar(255),
    ManagerEmail varchar(255),
    ManagerPhone varchar(255),
    ShippingAddress varchar(255),
    SendInvoice bool,
    TaxPayerId int REFERENCES ClientTaxPayers(id) NULL,
    ClientBrandId int REFERENCES ClientBrands(id) NULL
);

CREATE TABLE OrderTemplates ( 

    Id SERIAL PRIMARY KEY,
    Name varchar(255) NOT NULL,
    ClientId int REFERENCES Clients(id),
    BrandId int REFERENCES ClientBrands(id),
    Discount decimal (12,2) NULL,
    DiscountType int,
    HasFixedDiscount boolean NOT NULL
);

CREATE TABLE Orders ( 

    Id SERIAL PRIMARY KEY,
    TemplateId int REFERENCES OrderTemplates(id) NULL,
    ClientId int REFERENCES Clients(id),
    BrandId int REFERENCES ClientBrands(id),
    DeliveryDate DATE NOT NULL,
    Total decimal(12,2) NOT NULL,
    DeliveryStatus int NOT NULL,
    Invoice varchar(255) NULL,
    PaymentStatus int NOT NULL,
    PromisedPaymentDate DATE NULL,
    ActualPaymentDate DATE NULL,
    Discount decimal(12,2) NULL,
    DiscountType int NULL,
    HasFixedDiscount bool NOT NULL
);

CREATE TABLE OrderBranches (

    Id SERIAL PRIMARY KEY,
    BranchId int REFERENCES ClientBranches(id),
    OrderId int REFERENCES Orders (id) ON DELETE CASCADE

);

CREATE TABLE OrderProducts (
    Id SERIAL PRIMARY KEY,
    OrderId int REFERENCES Orders (id) ON DELETE CASCADE,
    ProductId int REFERENCES Products(id) NOT NULL,
    Specification varchar(255) NULL,
    Qty decimal(12,3) NOT NULL,
    FixedPrice decimal(12,2) NULL
);

CREATE TABLE OrderTemplateProducts (
    Id SERIAL PRIMARY KEY,
    OrderTemplateId int REFERENCES OrderTemplates (id) ON DELETE CASCADE,
    ProductId int REFERENCES Products(id) NOT NULL,
    Specification varchar(255) NULL,
    Qty decimal(12,3) NOT NULL,
    FixedPrice decimal(12,2) NULL
);





///////////////////////










SELECT * FROM OrderTemplates
Join Clients on Clients.Id = OrderTemplates.ClientId
Join ClientBrands on ClientBrands.Id = OrderTemplates.BrandId
Join ClientTaxPayers on ClientTaxPayers.Id = OrderTemplates.TaxPayerId
Join ClientBranches on ClientBranches.Id = OrderTemplates.BranchId

SELECT * FROM OrderProducts
JOIN Products on OrderProducts.ProductId = OrderProducts.Id



DO $$
DECLARE x int;
BEGIN
  INSERT INTO Units (Name, Key, HasDecimalPlaces) VALUES ('Litros', 'L', True) RETURNING id INTO x;
  INSERT INTO Products (UnitId) VALUES(x);
END;
$$;


DO $$
DECLARE newClientId int;
BEGIN

SELECT id FROM Products WHERE id = -1 into newClientId;

IF newClientId is NULL THEN
    SELECT id FROM Products WHERE id = 1804 into newClientId;
ELSE
	SELECT id FROM Products WHERE id = 1805 into newClientId;
END IF;


create temporary table _x on commit drop as select newClientId;

END;
$$;

SELECT newClientId FROM _x;



    TaxPayerId int REFERENCES ClientTaxPayers(id),
    BranchId int REFERENCES ClientBranches(id)