/*******************
PART-A
*******************/


CREATE TABLE Users (
     user_id INT NOT NULL AUTO_INCREMENT,
     email VARCHAR(255) NOT NULL,
     username VARCHAR(255) NOT NULL,
     password VARCHAR(255) NOT NULL,    
     avatar VARCHAR(255),
     PRIMARY KEY (user_id)
);

CREATE TABLE Create_Recipe (
     recipe_id INT NOT NULL AUTO_INCREMENT,
     user_id INT NOT NULL,
     recipe_name VARCHAR(255) NOT NULL,
     recipe_created TIMESTAMP NOT NULL,
     PRIMARY KEY (recipe_id),
     FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE View_Recipe (
     content_id INT NOT NULL AUTO_INCREMENT,
     user_id INT NOT NULL,
     recipe_id INT NOT NULL,
     recipe_content TEXT NOT NULL,
     recipe_created TIMESTAMP NOT NULL,
     PRIMARY KEY (content_id),
     FOREIGN KEY (user_id) REFERENCES Users(user_id),
     FOREIGN KEY (recipe_id) REFERENCES Create_Recipe(recipe_id)
);

CREATE TABLE Manage_Recipe (
     access_id INT NOT NULL AUTO_INCREMENT,
     user_id INT NOT NULL,
     recipe_id INT NOT NULL,
     status TINYINT NOT NULL,
     UNIQUE(recipe_id, user_id),
     PRIMARY KEY (access_id),
     FOREIGN KEY (user_id) REFERENCES Users(user_id),
     FOREIGN KEY (recipe_id) REFERENCES Create_Recipe(recipe_id)
);




/*******************
PART-B
*******************/



1. Sign-up Page

INSERT INTO Users (email, username, password, avatar)
VALUES ('john@example.com', 'johnny', 'johnnypass', 'avatars/john.png'),
       ('user@example.com', 'user', 'userpass', 'avatars/user.png'),
       ('test@example.com', 'test', 'testpass', 'avatars/test.png');



2. Create Recipe Page

• insert a new recipe

INSERT INTO Create_Recipe (user_id, recipe_name, recipe_created)
VALUES (1, 'Sandwich', NOW()),
       (2, 'Pizza', NOW()),
       (3, 'Coffee', NOW());

• insert the creator’s access-link row with status = 1.

INSERT INTO Manage_Recipe (user_id, recipe_id, status)
VALUES (1, 1, 1),
       (2, 2, 1),
       (3, 3, 1);



3. View Recipe Page

INSERT INTO View_Recipe (user_id, recipe_id, recipe_content, recipe_created)
VALUES (1, 1, 'Loved the stuffing of the Sandwich', NOW()),
       (2, 2, 'Used olive oil at the presentation', NOW()),
       (3, 3, 'Crushed the coffee beans at the correct consistency', NOW());



4. Grant/Revoke Access Page

• grant access to a user to contribute to a specific topic

INSERT INTO Manage_Recipe (user_id, recipe_id, status)
VALUES (1, 2, 1)
ON DUPLICATE KEY UPDATE status = 1;

       
• revoke access from a user for a specific topic

INSERT INTO Manage_Recipe (user_id, recipe_id, status)
VALUES (1, 2, 0)
ON DUPLICATE KEY UPDATE status = 0;




/*******************
PART-C
*******************/


1. Login Form

SELECT user_id, username, avatar
FROM Users
WHERE email = 'user@example.com' AND password = 'userpass';


2. Recipe List Page

SELECT 
    cr.recipe_id,
    cr.recipe_name,
    cr.recipe_created,
    MAX(vr.recipe_created) AS last_note_date,
    COUNT(vr.content_id) AS total_notes
FROM Create_Recipe cr
JOIN Manage_Recipe mr ON cr.recipe_id = mr.recipe_id
LEFT JOIN View_Recipe vr ON cr.recipe_id = vr.recipe_id
WHERE mr.user_id = 1 AND mr.status = 1
GROUP BY cr.recipe_id
ORDER BY cr.recipe_created DESC;


3. View Recipe Page

• get recipe info 

SELECT recipe_id, recipe_name, recipe_created,
       (SELECT MAX(recipe_created) FROM View_Recipe WHERE recipe_id = 2) AS last_note_date
FROM Create_Recipe
WHERE recipe_id = 2;


• get recipe notes details

SELECT 
    vr.recipe_content,
    vr.recipe_created,
    u.username,
    u.avatar
FROM View_Recipe vr
JOIN Users u ON vr.user_id = u.user_id
WHERE vr.recipe_id = 2
ORDER BY vr.recipe_created ASC;


4. Grant/Revoke Access Page

SELECT 
    u.user_id,
    u.username,
    COALESCE(mr.status, 0) AS access_status
FROM Users u
LEFT JOIN Manage_Recipe mr ON u.user_id = mr.user_id AND mr.recipe_id = 2;