--Create the tables for Note, Category, Reminder, User, UserNote, NoteReminder and NoteCategory.

--User table fields: user_id, user_name, user_added_date, user_password, user_mobile

create table User1 (user_id int primary key, 
                    user_name char(30), 
                    user_added_date date, 
                    user_password char(20), 
                    user_mobile char(10));

--Note table fields: note_id, note_title, note_content, note_status, note_creation_date

create table note (note_id int primary key, 
                  note_title varchar(30), 
                  note_content varchar(900), 
                  note_status char(10), 
                  note_creation_date date);

--Category table fields : category_id, category_name, category_descr, category_creation_date, category_creator

create table Category (category_id int primary key, 
                      category_name char(30), 
                      category_descr varchar(500), 
                      category_creation_date date, 
                      category_creator char(30));

--Reminder table fields : reminder_id, reminder_name, reminder_descr, reminder_type, reminder_creation_date, reminder_creator

create table Reminder (reminder_id int primary key, 
                      reminder_name char(20), 
                      reminder_descr varchar(200), 
                      reminder_type char(20), 
                      reminder_creation_date date, 
                      reminder_creator char(30));

--NoteCategory table fields : notecategory_id, note_id, category_id

create table NoteCategory (notecategory_id int primary key, 
                          note_id int references note(note_id) on delete cascade, 
                          category_id int references Category(category_id) on delete cascade);

--Notereminder table fields : notereminder_id, note_id, reminder_id

create table Notereminder (notereminder_id int primary key, 
                          note_id int references note(note_id) on delete cascade, 
                          reminder_id int references Reminder(reminder_id) on delete cascade);

--usernote table fields : usernote_id, user_id, note_id

create table usernote (usernote_id int primary key, 
                      user_id int references User1(user_id) on delete cascade,
                      note_id int references note(note_id) on delete cascade);


--Insert the rows into the created tables (Note, Category, Reminder, User, UserNote, NoteReminder and NoteCategory).

insert into User1 values  (1,'Albert', '2022-05-01', 'Password1', 8171635623),
                          (2,'Aldin', '2022-05-02', 'Password2', 8172235623),
                          (3,'Robert', '2022-05-03', 'Password3', 8187635623),
                          (4,'Robin', '2022-05-04', 'Password4', 8171335623),
                          (5,'Garry', '2022-05-05', 'Password5', 8171635678);
                          
                          
insert into note values (1, 'title1', 'note content 1', 'yes', '2022-05-06'),
                        (2, 'title2', 'note content 2', 'yes', '2022-05-07'),
                        (3, 'title3', 'note content 3', 'no', '2022-05-08'),
                        (4, 'title4', 'note content 4', 'yes', '2022-05-09'),
                        (5, 'title5', 'note content 5', 'no', '2022-05-10');
                        
 
 insert into category values  (1, 'category name 1', 'category descr 1', '2022-05-08', 'category_creator 1'),
                              (2, 'category name 2', 'category descr 2', '2022-05-11', 'category_creator 2'),
                              (3, 'category name 3', 'category descr 3', '2022-05-12', 'category_creator 3'),
                              (4, 'category name 4', 'category descr 4', '2022-05-13', 'category_creator 4'),
                              (5, 'category name 5', 'category descr 5', '2022-05-14', 'category_creator 5');


insert into Reminder values (1, 'reminder_name 1', 'reminder_descr 1', 'reminder_type 1', '2022-04-01', 'reminder_creator 1'),
                            (2, 'reminder_name 2', 'reminder_descr 2', 'reminder_type 2', '2022-04-02', 'reminder_creator 2'),
                            (3, 'reminder_name 3', 'reminder_descr 3', 'reminder_type 3', '2022-04-03', 'reminder_creator 3'),
                            (4, 'reminder_name 4', 'reminder_descr 4', 'reminder_type 4', '2022-04-04', 'reminder_creator 4'),
                            (5, 'reminder_name 5', 'reminder_descr 5', 'reminder_type 5', '2022-04-05', 'reminder_creator 5');


insert into NoteCategory values (1, 1, 1),
                                 (2, 2, 2),
                                 (3 ,3 ,3),
                                 (4, 4, 4),
                                 (5, 5, 5);
 
 
 insert into Notereminder values (1, 1, 1),
                                 (2, 2, 2),
                                 (3 ,3 ,3),
                                 (4, 4, 4),
                                 (5, 5, 5);
                                 
insert into usernote values (1, 1, 1),
                             (2, 2, 2),
                             (3 ,3 ,3),
                             (4, 4, 4),
                             (5, 5, 5);

 

 --Fetch the row from User table based on Id and Password.
 
 select user_id, user_password from User1 where user_id = 1;
 
 
 --Fetch all the rows from Note table based on the field note_creation_date.

 select note_creation_date from Note;
 
 
 --Fetch all the Categories created after the particular Date.


select * from category where category_creation_date > '2022-05-12';
 
 
 --Fetch all the Note ID from UserNote table for a given User.


select * from usernote where note_id = 1;
 
 -- Write Update query to modify particular Note for the given note Id.
 
 update note set note_content = 'note_content 5.1' where note_id = 5;
 
 
--Fetch all the Notes from the Note table by a particular User.


select * from Note where note_id = 5;
 
 
--Fetch all the Notes from the Note table for a particular Category.


select N.note_content, NC.category_id from note N inner join notecategory NC on N.note_id = NC.note_id;


--Fetch all the reminder details for a given note id.


select * from reminder rem inner join Notereminder norem on rem.reminder_id = norem.reminder_id where rem.reminder_id=1;

--Fetch the reminder details for a given reminder id.

select * from reminder where reminder_id = 3;


--Write a query to create a new Note from particular User (Use Note and UserNote tables - insert statement).

insert into Note values (6, 'title6', 'note content 6', 'yes', '2022-05-11');

--Write a query to create a new Note from particular User to particular Category(Use Note and NoteCategory tables - insert statement)

insert into note values (7, 'title7', 'note content 7', 'no', '2022-05-13');
insert into NoteCategory values (6, 5, 5);


--Write a query to set a reminder for a particular note (Use Reminder and NoteReminder tables - insert statement)

insert into Reminder values (6, 'reminder_name 6', 'reminder_descr 6', 'reminder_type 6', '2022-04-06', 'reminder_creator 6');
insert into NoteReminder values (6, 6, 6);

--Write a query to delete particular Note added by a User(Note and UserNote tables - delete statement)


delete from note where note_id= 3;

select * from note;
select * from UserNote;


--Write a query to delete particular Note from particular Category(Note and NoteCategory tables - delete statement)


delete from note where note_id= 4;

select * from note;
select * from NoteCategory;