-- FlieMart: add contact + social columns (run once in phpMyAdmin)
-- Safe to re-run: skips columns that already exist.
-- Works whether the table is SiteSettings or sitesettings (Linux MariaDB).

SET @db := DATABASE();
SET @tbl := (
  SELECT TABLE_NAME
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME IN ('SiteSettings', 'sitesettings')
  LIMIT 1
);

-- Abort clearly if the settings table is missing
SET @tbl := IFNULL(@tbl, 'SiteSettings');

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'supportPhone'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `supportPhone` VARCHAR(191) NOT NULL DEFAULT ''+1 (800) 555-0147''')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'address'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `address` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'facebookUrl'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `facebookUrl` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'instagramUrl'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `instagramUrl` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'twitterUrl'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `twitterUrl` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'youtubeUrl'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `youtubeUrl` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = 'tiktokUrl'
    ),
    'SELECT 1',
    CONCAT('ALTER TABLE `', @tbl, '` ADD COLUMN `tiktokUrl` TEXT NULL')
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := CONCAT(
  'UPDATE `', @tbl, '` SET ',
  '`logoUrl` = IF(`logoUrl` IS NULL OR `logoUrl` = '''', ''/brand/fliemart-logo.png'', `logoUrl`), ',
  '`faviconUrl` = IF(`faviconUrl` IS NULL OR `faviconUrl` = '''', ''/favicon.png'', `faviconUrl`), ',
  '`supportPhone` = IF(`supportPhone` IS NULL OR `supportPhone` = '''', ''+1 (800) 555-0147'', `supportPhone`), ',
  '`address` = IF(`address` IS NULL OR TRIM(`address`) = '''', ''1200 Market Street, Suite 400, San Francisco, CA 94103, USA'', `address`), ',
  '`facebookUrl` = IFNULL(`facebookUrl`, ''''), ',
  '`instagramUrl` = IFNULL(`instagramUrl`, ''''), ',
  '`twitterUrl` = IFNULL(`twitterUrl`, ''''), ',
  '`youtubeUrl` = IFNULL(`youtubeUrl`, ''''), ',
  '`tiktokUrl` = IFNULL(`tiktokUrl`, '''') ',
  'WHERE `id` = ''default'''
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;