exports.up = async (knex) => {
	await knex.schema.alterTable('random_campaign', (table) => {
		table.integer('id')
			.notNullable()
			.references('id')
			.inTable('campaigns');
	});

	await knex.raw(`
		CREATE OR REPLACE FUNCTION get_random_campaign(min_ratio decimal default 0, max_ratio decimal default 1000.0) RETURNS random_campaign AS $$
			SELECT * FROM (
				SELECT DISTINCT ON (CASE WHEN parent_id IS NOT NULL THEN parent_id ELSE entity_id END)
					banner_id, url, entity_id, affiliate_id, parent_id, id
				FROM (
					SELECT
						campaigns.*, entities.parent_id as parent_id
					FROM campaigns
					LEFT JOIN entities ON entities.id = campaigns.entity_id
					LEFT JOIN banners ON banners.id = campaigns.banner_id
					WHERE banner_id IS NOT NULL
					AND ratio >= min_ratio
					AND ratio <= max_ratio
					ORDER BY RANDOM()
				) random_campaigns
			) random_banners
			ORDER BY RANDOM()
			LIMIT 1;
		$$ LANGUAGE SQL STABLE;
	`);
};

exports.down = async (knex) => {
	await knex.schema.alterTable('random_campaign', (table) => {
		table.dropColumn('campaign_id');
	});

	await knex.raw(`
		CREATE OR REPLACE FUNCTION get_random_campaign(min_ratio decimal default 0, max_ratio decimal default 1000.0) RETURNS random_campaign AS $$
			SELECT * FROM (
				SELECT DISTINCT ON (CASE WHEN parent_id IS NOT NULL THEN parent_id ELSE entity_id END)
					banner_id, url, entity_id, affiliate_id, parent_id
				FROM (
					SELECT
						campaigns.*, entities.parent_id as parent_id
					FROM campaigns
					LEFT JOIN entities ON entities.id = campaigns.entity_id
					LEFT JOIN banners ON banners.id = campaigns.banner_id
					WHERE banner_id IS NOT NULL
					AND ratio >= min_ratio
					AND ratio <= max_ratio
					ORDER BY RANDOM()
				) random_campaigns
			) random_banners
			ORDER BY RANDOM()
			LIMIT 1;
		$$ LANGUAGE SQL STABLE;
	`);
};