SET NAMES utf8mb4;

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
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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);