create database jets;
use jets;
create table admin(
    id int auto_increment primary key not null,
    username varchar(255),
    password varchar(255)
);
insert into admin(username, password) values("JMS@root","ba45c13d808e2dbe95ea49974e744e50");
create table participants(
    id int primary key auto_increment not null,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    grade int not null,
    grade_section varchar(255) not null,
    date_of_birth date not null,
    school varchar(255),
    district varchar(255) not null,
    category varchar(255) not null,
    status varchar(255) not null,
    points float not null,
    participation_year year default current_timestamp()
);

create table results(
    id int primary key auto_increment not null,
    full_name varchar(255) not null,
    grade_section varchar(255) not null,
    school varchar(255),
    district varchar(255) not null,
    category varchar(255) not null,
    points varchar(255) not null,
    participation_year year default current_timestamp()

);

create table categories(
    id int primary key auto_increment not null,
    category_name varchar(255)
);

insert into categories(category_name) values 
("Agricultural Science Innovations"),
("Chemistry Innovations"),
("Physics & Renewable Energy Innovations"),
("Computer Science and Software Development Innovations"),
("Mathematics Innovations"),
("Medicine & Health Innovations"),
("Robotics and Artificial Intelligent Innovations"),
("Food Science, Technology and Hospitality Innovations"),
("Environmental Sustainable Development Innovations"),
("Primary Quiz and Olympiads (Mathematics)"),
("Primary Quiz and Olympiads (Science)"),
("Primary Quiz and Olympiads (CTS)"),
("Junior Secondary Quiz and Olympiads (Biology & Chemistry)"),
("Junior Secondary Quiz and Olympiads (Mathematics & Physics)"),
("Senior Secondary Quiz and Olympiads (Biology & Chemistry)"),
("Senior Secondary Quiz and Olympiads (Mathematics & Physics)"),
("Civil Engineering"),
("Mechanical Engineering"),
("Electronics Services"),
("Fashion Technology "),
("Cosmetology");




create table school_admin(
    id int primary key auto_increment not null,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    phone varchar(255) not null,
    district varchar(255) not null,
    school varchar(255) not null,
    username varchar(255) not null,
    password varchar(255) not null
);
create table grade_level(
    id int primary key not null auto_increment,
    grade varchar(255) not null
);
insert into grade_level(grade) values
("ECE"),("1"),("2"),("3"),("4"),("5"),("6"),("7"),("8"),("9"),("10"),("11"),("12"),("Out of School / Students"),("Teacher");
create table districts(
    id int primary key not null auto_increment,
    district_name varchar(255) not null
);
insert into districts(district_name) values
("Nakonde"),("Isoka"),("Mafinga"),("Chinsali"),("Shiwangándu"),("Mpika"),("Kanchibiya"),("Lavushimanda"),("All");
create table schools(
    id int auto_increment primary key not null,
    school_name varchar(255) not null,
    district varchar(255) not null
);
create table adjudicator(
    id int primary key auto_increment not null,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    phone varchar(255) not null,
    district varchar(255) not null,
    category varchar(255) not null,
    username varchar(255) not null,
    password varchar(255) not null
);
create table material(
    id int primary key not null auto_increment,
    material_name varchar(255) not null,
    material_category varchar(255) not null,
    material_location varchar(255) not null,
    posted_by varchar(255) not null,
    post_date year default current_timestamp()
);
create table events(
    id int primary key auto_increment not null,
    event_name varchar(255) not null,
    event_image varchar(255) not null,
    event_blog text not null,
    event_district varchar(255) not null,
    event_date date default current_timestamp()
);
create table notices(
    id int auto_increment primary key not null,
    notice_header varchar(255) not null,
    receipient varchar(255) not null,
    notice_content text not null,
    notice_date date default current_timestamp()
);
create table grade_section(
    id int auto_increment primary key not null,
    section_name varchar(255) not null
);
insert into grade_section(section_name) values 
("ECE & Primary"),("Junior Secondary"),("Senior Secondary"),("Out of School / Students"),("Teachers");
create table reports(
    id int not null auto_increment primary key,
    full_name varchar(255) not null,
    school varchar(255) not null,
    category varchar(255) not null,
    section_name varchar(255) not null,
    location varchar(255) not null
);