CREATE TABLE Product
(
    productId NUMBER (10) PRIMARY KEY,
    name VARCHAR2(60) NOT NULL,
    description VARCHAR2(100),
    price NUMBER (18,2) NOT NULL, --16 numra para presjes, 2 pas presjes
    registerDate DATE NOT NULL
);

--varchar (15) - kjo nuk i rezervon ne memorie
--char (15) - kjo i rezervon ne memorie
--per 10 shkronja, chari i rezervon 15 batje, kurse te varchari 11 bajte (bajti shtes tregon sa karaktere jan rujt, psh 10+1 bajt)
--per 200 shkronja, 200+2 rezervohen ne varchar (2 bajta kallzojn sa karaktere jan rujt)

--nvarchar/nchar (nationalVarchar) per shkronja UNICODE (arabisht, kinez, japan) (karakteret zan hapesire ka 2 bajta)
--varchar edhe char ASCIICODE (shkronjat e vogla + t'mdhaja + numrat + simbole tjera) (256 karaktere i permban) (ç edhe ë te UNICODE)

CREATE TABLE Client
(
    clientId NUMBER (10),
    firstName VARCHAR2 (50) NOT NULL,
    lastName VARCHAR2 (50) NOT NULL,
    tel VARCHAR2 (15),
    email VARCHAR2 (80),
    address VARCHAR2 (80),
    registerDate DATE NOT NULL,

    PRIMARY KEY (clientId)
);

CREATE TABLE PaymentType (
    paymentTypeId NUMBER (10),
    name VARCHAR2 (100) NOT NULL,

    PRIMARY KEY (paymentTypeId)
);

CREATE TABLE Location (
    locationId NUMBER (10),
    address VARCHAR2 (100) NOT NULL,
    email VARCHAR2 (80),
    telephone VARCHAR2 (15),
    PRIMARY KEY (locationId)
);

CREATE TABLE Invoice (
    InvoiceNo CHAR(15) PRIMARY KEY,
    clientId NUMBER(10) NOT NULL,
    invoiceDate DATE NOT NULL,
    totalAmount NUMBER(18,2) NOT NULL,
    paymentType NUMBER(10),
    locationId NUMBER(10) NOT NULL,

    foreign KEY (clientId) references Client(clientId),
    foreign KEY (paymentType) references PaymentType(paymentTypeId),
    foreign KEY (locationId) references Location(locationId)
);

CREATE TABLE InvoiceDetails (
    InvoiceNo CHAR(10),
    productId NUMBER(10),
    quantity NUMBER(18,2) NOT NULL,
    productPrice NUMBER(18,2) NOT NULL,

    PRIMARY KEY (InvoiceNo, productId),
    foreign KEY (InvoiceNo) References Invoice (InvoiceNo) ON DELETE CASCADE, --
    foreign KEY (productId) references Product (productId)
);

--Nr. identifikues i klientit te vendoset si autonumber, duhemi me sekuenc e me e thirr tani
--Krijimi i sekuences:
CREATE SEQUENCE client_seq START WITH 1;
CREATE OR REPLACE TRIGGER trg_clientSEQ before INSERT ON Client FOR each ROW
BEGIN
    SELECT client_seq.NEXTVAL
    INTO :NEW.clientId --eshte objekti i ri, perkatesisht reshti i ri qe po regjistrohet
    FROM dual;
END;

INSERT INTO client (
    firstname,
    lastname,
    tel,
    email,
    address,
    registerdate
) VALUES (
    'Agim',
    'Gashi',
    '044/111-222',
    'agim@gmail.com',
    'Rr. Adem Jashari',
    (SELECT SYSDATE FROM dual) --e merr daten aktuale
);

SELECT * FROM Client;
SELECT client_seq.NEXTVAL FROM dual; --nese e ekzekutojm ket sen, nuk mundemi ma t'njejtat numra ne ID se e ndryshon at klientin nalt
-- nese e regjistrojm ket tjetrin, kcen IDja n'4

SELECT * FROM dual;