-- PESYJE - BUSINESS GALLERY PREMIUM FAZA 1
-- Safe/idempotent upgrade. Import ONCE; re-running is safe.

SET FOREIGN_KEY_CHECKS=0;

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) NULL,
  `cover_gallery_id` bigint unsigned 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;

-- album_id on business_galleries
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='business_galleries' AND COLUMN_NAME='album_id');
SET @q := IF(@c=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;

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

-- description on albums
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='business_gallery_albums' AND COLUMN_NAME='description');
SET @q := IF(@c=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;

-- cover_gallery_id on albums
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='business_gallery_albums' AND COLUMN_NAME='cover_gallery_id');
SET @q := IF(@c=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;

-- index business_id + album_id
SET @c := (SELECT COUNT(*) FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='business_galleries'
 AND INDEX_NAME='business_galleries_business_id_album_id_index');
SET @q := IF(@c=0,
 'ALTER TABLE `business_galleries` ADD INDEX `business_galleries_business_id_album_id_index` (`business_id`,`album_id`)',
 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- album FK
SET @c := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='business_galleries'
 AND CONSTRAINT_NAME='business_galleries_album_id_foreign' AND CONSTRAINT_TYPE='FOREIGN KEY');
SET @q := IF(@c=0,
 'ALTER TABLE `business_galleries` ADD CONSTRAINT `business_galleries_album_id_foreign` FOREIGN KEY (`album_id`) REFERENCES `business_gallery_albums` (`id`) ON DELETE SET NULL',
 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- album business FK
SET @c := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='business_gallery_albums'
 AND CONSTRAINT_NAME='business_gallery_albums_business_id_foreign' AND CONSTRAINT_TYPE='FOREIGN KEY');
SET @q := IF(@c=0,
 'ALTER TABLE `business_gallery_albums` ADD CONSTRAINT `business_gallery_albums_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE',
 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- cover FK
SET @c := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='business_gallery_albums'
 AND CONSTRAINT_NAME='business_gallery_albums_cover_gallery_id_foreign' AND CONSTRAINT_TYPE='FOREIGN KEY');
SET @q := IF(@c=0,
 'ALTER TABLE `business_gallery_albums` ADD CONSTRAINT `business_gallery_albums_cover_gallery_id_foreign` FOREIGN KEY (`cover_gallery_id`) REFERENCES `business_galleries` (`id`) ON DELETE SET NULL',
 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Give existing photos a deterministic initial order.
SET @n := 0;
UPDATE `business_galleries`
SET `sort_order` = (@n := @n + 1)
WHERE `sort_order` = 0 OR `sort_order` IS NULL
ORDER BY `business_id`, `id` DESC;

SET FOREIGN_KEY_CHECKS=1;

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