Database_Structure.txt

database structure:
	CuapCuap:
		// 7 tables
		categories:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			name (varchar(64), NOT NULL, unique)

		threads:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			category_id (int, NOT NULL, FOREIGN_KEY)
			author_id (int, NOT NULL, FOREIGN_KEY)
			title (varchar(140), NOT NULL)
			tags (varchar(1000))
			content(text, NOT NULL)
			upvote (int, DEFAULT 0)
			comment_count (int, DEFAULT 0)
			created_at (DATE, DEFAULT)
			updated_at (DATE, DEFAULT==CreatedAt)
			sticky (boolean, NOT NULL) default FALSE // True jika sticky, False jika nonsticky
		
		replies:
			// create dummy as head reply
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			thread_id (int, NOT NULL, FOREIGN_KEY)
			user_id (int, NOT NULL, FOREIGN_KEY)
			content(text, NOT NULL)
			parent_id (int)
			position (varchar(255)) //langsung string isi list reply_id (append zero hingga 5 digit) comma separated yang merupakan urutan nestednya
			depth (int)
			upvote (int, DEFAULT 0)
			created_at (DATE, DEFAULT)
			updated_at (DATE, DEFAULT==CreatedAt)
			is_deleted (boolean, default false)
		
		users:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			username (varchar(255), NOT NULL, unique)
			fullname (varchar(255), NOT NULL)
			password (varchar(255) ,bcrypt hash, default bcrypt(''))
			email (varchar(255))
			birthday (DATE)
			profile_picture (varchar(255)) // Referens ke jpeg ato apalah
			exp (int, default 0)
			role_id (int, default 0) //0: user biasa, 1: moderator, 2:admin
			comment_count (int, default 0)
			thread_count (int, default 0)
			voted_threads(text)
			voted_replies(text)

		notifications:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			user_id (int, NOT NULL, FOREIGN_KEY)
			type (int, NOT NULL) //untuk sementara 0: messaging, 1:thread_owner reply
			content_id (int, NOT NULL) // id message/reply
			created_at(DATE, DEFAULT)
			is_read (boolean, DEFAULT FALSE)

		messages:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			sender_id (int, NOT NULL, FOREGIN_KEY)
			receiver_id (int, NOT NULL, FOREIGN_KEY)
			content(text, NOT NULL)
			created_at(DATE, DEFAULT)

		announcements:
			id (int, NOT NULL, AUTO INCREMENT, PRIMARY)
			title (varchar(140), NOT NULL)
			content(text, NOT NULL)
			created_at(DATE, DEFAULT)	
			updated_at(DATE, DEFAULT==CreatedAt)
			is_deleted (boolean, default false)