CREATE TABLE files (
	id INT AUTO_INCREMENT PRIMARY KEY,
	post_uid INT NOT NULL,
	file_name TEXT NOT NULL,
	stored_filename TEXT NOT NULL,
	file_ext VARCHAR(16) NOT NULL,
	file_md5 VARCHAR(600) NOT NULL,
	file_width INT DEFAULT NULL,
	file_height INT DEFAULT NULL,
	file_size BIGINT UNSIGNED NULL,
	mime_type VARCHAR(255) NULL,
	is_hidden TINYINT(1)  NOT NULL DEFAULT 0,
	is_thumb TINYINT(1) NOT NULL DEFAULT 0,

	CONSTRAINT fk_file_post_uid FOREIGN KEY (post_uid) REFERENCES posts(post_uid) ON DELETE CASCADE,

	-- Index for file_md5
	KEY idx_md5 (file_md5),

	-- Additional indexes
	KEY idx_post_uid (post_uid),  -- For queries filtering by post_uid
	KEY idx_file_ext (file_ext),  -- For queries filtering by file extension
	KEY idx_file_size (file_size), -- For queries filtering by file size
	KEY idx_file_name_prefix (file_name(255)), -- Prefix index for file_name (adjust length as necessary)
	KEY idx_mime_type (mime_type), -- For queries filtering by mime_type

	-- Composite index for queries filtering by both post_uid and file_md5
	KEY idx_post_uid_file_md5 (post_uid, file_md5)
) ENGINE=InnoDB;