CREATE TABLE IF NOT EXISTS customer_demands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  type ENUM('sale','rent') NOT NULL,
  category VARCHAR(100) NOT NULL,
  city_id BIGINT UNSIGNED NULL,
  district_id BIGINT UNSIGNED NULL,
  neighborhood_id BIGINT UNSIGNED NULL,
  min_price DECIMAL(15,2) NULL,
  max_price DECIMAL(15,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'TRY',
  room_count VARCHAR(30) NULL,
  min_area INT UNSIGNED NULL,
  max_area INT UNSIGNED NULL,
  status ENUM('open','matched','closed','cancelled') NOT NULL DEFAULT 'open',
  description TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customer_demands_customer (customer_id),
  INDEX idx_customer_demands_assigned_user (assigned_user_id),
  INDEX idx_customer_demands_status (status),
  INDEX idx_customer_demands_search (type, category, city_id, district_id, neighborhood_id),
  INDEX idx_customer_demands_price (min_price, max_price),
  CONSTRAINT fk_customer_demands_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_customer_demands_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_district FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_demands_neighborhood FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS customer_portfolio_matches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_demand_id BIGINT UNSIGNED NOT NULL,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  match_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  status ENUM('suggested','contacted','rejected','accepted') NOT NULL DEFAULT 'suggested',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_demand_portfolio_match (customer_demand_id, portfolio_id),
  INDEX idx_customer_portfolio_matches_portfolio (portfolio_id),
  INDEX idx_customer_portfolio_matches_status (status),
  CONSTRAINT fk_matches_demand FOREIGN KEY (customer_demand_id) REFERENCES customer_demands(id) ON DELETE CASCADE,
  CONSTRAINT fk_matches_portfolio FOREIGN KEY (portfolio_id) REFERENCES portfolios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;