1. SET NAMES utf8mb4;
2.  
3. INSERT INTO `export_metadata` (`name`, `description`, `file_headers`, `required_data`, `zip_file_name_format`, `csv_file_name_format`, `queries`, `need_to_use_external_query`, `created_at`, `updated_at`, `deleted_at`) VALUES
4. ('earn_credit_data',    'Exports earn credit data',     'name,email,credits,comment,created_at',        NULL,   'customer_details_##DATETIME##.zip',    'customer_details_##DATE##_##INDEX##.csv',      '{\"data_query_1\": \"SELECT customers.name, customers.email, customer_credits.credits, customer_credits.comment, customer_credits.created_at FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.rule_id IN :rule_id_value AND customer_credits.credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time ORDER BY customer_credits.id LIMIT :limit OFFSET :offset\", \"count_query_1\": \"select count(*) from customer_credits WHERE customer_credits.rule_id IN :rule_id_value AND customer_credits.credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',        0,      '2023-03-16 10:23:53',  '2023-03-16 10:23:53',  NULL),
5. ('spent_credit_data',   'Exports spent credit data',    'name,email,credits,comment,created_at',        NULL,   'customer_details_##DATETIME##.zip',    'customer_details_##DATE##_##INDEX##.csv',      '{\"data_query_1\": \"SELECT customers.name, customers.email, customer_credits.credits, customer_credits.comment, customer_credits.created_at FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.rule_id IN :rule_id_value AND customer_credits.credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time ORDER BY customer_credits.id LIMIT :limit OFFSET :offset\", \"count_query_1\": \"select count(*) from customer_credits WHERE customer_credits.rule_id IN :rule_id_value AND customer_credits.credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',        0,      '2023-03-16 10:23:53',  '2023-03-16 10:23:53',  NULL),
6. ('credits log with expiry date using specific comment', 'fetch name, email, expiry date and comments',  'name,email,credits,comment,expiry_date,created_at',    'user_id, append_created_at_in_file, timezone', 'credits_log_with_expiry_date_using_specific_comment_##DATETIME##.zip', 'credits_log_with_expiry_data_using_specific_comment_##DATE##_##INDEX##.csv',   '{\"data_query_1\": \"SELECT customers.name, customers.email, customers.credits, customer_credits.comment, customer_credits.expiry_date, customer_credits.created_at FROM customers JOIN customer_credits  ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.comment = :store_owner_comment AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.comment = :store_owner_comment AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',    0,      '2023-03-16 14:55:27',  '2023-03-16 18:33:45',  NULL),
7. ('customer_details',    'customer details',     'name, email, birthdate, phone, gender, created_at',    'user_id, append_created_at_in_file, timezone', 'customer_details_##DATETIME##.zip',    'customer_details_##DATE##_##INDEX##.csv',      '{\"data_query_1\": \"SELECT name, email, birthdate, phone, gender, created_at FROM customers WHERE customers.created_at >= :start_time AND customers.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers WHERE customers.created_at >= :start_time AND customers.created_at <= :end_time\"}',   0,      '2023-03-16 12:59:41',  '2023-03-16 18:33:45',  NULL),
8. ('customer credits balance',    'fetch credits balance',        'name, email, credits, created_at',     'user_id, append_created_at_in_file, timezone', 'customer_credits_balance_##DATETIME##.zip',    'customer_credits_balance_##DATE##_##INDEX##.csv',      '{\"data_query_1\": \"SELECT name, email, credits/100, created_at FROM customers WHERE customers.created_at >= :start_time AND customers.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers WHERE customers.created_at >= :start_time AND customers.created_at <= :end_time\"}',        0,      '2023-03-16 13:02:05',  '2023-03-16 18:33:45',  NULL),
9. ('customer inviter receiver details',   'fetch name, email and phone of inviters and receivers',        'inviter_name, inviter_email, inviter_phone, receiver_name, receiver_email, receiver_phone, created_at',        'user_id, append_created_at_in_file, timezone', 'customer_inviter_receiver_details_##DATETIME##.zip',   'customer_inviter_receiver_details_##DATE##_##INDEX##.csv',     '{\"data_query_1\": \"SELECT inviter.name, inviter.email, inviter.phone, receiver.name, receiver.email, receiver.phone, receiver.created_at FROM customers receiver LEFT JOIN customers inviter ON receiver.refer_by = inviter.id WHERE inviter.name IS NOT NULL AND receiver.created_at >= :start_time AND receiver.created_at <= :end_time ORDER BY inviter.name LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers receiver LEFT JOIN customers inviter ON receiver.refer_by = inviter.id WHERE inviter.name IS NOT NULL AND receiver.created_at >= :start_time AND receiver.created_at <= :end_time\"}',    0,      '2023-03-16 14:43:44',  '2023-03-16 18:33:45',  NULL),
10. ('Total referred count',        'fetch name, email and total referal count',    'inviter_name,inviter_email,referral_count,',   'user_id, append_created_at_in_file, timezone', 'total_referred_number_count_##DATETIME##.zip', 'total_referred_number_count_##DATE##_##INDEX##.csv',   '{\"data_query_1\": \"SELECT inviter.name, inviter.email, COUNT(receiver.refer_by) FROM customers receiver LEFT JOIN customers inviter ON receiver.refer_by = inviter.id WHERE receiver.refer_by > 0 GROUP BY inviter.name, inviter.email LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(DISTINCT receiver.refer_by) FROM customers receiver LEFT JOIN customers inviter ON receiver.refer_by = inviter.id WHERE receiver.refer_by > 0\"}',   0,      '2023-03-16 14:50:04',  '2023-03-16 18:33:45',  NULL),
11. ('credits log with specific comment',   'fetch name, email and comments',       'name, email, credits, comment, expiry_date, created_at',       'user_id, append_created_at_in_file, timezone', 'credits_log_with_specific_comment_##DATETIME##.zip',   'credits_log_with_specific_comment_##DATE##_##INDEX##.csv',     '{\"data_query_1\": \"SELECT customers.name, customers.email, customers.credits, customer_credits.comment, customer_credits.created_at FROM customers JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.comment = :store_owner_comment AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.comment = :store_owner_comment AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',   0,      '2023-03-16 14:55:27',  '2023-03-16 18:33:45',  NULL),
12. ('customer count based on earn credits',        'Fetch count of credits > 0',   'Total count of earn credits,', 'user_id, append_created_at_in_file, timezone', 'customer_details_with_earn_credits_##DATETIME##.zip',  'customer_count_based_on_earn_credits_##DATE##_##INDEX##.csv',  '{\"data_query_1\": \"SELECT COUNT(DISTINCT shopify_customer_id) FROM customer_credits WHERE credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT 1\"}',  0,      '2023-03-16 15:37:29',  '2023-03-16 18:33:45',  NULL),
13. ('customer count based on spent credits',       'Fetch count of credits < 0',   'Total count of spent credits,',        'user_id, append_created_at_in_file, timezone', 'customer_details_with_spent_credits_##DATETIME##.zip', 'customer_count_based_on_spent_credits_##DATE##_##INDEX##.csv', '{\"data_query_1\": \"SELECT COUNT(DISTINCT shopify_customer_id) FROM customer_credits WHERE credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\", \"count_query_1\": \"SELECT 1\"}',      0,      '2023-03-16 15:47:25',  '2023-03-16 18:33:45',  NULL),
14. ('earn credits sum',    'Fetch sum of credits > 0',     'Total sum of earn credits,',   'user_id, append_created_at_in_file, timezone', 'customer_details_with_earn_credits_##DATETIME##.zip',  'total_sum_based_on_earn_credits_##DATE##_##INDEX##.csv',       '{\"data_query_1\": \"SELECT SUM(credits/100) FROM customer_credits WHERE credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT 1\"}',     0,      '2023-03-16 15:50:23',  '2023-03-16 18:33:45',  NULL),
15. ('spent credits sum',   'Fetch sum of credits < 0',     'Total sum of spent credits,',  'user_id, append_created_at_in_file, timezone', 'customer_details_with_spent_credits_##DATETIME##.zip', 'total_sum_based_on_spent_credits_##DATE##_##INDEX##.csv',      '{\"data_query_1\": \"SELECT SUM(credits/100) FROM customer_credits WHERE credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT 1\"}',     0,      '2023-03-16 15:51:28',  '2023-03-16 18:33:45',  NULL),
16. ('customer details with earn credits',  'Fetch name, email, credits, comment, created_at',      'name, email, earn_credits, comment, created_at',       'user_id, append_created_at_in_file, timezone', 'customer_details_with_earn_credits_##DATETIME##.zip',  'customer_details_with_earn_credits_##DATE##_##INDEX##.csv',    '{\"data_query_1\": \"SELECT customers.name, customers.email, customers.phone, customer_credits.credits, customer_credits.comment, customer_credits.created_at FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.credits > 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',     0,      '2023-03-16 16:01:58',  '2023-03-16 18:33:45',  NULL),
17. ('customer details with spent credits', 'Fetch name, email, credits, comment, created_at',      'name, email, spent_credits, comment, created_at',      'user_id, append_created_at_in_file, timezone', 'customer_details_with_spent_credits_##DATETIME##.zip', 'customer_details_with_spent_credits_##DATE##_##INDEX##.csv',   '{\"data_query_1\": \"SELECT customers.name, customers.email, customers.phone, customer_credits.credits, customer_credits.comment, customer_credits.created_at FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time LIMIT :limit OFFSET :offset\", \"count_query_1\": \"SELECT COUNT(*) FROM customers INNER JOIN customer_credits ON customers.customer_id = customer_credits.shopify_customer_id WHERE customer_credits.credits < 0 AND customer_credits.created_at >= :start_time AND customer_credits.created_at <= :end_time\"}',     0,      '2023-03-16 16:03:13',  '2023-03-16 18:33:45',  NULL);