---

-- CLIENT
CREATE TABLE Client (
    id_client INT IDENTITY(1,1) PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(200)
);


-- ORDER
CREATE TABLE [Order] (
    id_order INT IDENTITY(1,1) PRIMARY KEY,
    id_client INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(50),

    FOREIGN KEY (id_client)
        REFERENCES Client(id_client)
);


-- PRODUCT
CREATE TABLE Product (
    id_product INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL
);


-- WAREHOUSE
CREATE TABLE Warehouse (
    id_warehouse INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(200)
);


-- ORDER POSITION
CREATE TABLE OrderPosition (
    id_position INT IDENTITY(1,1) PRIMARY KEY,
    id_order INT NOT NULL,
    id_product INT NOT NULL,
    id_warehouse INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,

    FOREIGN KEY (id_order)
        REFERENCES [Order](id_order),

    FOREIGN KEY (id_product)
        REFERENCES Product(id_product),

    FOREIGN KEY (id_warehouse)
        REFERENCES Warehouse(id_warehouse)
);


-- DELIVERY
CREATE TABLE Delivery (
    id_delivery INT IDENTITY(1,1) PRIMARY KEY,
    id_order INT NOT NULL,
    address VARCHAR(200),
    delivery_date DATE,
    status VARCHAR(50),

    FOREIGN KEY (id_order)
        REFERENCES [Order](id_order)
);


-- INVOICE
CREATE TABLE Invoice (
    id_invoice INT IDENTITY(1,1) PRIMARY KEY,
    id_order INT NOT NULL,
    invoice_date DATE,
    amount DECIMAL(10,2),

    FOREIGN KEY (id_order)
        REFERENCES [Order](id_order)
);


-- PAYMENT
CREATE TABLE Payment (
    id_payment INT IDENTITY(1,1) PRIMARY KEY,
    id_invoice INT NOT NULL,
    payment_date DATE,
    amount DECIMAL(10,2),
    payment_method VARCHAR(50),
    status VARCHAR(50),

    FOREIGN KEY (id_invoice)
        REFERENCES Invoice(id_invoice)
);