SELECT
    c.id,
    c.ins_time AS creation_date,
    c.STATUS,
    c.contract_number,

    c.sign_date,

    ap.act_sign_date,
    adp.delivery_date
/*
    case when c.status in ('SIGNED', 'DELIVERY_STARTED', 'DELIVERY_CREATED', 'DELIVERY_SHIPPED', 'DELIVERY_COMPLETED', 'ACT_SIGNED', 'IN_BACKOFFICE', 'CLOSED') and date_trunc('day', c.sign_date) = date_trunc('day', now()) then 1 else 0 end signed_day,
    case when c.status in ('ACT_SIGNED', 'DELIVERY_COMPLETED', 'IN_BACKOFFICE', 'CLOSED') and (date_trunc('day', ap.act_sign_date) = date_trunc('day', now()) or date_trunc('day', adp.delivery_date) = date_trunc('day', now())) then 1 else 0 end finished_day,

    case when c.status in ('SIGNED', 'DELIVERY_STARTED', 'DELIVERY_CREATED', 'DELIVERY_SHIPPED', 'DELIVERY_COMPLETED', 'ACT_SIGNED', 'IN_BACKOFFICE', 'CLOSED') and date_trunc('month', c.sign_date) = date_trunc('month', now()) then 1 else 0 end signed_month,
    case when c.status in ('ACT_SIGNED', 'DELIVERY_COMPLETED', 'IN_BACKOFFICE', 'CLOSED') and (date_trunc('month', ap.act_sign_date) = date_trunc('month', now()) or date_trunc('month', adp.delivery_date) = date_trunc('month', now())) then 1 else 0 end finished_month,

    case when c.status in ('SIGNED', 'DELIVERY_STARTED', 'DELIVERY_CREATED', 'DELIVERY_SHIPPED', 'DELIVERY_COMPLETED', 'ACT_SIGNED', 'IN_BACKOFFICE', 'CLOSED') and date_trunc('year', c.sign_date) = date_trunc('year', now()) then 1 else 0 end signed,
    case when c.status in ('ACT_SIGNED', 'DELIVERY_COMPLETED', 'IN_BACKOFFICE', 'CLOSED') and (date_trunc('year', ap.act_sign_date) = date_trunc('year', now()) or date_trunc('year', adp.delivery_date) = date_trunc('year', now())) then 1 else 0 end finished
*/
    FROM liza.leasing_contract c
    LEFT JOIN liza.act_process ap ON ap.contract_number = c.contract_number AND ap.TYPE <> 'RETURN' AND c.STATUS <> 'CANCELED'
    LEFT JOIN liza.act_delivery_process adp ON adp.contract_number = c.contract_number

    WHERE c.contract_type = 'NEW'
      AND LOWER(SUBSTRING(c.product_code, 1, 4)) IN ('sams', 'xbox')
      AND c.agent_id = '11c49bb9-7d33-4b7b-841f-cd2321507dbe'
      --and c.status <> 'CANCELED'
ORDER BY c.id DESC;