-- SEO Growth Intelligence Platform tables

CREATE TABLE IF NOT EXISTS seo_growth_pages (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    type         ENUM('industry','location','authority','faq-page','case-study','tender') NOT NULL,
    service      VARCHAR(200) NOT NULL,
    variant      VARCHAR(200) NOT NULL,
    slug         VARCHAR(300) NOT NULL,
    title        VARCHAR(300),
    meta_desc    TEXT,
    file_path    VARCHAR(600),
    status       ENUM('draft','published','queued','failed') DEFAULT 'draft',
    word_count   INT DEFAULT 0,
    created_by   INT,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    published_at DATETIME NULL,
    UNIQUE KEY uq_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seo_growth_faqs (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    page_key        VARCHAR(300) NOT NULL,
    question        TEXT NOT NULL,
    answer          TEXT NOT NULL,
    schema_injected TINYINT(1) DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_page (page_key(200))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seo_growth_queue (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    job_type     VARCHAR(50) NOT NULL,
    params       JSON,
    status       ENUM('pending','processing','done','failed') DEFAULT 'pending',
    result_id    INT NULL,
    error        TEXT NULL,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seo_growth_settings (
    k VARCHAR(100) PRIMARY KEY,
    v TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seo_growth_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    action     VARCHAR(100),
    details    TEXT,
    user_id    INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO seo_growth_settings (k,v) VALUES
  ('brand_name',     'SPRIT NETWORK'),
  ('brand_legal',    'Sprit Network Pvt Ltd'),
  ('brand_tagline',  'Let\'s grow with sprit'),
  ('site_url',       'https://spritnetwork.com'),
  ('contact_email',  'info@spritnetwork.com'),
  ('contact_phone',  '+94 11 234 5678'),
  ('hq_location',    'Sri Lanka'),
  ('industries',     '["Banks & Financial Institutions","Hospitals & Healthcare","Hotels & Hospitality","Universities & Education","Government Organizations","Manufacturing","Retail & E-commerce","Telecommunications","Insurance Companies","Law Firms","Real Estate","Non-Profit Organizations","Startups & SMEs","Construction & Engineering","Logistics & Supply Chain","Media & Entertainment","Energy & Utilities","Agriculture & Food","Tourism & Travel","Pharmaceutical & Life Sciences"]'),
  ('locations',      '["Colombo, Sri Lanka","Kandy, Sri Lanka","Galle, Sri Lanka","Negombo, Sri Lanka","Kurunegala, Sri Lanka","Jaffna, Sri Lanka","Matara, Sri Lanka","Anuradhapura, Sri Lanka","Ratnapura, Sri Lanka","Batticaloa, Sri Lanka","Trincomalee, Sri Lanka","Badulla, Sri Lanka","Nuwara Eliya, Sri Lanka","Polonnaruwa, Sri Lanka","Hambantota, Sri Lanka","Vavuniya, Sri Lanka","Ampara, Sri Lanka","Monaragala, Sri Lanka","Puttalam, Sri Lanka","Kalmunai, Sri Lanka","Kegalle, Sri Lanka","Matale, Sri Lanka","Mannar, Sri Lanka","Mullaitivu, Sri Lanka","Kilinochchi, Sri Lanka","Gampaha, Sri Lanka","Panadura, Sri Lanka","Weligama, Sri Lanka","Dambulla, Sri Lanka","Embilipitiya, Sri Lanka","Bandarawela, Sri Lanka","Avissawella, Sri Lanka","Kalutara, Sri Lanka","Beruwala, Sri Lanka","Bentota, Sri Lanka","Hikkaduwa, Sri Lanka","Chilaw, Sri Lanka","Wennappuwa, Sri Lanka","Aluthgama, Sri Lanka","Wellawaya, Sri Lanka","Aarhus, Denmark","Aba, Nigeria","Abidjan, Ivory Coast","Abu Dhabi, UAE","Abuja, Nigeria","Accra, Ghana","Addis Ababa, Ethiopia","Addu City, Maldives","Adelaide, Australia","Aden, Yemen","Agadir, Morocco","Agra, India","Ahmedabad, India","Ajman, UAE","Al Wakrah, Qatar","Aleppo, Syria","Alexandria, Egypt","Algiers, Algeria","Allahabad, India","Almaty, Kazakhstan","Amman, Jordan","Amritsar, India","Amsterdam, Netherlands","Andorra la Vella, Andorra","Annaba, Algeria","Antananarivo, Madagascar","Antofagasta, Chile","Antwerp, Belgium","Apia, Samoa","Arequipa, Peru","Arusha, Tanzania","Ashgabat, Turkmenistan","Asmara, Eritrea","Astana, Kazakhstan","Asuncion, Paraguay","Athens, Greece","Atlanta, USA","Auckland, New Zealand","Aurangabad, India","Austin, USA","Baghdad, Iraq","Baku, Azerbaijan","Bali, Indonesia","Baltimore, USA","Bamako, Mali","Bandar Seri Begawan, Brunei","Bandung, Indonesia","Bangalore, India","Bangkok, Thailand","Bangui, Central African Republic","Banja Luka, Bosnia and Herzegovina","Banjul, Gambia","Barcelona, Spain","Bari, Italy","Barquisimeto, Venezuela","Barranquilla, Colombia","Basel, Switzerland","Basra, Iraq","Beijing, China","Beira, Mozambique","Beirut, Lebanon","Bekasi, Indonesia","Belem, Brazil","Belfast, UK","Belgrade, Serbia","Belo Horizonte, Brazil","Benghazi, Libya","Benin City, Nigeria","Bergen, Norway","Berlin, Germany","Bern, Switzerland","Bhopal, India","Bilbao, Spain","Birmingham, UK","Bishkek, Kyrgyzstan","Bissau, Guinea-Bissau","Blantyre, Malawi","Bo, Sierra Leone","Bobo-Dioulasso, Burkina Faso","Bogota, Colombia","Bologna, Italy","Bordeaux, France","Boston, USA","Bouake, Ivory Coast","Braga, Portugal","Brampton, Canada","Brasilia, Brazil","Bratislava, Slovakia","Brazzaville, Congo","Bremen, Germany","Bridgetown, Barbados","Brisbane, Australia","Bristol, UK","Brno, Czech Republic","Bruges, Belgium","Brussels, Belgium","Bucharest, Romania","Budapest, Hungary","Buenos Aires, Argentina","Bujumbura, Burundi","Bulawayo, Zimbabwe","Busan, South Korea","Butare, Rwanda","Cairo, Egypt","Calgary, Canada","Cali, Colombia","Caloocan, Philippines","Campinas, Brazil","Can Tho, Vietnam","Canberra, Australia","Cancun, Mexico","Cape Town, South Africa","Caracas, Venezuela","Cardiff, UK","Cartagena, Colombia","Casablanca, Morocco","Castries, Saint Lucia","Catania, Italy","Cebu City, Philippines","Chandigarh, India","Changsha, China","Charlotte, USA","Chelyabinsk, Russia","Chengdu, China","Chennai, India","Chiang Mai, Thailand","Chicago, USA","Chiclayo, Peru","Chisinau, Moldova","Chittagong, Bangladesh","Chongqing, China","Christchurch, New Zealand","Ciudad del Este, Paraguay","Cleveland, USA","Cluj-Napoca, Romania","Cochabamba, Bolivia","Coimbatore, India","Coimbra, Portugal","Cologne, Germany","Columbus, USA","Comilla, Bangladesh","Conakry, Guinea","Concepcion, Chile","Constantine, Algeria","Copenhagen, Denmark","Cordoba, Argentina","Cork, Ireland","Cotonou, Benin","Cuenca, Ecuador","Curitiba, Brazil","Da Nang, Vietnam","Daegu, South Korea","Daejeon, South Korea","Dakar, Senegal","Dalian, China","Dallas, USA","Damascus, Syria","Dammam, Saudi Arabia","Dar es Salaam, Tanzania","Darwin, Australia","Daugavpils, Latvia","Davao, Philippines","Debrecen, Hungary","Delhi, India","Denver, USA","Detroit, USA","Dhaka, Bangladesh","Dhanbad, India","Dili, Timor-Leste","Dire Dawa, Ethiopia","Djibouti City, Djibouti","Dnipro, Ukraine","Dodoma, Tanzania","Doha, Qatar","Dongguan, China","Dortmund, Germany","Douala, Cameroon","Dresden, Germany","Dubai, UAE","Dublin, Ireland","Dunedin, New Zealand","Durban, South Africa","Durres, Albania","Dushanbe, Tajikistan","Dusseldorf, Germany","East London, South Africa","Edinburgh, UK","Edmonton, Canada","Eindhoven, Netherlands","Eldoret, Kenya","Enugu, Nigeria","Erbil, Iraq","Espoo, Finland","Essen, Germany","Faisalabad, Pakistan","Faridabad, India","Fez, Morocco","Florence, Italy","Fort Worth, USA","Fortaleza, Brazil","Foshan, China","Francistown, Botswana","Frankfurt, Germany","Freetown, Sierra Leone","Fukuoka, Japan","Funafuti, Tuvalu","Fuzhou, China","Gaborone, Botswana","Galway, Ireland","Garoua, Cameroon","Gaza, Palestine","Gdansk, Poland","Geelong, Australia","Geneva, Switzerland","Genoa, Italy","George Town, Malaysia","Georgetown, Guyana","Ghaziabad, India","Ghent, Belgium","Giza, Egypt","Glasgow, UK","Goiania, Brazil","Gold Coast, Australia","Gomel, Belarus","Gondar, Ethiopia","Gothenburg, Sweden","Graz, Austria","Guadalajara, Mexico","Guangzhou, China","Guarulhos, Brazil","Guatemala City, Guatemala","Guayaquil, Ecuador","Gulu, Uganda","Guwahati, India","Gwalior, India","Gwangju, South Korea","Hai Phong, Vietnam","Haifa, Israel","Halifax, Canada","Hamburg, Germany","Hamilton, Canada","Hamilton, New Zealand","Hangzhou, China","Hannover, Germany","Hanoi, Vietnam","Harare, Zimbabwe","Harbin, China","Hargeisa, Somalia","Hat Yai, Thailand","Havana, Cuba","Helsinki, Finland","Heraklion, Greece","Hiroshima, Japan","Ho Chi Minh City, Vietnam","Hobart, Australia","Homs, Syria","Hong Kong","Honiara, Solomon Islands","Houston, USA","Howrah, India","Huambo, Angola","Hyderabad, India","Iasi, Romania","Ibadan, Nigeria","Incheon, South Korea","Indianapolis, USA","Indore, India","Ipoh, Malaysia","Irbid, Jordan","Isfahan, Iran","Islamabad, Pakistan","Jabalpur, India","Jacksonville, USA","Jaipur, India","Jakarta, Indonesia","Jeddah, Saudi Arabia","Jerusalem, Israel","Jinan, China","Jodhpur, India","Johannesburg, South Africa","Johor Bahru, Malaysia","Juarez, Mexico","Juba, South Sudan","Kabul, Afghanistan","Kampala, Uganda","Kandahar, Afghanistan","Kano, Nigeria","Kanpur, India","Kansas City, USA","Kaohsiung, Taiwan","Kaolack, Senegal","Karachi, Pakistan","Karaj, Iran","Kathmandu, Nepal","Kaunas, Lithuania","Kawasaki, Japan","Kazan, Russia","Kharkiv, Ukraine","Khartoum, Sudan","Khobar, Saudi Arabia","Khulna, Bangladesh","Kigali, Rwanda","Kingston, Jamaica","Kinshasa, DR Congo","Kisangani, DR Congo","Kisumu, Kenya","Kitwe, Zambia","Kobe, Japan","Kochi, India","Kolkata, India","Kosice, Slovakia","Kota Kinabalu, Malaysia","Kota, India","Krakow, Poland","Kuala Lumpur, Malaysia","Kuching, Malaysia","Kumasi, Ghana","Kunming, China","Kuwait City, Kuwait","Kyiv, Ukraine","Kyoto, Japan","La Paz, Bolivia","Lagos, Nigeria","Lahore, Pakistan","Lalitpur, Nepal","Las Palmas, Spain","Las Vegas, USA","Lausanne, Switzerland","Leeds, UK","Leicester, UK","Leipzig, Germany","Leon, Mexico","Libreville, Gabon","Lille, France","Lilongwe, Malawi","Lima, Peru","Limassol, Cyprus","Linz, Austria","Lisbon, Portugal","Liverpool, UK","Ljubljana, Slovenia","Lobito, Angola","Lodz, Poland","Logan City, Australia","Lome, Togo","London, UK","Los Angeles, USA","Luanda, Angola","Luang Prabang, Laos","Lubumbashi, DR Congo","Lucknow, India","Ludhiana, India","Lusaka, Zambia","Luxembourg City, Luxembourg","Lviv, Ukraine","Lyon, France","Macau","Madrid, Spain","Madurai, India","Maiduguri, Nigeria","Majuro, Marshall Islands","Makassar, Indonesia","Malabo, Equatorial Guinea","Malacca, Malaysia","Malaga, Spain","Male, Maldives","Malmo, Sweden","Managua, Nicaragua","Manama, Bahrain","Manaus, Brazil","Manchester, UK","Mandalay, Myanmar","Manila, Philippines","Maputo, Mozambique","Maracaibo, Venezuela","Marrakech, Morocco","Marseille, France","Maseru, Lesotho","Mashhad, Iran","Mbabane, Eswatini","Mbarara, Uganda","Mbuji-Mayi, DR Congo","Mecca, Saudi Arabia","Medan, Indonesia","Medellin, Colombia","Medina, Saudi Arabia","Meerut, India","Mekelle, Ethiopia","Melbourne, Australia","Mendoza, Argentina","Merida, Mexico","Mexico City, Mexico","Miami, USA","Milan, Italy","Milwaukee, USA","Minneapolis, USA","Minsk, Belarus","Miskolc, Hungary","Misrata, Libya","Mississauga, Canada","Mogadishu, Somalia","Mombasa, Kenya","Monaco","Monrovia, Liberia","Monterrey, Mexico","Montevideo, Uruguay","Montpellier, France","Montreal, Canada","Moroni, Comoros","Moscow, Russia","Mosul, Iraq","Moundou, Chad","Multan, Pakistan","Mumbai, India","Munich, Germany","Murcia, Spain","Muscat, Oman","Mwanza, Tanzania","Mymensingh, Bangladesh","Nadi, Fiji","Nagoya, Japan","Nagpur, India","Nairobi, Kenya","Nakuru, Kenya","Nampula, Mozambique","Nanjing, China","Nantes, France","Naples, Italy","Narayanganj, Bangladesh","Nashik, India","Nashville, USA","Nassau, Bahamas","Navi Mumbai, India","Naypyidaw, Myanmar","Ndjamena, Chad","Ndola, Zambia","New Delhi, India","New Orleans, USA","New Taipei, Taiwan","New York, USA","Newcastle, Australia","Ngerulmud, Palau","Niamey, Niger","Nice, France","Nicosia, Cyprus","Ningbo, China","Nis, Serbia","Nouakchott, Mauritania","Novi Sad, Serbia","Novosibirsk, Russia","Nukualofa, Tonga","Nuremberg, Germany","Nzerekore, Guinea","Odense, Denmark","Odessa, Ukraine","Omdurman, Sudan","Omsk, Russia","Oran, Algeria","Orlando, USA","Osaka, Japan","Oslo, Norway","Ostrava, Czech Republic","Ottawa, Canada","Ouagadougou, Burkina Faso","Palembang, Indonesia","Palermo, Italy","Palikir, Micronesia","Palma, Spain","Panama City, Panama","Paramaribo, Suriname","Paris, France","Pasig, Philippines","Patna, India","Patras, Greece","Pattaya, Thailand","Perth, Australia","Peshawar, Pakistan","Philadelphia, USA","Phnom Penh, Cambodia","Phoenix, USA","Phuket, Thailand","Pimpri-Chinchwad, India","Pittsburgh, USA","Plovdiv, Bulgaria","Podgorica, Montenegro","Pointe-Noire, Congo","Pokhara, Nepal","Port Elizabeth, South Africa","Port Harcourt, Nigeria","Port Louis, Mauritius","Port Moresby, Papua New Guinea","Port Said, Egypt","Port Sudan, Sudan","Port Vila, Vanuatu","Port of Spain, Trinidad and Tobago","Port-au-Prince, Haiti","Portland, USA","Porto Alegre, Brazil","Porto, Portugal","Porto-Novo, Benin","Poznan, Poland","Prague, Czech Republic","Praia, Cape Verde","Pretoria, South Africa","Pristina, Kosovo","Puebla, Mexico","Pune, India","Qingdao, China","Quebec City, Canada","Quetta, Pakistan","Quezon City, Philippines","Quito, Ecuador","Rabat, Morocco","Raipur, India","Rajkot, India","Rajshahi, Bangladesh","Raleigh, USA","Ramallah, Palestine","Ranchi, India","Ras Al Khaimah, UAE","Rawalpindi, Pakistan","Recife, Brazil","Reykjavik, Iceland","Riga, Latvia","Rijeka, Croatia","Rio de Janeiro, Brazil","Rishon LeZion, Israel","Riyadh, Saudi Arabia","Rome, Italy","Rosario, Argentina","Rostov-on-Don, Russia","Rotterdam, Netherlands","Sacramento, USA","Saint Petersburg, Russia","Saitama, Japan","Salalah, Oman","Salvador, Brazil","Salzburg, Austria","Samarkand, Uzbekistan","San Antonio, USA","San Diego, USA","San Francisco, USA","San Jose, Costa Rica","San Jose, USA","San Marino","San Salvador, El Salvador","Sanaa, Yemen","Santa Cruz, Bolivia","Santiago, Chile","Santo Domingo, Dominican Republic","Sao Paulo, Brazil","Sao Tome, Sao Tome and Principe","Sapporo, Japan","Sarajevo, Bosnia and Herzegovina","Seattle, USA","Semarang, Indonesia","Sendai, Japan","Seoul, South Korea","Seville, Spain","Sfax, Tunisia","Shanghai, China","Sharjah, UAE","Sheffield, UK","Shenyang, China","Shenzhen, China","Shiraz, Iran","Shubra El Kheima, Egypt","Siem Reap, Cambodia","Sikasso, Mali","Singapore","Skopje, North Macedonia","Sofia, Bulgaria","Sohar, Oman","Sokode, Togo","Sousse, Tunisia","Soweto, South Africa","Split, Croatia","Srinagar, India","St George\'s, Grenada","St John\'s, Antigua and Barbuda","Stockholm, Sweden","Strasbourg, France","Stuttgart, Germany","Sulaymaniyah, Iraq","Surabaya, Indonesia","Surat, India","Surrey, Canada","Suva, Fiji","Suzhou, China","Sydney, Australia","Sylhet, Bangladesh","Tabriz, Iran","Tabuk, Saudi Arabia","Taichung, Taiwan","Tainan, Taiwan","Taipei, Taiwan","Taiz, Yemen","Tallinn, Estonia","Tamale, Ghana","Tampa, USA","Tampere, Finland","Tangier, Morocco","Tarawa, Kiribati","Tartu, Estonia","Tashkent, Uzbekistan","Tauranga, New Zealand","Tbilisi, Georgia","Tegucigalpa, Honduras","Tehran, Iran","Tel Aviv, Israel","Tema, Ghana","Thane, India","The Hague, Netherlands","Thessaloniki, Greece","Thies, Senegal","Thimphu, Bhutan","Thiruvananthapuram, India","Tianjin, China","Tijuana, Mexico","Timisoara, Romania","Tirana, Albania","Toamasina, Madagascar","Tokyo, Japan","Toronto, Canada","Toulouse, France","Tripoli, Lebanon","Tripoli, Libya","Trondheim, Norway","Trujillo, Peru","Tucson, USA","Tucuman, Argentina","Tunis, Tunisia","Turin, Italy","Turku, Finland","Ulaanbaatar, Mongolia","Ulsan, South Korea","Utrecht, Netherlands","Vadodara, India","Valencia, Spain","Valencia, Venezuela","Valletta, Malta","Valparaiso, Chile","Vancouver, Canada","Varanasi, India","Varna, Bulgaria","Venice, Italy","Victoria, Seychelles","Vienna, Austria","Vientiane, Laos","Vijayawada, India","Vilnius, Lithuania","Virginia Beach, USA","Visakhapatnam, India","Walvis Bay, Namibia","Warsaw, Poland","Washington DC, USA","Wellington, New Zealand","Wenzhou, China","Windhoek, Namibia","Winnipeg, Canada","Wollongong, Australia","Wroclaw, Poland","Wuhan, China","Xian, China","Yamoussoukro, Ivory Coast","Yangon, Myanmar","Yaounde, Cameroon","Yaren, Nauru","Yekaterinburg, Russia","Yerevan, Armenia","Yokohama, Japan","Zagreb, Croatia","Zamboanga City, Philippines","Zanzibar City, Tanzania","Zapopan, Mexico","Zaragoza, Spain","Zaria, Nigeria","Zarqa, Jordan","Zhengzhou, China","Zinder, Niger","Zurich, Switzerland"]'),
  ('services',       '["Cybersecurity Services","Server Management","IT Outsourcing","Cloud Services","Enterprise Networking","IT Consulting","AI Automation","Website Security Audits","Data Centre Security","Perimeter Security","Workstation Management","Hosting & Cloud","ERP Systems","CRM Solutions","LMS Platforms","POS Systems","HR & Payroll Systems"]'),
  ('blog_topics',    '["cybersecurity threats","AI automation","IT outsourcing","cloud security","server management","enterprise networking","digital transformation","data protection","ransomware prevention","zero trust security"]'),
  ('autopilot_on',   '0'),
  ('blog_daily_count','3');