CREATE VIEW stat_debt_first AS
select
	d1.id,
	d1.attachment_id,
	d1.update_date,
	d1.penalty,
	d1.balance,
	d1.payment,
	d1.insurance,
	d1.fee,
	d1.payed,
	d1.created_at,
	d1.updated_at,
	d1.created_by,
	d1.updated_by
from debt d1
left join debt d2 on d1.attachment_id = d2.attachment_id and d1.id > d2.id
where isnull(d2.attachment_id);

CREATE VIEW stat_debt_last AS
select
	d1.id,
	d1.attachment_id,
	d1.update_date,
	d1.penalty,
	d1.balance,
	d1.payment,
	d1.insurance,
	d1.fee,
	d1.payed,
	d1.created_at,
	d1.updated_at,
	d1.created_by,
	d1.updated_by
from debt d1
left join debt d2 on d1.attachment_id = d2.attachment_id and d1.id < d2.id
where isnull(d2.attachment_id);

CREATE VIEW statistic AS
select a.id AS attachment_id,
	c.id AS card_id,
	s.id AS supplier_id,
	c.user_id AS user_id,
	u.first_name AS user_first_name,
	u.last_name AS user_last_name,
	a.contract_type AS contract_type,
	s.name AS supplier,
	a.supplier_code AS supplier_code,
	c.client_name AS client,
	ifnull(a.requisition_date,c.created_at) AS start_date,
	c.case_end_date AS end_date,
	(to_days(c.case_end_date) - to_days(ifnull(a.requisition_date,c.created_at))) AS case_days,
	c.state AS state,
	debt_first.update_date AS first_date,
	debt_last.update_date AS last_date,
	((((12 * year(debt_last.update_date)) + month(debt_last.update_date)) - (12 * year(debt_first.update_date))) - month(debt_first.update_date)) AS rozdil_mesicu,
	((((ifnull(debt_last.penalty,0) + ifnull(debt_last.insurance,0)) + ifnull(debt_last.fee,0)) + ifnull(debt_last.payment,0)) + (a.price_payment * ((((12 * year(debt_last.update_date)) + month(debt_last.update_date)) - (12 * year(debt_first.update_date))) - month(debt_first.update_date)))) AS sum_supplier,
	((select sum(payment.price) from payment where ((payment.attachment_id = a.id) and (payment.payment_type <> 'PREPLATEK'))) + (select payment.price from payment where ((payment.attachment_id = a.id) and (payment.payment_type = 'PREPLATEK')) order by payment.payment_date desc limit 1)) AS sum_payment,
	c.case_penalty AS case_penalty,
	a.cert_taken AS cert_taken,
	c.case_income AS case_income,
    c.user_finished AS user_finished
from attachment a
join card c on c.id = a.card_id
join supplier s on s.id = c.supplier_id
join sf_guard_user_profile u on u.user_id = c.user_id
left join stat_debt_first debt_first on debt_first.attachment_id = a.id
left join stat_debt_last debt_last on debt_last.attachment_id = a.id;