DROP VIEW IF EXISTS view_searcher_pipeline;
DROP VIEW IF EXISTS view_owner_pipeline;

CREATE VIEW view_owner_pipeline AS
SELECT
  p.id AS property_id,
  p.owner_client_id,
  c.full_name AS owner_name,
  p.deal_type,
  p.property_type,
  p.area,
  p.floor,
  p.city,
  p.district,
  p.address,
  u.initials AS broker_initials,
  p.price,
  p.currency,
  p.rent_period,
  p.contract_type,
  p.pipeline_status,
  rc.end_date AS rental_end_date,
  p.assigned_broker_id
FROM properties p
INNER JOIN clients c ON c.id = p.owner_client_id AND c.deleted_at IS NULL
LEFT JOIN users u ON u.id = p.assigned_broker_id
LEFT JOIN LATERAL (
  SELECT rc2.end_date
  FROM rental_contracts rc2
  WHERE rc2.property_id = p.id AND rc2.status = 'active'
  ORDER BY rc2.end_date DESC
  LIMIT 1
) rc ON TRUE
WHERE p.deleted_at IS NULL;

CREATE VIEW view_searcher_pipeline AS
SELECT
  sr.id AS search_request_id,
  sr.client_id,
  c.full_name AS client_name,
  sr.deal_type,
  sr.property_type,
  sr.preferred_area,
  sr.preferred_floor,
  sr.location_text,
  u.initials AS broker_initials,
  sr.target_price,
  sr.currency,
  sr.rent_period,
  sr.contract_type,
  sr.pipeline_status,
  rc.end_date AS rental_end_date,
  sr.assigned_broker_id
FROM search_requests sr
INNER JOIN clients c ON c.id = sr.client_id AND c.deleted_at IS NULL
LEFT JOIN users u ON u.id = sr.assigned_broker_id
LEFT JOIN LATERAL (
  SELECT rc2.end_date
  FROM rental_contracts rc2
  INNER JOIN deals d ON d.id = rc2.deal_id
  WHERE d.search_request_id = sr.id AND rc2.status = 'active'
  ORDER BY rc2.end_date DESC
  LIMIT 1
) rc ON TRUE
WHERE sr.deleted_at IS NULL;