CREATE OR REPLACE VIEW view_owner_pipeline AS
SELECT
  p.id AS property_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 OR REPLACE VIEW view_searcher_pipeline AS
SELECT
  sr.id AS search_request_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;

CREATE OR REPLACE VIEW view_expiring_rentals AS
SELECT
  rc.id AS rental_contract_id,
  p.property_type,
  COALESCE(p.location_label, CONCAT_WS(', ', p.city, p.district, p.address)) AS location_label,
  u.initials AS broker_initials,
  p.area,
  rc.monthly_rent,
  rc.currency,
  rc.end_date,
  (rc.end_date - CURRENT_DATE) AS days_until_expiry,
  CASE
    WHEN rc.end_date < CURRENT_DATE THEN 'overdue'
    WHEN rc.end_date <= CURRENT_DATE + INTERVAL '7 days' THEN 'next_7_days'
    WHEN rc.end_date <= CURRENT_DATE + INTERVAL '30 days' THEN 'next_30_days'
    WHEN rc.end_date <= CURRENT_DATE + INTERVAL '90 days' THEN 'next_90_days'
    ELSE 'later'
  END AS expiry_group,
  rc.broker_id
FROM rental_contracts rc
INNER JOIN properties p ON p.id = rc.property_id AND p.deleted_at IS NULL
LEFT JOIN users u ON u.id = rc.broker_id
WHERE rc.status = 'active';

CREATE OR REPLACE VIEW view_deal_prices AS
SELECT
  d.id AS deal_id,
  COALESCE(p.location_label, CONCAT_WS(', ', p.city, p.district, p.address), sr.location_text) AS location_label,
  COALESCE(p.property_type, sr.property_type) AS property_type,
  COALESCE(p.area, sr.preferred_area) AS area,
  d.final_price,
  d.currency,
  ROUND(d.final_price / NULLIF(COALESCE(p.area, sr.preferred_area), 0), 2) AS price_per_sqm,
  u.initials AS broker_initials,
  d.closed_at,
  d.assigned_broker_id
FROM deals d
LEFT JOIN properties p ON p.id = d.property_id
LEFT JOIN search_requests sr ON sr.id = d.search_request_id
LEFT JOIN users u ON u.id = d.assigned_broker_id
WHERE d.deleted_at IS NULL
  AND d.stage = 'closed_by_us'
  AND d.final_price IS NOT NULL
  AND COALESCE(p.area, sr.preferred_area) IS NOT NULL
  AND COALESCE(p.area, sr.preferred_area) > 0;

CREATE OR REPLACE VIEW view_statistics_overview AS
SELECT
  (
    SELECT COUNT(*)::int
    FROM deals d
    WHERE d.deleted_at IS NULL
      AND d.stage NOT IN ('closed_by_us', 'closed_without_us', 'cancelled_by_client', 'cancelled_by_us')
  ) AS active_deals,
  (
    SELECT COUNT(*)::int
    FROM deals d
    WHERE d.deleted_at IS NULL AND d.stage = 'closed_by_us'
  ) AS closed_by_us,
  (
    SELECT COALESCE(SUM(d.commission_amount), 0)
    FROM deals d
    WHERE d.deleted_at IS NULL AND d.stage = 'closed_by_us' AND d.commission_currency = 'EUR'
  ) AS total_commissions,
  (SELECT COUNT(*)::int FROM clients c WHERE c.deleted_at IS NULL) AS clients_count;

CREATE OR REPLACE VIEW view_map_properties AS
SELECT
  p.id AS property_id,
  d.id AS deal_id,
  p.property_type,
  p.deal_type,
  p.pipeline_status AS status,
  COALESCE(d.final_price, p.price) AS price,
  COALESCE(d.currency, p.currency) AS currency,
  p.city,
  p.district,
  p.address,
  p.latitude,
  p.longitude,
  u.initials AS broker_initials,
  p.assigned_broker_id
FROM properties p
LEFT JOIN deals d ON d.property_id = p.id AND d.deleted_at IS NULL
LEFT JOIN users u ON u.id = p.assigned_broker_id
WHERE p.deleted_at IS NULL
  AND p.latitude IS NOT NULL
  AND p.longitude IS NOT NULL;