scripts

--select * from HealthFriends;
--select * from infodetails;
--select * from Observations;
--select * from Users;
--select * from Type;
--select * from Category;
--select distinct P1.PTID,P2.PTID 
--from  PatientClass P1, PatientClass P2
--where P1.Cname = P2.Cname AND P1.PTID <> P2.PTID
insert into USERS values ('ggeorge', 'Gary George', '2806 Conifer Drive', 23, 'M','geo123', 'Yes','',0,'Raleigh','NC','27606');
--delete from USERS
--alter table USERS add (lastlogin DATE)
--alter table USERS add (alert integer)
delete from users where id = 'meghan';
create table Users(
id varchar2(20) not null primary key,
name varchar2(50) not null,
address varchar2(100) not null,
Age  integer not null,
sex  char(1) not null,
password varchar2(20) not null,
status varchar2(20),
clinic varchar2(30),
usertype integer not null,
City varchar2(30),
State varchar2(20),
Pincode varchar2(10)
);


create table PatientClass (
cid integer not null primary key,
cname  varchar2(50) not null,
ptid varchar2(20) not null references Users(id)
);
create sequence ptclass_seq;


create table Category(
cid integer not null primary key,
cname varchar2(30)
);
create sequence category_seq;


create table Type(
tid integer not null primary key,
tname varchar2(20),
info1 varchar2(30),
info2 varchar2(30),
info3 varchar2(30),
class varchar2(50),
ctid integer not null references Category(cid)
); 
create sequence type_seq;

--select * from observations
--alter table Observations add (tname varchar2(50))

create table Observations(
obsid integer primary key,
dtimeobserve TIMESTAMP,
dtimerecord TIMESTAMP,
ptid varchar2(50) not null references Users(id),
tname varchar2(50),
info1text varchar2(30),
info2text varchar2(30),
info3text varchar2(30),
info1value varchar2(50),
info2value varchar2(50),
info3value varchar2(50)
);
create sequence observation_seq;

--drop table infodetails
create table infodetails(
infoid integer not null primary key,
infoname varchar2(20),
infothreshold integer,
tyname varchar2(20),
ctid integer not null references Category(cid)
);
create sequence info_seq;

drop table HealthFriends
create table HealthFriends(
hfid integer not null primary key,
patientid varchar2(50) not null references Users(id),
friendid varchar2(50) not null references Users(id),
initDate date
);
create sequence hf_seq;