Learners api

This is the backend for the learners app. This repository contains api endpoints and data for the learners app......

For the app go to : https://github.com/Onnesok/Learners

Database creation

Create user database......here user database table is named sign_up

CREATE TABLE sign_up (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  -- Primary key with auto-increment
    uemail VARCHAR(40) NOT NULL UNIQUE,      -- Email column with unique constraint
    ufname VARCHAR(40) NOT NULL,             -- User's first name
    ulname VARCHAR(40) NOT NULL,             -- User's last name
    upassword VARCHAR(40) NOT NULL           -- User's password
);

Now create categories database

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique identifier for each category
    title VARCHAR(255) NOT NULL,
    image VARCHAR(255) NOT NULL,                -- Category Image
    description TEXT                            -- Description of the category
);

sample inserts

INSERT INTO categories (title, image, description) VALUES
('Programming', '/assets/images/programming.png', 'Courses related to competitive programming, app development, web dev and more'),
('UI/UX', '/assets/images/designer.png', 'Courses on UI/UX design'),
('Digital Marketing', '/assets/images/marketing.png', 'Courses for digital marketing'),
('Robotics', '/assets/images/robotics.png', 'Courses for Robotics'),
('Data entry', '/assets/images/management.png', 'Courses for dataentry');

Create courses database

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,    -- Unique identifier for each course
    title VARCHAR(255) NOT NULL, 
    instructor_name VARCHAR(255) NOT NULL,
    duration VARCHAR(255) NOT NULL, 
    price DECIMAL(10, 2) DEFAULT 0.00,         
    release_date DATE, 
    video_content TEXT,                          -- Multiple video links, comma-separated
    description TEXT,                            -- Course description
    video_title VARCHAR(255),                    -- Title for the video content
    prerequisite TEXT, 
    rating_count INT DEFAULT 0,                  -- Number of ratings received
    certificate BOOLEAN DEFAULT FALSE,           -- Indicates if a certificate is provided
    intro_video VARCHAR(255),                    -- URL of the introductory video
    image VARCHAR(255),                          -- Course banner
    stars DECIMAL(2, 1) NOT NULL,                -- Average rating in stars
    discount VARCHAR(255),                       -- Discount applied to the course
    category_id INT,                             -- Foreign key referencing the category
    FOREIGN KEY (category_id) REFERENCES categories(category_id)  -- Foreign key constraint
);

sample inserts

INSERT INTO courses 
(title, instructor_name, duration, price, release_date, video_content, description, video_title, prerequisite, rating_count, certificate, intro_video, image, stars, discount, category_id) VALUES
('Introduction to Flutter', 'John Doe', '10 hours', 0.00, '2024-08-01', 'video1_id,video2_id', 'Learn Flutter from scratch with hands-on examples.', 'Introduction to Flutter,Flutter Setup', 'Basic programming knowledge', 0, FALSE, 'intro_video_id1', '/assets/images/iron_man.jpg', 5.0, '10%', 1),
('Advanced App Development', 'Jane Smith', '12 hours', 0.00, '2024-09-01', 'video3_id,video4_id', 'Take your app development skills to the next level.', 'App Development Introduction,App Optimization', 'Completion of Introduction to Flutter', 0, FALSE, 'intro_video_id2', '/assets/images/iron_man.jpg', 4.8, '15%', 1),
('UX Principles', 'Emily Davis', '8 hours', 0.00, '2024-07-15', 'video5_id,video6_id', 'Understand the key principles of user experience design.', 'UX Introduction,User Research', 'None', 0, FALSE, 'intro_video_id3', '/assets/images/apex.jpg', 4.5, 'No', 2),
('Digital Marketing Basics', 'Alex Johnson', '6 hours', 0.00, '2024-05-20', 'video7_id,video8_id', 'Learn the fundamentals of digital marketing strategies.', 'Digital Marketing Introduction,Social Media Strategy', 'Basic understanding of social media', 0, FALSE, 'intro_video_id4', '/assets/images/league1.jpg', 4.7, '10%', 3),
('Intro to Data Entry', 'Tony Stark', '3 hours', 0.00, '2024-08-01', 'video9_id,video10_id', 'Learn the basics of data entry operations.', 'Data Entry Introduction,Data Entry Practices', 'Basic Computer knowledge', 0, FALSE, 'intro_video_id5', '/assets/images/dataentry.jpg', 5.0, '10%', 5),
('Introduction to Robotics', 'Ratul Hasan', '60 hours', 0.00, '2024-08-01', 'video11_id,video12_id', 'An introduction to robotics and its applications.', 'Robotics Introduction,Robotics Assembly', 'Basic programming knowledge', 0, FALSE, 'intro_video_id6', '/assets/images/robotics1.jpg', 5.0, '10%', 4);

Create enrollment table

CREATE TABLE enrollment (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    uemail VARCHAR(40) NOT NULL,
    course_id INT NOT NULL,
    FOREIGN KEY (uemail) REFERENCES sign_up(uemail),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE KEY (uemail, course_id)  -- Ensures a user cannot enroll in the same course more than once
);

Sample inserts

INSERT INTO enrollment (uemail, course_id) VALUES
('stark@gmail.com', 1),
('stark@gmail.com', 3),
('dave@gmail.com', 5);

Now create App rating database

CREATE TABLE app_rating_db (
    app_rating_db_id INT AUTO_INCREMENT PRIMARY KEY,  -- Primary key with auto-increment
    uemail VARCHAR(40) NOT NULL,                      -- Email associated with the rating
    app_rating INT NOT NULL,                          -- App rating (e.g., 1-5 stars)
    FOREIGN KEY (uemail) REFERENCES sign_up(uemail),  -- Foreign key referencing uemail in sign_up
    UNIQUE KEY (uemail)                               -- Ensures each user can only rate once
);

Ok now bug report database

CREATE TABLE bug_report (
    bug_report_id INT AUTO_INCREMENT PRIMARY KEY,   -- Primary key with auto-increment
    uemail VARCHAR(40) NOT NULL,                    -- Email of the user reporting the bug
    issue_description TEXT NOT NULL,                -- Description of the reported issue
    FOREIGN KEY (uemail) REFERENCES sign_up(uemail) -- Foreign key referencing uemail in sign_up
);