<?php
  include "../album_library.php";

	/**
	 * This SQL sniplit is used in a number of places, so it was easier to contain it centrally
	 */
function getPublicSubQuery( $privateFlag ) {
			if( $privateFlag == 1 ) {
			$sql = "
    (
      SELECT i.id
      FROM Images i
    )";
		} else {
			$sql = "
    (
      SELECT i.id
      FROM Images i
      INNER JOIN ImageTags it
        ON it.imageid = i.id
      INNER JOIN Tags t
        ON t.id = it.tagid
        AND t.name = 'Public'
    )";
		}

		return $sql;
	}

	function updateAlbumsView($db, $privateFlag) {
		$tableName = "AlbumsView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}

		$sql = "CREATE TABLE {$tableName}_temp AS
		SELECT
			a.id as album_id, a.relativePath as album_path, a.caption as album_caption, a.collection, a.icon, a.date as album_date,
			i.id as image_id, i.name as image_name,
			icon_album.relativePath as image_path,
			ittl.comment as image_title,
			ic.comment as image_caption,
			ii.creationDate, i.modificationDate,
			md.make, md.model, md.lens, md.aperture, md.focalLength, md.exposureTime, md.sensitivity, md.meteringMode,
			pos.latitudeNumber, pos.longitudeNumber,
			ImageCount.num_images as num_images
		FROM Albums a
		INNER JOIN Images i
			ON i.id = a.icon
		INNER JOIN Albums icon_album
			ON icon_album.id = i.album
		LEFT OUTER JOIN ImageComments ittl
			ON ittl.imageid = i.id
			AND ittl.type = 3 
		LEFT OUTER JOIN ImageComments ic
			ON ic.imageid = i.id
			AND ic.type = 1
		LEFT OUTER JOIN ImageInformation ii
			ON ii.imageid = i.id
		LEFT OUTER JOIN ImageMetadata md
			ON md.imageid = i.id
		LEFT OUTER JOIN ImagePositions pos
			ON pos.imageid = i.id
		LEFT OUTER JOIN (
				SELECT i.album, count(*) AS num_images FROM Images i 
				WHERE i.id IN " . getPublicSubQuery($privateFlag) . "
				GROUP BY i.album
			) as ImageCount
			ON ImageCount.album = a.id
		WHERE i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql( $sql, null );

		$sql = "CREATE UNIQUE INDEX IX_AlbumsView_AlbumId ON {$tableName}_temp ( album_id )";
		$db->executeSql( $sql, null );
		$sql = "CREATE INDEX IX_AlbumsView_AlbumDate ON {$tableName}_temp ( album_date )";
		$db->executeSql( $sql, null );

		$sql = "DROP TABLE {$tableName}";
		$db->executeSql( $sql, null );

		$sql = "RENAME TABLE {$tableName}_temp TO {$tableName}";
		$db->executeSql( $sql, null );
	}
	
	function updateImagesView($db, $privateFlag) {
		$tableName = "ImagesView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}
		$sql = "CREATE TABLE {$tableName}_temp AS
		SELECT
			a.id as album_id, a.relativePath as album_path, a.caption as album_caption, a.collection, a.date as album_date,
			i.id as image_id, i.name as image_name,
			a.relativePath as image_path,
			ittl.comment as image_title,
			ic.comment as image_caption,
			ii.creationDate, i.modificationDate,
			md.make, md.model, md.lens, md.aperture, md.focalLength, md.exposureTime, md.sensitivity, md.meteringMode,
			pos.latitudeNumber, pos.longitudeNumber
		FROM Albums a
		INNER JOIN Images i
			ON i.album = a.id
		LEFT OUTER JOIN ImageComments ittl
			ON ittl.imageid = i.id
			AND ittl.type = 3 
		LEFT OUTER JOIN ImageComments ic
			ON ic.imageid = i.id
			AND ic.type = 1
		LEFT OUTER JOIN ImageInformation ii
			ON ii.imageid = i.id
		LEFT OUTER JOIN ImageMetadata md
			ON md.imageid = i.id
		LEFT OUTER JOIN ImagePositions pos
			ON pos.imageid = i.id
		WHERE upper(i.name) like '%JPG'
			AND i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql($sql, null);

		$sql = "CREATE UNIQUE INDEX IX_ImagesView_ImageId ON {$tableName}_temp ( image_id )";
		$db->executeSql($sql, null);
		$sql = "CREATE INDEX IX_ImagesView_AlbumId ON {$tableName}_temp ( album_id )";
		$db->executeSql($sql, null);

		$sql = "DROP TABLE {$tableName}";
		$db->executeSql($sql, null);

		$sql = "RENAME TABLE ${tableName}_temp TO ${tableName}";
		$db->executeSql($sql, null);

		$tableName = "ImageTagsView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}
    $sql = "CREATE TABLE {$tableName}_temp AS
		SELECT i.album as album_id, i.id as image_id, t.id as tag_id, t.name as tag_name
		FROM Images i
		INNER JOIN ImageTags it
      ON it.imageid = i.id
    INNER JOIN Tags t
      ON t.id = it.tagid
      AND t.pid not in (
				SELECT id FROM Tags WHERE name IN ( '_Digikam_Internal_Tags_', 'Visibility' )
			)
		WHERE upper(i.name) like '%JPG'
			AND i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql($sql, null);

		$sql = "CREATE INDEX IX_ImageTagsView_TagId ON {$tableName}_temp ( tag_id )";
		$db->executeSql($sql, null);
		$sql = "CREATE INDEX IX_ImageTagsView_ImageId ON {$tableName}_temp ( image_id )";
		$db->executeSql($sql, null);
		$sql = "CREATE INDEX IX_ImageTagsView_AlbumId ON {$tableName}_temp ( album_id )";
		$db->executeSql($sql, null);

		$sql = "DROP TABLE $tableName";
		$db->executeSql($sql, null);

		$sql = "RENAME TABLE {$tableName}_temp TO $tableName";
		$db->executeSql($sql, null);

	}

	
	
	$db = new DatabaseConnection();
	$sql = "CREATE INDEX IX_Albums_id ON Albums ( id )";
	$db->executeSql($sql, null);

	for( $i = 0 ; $i <= 1 ; $i++ ) {
		updateAlbumsView($db, $i);
		updateImagesView($db, $i);
	}

?>