022-create-countries-table.sql
1 -- Migration 022: Create countries table for multi-country pricing and configuration 2 -- Created: 2024-02-06 3 -- 4 -- This table centralizes country-specific configuration including: 5 -- - PPP-adjusted pricing with psychological pricing (97/47 endings) 6 -- - Currency and localization settings 7 -- - Market tier classifications 8 -- - GDPR compliance flags 9 -- 10 -- Pricing strategy: PPP-adjusted based on World Bank GDP per capita data (2023) 11 -- Base price: $297 USD (United States) 12 13 -- Drop table if it exists (for development/testing) 14 -- In production, use ALTER TABLE for schema changes 15 DROP TABLE IF EXISTS countries; 16 17 -- Create countries table 18 CREATE TABLE IF NOT EXISTS countries ( 19 -- Primary key 20 country_code TEXT PRIMARY KEY, -- ISO 3166-1 alpha-2 (e.g., 'US', 'AU', 'UK') 21 22 -- Basic info 23 country_name TEXT NOT NULL, 24 google_domain TEXT NOT NULL, -- For SERP scraping (e.g., 'google.com.au') 25 language_code TEXT NOT NULL, -- ISO 639-1 (e.g., 'en', 'es', 'de') 26 timezone TEXT NOT NULL, -- IANA timezone (e.g., 'America/New_York') 27 28 -- Currency and localization 29 currency_code TEXT NOT NULL, -- ISO 4217 (e.g., 'USD', 'EUR', 'AUD') 30 currency_symbol TEXT NOT NULL, -- Display symbol (e.g., '$', '€', '£') 31 date_format TEXT NOT NULL, -- Format pattern (e.g., 'DD/MM/YYYY') 32 33 -- Pricing (USD equivalent for comparison) 34 price_usd INTEGER NOT NULL, -- Price in USD cents (e.g., 29700 = $297) 35 pricing_tier TEXT NOT NULL CHECK(pricing_tier IN ('Premium+', 'Premium', 'Standard', 'Moderate', 'Emerging', 'Developing')), 36 37 -- Economic indicators 38 ppp_gdp_per_capita INTEGER, -- PPP GDP per capita (international dollars) 39 total_gdp_trillions REAL, -- Total GDP in trillions USD 40 41 -- Market classification 42 market_notes TEXT, -- Notes about market characteristics 43 is_price_sensitive BOOLEAN DEFAULT 0, -- Whether market is price-sensitive 44 is_premium_market BOOLEAN DEFAULT 0, -- Whether to emphasize premium positioning 45 46 -- Compliance and regulations 47 requires_gdpr_check BOOLEAN DEFAULT 0, -- EU/EEA countries requiring GDPR compliance 48 phone_format TEXT, -- E.164 format prefix (e.g., '+61', '+1') 49 mobile_pattern TEXT, -- Regex pattern to identify mobile numbers 50 51 -- Browser and localization settings 52 accept_language TEXT, -- Accept-Language header for HTTP requests (e.g., 'en-US,en;q=0.9') 53 54 -- GDPR-specific fields (for EU countries) 55 company_types TEXT, -- JSON array of common company types (e.g., ['GmbH', 'AG']) 56 company_keywords TEXT, -- JSON array of company registration keywords 57 key_page_names TEXT, -- JSON array of common page names (contact, about, legal) 58 59 -- SEO and keyword data 60 common_cities TEXT, -- JSON array of major cities for keyword generation 61 62 -- Status and metadata 63 is_active BOOLEAN DEFAULT 1, -- Whether country is actively supported 64 created_at TEXT DEFAULT CURRENT_TIMESTAMP, 65 updated_at TEXT DEFAULT CURRENT_TIMESTAMP, 66 67 -- Pricing override tracking 68 price_overridden BOOLEAN DEFAULT 0, -- Whether price was manually overridden 69 override_reason TEXT, -- Reason for price override 70 override_date TEXT -- When price was overridden 71 ); 72 73 -- Create indexes 74 CREATE INDEX IF NOT EXISTS idx_countries_active ON countries(is_active); 75 CREATE INDEX IF NOT EXISTS idx_countries_tier ON countries(pricing_tier); 76 CREATE INDEX IF NOT EXISTS idx_countries_currency ON countries(currency_code); 77 CREATE INDEX IF NOT EXISTS idx_countries_gdpr ON countries(requires_gdpr_check); 78 79 -- Insert all 25 countries with PPP-adjusted pricing 80 -- Prices use psychological pricing (97 or 47 endings) 81 INSERT INTO countries ( 82 country_code, country_name, google_domain, language_code, timezone, 83 currency_code, currency_symbol, date_format, 84 price_usd, pricing_tier, ppp_gdp_per_capita, total_gdp_trillions, 85 market_notes, is_price_sensitive, is_premium_market, requires_gdpr_check, phone_format, 86 mobile_pattern, accept_language, company_types, company_keywords, key_page_names, common_cities 87 ) VALUES 88 -- Premium+ Tier ($397) 89 ('SG', 'Singapore', 'google.com.sg', 'en', 'Asia/Singapore', 'SGD', 'S$', 'DD/MM/YYYY', 39700, 'Premium+', 111247, 0.515, 'High-income city-state, strong purchasing power', 0, 1, 0, '+65', '^(\+65|65)?[89]\d{7}$', 'en-SG,en;q=0.9', NULL, NULL, NULL, '["singapore","jurong west","woodlands","tampines","bedok"]'), 90 ('IE', 'Ireland', 'google.ie', 'en', 'Europe/Dublin', 'EUR', '€', 'DD/MM/YYYY', 39700, 'Premium+', 109205, 0.545, 'High GDP per capita (corporate tax effect)', 0, 1, 1, '+353', '^(\+353|0)8[356789]\d{7}$', 'en-IE,en;q=0.9', '["Ltd","Limited","PLC","Teoranta","Teo"]', '["Company number","CRO number","VAT number","Registered office"]', '["contact","about","legal","privacy policy"]', '["dublin","cork","limerick","galway","waterford","drogheda","dundalk","swords","bray","navan"]'), 91 92 -- Premium Tier ($297) 93 ('US', 'United States', 'google.com', 'en', 'America/New_York', 'USD', '$', 'MM/DD/YYYY', 29700, 'Premium', 80412, 28.78, 'Baseline market, largest economy', 0, 1, 0, '+1', '^(\+1|1)?[2-9]\d{9}$', 'en-US,en;q=0.9', NULL, NULL, NULL, '["new york","los angeles","chicago","houston","phoenix","philadelphia","san antonio","san diego","dallas","austin"]'), 94 ('NO', 'Norway', 'google.no', 'en', 'Europe/Oslo', 'NOK', 'kr', 'DD.MM.YYYY', 29700, 'Premium', 77808, 0.546, 'High purchasing power, oil-rich economy', 0, 1, 1, '+47', '^(\+47|47)?[49]\d{7}$', 'no-NO,en;q=0.8', '["AS","ASA","BA","ANS"]', '["Organisasjonsnummer","MVA-nummer","Forretningsadresse"]', '["kontakt","om oss","juridisk informasjon","personvern"]', '["oslo","bergen","stavanger","trondheim","drammen","fredrikstad","kristiansand","sandnes","tromso","sarpsborg"]'), 95 ('CH', 'Switzerland', 'google.ch', 'de', 'Europe/Zurich', 'CHF', 'CHF', 'DD.MM.YYYY', 29700, 'Premium', 76493, 0.938, 'High cost of living, strong economy', 0, 1, 0, '+41', '^(\+41|0)7[6-9]\d{7}$', 'de-CH,fr-CH;q=0.9,it-CH;q=0.8,en;q=0.7', NULL, NULL, NULL, '["zurich","geneva","basel","lausanne","bern","winterthur","lucerne","st gallen","lugano","biel"]'), 96 ('NL', 'Netherlands', 'google.nl', 'nl', 'Europe/Amsterdam', 'EUR', '€', 'DD-MM-YYYY', 29700, 'Premium', 71888, 1.09, 'Strong economy, business-friendly', 0, 1, 1, '+31', '^(\+31|0)6\d{8}$', 'nl-NL,en;q=0.8', '["BV","NV","VOF","CV"]', '["KvK-nummer","BTW-nummer","Vestigingsadres","Handelsregister"]', '["contact","over ons","juridisch","privacybeleid"]', '["amsterdam","rotterdam","the hague","utrecht","eindhoven","groningen","tilburg","almere","breda","nijmegen"]'), 97 ('DK', 'Denmark', 'google.dk', 'da', 'Europe/Copenhagen', 'DKK', 'kr', 'DD-MM-YYYY', 29700, 'Premium', 71403, 0.406, 'High standard of living', 0, 1, 1, '+45', '^(\+45|45)?[2-9]\d{7}$', 'da-DK,en;q=0.8', '["ApS","A/S","I/S","K/S"]', '["CVR-nummer","Momsregistreringsnummer","Hjemsted"]', '["kontakt","om os","juridisk information","privatlivspolitik"]', '["copenhagen","aarhus","odense","aalborg","esbjerg","randers","kolding","horsens","vejle","roskilde"]'), 98 99 -- Standard Tier ($247) 100 ('AT', 'Austria', 'google.at', 'de', 'Europe/Vienna', 'EUR', '€', 'DD.MM.YYYY', 24700, 'Standard', 70277, 0.516, 'Strong European economy', 0, 1, 1, '+43', '^(\+43|0)6\d{8,9}$', 'de-AT,en;q=0.8', '["GmbH","AG","OG","KG"]', '["Firmenbuchnummer","UID-Nummer","Geschäftsführer","Firmensitz"]', '["impressum","kontakt","über uns","rechtliches"]', '["vienna","graz","linz","salzburg","innsbruck","klagenfurt","villach","wels","st polten","dornbirn"]'), 101 ('SE', 'Sweden', 'google.se', 'sv', 'Europe/Stockholm', 'SEK', 'kr', 'YYYY-MM-DD', 24700, 'Standard', 67168, 0.627, 'Nordic country, innovative market', 0, 1, 1, '+46', '^(\+46|0)7[0-9]\d{7}$', 'sv-SE,en;q=0.8', '["AB","Aktiebolag"]', '["Organisationsnummer","Momsregistreringsnummer","Säte"]', '["kontakt","om oss","juridisk information","integritetspolicy"]', '["stockholm","gothenburg","malmo","uppsala","vasteras","orebro","linkoping","helsingborg","jonkoping","norrkoping"]'), 102 ('BE', 'Belgium', 'google.be', 'nl', 'Europe/Brussels', 'EUR', '€', 'DD/MM/YYYY', 24700, 'Standard', 65355, 0.632, 'Western Europe, EU headquarters', 0, 1, 1, '+32', '^(\+32|0)4\d{8}$', 'nl-BE,fr-BE;q=0.9,en;q=0.8', '["BV","NV","BVBA","SPRL"]', '["Ondernemingsnummer","BTW-nummer","Maatschappelijke zetel"]', '["contact","over ons","juridische informatie","privacy"]', '["brussels","antwerp","ghent","charleroi","liege","bruges","namur","leuven","mons","aalst"]'), 103 ('AU', 'Australia', 'google.com.au', 'en', 'Australia/Sydney', 'AUD', '$', 'DD/MM/YYYY', 24700, 'Standard', 64674, 1.79, 'Developed market, strong economy', 0, 1, 0, '+61', '^(\+61|0)(4\d{8}|614\d{6})$', 'en-AU,en;q=0.9', NULL, NULL, NULL, '["sydney","melbourne","brisbane","perth","adelaide","gold coast","canberra","newcastle","wollongong","hobart"]'), 104 ('DE', 'Germany', 'google.de', 'de', 'Europe/Berlin', 'EUR', '€', 'DD.MM.YYYY', 24700, 'Standard', 63835, 4.59, 'Largest European economy', 0, 1, 1, '+49', '^(\+49|0)(15|16|17)\d{8,9}$', 'de-DE,en;q=0.8', '["GmbH","AG","UG (haftungsbeschränkt)","UG","KG","e.V.","OHG"]', '["Handelsregister","Unternehmensnummer","USt-IdNr","Geschäftsführer","Registergericht","Amtsgericht"]', '["impressum","kontakt","über uns","rechtliches","datenschutz"]', '["berlin","munich","hamburg","cologne","frankfurt","stuttgart","dusseldorf","dortmund","essen","leipzig"]'), 105 106 -- Standard Tier ($197) 107 ('CA', 'Canada', 'google.ca', 'en', 'America/Toronto', 'CAD', '$', 'DD/MM/YYYY', 19700, 'Standard', 60177, 2.24, 'North American market, similar to US', 0, 0, 0, '+1', '^(\+1|1)?[2-9]\d{9}$', 'en-CA,fr-CA;q=0.8', NULL, NULL, NULL, '["toronto","montreal","vancouver","calgary","ottawa","edmonton","winnipeg","quebec city","hamilton","kitchener"]'), 108 ('KR', 'South Korea', 'google.co.kr', 'ko', 'Asia/Seoul', 'KRW', '₩', 'YYYY.MM.DD', 19700, 'Standard', 59330, 1.79, 'Advanced economy, tech-savvy', 0, 0, 0, '+82', '^(\+82|0)?1[0-9]\d{7,8}$', 'ko-KR,en;q=0.7', NULL, NULL, NULL, '["seoul","busan","incheon","daegu","daejeon","gwangju","ulsan","suwon","changwon","goyang"]'), 109 ('FR', 'France', 'google.fr', 'fr', 'Europe/Paris', 'EUR', '€', 'DD/MM/YYYY', 19700, 'Standard', 58765, 3.13, 'Major European market', 0, 0, 1, '+33', '^(\+33|0)(6|7)\d{8}$', 'fr-FR,en;q=0.7', '["SARL","SA","SAS","EURL","SNC","SCS"]', '["Numéro SIREN","Numéro SIRET","RCS","Capital social","Siège social","Numéro de TVA"]', '["contact","à propos","mentions légales","informations légales"]', '["paris","marseille","lyon","toulouse","nice","nantes","montpellier","strasbourg","bordeaux","lille"]'), 110 ('UK', 'United Kingdom', 'google.co.uk', 'en', 'Europe/London', 'GBP', '£', 'DD/MM/YYYY', 19700, 'Standard', 56836, 3.71, 'Post-Brexit economy, mature market', 0, 0, 1, '+44', '^(\+44|0)(7\d{9}|447\d{8})$', 'en-GB,en;q=0.9', '["Ltd","Limited","PLC","LLP","Limited Liability Partnership"]', '["Company number","Company registration","Registered in England","Registered in Scotland","Registered in Wales","Registered office","VAT number"]', '["contact","about","legal","company information","privacy policy"]', '["london","manchester","birmingham","glasgow","liverpool","edinburgh","leeds","bristol","cardiff","belfast"]'), 111 ('NZ', 'New Zealand', 'google.co.nz', 'en', 'Pacific/Auckland', 'NZD', '$', 'DD/MM/YYYY', 19700, 'Standard', 54196, 0.253, 'Pacific developed market', 0, 0, 0, '+64', '^(\+64|0)(2[0-9]\d{7,8})$', 'en-NZ,en;q=0.9', NULL, NULL, NULL, '["auckland","wellington","christchurch","hamilton","tauranga","dunedin","palmerston north","napier","porirua","new plymouth"]'), 112 ('IT', 'Italy', 'google.it', 'it', 'Europe/Rome', 'EUR', '€', 'DD/MM/YYYY', 19700, 'Standard', 53203, 2.33, 'Southern Europe, design-focused', 0, 0, 1, '+39', '^(\+39|39)?3\d{8,9}$', 'it-IT,en;q=0.7', '["S.r.l.","S.p.A.","S.n.c.","S.a.s."]', '["Partita IVA","Codice Fiscale","Registro Imprese","Capitale sociale","Sede legale"]', '["contatti","chi siamo","note legali","informazioni legali"]', '["rome","milan","naples","turin","palermo","genoa","bologna","florence","bari","catania"]'), 113 ('JP', 'Japan', 'google.co.jp', 'ja', 'Asia/Tokyo', 'JPY', '¥', 'YYYY/MM/DD', 19700, 'Standard', 52120, 4.11, 'Mature economy, high quality standards', 0, 0, 0, '+81', '^(\+81|0)(70|80|90)\d{8}$', 'ja-JP,en;q=0.7', NULL, NULL, NULL, '["tokyo","osaka","yokohama","nagoya","sapporo","fukuoka","kobe","kyoto","kawasaki","saitama"]'), 114 115 -- Moderate Tier ($197) 116 ('ES', 'Spain', 'google.es', 'es', 'Europe/Madrid', 'EUR', '€', 'DD/MM/YYYY', 19700, 'Moderate', 51693, 1.58, 'Southern Europe, growing market', 0, 0, 1, '+34', '^(\+34|34)?[67]\d{8}$', 'es-ES,en;q=0.7', '["SL","SA","SRL","SLU"]', '["CIF","NIF","Registro Mercantil","Capital social","Domicilio social"]', '["contacto","acerca de","aviso legal","información legal"]', '["madrid","barcelona","valencia","seville","zaragoza","malaga","murcia","palma","bilbao","alicante"]'), 117 ('PL', 'Poland', 'google.pl', 'pl', 'Europe/Warsaw', 'PLN', 'zł', 'DD.MM.YYYY', 19700, 'Moderate', 48283, 0.844, 'Central Europe, rapidly growing', 0, 0, 1, '+48', '^(\+48|48)?[5-8]\d{8}$', 'pl-PL,en;q=0.7', '["Sp. z o.o.","S.A.","sp.j.","sp.k."]', '["NIP","REGON","KRS","Siedziba"]', '["kontakt","o nas","informacje prawne","polityka prywatności"]', '["warsaw","krakow","lodz","wroclaw","poznan","gdansk","szczecin","bydgoszcz","lublin","katowice"]'), 118 119 -- Emerging Tier ($97) 120 ('CN', 'China', 'google.com.hk', 'zh', 'Asia/Shanghai', 'CNY', '¥', 'YYYY/MM/DD', 9700, 'Emerging', 24558, 18.53, 'Large emerging market, limited Google access', 1, 0, 0, '+86', '^(\+86|86)?1[3-9]\d{9}$', 'zh-CN,en;q=0.5', NULL, NULL, NULL, '["beijing","shanghai","guangzhou","shenzhen","chengdu","hangzhou","wuhan","xian","tianjin","nanjing"]'), 121 ('MX', 'Mexico', 'google.com.mx', 'es', 'America/Mexico_City', 'MXN', '$', 'DD/MM/YYYY', 9700, 'Emerging', 23451, 1.81, 'Latin American market, price-conscious', 1, 0, 0, '+52', '^(\+52|52)?1?\d{10}$', 'es-MX,en;q=0.6', NULL, NULL, NULL, '["mexico city","guadalajara","monterrey","puebla","tijuana","leon","juarez","zapopan","merida","cancun"]'), 122 123 -- Developing Tier ($47) 124 ('ID', 'Indonesia', 'google.co.id', 'id', 'Asia/Jakarta', 'IDR', 'Rp', 'DD/MM/YYYY', 4700, 'Developing', 15016, 1.48, 'Southeast Asia, highly price-sensitive', 1, 0, 0, '+62', '^(\+62|0)8\d{8,10}$', 'id-ID,en;q=0.6', NULL, NULL, NULL, '["jakarta","surabaya","bandung","medan","semarang","makassar","palembang","tangerang","depok","bekasi"]'), 125 ('IN', 'India', 'google.co.in', 'en', 'Asia/Kolkata', 'INR', '₹', 'DD/MM/YYYY', 4700, 'Developing', 9183, 4.11, 'Highly price-sensitive market, huge potential', 1, 0, 0, '+91', '^(\+91|91|0)?[6-9]\d{9}$', 'en-IN,hi;q=0.8', NULL, NULL, NULL, '["mumbai","delhi","bangalore","hyderabad","chennai","kolkata","pune","ahmedabad","jaipur","surat"]'); 126 127 -- Create trigger to update updated_at timestamp 128 CREATE TRIGGER IF NOT EXISTS update_countries_timestamp 129 AFTER UPDATE ON countries 130 BEGIN 131 UPDATE countries SET updated_at = CURRENT_TIMESTAMP WHERE country_code = NEW.country_code; 132 END; 133 134 -- Create view for pricing analysis 135 CREATE VIEW IF NOT EXISTS v_country_pricing_summary AS 136 SELECT 137 pricing_tier, 138 COUNT(*) as country_count, 139 ROUND(AVG(price_usd) / 100.0, 2) as avg_price_usd, 140 MIN(price_usd) / 100.0 as min_price_usd, 141 MAX(price_usd) / 100.0 as max_price_usd, 142 GROUP_CONCAT(country_code) as countries 143 FROM countries 144 WHERE is_active = 1 145 GROUP BY pricing_tier 146 ORDER BY avg_price_usd DESC; 147 148 -- Migration complete 149 SELECT 'Migration 022 completed: countries table created with ' || COUNT(*) || ' countries' as result 150 FROM countries;