<?xml version="1.0" encoding="UTF-8"?>
<dataConfig>
    <dataSource type="JdbcDataSource"
                driver="com.mysql.jdbc.Driver"
                url="jdbc:mysql://172.17.0.2:3306/kwucmf-clean"
                user="joshteam"
                password="joshteam"/>

    <document>
        <entity name="product"
                query="SELECT
                p.product_id,
                p.type_id,
                pt.name as `type`,
                p.slug,
                p.description,
                (SELECT GROUP_CONCAT(pk.name) FROM product_keyword pk WHERE pk.product_id=p.product_id) as `keywords`,
                p.image_url as thumbnail,
                DATE_FORMAT(p.created_at,'%Y-%m-%dT%TZ') as 'created_at',
                DATE_FORMAT(p.updated_at,'%Y-%m-%dT%TZ') as 'updated_at',
                p.likes_count,
                p.view_count,
                p.review_count,
                COALESCE((SELECT CASE WHEN membership_id THEN 1 ELSE 0 END FROM product_membership pm WHERE pm.product_id=p.product_id LIMIT 1),0) AS `private`,
                CASE WHEN p.creator_id=1 THEN 0 ELSE 1 END as `ugc`,
                u.id AS `creator_id`,
                u.first_name AS `creator_first_name`,
                u.last_name AS `creator_last_name`,
                (SELECT upa.value FROM user_profile_attribute upa WHERE upa.name='photo' and upa.user_id=p.`creator_id` ORDER BY updated_at DESC LIMIT 1) as `creator_thumbnail`,
                (SELECT group_concat(m.id) FROM membership m JOIN `product_membership` pm on pm.membership_id = m.id where pm.product_id=p.product_id and m.type IN (1,3)) AS `allowed_membership`,
                (SELECT group_concat(DISTINCT ms.market_center_id) FROM membership m JOIN `product_membership` pm on pm.membership_id = m.id join membership_subscription ms on ms.membership_id = m.id where pm.product_id=p.product_id and m.type=2) AS `allowed_market_center`,
                (SELECT group_concat(DISTINCT ms.region_id) FROM membership m JOIN `product_membership` pm on pm.membership_id = m.id join membership_subscription ms on ms.membership_id = m.id where pm.product_id=p.product_id and m.type=2) AS `allowed_region`,
                'META' as meta
                FROM product p
                JOIN product_type pt ON pt.type_id = p.type_id
                JOIN fos_user u ON u.id = p.creator_id">
        </entity>
    </document>

</dataConfig>