-- Adds paid add-on subscriptions + paid-only listing analytics
SET NAMES utf8mb4;

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;

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;