package pgtables

import (
	"reflect"
	"sort"
	"testing"

	"code.justin.tv/release/trace/pgparse"
)

func TestTables(t *testing.T) {
	tests := [...]struct {
		query  string
		tables []string
	}{
		{query: `SELECT  "purchase_payments"."id" AS t0_r0, "purchase_payments"."purchase_profile_id" AS t0_r1, "purchase_payments"."ext_transaction_id" AS t0_r2, "purchase_payments"."initial_transaction_id" AS t0_r3, "purchase_payments"."state" AS t0_r4, "purchase_payments"."payment_provider" AS t0_r5, "purchase_payments"."gross_amount" AS t0_r6, "purchase_payments"."est_fees" AS t0_r7, "purchase_payments"."details" AS t0_r8, "purchase_payments"."created_on" AS t0_r9, "purchase_payments"."updated_on" AS t0_r10, "purchase_payments"."received_on" AS t0_r11, "purchase_payments"."recognized_on" AS t0_r12, "ticket_products"."id" AS t1_r0, "ticket_products"."ticket_type" AS t1_r1, "ticket_products"."is_template" AS t1_r2, "ticket_products"."name" AS t1_r3, "ticket_products"."ticket_product_owner_type" AS t1_r4, "ticket_products"."ticket_product_owner_id" AS t1_r5, "ticket_products"."availability_state" AS t1_r6, "ticket_products"."permissions" AS t1_r7, "ticket_products"."desc_invoice" AS t1_r8, "ticket_products"."desc_paywall" AS t1_r9, "ticket_products"."desc_checkout" AS t1_r10, "ticket_products"."features" AS t1_r11, "ticket_products"."default_price" AS t1_r12, "ticket_products"."prices" AS t1_r13, "ticket_products"."interval_unit" AS t1_r14, "ticket_products"."interval_number" AS t1_r15, "ticket_products"."rev_split_steps" AS t1_r16, "ticket_products"."updated_on" AS t1_r17, "ticket_products"."created_on" AS t1_r18, "ticket_products"."hidden" AS t1_r19, "ticket_products"."ext_product_id" AS t1_r20, "ticket_products"."short_name" AS t1_r21, "ticket_products"."hide_ads" AS t1_r22, "ticket_products"."ticket_access_start" AS t1_r23, "ticket_products"."ticket_access_end" AS t1_r24, "ticket_products"."rev_share" AS t1_r25, "ticket_products"."recurring" AS t1_r26 FROM "purchase_payments" INNER JOIN "purchase_profiles" ON "purchase_profiles"."id" = "purchase_payments"."purchase_profile_id" INNER JOIN "tickets" ON "tickets"."id" = "purchase_profiles"."purchasable_id" INNER JOIN "ticket_products" ON "ticket_products"."id" = "tickets"."ticket_product_id" WHERE "ticket_products"."id" IN (1950) AND ("purchase_payments"."recognized_on" >= '2014-10-01' AND "purchase_payments"."recognized_on" < '2014-10-31') AND (NOT ("purchase_payments"."state" IN ('test'))) AND ("purchase_payments"."purchase_profile_id" IS NOT NULL) ORDER BY "purchase_payments"."id" ASC LIMIT 1000 /*application:Twitch*/ /* Trace-Id=c01d6839aeaaa5f60583cc46bd10d7c5 Trace-Span= */`,
			tables: []string{"purchase_payments", "purchase_profiles", "ticket_products", "tickets"}},
		{query: `SELECT  "tickets".* FROM "tickets" INNER JOIN "ticket_products" ON "ticket_products"."id" = "tickets"."ticket_product_id" WHERE "tickets"."owner_id" = 43296162 AND "ticket_products"."ticket_type" = 'turbo' AND (NOT (("tickets"."access_end" IS NOT NULL AND "tickets"."access_end" < '2014-12-04 00:59:09.543281' OR "tickets"."access_start" IS NOT NULL AND "tickets"."access_start" > '2014-12-04 00:59:09.543325'))) ORDER BY created_on LIMIT 1 /*application:Twitch,controller:viewer,action:info*/`,
			tables: []string{"ticket_products", "tickets"}},
		{query: `SELECT  "favorites".* FROM "favorites" INNER JOIN "users" ON "users"."id" = "favorites"."to_user_id" WHERE "favorites"."to_user_id" = 43362782 AND (users.category = 'gaming') AND (hidden is distinct from true) ORDER BY favorites.created_on DESC LIMIT 100 OFFSET 0 /*application:Twitch,controller:channels,action:follows*/`,
			tables: []string{"favorites", "users"}},
		{query: `SELECT  "tickets".* FROM "tickets"  WHERE "tickets"."ticket_product_id" IN (SELECT "ticket_products"."id" FROM "ticket_products"  WHERE (("ticket_products"."ticket_product_owner_type" = 'User' AND "ticket_products"."ticket_product_owner_id" = 14342567 OR "ticket_products"."ticket_product_owner_type" = 'Team' AND "ticket_products"."ticket_product_owner_id" IN (535, 1073, 1155, 1116))) ORDER BY default_price, created_on) AND (NOT (("tickets"."access_end" IS NOT NULL AND "tickets"."access_end" < '2014-12-04 00:59:09.610134' OR "tickets"."access_start" IS NOT NULL AND "tickets"."access_start" > '2014-12-04 00:59:09.610147'))) ORDER BY created_on DESC LIMIT 25 OFFSET 0 /*application:Twitch,controller:subscriptions,action:index*/`,
			tables: []string{"ticket_products", "tickets"}},
		{query: `SELECT  "messages".* FROM "messages"  WHERE "messages"."user_id" = 36114941 AND "messages"."safe" = 'f' AND (user_id = to_user_id) ORDER BY created_on DESC LIMIT 10 OFFSET 0 /*application:Twitch,controller:message,action:list*/`,
			tables: []string{"messages"}},
		{query: `SELECT  "message_blocks".* FROM "message_blocks"  WHERE "message_blocks"."user_id" = 53032055 ORDER BY updated_on DESC NULLS LAST LIMIT 25 OFFSET 0 /*application:Twitch,controller:blocks,action:index*/`,
			tables: []string{"message_blocks"}},
		{query: `SELECT "users".* FROM "users"  WHERE (lower(email) = 'redacted@gmail.com') /*application:Twitch,controller:follows,action:update*/`,
			tables: []string{"users"}},
		{query: `SELECT  "oauth2_clients".* FROM "oauth2_clients"  WHERE "oauth2_clients"."client_id" = 'redacted' AND (hidden IS false) LIMIT 1 /*application:Twitch,controller:clients,action:video_status*/`,
			tables: []string{"oauth2_clients"}},
		{query: `SELECT featured_events.* FROM "featured_events" join events ce on ce.id = event_id WHERE (now() at time zone 'utc' BETWEEN ce.start_time AND (ce.start_time + ce.length * '1 second'::interval)) AND (promotional_text IS NOT NULL and promotional_image IS NOT NULL) AND (COALESCE(language, '') = '' OR 'es' = ANY(regexp_split_to_array(language, ' *, *'))) ORDER BY featured_events.position ASC, random() /*application:Twitch,controller:streams,action:featured*/`,
			tables: []string{"events", "featured_events"}},
		// {query: `SELECT featured_events.* FROM "featured_events" join events ce on ce.id = event_id WHERE (now() at time zone 'utc' BETWEEN ce.start_time AND (ce.start_time + ce.length * '1 second'::interval)) AND (xarth) AND (promotional_text IS NOT NULL and promotional_image IS NOT NULL) AND (COALESCE(language, '') = '' OR 'es' = ANY(regexp_split_to_array(language, ' *, *'))) ORDER BY featured_events.position ASC, random() /*application:Twitch,controller:streams,action:featured*/`,
		// 	tables: []string{}},
		{query: `SELECT COUNT(*) FROM "messages"  WHERE "messages"."user_id" = 7501036 AND (user_id = to_user_id) AND (safe is null or safe = true) /*application:Twitch,controller:message,action:list*/`,
			tables: []string{"messages"}},
		{query: `SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "channel_audits"  WHERE "channel_audits"."channel_id" = 43404079 AND "channel_audits"."action" IN ('commercial', 'cut', 'status_change', 'game_change', 'add_editor', 'remove_editor') AND (channel_audits.id < 78523808) LIMIT 10) "subquery_for_count"  /*application:Twitch,controller:dashboards,action:action_log*/`,
			tables: []string{"channel_audits"}},

		{query: `SELECT COUNT(*) FROM "message_blocks" WHERE "message_blocks"."user_id" = ? AND "message_blocks"."blocked_user_id" = ?`,
			tables: []string{"message_blocks"}},
		{query: `SELECT "teams".* FROM "teams" INNER JOIN "team_users" ON "teams"."id" = "team_users"."team_id" WHERE "team_users"."user_id" = ?`,
			tables: []string{"team_users", "teams"}},
		{query: `SELECT "favorites".* FROM "favorites" WHERE "favorites"."from_user_id" = ?`,
			tables: []string{"favorites"}},
		{query: `SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?`,
			tables: []string{"users"}},
		{query: `SELECT "arb_keys".* FROM "arb_keys" WHERE "arb_keys"."k" = ? LIMIT ?`,
			tables: []string{"arb_keys"}},
		{query: `SELECT "ticket_products".* FROM "ticket_products" WHERE "ticket_products"."is_template" = ? AND "ticket_products"."availability_state" = ? AND (("ticket_products"."ticket_product_owner_type" = ? AND "ticket_products"."ticket_product_owner_id" = ? OR "ticket_products"."ticket_product_owner_type" = ? AND ?=?)) ORDER BY default_price, created_on`,
			tables: []string{"ticket_products"}},
		{query: `SELECT "tickets".* FROM "tickets" WHERE "tickets"."owner_id" = ? AND "tickets"."ticket_product_id" IN (SELECT "ticket_products"."id" FROM "ticket_products" WHERE (("ticket_products"."ticket_product_owner_type" = ? AND "ticket_products"."ticket_product_owner_id" = ? OR "ticket_products"."ticket_product_owner_type" = ? AND ?=?)) ORDER BY default_price, created_on) AND (NOT (("tickets"."access_end" IS NOT NULL AND "tickets"."access_end" < ? OR "tickets"."access_start" IS NOT NULL AND "tickets"."access_start" > ?))) ORDER BY created_on DESC LIMIT ? OFFSET ?`,
			tables: []string{"ticket_products", "tickets"}},
		// {query: ``,
		// 	tables: []string{}},

		{query: `SELECT id, extract(hour from created_on) FROM tickets WHERE tickets.owner_id = ?`,
			tables: []string{"tickets"}},
		{query: `INSERT INTO "favorites" ("block_notifications", "created_on", "from_user_id", "hidden", "to_user_id") VALUES (?, ?, ?, ?, ?) RETURNING "id"`,
			tables: []string{"favorites"}},
		{query: `UPDATE "users" SET "views_count" = ?, "updated_on" = ? WHERE "users"."id" = ?`,
			tables: []string{"users"}},
		{query: `DELETE FROM "favorites" WHERE "favorites"."id" = ?`,
			tables: []string{"favorites"}},
	}

	for _, tt := range tests {
		stmt, err := pgparse.Parse(tt.query)
		if err != nil {
			t.Logf("query=%q", tt.query)
			t.Errorf("parse: %v", err)
			continue
		}
		tr := newStatementTracker()
		tr.loadStmt(stmt)
		if err := tr.Err(); err != nil {
			t.Logf("query=%q", tt.query)
			t.Errorf("tr.loadStmt(); err == %q", err)
			continue
		}
		sort.Strings(tt.tables)
		if have, want := tr.Tables(), tt.tables; !reflect.DeepEqual(have, want) {
			t.Logf("query=%q", tt.query)
			t.Errorf("tr.Tables(); %q != %q", have, want)
			continue
		}
	}

}

func BenchmarkTables(b *testing.B) {
	sql := `SELECT "tickets".* FROM "tickets" WHERE "tickets"."owner_id" = ? AND "tickets"."ticket_product_id" IN (SELECT "ticket_products"."id" FROM "ticket_products" WHERE (("ticket_products"."ticket_product_owner_type" = ? AND "ticket_products"."ticket_product_owner_id" = ? OR "ticket_products"."ticket_product_owner_type" = ? AND ?=?)) ORDER BY default_price, created_on) AND (NOT (("tickets"."access_end" IS NOT NULL AND "tickets"."access_end" < ? OR "tickets"."access_start" IS NOT NULL AND "tickets"."access_start" > ?))) ORDER BY created_on DESC LIMIT ? OFFSET ?`

	for i := 0; i < b.N; i++ {
		stmt, err := pgparse.Parse(sql)
		if err != nil {
			b.Fatalf("parse: %v", err)
		}
		tr := newStatementTracker()
		tr.loadStmt(stmt)
		if err := tr.Err(); err != nil {
			b.Fatalf("load: %v", err)
		}
	}

}
