DROP TABLE USERDATA CASCADE;

CREATE TABLE USERDATA(
  ID BIGSERIAL PRIMARY KEY NOT NULL,
  USERNAME VARCHAR(20) UNIQUE NOT NULL,
  PASSWORD VARCHAR(30) NOT NULL,
  COUNTER INTEGER NOT NULL
);

drop table teams CASCADE;

create table teams(
  id BIGSERIAL PRIMARY KEY not null,
  teamname VARCHAR(30) not null
);

drop table userteams CASCADE;

create table userteams(
  userid BIGINT REFERENCES USERDATA(ID),
  teamid BIGINT REFERENCES teams(ID),
  CONSTRAINT user_team_pk PRIMARY KEY (userid,teamid)
);

DROP TABLE DATASOURCE CASCADE;

CREATE TABLE DATASOURCE(
  ID BIGSERIAL PRIMARY KEY NOT NULL,
  USERID BIGINT NOT NULL,
  TEAMID BIGINT NOT NULL,
  DASHBOARDNAME VARCHAR(20) NULL,
  NAME VARCHAR(20) NOT NULL,
  DATABASETYPE VARCHAR(20) NOT NULL ,
  DATABASEURL VARCHAR(200) NOT NULL,
  DATABASEPASSWORD VARCHAR(20) NOT NULL,
  CONSTRAINT unique_dashboard_per_user UNIQUE(USERID, DASHBOARDNAME),
  CONSTRAINT user_team_pk FOREIGN KEY (USERID,TEAMID) REFERENCES userteams
);

DROP TABLE LAYOUT CASCADE;

CREATE TABLE LAYOUT(
  ID BIGSERIAL PRIMARY KEY NOT NULL,
  DID SERIAL REFERENCES DATASOURCE (ID),
  TYPE VARCHAR(30) NOT NULL,
  WIDTH INTEGER,
  HEIGHT INTEGER,
  POSIITION INTEGER,
  LAYOUT JSONB NULL
);


INSERT into USERDATA VALUES (DEFAULT ,'bi','bi',1);
insert into teams values (DEFAULT , 'Owner');
insert into userteams values (1,1);
insert into DATASOURCE VALUES (DEFAULT,1,1,'world','test','test','test','test');

select * from DATASOURCE;

select * from userdata where USERNAME = 'unique' AND PASSWORD = 'password234';

select * from USERDATA;

select * from datasource;

insert into DATASOURCE values (default,1,1,'RealDatabase','realdatasource','mysql','jdbc:mysql://localhost:3306/library','');

insert into layout values (default,2,'pie',1,2,4,'{"title":{"text":"Sample Pie","subtext":"Sample SubText","x":"center"},"tooltip":{"trigger":"item","formatter":"{a} <br/>{b} : {c} ({d}%)"},"legend":{"orient":"vertical","left":"left","data":["1","2","3","4","5"]},"series":[{"name":"Whats this?","type":"pie","radius":"55%","center":["50%","60%"],"data":[{"value":179973,"name":"Male"},{"value":120051,"name":"Female"}]}]}');
insert into LAYOUT values (default,2,'line',1,2,4,'{"title":{"text":"Sample Line Chart"},"tooltip":{"trigger":"axis"},"legend":{"data":["Anto"]},"grid":{"left":"3%","right":"4%","bottom":"3%","containLabel":true},"toolbox":{"feature":{"saveAsImage":{}}},"xAxis":{"type":"category","boundaryGap":false,"data":["Jan","Feb","Mar","Apr","May","Dec","Sep"]},"yAxis":{"type":"value"},"series":[{"name":"Population","type":"line","stack":"总量","data":[120,132,101,134,90,230,210]}]}');

select * from LAYOUT;

update layout set HEIGHT = 1 where ID = 2;
update layout set HEIGHT = 2 where ID = 3;

select * from DATASOURCE INNER JOIN layout
  on DATASOURCE.ID = LAYOUT.DID;


select * from teams;

select * from USERDATA;

INSERT into USERDATA VALUES (DEFAULT ,'antoaravinth','bi',1);
insert into USERDATA values (DEFAULT ,'Nithya','bi',1);
insert into USERDATA values (DEFAULT ,'Praveen','bi',1);
insert into USERDATA values (DEFAULT ,'Murali','bi',1);
insert into USERDATA values (DEFAULT ,'Arun','bi',1);



select * from userdata;

select * from userteams INNER JOIN teams ON userteams.teamid = teams.ID;