package pglex

import "testing"

func TestNormalize(t *testing.T) {
	tests := []struct {
		sql  string
		norm string
	}{
		{
			sql:  `SELECT 1 FROM rel WHERE col IN ('val', 2, ?, $3)`,
			norm: `SELECT ? FROM "rel" WHERE "col" IN (?, ?, ?, ?)`,
		},
		{
			sql:  `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*/`,
			norm: `SELECT "tickets".* FROM "tickets" WHERE "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 "ticket_products"."ticket_product_owner_id" IN (?, ?, ?, ?))) 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 ?`,
		},
		{
			sql:  `UPDATE tmi_user_properties SET kind = 'string', val = $1 WHERE user_id = $2  AND name = $3`,
			norm: `UPDATE "tmi_user_properties" SET "kind" = ?, "val" = ? WHERE "user_id" = ? AND "name" = ?`,
		},
		{
			sql:  `SELECT 1 >= 2, 2 <= 1`,
			norm: `SELECT ? >= ?, ? <= ?`,
		},
		{
			sql:  `SELECT 1 <> 2, 1 != 2`,
			norm: `SELECT ? != ?, ? != ?`,
		},
		{ // XXX AGB: No such operator in postgres. 2015-08-04
			sql:  `SELECT 1 <=> NULL`,
			norm: `SELECT ? <=> NULL`,
		},
		{
			sql:  `SELECT 1 WHERE col ILIKE 'how do you%'`,
			norm: `SELECT ? WHERE "col" ILIKE ?`,
		},
		{
			sql:  `SELECT cons.conkey[1] FROM pg_constraint WHERE cons.conrelid = 1000`,
			norm: `SELECT "cons"."conkey"[?] FROM "pg_constraint" WHERE "cons"."conrelid" = ?`,
		},
		{
			sql:  `SELECT relname FROM pg_class WHERE relkind <> 'i' AND nspname !~ '^pg_toast'`,
			norm: `SELECT "relname" FROM "pg_class" WHERE "relkind" != ? AND "nspname" !~ ?`,
		},
		{
			sql: `SELECT
					vods.id,
					GREATEST(vods.started_on::timestamp + COALESCE(user_vod_properties.vod_storage_days::integer, 14) * interval '1 day', vods.delete_at::timestamp)
						AT TIME ZONE 'UTC' as adjusted_expires,
					'2015-07-24 00:05:06 UTC'::timestamp as start_time,
					'2015-07-24 00:10:06 UTC'::timestamp as end_time
				FROM vods

				LEFT JOIN user_vod_properties ON vods.owner_id = user_vod_properties.user_id
				LEFT JOIN vod_appeals ON (vods.id = vod_appeals.vod_id)

				WHERE vods.broadcast_type = 'archive'
					AND user_vod_properties.save_vods_forever IS NOT TRUE
					AND vod_appeals.vod_id IS NULL
					AND vods.id > 7865018
				ORDER BY vods.id
				LIMIT 1000
 				/*application:Twitch*/ /* Trace-Id=466b933431551aa52934f2f2dc029a31 Trace-Span= */`,
			norm: `SELECT "vods"."id", "GREATEST" ("vods"."started_on"::"timestamp" + "COALESCE" ("user_vod_properties"."vod_storage_days"::"integer", ?)* "interval" ?, "vods"."delete_at"::"timestamp") AT TIME ZONE ? AS "adjusted_expires", ?::"timestamp" AS "start_time", ?::"timestamp" AS "end_time" FROM "vods" LEFT JOIN "user_vod_properties" ON "vods"."owner_id" = "user_vod_properties"."user_id" LEFT JOIN "vod_appeals" ON ("vods"."id" = "vod_appeals"."vod_id") WHERE "vods"."broadcast_type" = ? AND "user_vod_properties"."save_vods_forever" IS NOT TRUE AND "vod_appeals"."vod_id" IS NULL AND "vods"."id" > ? ORDER BY "vods"."id" LIMIT ?`,
		},
		{
			sql:  `SELECT pg_stat_activity.* FROM pg_stat_activity WHERE ( now() - state_change ) > '1/1/2000'`,
			norm: `SELECT "pg_stat_activity".* FROM "pg_stat_activity" WHERE ("now" ()- "state_change")> ?`,
		},
	}

	for _, tt := range tests {
		have := Normalize(tt.sql)
		if have != tt.norm {
			t.Errorf("Normalize(%q) = \n%q; want \n%q", tt.sql, have, tt.norm)
		}
	}
}

func BenchmarkNormalize(b *testing.B) {
	sql := `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*/`
	for i := 0; i < b.N; i++ {
		Normalize(sql)
	}
}

func BenchmarkTrivialNormalize(b *testing.B) {
	sql := `SELECT 1`
	for i := 0; i < b.N; i++ {
		Normalize(sql)
	}
}
