#
# albums
#
CREATE TABLE IF NOT EXISTS albums (
	`id` 									int(11) unsigned NOT NULL auto_increment,
	`name`								varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	`user_fid`						int(11) unsigned NOT NULL,
	`added_by`						int(11) unsigned NOT NULL,
	`project_fid`					int(11) unsigned NOT NULL,
	`count`								int(8) unsigned NOT NULL,
	`status`							int(1) unsigned NULL,
	`created`							datetime NOT NULL,
	`modified`						timestamp NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY(`user_fid`) REFERENCES users(`id`),
	FOREIGN KEY(`added_by`) REFERENCES users(`id`),
	FOREIGN KEY(`project_fid`) REFERENCES projects(`id`)	
) ENGINE=InnoDB COMMENT='albums';

#
# album zimes
# 	UNIQUE KEY unique_nr (`album_fid`, `nr`)
#
CREATE TABLE IF NOT EXISTS album_zimes (
	`id` 									int(11) unsigned NOT NULL auto_increment,
	`album_fid`						int(11) unsigned NOT NULL,
	`zime_fid`						int(11) unsigned NOT NULL,
	`nr`									int(8) unsigned NOT NULL,
	`name`								varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	`added_by`						int(11) unsigned NOT NULL,
	`status`							int(1) unsigned NULL,
	`added`								datetime NOT NULL,
	`modified`						timestamp NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY(`album_fid`) REFERENCES albums(`id`),
	FOREIGN KEY(`added_by`) REFERENCES users(`id`)
) ENGINE=InnoDB COMMENT='album_zimes';

#
# albums_log
#
CREATE TABLE IF NOT EXISTS albums_log (
	`id` 									int(11) unsigned NOT NULL auto_increment,
	`album_zime_fid`			int(11) unsigned NOT NULL,
	`nr_old`							int(8) NOT NULL,
	`nr_new`							int(8) NOT NULL,
	`nr_diff`							int(8) NOT NULL,
	`nr_dir`							int(1) NOT NULL,
	`sorted_by`						int(11) unsigned NOT NULL,
	`created`							datetime NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY(`album_zime_fid`) REFERENCES album_zimes(`id`),
	FOREIGN KEY(`sorted_by`) REFERENCES users(`id`)
) ENGINE=InnoDB COMMENT='albums_log';

#
# top added to album zimes
#

#
# top added to album zimes by different users
#

#
# top reordered zimes
#
SELECT COUNT( * ) AS zime_reorders_count, z.id, z.message
FROM  `albums_log` AS al
LEFT OUTER JOIN album_zimes AS az ON az.id = al.album_zime_fid
LEFT OUTER JOIN zimes_myisam AS z ON z.id = az.zime_fid
WHERE 1 
GROUP BY z.id
ORDER BY zime_reorders_count DESC 
LIMIT 0 , 30

#
# top reordered zimes by different users
#

#
# album_zimes_viewed_by
# count, times viewed by the viewed_by_fid
#
CREATE TABLE IF NOT EXISTS album_zimes_viewed_by (
	`id` 									int(11) unsigned NOT NULL auto_increment,
	`album_zime_fid`			int(11) unsigned NOT NULL,
	`viewed_by_fid`				int(11) unsigned NOT NULL,
	`count`								int(8) unsigned NOT NULL DEFAULT 1,
	`modified`						timestamp NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY unique_zime_viewed_by (`album_zime_fid`, `viewed_by_fid`),
	FOREIGN KEY(`album_zime_fid`) REFERENCES album_zimes(`id`),
	FOREIGN KEY(`viewed_by_fid`) REFERENCES users(`id`)
) ENGINE=InnoDB COMMENT='album_zimes_viewed_by';

#
# reordered_zimes_viewed_by
# count, times viewed by the viewed_by_fid
#
CREATE TABLE IF NOT EXISTS reordered_zimes_viewed_by (
	`id` 									int(11) unsigned NOT NULL auto_increment,
	`album_log_fid`				int(11) unsigned NOT NULL,
	`viewed_by_fid`				int(11) unsigned NOT NULL,
	`count`								int(8) unsigned NOT NULL DEFAULT 1,
	`modified`						timestamp NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY unique_zime_viewed_by (`album_log_fid`, `viewed_by_fid`),
	FOREIGN KEY(`album_log_fid`) REFERENCES albums_log(`id`),
	FOREIGN KEY(`viewed_by_fid`) REFERENCES users(`id`)
) ENGINE=InnoDB COMMENT='reordered_zimes_viewed_by';


