-- Run once on production/staging if admin listing edit returns HTTP 500.
-- Safe to re-run: uses information_schema checks and CREATE TABLE IF NOT EXISTS.
-- Works on MySQL 5.7+ / MariaDB 10.x (no ADD COLUMN IF NOT EXISTS). Run via phpMyAdmin or mysql CLI.

SET NAMES utf8mb4;

-- business_users.phone
SET @has_phone := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'business_users' AND COLUMN_NAME = 'phone'
);
SET @sql := IF(@has_phone = 0, 'ALTER TABLE business_users ADD COLUMN phone VARCHAR(80) DEFAULT NULL AFTER name', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- listings.featured_image_path
SET @has_feat_img := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'featured_image_path'
);
SET @sql := IF(@has_feat_img = 0, 'ALTER TABLE listings ADD COLUMN featured_image_path VARCHAR(500) DEFAULT NULL AFTER logo_path', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- listings.qualifications
SET @has_qual := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'qualifications'
);
SET @sql := IF(@has_qual = 0, 'ALTER TABLE listings ADD COLUMN qualifications TEXT NULL DEFAULT NULL AFTER services_offered', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- listings address + map coordinates
SET @has_addr := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'address'
);
SET @sql := IF(@has_addr = 0,
  'ALTER TABLE listings ADD COLUMN address TEXT NULL DEFAULT NULL AFTER town_area, ADD COLUMN latitude DECIMAL(10,7) NULL DEFAULT NULL AFTER address, ADD COLUMN longitude DECIMAL(11,7) NULL DEFAULT NULL AFTER latitude',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- listings.first_activated_at
SET @has_first := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'first_activated_at'
);
SET @sql := IF(@has_first = 0, 'ALTER TABLE listings ADD COLUMN first_activated_at TIMESTAMP NULL DEFAULT NULL AFTER status', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_first_idx := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND INDEX_NAME = 'idx_listing_first_activation'
);
SET @sql := IF(@has_first_idx = 0, 'ALTER TABLE listings ADD KEY idx_listing_first_activation (first_activated_at)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

UPDATE listings
SET first_activated_at = COALESCE(created_at, updated_at)
WHERE status = 'active' AND first_activated_at IS NULL;

-- listings.is_frontpage_featured
SET @has_fp := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'is_frontpage_featured'
);
SET @sql := IF(@has_fp = 0, 'ALTER TABLE listings ADD COLUMN is_frontpage_featured TINYINT(1) NOT NULL DEFAULT 0 AFTER is_featured', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- listings.is_premium
SET @has_prem := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'listings' AND COLUMN_NAME = 'is_premium'
);
SET @sql := IF(@has_prem = 0, 'ALTER TABLE listings ADD COLUMN is_premium TINYINT(1) NOT NULL DEFAULT 0 AFTER is_frontpage_featured', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

CREATE TABLE IF NOT EXISTS listing_subscriptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED NOT NULL,
  stripe_customer_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL UNIQUE,
  status VARCHAR(40) NOT NULL DEFAULT 'incomplete',
  current_period_start DATETIME DEFAULT NULL,
  current_period_end DATETIME DEFAULT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_listing_subscription_listing (listing_id),
  KEY idx_listing_subscription_status (status),
  KEY idx_listing_subscription_business (business_user_id),
  CONSTRAINT fk_listing_subscription_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_listing_subscription_business FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_subscription_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_subscription_id INT UNSIGNED NOT NULL,
  addon_key ENUM('featured','frontpage_featured','premium') NOT NULL,
  monthly_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  stripe_item_id VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_listing_subscription_addon (listing_subscription_id, addon_key),
  KEY idx_listing_subscription_item_key (addon_key),
  CONSTRAINT fk_listing_subscription_item_subscription FOREIGN KEY (listing_subscription_id) REFERENCES listing_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  event_type ENUM('view','website_click','quote_sent') NOT NULL,
  session_id VARCHAR(64) DEFAULT NULL,
  ip_hash CHAR(64) DEFAULT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_listing_event_lookup (listing_id, event_type, occurred_at),
  KEY idx_listing_event_occurred (occurred_at),
  CONSTRAINT fk_listing_event_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin payment links (required for /admin/listings/edit/{id})
CREATE TABLE IF NOT EXISTS listing_payment_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED DEFAULT NULL,
  admin_id INT UNSIGNED DEFAULT NULL,
  contact_email VARCHAR(255) DEFAULT NULL,
  addon_keys_csv VARCHAR(120) NOT NULL,
  addon_amounts_json TEXT DEFAULT NULL,
  custom_total_cents INT UNSIGNED NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'eur',
  token VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('pending','checkout_started','paid','cancelled','expired') NOT NULL DEFAULT 'pending',
  stripe_checkout_session_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL,
  paid_at DATETIME DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_listing_payment_links_listing (listing_id, created_at),
  KEY idx_listing_payment_links_status (status),
  CONSTRAINT fk_listing_payment_links_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_listing_payment_links_business FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_listing_payment_links_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ad_subscriptions.business_user_id (required for /admin/ads)
SET @has_ad_bu := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ad_subscriptions' AND COLUMN_NAME = 'business_user_id'
);
SET @sql := IF(@has_ad_bu = 0, 'ALTER TABLE ad_subscriptions ADD COLUMN business_user_id INT UNSIGNED DEFAULT NULL AFTER ad_product_id', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_ad_bu_idx := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ad_subscriptions' AND INDEX_NAME = 'idx_ad_subs_business_user'
);
SET @sql := IF(@has_ad_bu_idx = 0, 'ALTER TABLE ad_subscriptions ADD KEY idx_ad_subs_business_user (business_user_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_ad_bu_fk := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ad_subscriptions' AND CONSTRAINT_NAME = 'fk_ad_subs_business_user'
);
SET @sql := IF(@has_ad_bu_fk = 0, 'ALTER TABLE ad_subscriptions ADD CONSTRAINT fk_ad_subs_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

CREATE TABLE IF NOT EXISTS url_redirects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_path VARCHAR(500) NOT NULL,
  to_url VARCHAR(1000) NOT NULL,
  hit_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_hit_at TIMESTAMP NULL DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_url_redirects_from (from_path),
  KEY idx_url_redirects_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS url_not_found_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  path VARCHAR(500) NOT NULL,
  hit_count INT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_hit_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_url_not_found_path (path),
  KEY idx_url_not_found_hits (hit_count, last_hit_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;