commands.sql

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, college VARCHAR(255) NOT NULL, sid VARCHAR(255), email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, contact VARCHAR(10) NOT NULL UNIQUE, uuid VARCHAR(255) NOT NULL UNIQUE, createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE sponserTypes (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, priorty INT NOT NULL DEFAULT 1, createdAt DATETIME DEFAULT NOW() NOT NULL, updatedAt DATETIME DEFAULT NOW() ON UPDATE NOW() NOT NULL);
CREATE TABLE sponsers (id INT PRIMARY KEY AUTO_INCREMENT, link VARCHAR(255) NOT NULL, typeId INT NOT NULL, isDeleted BOOLEAN DEFAULT FALSE NOT NULL, createdAt DATETIME DEFAULT NOW() NOT NULL, updatedAt DATETIME DEFAULT NOW() ON UPDATE NOW() NOT NULL, FOREIGN KEY (typeId) REFERENCES sponserTypes(id));
CREATE TABLE admins (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, domain VARCHAR(255) NOT NULL, createdAt DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL);
CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, adminId INT NOT NULL, name VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL, startDate DATE NOT NULL, startTime TIME NOT NULL, endDate DATE NOT NULL, endTime TIME NOT NULL, venue VARCHAR(255) NOT NULL, eventType ENUM('CULTURAL', 'TECHNICAL', 'WORKSHOP', 'MEGASHOW') NOT NULL, participationType ENUM('SINGLE', 'TEAM') NOT NULL, minParticipants INT, maxParticipants INT, paymentType ENUM('FREE', 'PAID') NOT NULL, registrationfee FLOAT, haveRuleBook BOOLEAN NOT NULL, rulebooklink VARCHAR(255), tags JSON, image VARCHAR(255), createdAt DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL, FOREIGN KEY (adminId) REFERENCES admins(id));
CREATE TABLE tags (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL);
CREATE TABLE heads (id INT AUTO_INCREMENT PRIMARY KEY, eventId INT NOT NULL, name VARCHAR(255) NOT NULL, phoneNumber VARCHAR(255) NOT NULL, FOREIGN KEY (eventId) REFERENCES events(id));


INSERT INTO events (adminId, name, description, startDate, startTime, endDate, endTime, venue, eventType, participationType, minParticipants, maxParticipants, paymentType, registrationfee, haveRuleBook, rulebooklink, tags, image, createdAt, updatedAt) VALUES (1, 'Sample Event', 'Description of the event', '2024-10-10', '10:00:00', '2024-10-10', '18:00:00', 'Main Hall', 'Cultural', 'Team', 2, 5, 'Paid', 500.0, true, 'http://example.com/rulebook.pdf', '["tag1", "tag2"]', 'http://example.com/image.jpg', NOW(), NOW());


-- use 'SET GLOBAL sql_mode='';' in case of error: '(1364, "Field 'date' doesn't have a default value")'