coins_history view

DROP VIEW IF EXISTS coins_history;

CREATE VIEW `coins_history` AS

SELECT
  t1.datum_request AS `date`,
  t1.user_details AS `user_id`,
  'charge' AS `scope`,
  t2.coins AS `coins`,
  t1.id
FROM
  cam_charge t1
  INNER JOIN cam_chargeamounts t2
    ON t2.id = t1.chargeamount

UNION SELECT
  t1.datum_request,
  t1.user_details,
  'charge bounce',
  - t2.coins,
  t1.id
FROM
  cam_charge t1
  INNER JOIN cam_chargeamounts t2
    ON t2.id = t1.chargeamount
WHERE
  t1.bounced = 1

UNION SELECT
  start,
  user_details,
  'visit-x chat',
  - (coins_per_minute * videotime / 60),
  id
FROM
  cam_call
WHERE
  videotime > 0
  AND bill = 1


UNION SELECT
  t2.start AS date,
  t1.user_details AS user_id,
  'visit-x private chat' AS scope,
  - (t2.duration / 60 * t2.coins_per_minute) AS coins,
  t2.id AS id

FROM
  cam_call t1
  INNER JOIN cam_private_call t2
    ON t2.paid_chat_id = t1.id

WHERE
  t2.duration > 0


UNION SELECT
  start,
  user_details,
  'imlive chat',
  - (videotime * coins_per_minute / 60),
  id
FROM
  tbl_imlive_chat
WHERE
  videotime > 0

UNION SELECT
  `date`,
  user_id,
  'video unlock',
  - coinsprice,
  id
FROM
  tbl_bought_video
WHERE
  coinsprice <> 0

UNION SELECT
  `date`,
  user_id,
  'gallery unlock',
  - coinsprice,
  id
FROM
  tbl_bought_galleries
WHERE
  coinsprice <> 0

UNION SELECT
  `date`,
  user_id,
  'camera mail',
  - coins,
  id
FROM
  cam_mails
WHERE
  coins <> 0

UNION SELECT
  t1.`date`,
  t1.user_id,
  'tips',
  - t1.coins,
  t1.id
FROM
  tbl_playdate_tips t1
  INNER JOIN tbl_playdate_poll_vote t2
    ON t2.tip_id = t1.id
  INNER JOIN tbl_playdate_poll_option t3
    ON t3.id = t2.option_id
WHERE
  t3.is_winner = 1
  AND t1.coins <> 0

UNION
SELECT
  purchase_date AS date,
  user_id,
  'playdate ticket',
  - coins,
  id
FROM
  tbl_playdate_ticket

UNION
SELECT
  bounced_date AS date,
  user_id,
  'playdate ticket bounce',
  coins,
  id
FROM
  tbl_playdate_ticket
WHERE
  bounced_date IS NOT NULL

UNION SELECT
  t1.`date`,
  t1.user_id,
  'playdate tips',
  - t1.coins,
  id
FROM
  tbl_playdate_tips t1
WHERE
  t1.entity_type <> 'ErstiesPlaydate\\Models\\Poll'
  AND t1.coins <> 0

;