CREATE VIEW public.v_deals_and_customer
AS
SELECT t.company AS customer,
    t.forename,
    t.surname,
    s.company AS supplier,
    u.user_name,
    s.sales_contact,
    d.supplier_id,
    d.customer_id,
    d.deal_no,
    d.capital_cost,
    d.equipment_breakdown,
    d.payment_term,
    d.payment_profile_1,
    d.payment_profile_2,
    d.billing,
    d.notes,
    d.state,
    d.bylender,
    d.ref_number,
    d.upgrade,
    d.special,
    d.rental,
    d.reason,
    d.term,
    d.state_date,
    d.docs_received,
    d.docs_sent,
    d.gross_profit,
    d.deferal_payment,
    d.suppliers_comission,
    d.title_sent_date,
    d.title_paid_date,
    d.paid_date,
    d.paid,
    d.user_id_paid,
    d.eq_code,
    t.address AS customer_address,
    s.address AS supplier_address,
    --now() AS estimate_completion_date  --<----- заглушка
   getestimationdate(d.deal_no) AS estimate_completion_date
FROM deals d
     JOIN customers t ON d.customer_id = t.id
     JOIN suppliers s ON d.supplier_id = s.id
     JOIN users u ON s.agent_id = u.user_id;
     
---- 
     
CREATE OR REPLACE FUNCTION public.getestimationdate (
  _dealno integer
)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS
$body$
	select    a.msg_date + payment_term * INTERVAL '1 month'
    --DATEADD(MONTH,payment_term,a.msg_date)
	from deals d
    inner join  crm_activity a
    on d.deal_no=a.msg_dealno
	and a.msg_text ilike '%Paid%'
	and d.deal_no=_dealno
    and d.state_date is not null
    limit 1;
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;