-- Double_Yield
-- Author: KCucumber
-- DateCreated: 12/20/2017 9:15:25 PM
--------------------------------------------------------------
INSERT OR IGNORE INTO StartBiasFeatures (CivilizationType, FeatureType, Tier)
SELECT ('CIVILIZATION_CV_SPAIN'), Features.FeatureType, 1
FROM Features WHERE Features.NaturalWonder = 1;

--------------------------------------------------------------
DROP TABLE IF EXISTS TempNW;
CREATE TABLE TempNW
    (	
	 FeatureType TEXT,
     YieldType TEXT,
     YieldChange INT,
	 YC INT 
    ); 

INSERT INTO TempNW (FeatureType,YieldType,YieldChange,YC)
SELECT f.FeatureType ,
       CASE 
	   WHEN fy.FeatureType IS NOT NULL THEN fy.YieldType ELSE  fa.YieldType END,
	   CASE 
	   WHEN fy.FeatureType IS NOT NULL THEN fy.YieldChange ELSE  fa.YieldChange END,
	   CASE 
	   WHEN fy.FeatureType IS NOT NULL THEN 1 ELSE 0 END
FROM Features AS f 
LEFT JOIN Feature_YieldChanges AS fy ON f.FeatureType = fy.FeatureType
LEFT JOIN Feature_AdjacentYields AS fa ON f.FeatureType = fa.FeatureType
WHERE f.NaturalWonder = 1 AND (fy.FeatureType IS NOT NULL OR fa.FeatureType IS NOT NULL);

INSERT OR IGNORE INTO RequirementSets (RequirementSetId ,RequirementSetType)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS' ELSE 'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS' END,
		'REQUIREMENTSET_TEST_ALL'
FROM TempNW AS f;

INSERT OR IGNORE INTO RequirementSetRequirements (RequirementSetId ,RequirementId)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS' ELSE 'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS' END,
		CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType ELSE 'CV_PLOT_IS_' || f.FeatureType END
FROM TempNW AS f;

INSERT OR IGNORE INTO Requirements (RequirementId ,RequirementType)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType ELSE 'CV_PLOT_IS_' || f.FeatureType END,
		CASE WHEN f.YC = 0 THEN 'REQUIREMENT_PLOT_ADJACENT_FEATURE_TYPE_MATCHES' ELSE 'REQUIREMENT_PLOT_FEATURE_TYPE_MATCHES' END
FROM TempNW AS f;

INSERT OR IGNORE INTO RequirementArguments (RequirementId ,Name, Value)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType ELSE 'CV_PLOT_IS_' || f.FeatureType END,
		'FeatureType',
		f.FeatureType
FROM TempNW AS f;

INSERT INTO TraitModifiers (ModifierId ,TraitType)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType END,
		'TRAIT_LEADER_SEVEN_CITIES_OF_GOLD'
FROM TempNW AS f;

INSERT INTO Modifiers (ModifierId ,ModifierType)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType END,
		'MODIFIER_PLAYER_CITIES_ATTACH_MODIFIER'
FROM TempNW AS f;

INSERT INTO Modifiers (ModifierId ,ModifierType ,SubjectRequirementSetId)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" END,
		'MODIFIER_CITY_PLOT_YIELDS_ADJUST_PLOT_YIELD',
		CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS' ELSE 'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS' END
FROM TempNW AS f;

INSERT INTO ModifierArguments (ModifierId ,Name ,Value)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType END,
		'ModifierId',
		CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" END
FROM TempNW AS f;

INSERT INTO ModifierArguments (ModifierId ,Name ,Value)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" END,
		'YieldType',
		f.YieldType
FROM TempNW AS f;

INSERT INTO ModifierArguments (ModifierId ,Name ,Value)
SELECT DISTINCT CASE WHEN f.YC = 0 THEN 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" ELSE 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER" END,
		'Amount',
		f.YieldChange
FROM TempNW AS f;

DELETE FROM TempNW;
--------------------------------------------------------------
CREATE TRIGGER NaturalWonderOnAdjacentYields
AFTER INSERT ON Feature_AdjacentYields
BEGIN
	INSERT INTO TempNW (FeatureType,YieldType,YieldChange,YC)
	SELECT f.FeatureType ,fa.YieldType ,fa.YieldChange ,0
	FROM Features AS f 
	LEFT JOIN Feature_AdjacentYields AS fa ON f.FeatureType = fa.FeatureType
	WHERE f.NaturalWonder = 1 AND fa.FeatureType = NEW.FeatureType AND fa.YieldType = NEW.YieldType;
	
	INSERT OR IGNORE INTO RequirementSets (RequirementSetId ,RequirementSetType)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS',
			'REQUIREMENTSET_TEST_ALL'
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO RequirementSetRequirements (RequirementSetId ,RequirementId)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS',
			'CV_ADJACENT_TO_' || f.FeatureType
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO Requirements (RequirementId ,RequirementType)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType, 
			'REQUIREMENT_PLOT_ADJACENT_FEATURE_TYPE_MATCHES'
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO RequirementArguments (RequirementId ,Name, Value)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType, 
			'FeatureType', 
			f.FeatureType
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO TraitModifiers (ModifierId ,TraitType)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType, 
			'TRAIT_LEADER_SEVEN_CITIES_OF_GOLD'
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO Modifiers (ModifierId ,ModifierType)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType, 
			'MODIFIER_PLAYER_CITIES_ATTACH_MODIFIER'
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO Modifiers (ModifierId ,ModifierType ,SubjectRequirementSetId)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER", 
			'MODIFIER_CITY_PLOT_YIELDS_ADJUST_PLOT_YIELD', 
			'CV_ADJACENT_TO_' || f.FeatureType || '_REQUIREMENTS'
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType, 
			'ModifierId', 
			'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER"
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER", 
			'YieldType', 
			f.YieldType
	FROM TempNW AS f WHERE f.YC = 0;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_ADJACENT_TO_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER", 
			'Amount', 
			f.YieldChange
	FROM TempNW AS f WHERE f.YC = 0;
	
	DELETE FROM TempNW;
END;

--------------------------------------------------------------
CREATE TRIGGER NaturalWonderOnYieldChanges
AFTER INSERT ON Feature_YieldChanges
BEGIN
	INSERT INTO TempNW (FeatureType,YieldType,YieldChange,YC)
	SELECT f.FeatureType ,fy.YieldType ,fy.YieldChange ,1
	FROM Features AS f 
	LEFT JOIN Feature_YieldChanges AS fy ON f.FeatureType = fy.FeatureType
	WHERE f.NaturalWonder = 1 AND fy.FeatureType = NEW.FeatureType AND fy.YieldType = NEW.YieldType;
	
	INSERT OR IGNORE INTO RequirementSets (RequirementSetId ,RequirementSetType)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS',
			'REQUIREMENTSET_TEST_ALL'
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO RequirementSetRequirements (RequirementSetId ,RequirementId)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS',
			'CV_PLOT_IS_' || f.FeatureType
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO Requirements (RequirementId ,RequirementType)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType,
			'REQUIREMENT_PLOT_FEATURE_TYPE_MATCHES'
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO RequirementArguments (RequirementId ,Name, Value)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType,
			'FeatureType',
			f.FeatureType
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO TraitModifiers (ModifierId ,TraitType)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType,
			'TRAIT_LEADER_SEVEN_CITIES_OF_GOLD'
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO Modifiers (ModifierId ,ModifierType)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType,
			'MODIFIER_PLAYER_CITIES_ATTACH_MODIFIER'
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO Modifiers (ModifierId ,ModifierType ,SubjectRequirementSetId)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER",
			'MODIFIER_CITY_PLOT_YIELDS_ADJUST_PLOT_YIELD',
			'CV_PLOT_IS_' || f.FeatureType || '_REQUIREMENTS'
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType,
			'ModifierId',
			'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER"
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER",
			'YieldType',
			f.YieldType
	FROM TempNW AS f WHERE f.YC = 1;
	
	INSERT OR IGNORE INTO ModifierArguments (ModifierId ,Name ,Value)
	SELECT DISTINCT 'CV_PLOT_IS_' || f.FeatureType || "_" || f.YieldType || "_MODIFIER",
			'Amount',
			f.YieldChange
	FROM TempNW AS f WHERE f.YC = 1;
	
	DELETE FROM TempNW;
END;