import "org/killbill/billing/util/entity/dao/EntitySqlDao.sql.stg"

andCheckSoftDeletionWithComma(prefix) ::= "and <prefix>is_active"

tableName() ::= "custom_fields"

tableFields(prefix) ::= <<
  <prefix>object_id
, <prefix>object_type
, <prefix>is_active
, <prefix>field_name
, <prefix>field_value
, <prefix>created_by
, <prefix>created_date
, <prefix>updated_by
, <prefix>updated_date
>>

tableValues() ::= <<
  :objectId
, :objectType
, :isActive
, :fieldName
, :fieldValue
, :createdBy
, :createdDate
, :updatedBy
, :updatedDate
>>

historyTableName() ::= "custom_field_history"

markTagAsDeleted() ::= <<
update <tableName()>
set is_active = false
where <idField("")> = :id
<AND_CHECK_TENANT("")>
;
>>

updateValue() ::= <<
update <tableName()>
set field_value = :fieldValue
where <idField("")> = :id
<AND_CHECK_TENANT("")>
;
>>


getCustomFieldsForObject() ::= <<
select
<allTableFields("")>
from <tableName()>
where
object_id = :objectId
and object_type = :objectType
and is_active
<AND_CHECK_TENANT("")>
<defaultOrderBy("")>
;
>>

searchQuery(prefix) ::= <<
     <idField(prefix)> = :searchKey
  or <prefix>object_type like :likeSearchKey
  or <prefix>object_id like :likeSearchKey
  or <prefix>field_name like :likeSearchKey
  or <prefix>field_value like :likeSearchKey
>>

getSearchCountByObjectTypeAndFieldName() ::= <<
select
count(1) as count
from <tableName()>
where
object_type = :objectType
and field_name = :fieldName
<andCheckSoftDeletionWithComma("")>
<AND_CHECK_TENANT("")>
;
>>

searchByObjectTypeAndFieldName(ordering) ::= <<
select
<allTableFields()>
from <tableName()>
where
object_type = :objectType
and field_name = :fieldName
<andCheckSoftDeletionWithComma("")>
<AND_CHECK_TENANT("")>
order by <recordIdField("")> <ordering>
limit :rowCount offset :offset
;
>>

getSearchCountByObjectTypeAndFieldNameValue() ::= <<
select
count(1) as count
from <tableName()>
where
object_type = :objectType
and field_name = :fieldName
and field_value = :fieldValue
<andCheckSoftDeletionWithComma("")>
<AND_CHECK_TENANT("")>
;
>>

searchByObjectTypeAndFieldNameValue(ordering) ::= <<
select
<allTableFields()>
from <tableName()>
where
object_type = :objectType
and field_name = :fieldName
and field_value = :fieldValue
<andCheckSoftDeletionWithComma("")>
<AND_CHECK_TENANT("")>
order by <recordIdField("")> <ordering>
limit :rowCount offset :offset
;
>>



