CREATE TABLE products (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    CODE VARCHAR2(50),
    description VARCHAR2(4000)
);

CREATE INDEX idx_text_description ON products(description)
INDEXTYPE IS CTXSYS.CONTEXT;

-- Table main: INVOICE
CREATE TABLE INVOICE (
    NO_INVOICE         VARCHAR2(20) PRIMARY KEY,
    CODE_CUSTOMER    VARCHAR2(20) NOT NULL,
    NAME_CUSTOMER      VARCHAR2(100),
    VALUE_TOTAL       NUMBER(15, 2),
    DATE_PRINT        DATE,
    CITY            VARCHAR2(100),
    STATE            VARCHAR2(2)   -- Ex: SP, RJ, MG
);

-- Table of itens: ITEM_INVOICE
CREATE TABLE ITEM_INVOICE (
    NO_INVOICE         VARCHAR2(20) NOT NULL,
    NO_ITEM       NUMBER(5) NOT NULL,
    CODE_EAN        VARCHAR2(20),
    DESCRIPTION_PRODUCT VARCHAR2(200),
    VALUE_UNITARY    NUMBER(12, 4),
    QUANTITY        NUMBER(10, 2),
    VALUE_TOTAL       NUMBER(15, 2),
    VALUE_TAXES    NUMBER(15, 2),
    
    -- Primary key
    CONSTRAINT PK_ITEM_INVOICE PRIMARY KEY (NO_INVOICE, NO_ITEM),

    -- Foreign key for INVOICE
    CONSTRAINT FK_ITEM_INVOICE FOREIGN KEY (NO_INVOICE)
        REFERENCES INVOICE (NO_INVOICE)
        ON DELETE CASCADE
);

-- Index to accelerate searches for invoice item
CREATE INDEX IDX_ITEM_INVOICE_EAN ON ITEM_INVOICE (CODE_EAN);