-- PESYJE BUSINESS GALLERY PREMIUM
-- Safe/idempotent install for phpMyAdmin.
-- It does NOT delete existing photos.

SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS `business_gallery_albums` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `business_id` bigint unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `cover_gallery_id` bigint unsigned DEFAULT NULL,
  `sort_order` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `business_gallery_albums_business_id_slug_unique` (`business_id`,`slug`),
  KEY `business_gallery_albums_business_id_index` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @has_album_id := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_galleries' AND COLUMN_NAME='album_id'
);
SET @q := IF(@has_album_id=0,
  'ALTER TABLE `business_galleries` ADD COLUMN `album_id` bigint unsigned NULL AFTER `business_id`',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_sort_order := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_galleries' AND COLUMN_NAME='sort_order'
);
SET @q := IF(@has_sort_order=0,
  'ALTER TABLE `business_galleries` ADD COLUMN `sort_order` int unsigned NOT NULL DEFAULT 0 AFTER `downloads`',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_desc := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_gallery_albums' AND COLUMN_NAME='description'
);
SET @q := IF(@has_desc=0,
  'ALTER TABLE `business_gallery_albums` ADD COLUMN `description` varchar(500) NULL AFTER `slug`',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_cover := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_gallery_albums' AND COLUMN_NAME='cover_gallery_id'
);
SET @q := IF(@has_cover=0,
  'ALTER TABLE `business_gallery_albums` ADD COLUMN `cover_gallery_id` bigint unsigned NULL AFTER `description`',
  'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SELECT
  (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_gallery_albums') AS albums_table,
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_galleries' AND COLUMN_NAME='album_id') AS album_id,
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_galleries' AND COLUMN_NAME='sort_order') AS sort_order,
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='business_gallery_albums' AND COLUMN_NAME='cover_gallery_id') AS cover_gallery_id;