DROP TABLE passwordstore;
DROP TABLE userstatistic;
DROP TABLE userdata;



CREATE TABLE userdata(
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    dateOfReg DATE NOT NULL,
    id INTEGER NOT NULL,
    tablename VARCHAR(100) NOT NULL,
    CONSTRAINT us_da_pk PRIMARY KEY (username),
    CONSTRAINT us_da_uk UNIQUE (email, id, tablename)
);

CREATE TABLE passwordstore(
    username VARCHAR(50) NOT NULL,
    password VARCHAR(32) NOT NULL,
    CONSTRAINT pa_fk FOREIGN KEY (username) REFERENCES userdata(username)
);


CREATE TABLE userstatistic(
    username VARCHAR(50) NOT NULL,
    allGame INTEGER NOT NULL,
    winned INTEGER NOT NULL,
    lost INTEGER NOT NULL,
    gamerestarted INTEGER NOT NULL,
    CONSTRAINT us_st_fk FOREIGN KEY (username) REFERENCES userdata(username),
    CONSTRAINT us_st_ch CHECK (allGame>=0 AND winned>=0 AND gamerestarted>=0 AND lost>=0)
);



