X4RhQAnT

1-- Tournaments
2INSERT IGNORE INTO total_recall.tournament (`tournament_slug`, `category_slug`, `sport_slug`, `hash`)
3SELECT
4    tournaments.slug AS tournament_slug,
5    categories.slug AS category_slug,
6    sports.slug AS sport_slug,
7    SHA1(CONCAT(tournaments.slug, '|',categories.slug, '|', sports.slug)) AS hash
8FROM
9    tournaments
10        INNER JOIN
11    (categories
12    INNER JOIN sports ON categories.sport_id = sports.id) ON tournaments.category_id = categories.id;
13
14-- Create temporary table
15CREATE TEMPORARY TABLE IF NOT EXISTS
16    temporary_leginfo_data
17
18SELECT
19    bet,
20    user_id,
21    market,
22    selection,
23    sport,
24    sport_id,
25    team,
26    trainer,
27    jockey,
28    tournament_hash,
29    CASE
30        WHEN sport_id NOT IN (1 , 7) THEN SHA1(CONCAT(tournament_slug, '|', team, '|', market))
31        WHEN sport_id IN (1 , 7) AND trainer IS NOT NULL THEN SHA1(CONCAT(tournament_slug, '|', market, '|', trainer, '|', selection))
32        WHEN sport_id IN (1 , 7) AND jockey IS NOT NULL THEN SHA1(CONCAT(tournament_slug, '|', market, '|', jockey, '|', selection))
33        WHEN sport_id IN (1 , 7) AND jockey IS NOT NULL AND trainer IS NOT NULL THEN SHA1(CONCAT(tournament_slug, '|', market, '|', trainer, '|', jockey, '|', selection))
34        ELSE SHA1(CONCAT(tournament_slug, '|', market, '|', selection))
35    END AS hash
36
37FROM
38    (SELECT
39            s.id,
40            s.selection_type_id AS selection_type,
41            b.id AS bet,
42            b.customer_id AS user_id,
43            m.name AS market,
44            s.name AS selection,
45            sp.name AS sport,
46            sp.id AS sport_id,
47            t.slug AS tournament_slug,
48            MAX(CASE WHEN sei.attribute = 'trainer_name' THEN sei.value END) AS trainer,
49            MAX(CASE WHEN sei.attribute = 'jockey_name' THEN sei.value END) AS jockey,
50            SHA1(CONCAT(t.slug, '|', cat.slug, '|', sp.slug)) AS tournament_hash,
51            CASE
52                WHEN s.name SOUNDS LIKE t1.name OR s.selection_type_id = 1 THEN t1.name
53                WHEN s.name SOUNDS LIKE t2.name OR s.selection_type_id = 3 THEN t2.name
54            END AS team,
55            t1.name AS team1,
56            t2.name AS team2
57    FROM
58        (
59    bets AS b
60    INNER JOIN sportsbook.bet_data AS bd ON bd.bet_id = b.id
61    INNER JOIN sportsbook.markets AS m ON bd.market_id = m.id
62    INNER JOIN sportsbook.selections AS s ON bd.selection_id = s.id
63    INNER JOIN sportsbook.events AS e ON bd.event_id = e.id
64    INNER JOIN sportsbook.tournaments AS t ON e.tournament_id = t.id
65    INNER JOIN sportsbook.categories AS cat ON t.category_id = cat.id
66    INNER JOIN sportsbook.sports AS sp ON e.sport_id = sp.id)
67    LEFT JOIN sportsbook.selections_extra_info AS sei ON sei.selection_id = s.id
68    LEFT JOIN teams t1 ON t1.id = e.home_team_id
69    LEFT JOIN teams t2 ON t2.id = e.away_team_id
70    WHERE
71        (
72            (s.inplay AND e.event_type_id = 2 AND sp.slug = "golf") -- Allowed outright event
73            OR (s.inplay AND NOT (e.event_type_id = 2 AND sp.slug = "golf" AND b.settled_on IS NOT NULL)) -- I suppose that the last part is an indication that the bet has been settled
74            OR NOT s.inplay
75        )
76        AND b.placed_on > NOW() - INTERVAL 2 MONTH
77        AND (sp.id IN (1, 7) OR (sp.id NOT IN (1, 7) AND m.selection_schema IN (1, 25)))
78    GROUP BY b.id, s.id
79) AS a
80WHERE
81    (sport_id NOT IN (1, 7) AND team IS NOT NULL)
82    OR sport_id IN (1, 7)
83;
84
85-- Insert Leginfo
86INSERT INTO total_recall.leginfo (`tournament_id`, `team`, `market`, `selection`, `hash`)
87SELECT
88    t.id AS tournament_id,
89    tld.team AS team,
90    tld.market AS market,
91    tld.selection AS selection,
92    tld.hash AS hash
93FROM
94    temporary_leginfo_data AS tld INNER JOIN total_recall.tournament AS t ON tld.tournament_hash = t.hash
95WHERE
96    sport_id NOT IN (1, 7)
97ON DUPLICATE KEY UPDATE
98    popularity = popularity + 1
99;
100
101INSERT INTO total_recall.customer_leginfo (`user_id`, `leginfo_id`)
102SELECT
103    tld.user_id AS user_id,
104    l.id AS leginfo_id
105FROM
106    temporary_leginfo_data AS tld INNER JOIN total_recall.leginfo AS l ON tld.hash = l.hash
107ON DUPLICATE KEY UPDATE
108    total_recall.customer_leginfo.popularity = total_recall.customer_leginfo.popularity + 1
109;
110
111-- Insert racing leginfo
112INSERT IGNORE INTO total_recall.racing_leginfo (`tournament_id`, `trainer`, `jockey`, `market`, `selection`, `hash`)
113SELECT
114    t.id AS tournament_id,
115    tld.trainer AS trainer,
116    tld.jockey AS jockey,
117    tld.market AS market,
118    tld.selection AS selection,
119    tld.hash AS hash
120FROM
121    temporary_leginfo_data AS tld INNER JOIN total_recall.tournament AS t ON tld.tournament_hash = t.hash
122WHERE
123    sport_id IN (1, 7)
124ON DUPLICATE KEY UPDATE
125    popularity = popularity + 1
126;
127
128INSERT INTO total_recall.customer_racing_leginfo (`user_id`, `racing_leginfo_id`)
129SELECT
130    tld.user_id AS user_id,
131    l.id AS racing_leginfo_id
132FROM
133    temporary_leginfo_data AS tld INNER JOIN total_recall.racing_leginfo AS l ON tld.hash = l.hash
134ON DUPLICATE KEY UPDATE
135    total_recall.customer_racing_leginfo.popularity = total_recall.customer_racing_leginfo.popularity + 1
136;
137
138DROP TEMPORARY TABLE IF EXISTS temporary_leginfo_data;