-- Generated by Oracle SQL Developer Data Modeler 19.4.0.350.1424
--   at:        2020-01-19 14:21:44 CET
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE adress (
    id_adress      NUMBER NOT NULL,
    city           VARCHAR2(10),
    street         VARCHAR2(10),
    number_street  NUMBER,
    state          VARCHAR2(10)
);

ALTER TABLE adress ADD CONSTRAINT adress_pk PRIMARY KEY ( id_adress );

CREATE TABLE bucket (
    id_bucket       NUMBER NOT NULL,
    COUNT           NUMBER,
    item_id_item    NUMBER NOT NULL,
    order_id_order  NUMBER NOT NULL
);

CREATE UNIQUE INDEX bucket__idx ON
    bucket (
        order_id_order
    ASC );

CREATE UNIQUE INDEX bucket__idxv1 ON
    bucket (
        item_id_item
    ASC );

ALTER TABLE bucket ADD CONSTRAINT bucket_pk PRIMARY KEY ( id_bucket );

CREATE TABLE client (
    id_client         NUMBER NOT NULL,
    name              VARCHAR2(10),
    surname           VARCHAR2(10),
    adress_id_adress  NUMBER NOT NULL,
    email             VARCHAR2(10),
    phone_number      VARCHAR2(9)
);

ALTER TABLE client ADD CONSTRAINT client_pk PRIMARY KEY ( id_client );

CREATE TABLE complaint (
    id_complaint           NUMBER NOT NULL,
    date_of_complain       DATE,
    date_of_consideration  DATE,
    complain_status        VARCHAR2(15)
);

ALTER TABLE complaint ADD CONSTRAINT complaint_pk PRIMARY KEY ( id_complaint );

CREATE TABLE delivery (
    id_delivery     NUMBER NOT NULL,
    name            VARCHAR2(15),
    delivery_cost   NUMBER,
    order_id_order  NUMBER NOT NULL
);

CREATE UNIQUE INDEX delivery__idx ON
    delivery (
        order_id_order
    ASC );

ALTER TABLE delivery ADD CONSTRAINT delivery_pk PRIMARY KEY ( id_delivery );

CREATE TABLE item (
    id_item    NUMBER NOT NULL,
    name_item  VARCHAR2(15),
    TYPE       VARCHAR2(10)
);

ALTER TABLE item ADD CONSTRAINT item_pk PRIMARY KEY ( id_item );

CREATE TABLE item_spec (
    id_type         NUMBER NOT NULL,
    color           VARCHAR2(10),
    "Size"          VARCHAR2(5),
    price           NUMBER,
    item_id_item    NUMBER NOT NULL,
    stock_stock_id  NUMBER NOT NULL
);

CREATE UNIQUE INDEX item_spec__idx ON
    item_spec (
        stock_stock_id
    ASC );

ALTER TABLE item_spec ADD CONSTRAINT item_spec_pk PRIMARY KEY ( id_type );

CREATE TABLE "Order" (
    id_order                NUMBER NOT NULL,
    date_of_order           DATE,
    date_of_sending_order   DATE,
    pay_status              VARCHAR2(10),
    payment_method          VARCHAR2(8),
    order_status            VARCHAR2(10),
    order_cost              NUMBER,
    client_id_client        NUMBER NOT NULL,
    bucket_id_bucket        NUMBER NOT NULL,
    complaint_id_complaint  NUMBER NOT NULL,
    delivery_id_delivery    NUMBER NOT NULL
);

CREATE UNIQUE INDEX order__idx ON
    "Order" (
        complaint_id_complaint
    ASC );

CREATE UNIQUE INDEX order__idxv1 ON
    "Order" (
        delivery_id_delivery
    ASC );

CREATE UNIQUE INDEX order__idxv2 ON
    "Order" (
        bucket_id_bucket
    ASC );

ALTER TABLE "Order" ADD CONSTRAINT order_pk PRIMARY KEY ( id_order );

CREATE TABLE photo (
    id_photo      NUMBER NOT NULL,
    photo         BLOB,
    item_id_item  NUMBER NOT NULL
);

ALTER TABLE photo ADD CONSTRAINT photo_pk PRIMARY KEY ( id_photo );

CREATE TABLE stock (
    count_stock        NUMBER,
    item_spec_id_type  NUMBER NOT NULL,
    stock_id           NUMBER NOT NULL
);

CREATE UNIQUE INDEX stock__idx ON
    stock (
        item_spec_id_type
    ASC );

ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY ( stock_id );

CREATE TABLE "User" (
    id_user           NUMBER NOT NULL,
    login             VARCHAR2(10),
    password          VARCHAR2(10),
    client_id_client  NUMBER NOT NULL
);

CREATE UNIQUE INDEX user__idx ON
    "User" (
        client_id_client
    ASC );

ALTER TABLE "User" ADD CONSTRAINT user_pk PRIMARY KEY ( id_user );

ALTER TABLE bucket
    ADD CONSTRAINT bucket_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE bucket
    ADD CONSTRAINT bucket_order_fk FOREIGN KEY ( order_id_order )
        REFERENCES "Order" ( id_order );

ALTER TABLE client
    ADD CONSTRAINT client_adress_fk FOREIGN KEY ( adress_id_adress )
        REFERENCES adress ( id_adress );

ALTER TABLE delivery
    ADD CONSTRAINT delivery_order_fk FOREIGN KEY ( order_id_order )
        REFERENCES "Order" ( id_order );

ALTER TABLE item_spec
    ADD CONSTRAINT item_spec_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE item_spec
    ADD CONSTRAINT item_spec_stock_fk FOREIGN KEY ( stock_stock_id )
        REFERENCES stock ( stock_id );

ALTER TABLE "Order"
    ADD CONSTRAINT order_bucket_fk FOREIGN KEY ( bucket_id_bucket )
        REFERENCES bucket ( id_bucket );

ALTER TABLE "Order"
    ADD CONSTRAINT order_client_fk FOREIGN KEY ( client_id_client )
        REFERENCES client ( id_client );

ALTER TABLE "Order"
    ADD CONSTRAINT order_complaint_fk FOREIGN KEY ( complaint_id_complaint )
        REFERENCES complaint ( id_complaint );

ALTER TABLE "Order"
    ADD CONSTRAINT order_delivery_fk FOREIGN KEY ( delivery_id_delivery )
        REFERENCES delivery ( id_delivery );

ALTER TABLE photo
    ADD CONSTRAINT photo_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE stock
    ADD CONSTRAINT stock_item_spec_fk FOREIGN KEY ( item_spec_id_type )
        REFERENCES item_spec ( id_type );

ALTER TABLE "User"
    ADD CONSTRAINT user_client_fk FOREIGN KEY ( client_id_client )
        REFERENCES client ( id_client );

CREATE SEQUENCE stock_stock_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER stock_stock_id_trg BEFORE
    INSERT ON stock
    FOR EACH ROW
    WHEN ( NEW.stock_id IS NULL )
BEGIN
    :NEW.stock_id := stock_stock_id_seq.NEXTVAL;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                            11
-- CREATE INDEX                             9
-- ALTER TABLE                             24
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           1
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          1
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- REDACTION POLICY                         0
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0