-- Run once on existing databases (fresh schema.sql includes these)
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS business_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(180) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_business_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE listings
  ADD COLUMN business_user_id INT UNSIGNED DEFAULT NULL AFTER status,
  ADD KEY idx_listing_owner (business_user_id),
  ADD CONSTRAINT fk_listing_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS listing_claims (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED NOT NULL,
  message VARCHAR(1000) DEFAULT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_note VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at TIMESTAMP NULL DEFAULT NULL,
  KEY idx_claim_listing (listing_id),
  KEY idx_claim_status (status),
  KEY idx_claim_user (business_user_id),
  CONSTRAINT fk_claim_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_claim_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;