CREATE TABLE users(
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50),
 password VARCHAR(255),
 fullname VARCHAR(100),
 role VARCHAR(20)
);

CREATE TABLE problems(
 id INT AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(255),
 description TEXT,
 difficulty VARCHAR(50)
);

CREATE TABLE testcases(
 id INT AUTO_INCREMENT PRIMARY KEY,
 problem_id INT,
 input_file TEXT,
 output_file TEXT,
 score INT,
 is_hidden BOOLEAN
);

CREATE TABLE contests(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255),
 start_time DATETIME,
 end_time DATETIME,
 duration INT
);

CREATE TABLE submissions(
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 problem_id INT,
 status VARCHAR(50),
 score INT,
 runtime FLOAT
);

CREATE TABLE rankings(
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 score INT,
 rank_position INT
);