-- ================================================================
--  VERIFORGE 150M+ — Complete Database Schema
--  Copy & paste this entire file into phpMyAdmin SQL tab
--  or run: mysql -u root -p your_database < schema.sql
-- ================================================================

-- ── COMPANIES TABLE ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    domain VARCHAR(255),
    website VARCHAR(500),
    industry VARCHAR(150),
    sub_industry VARCHAR(150),
    country VARCHAR(100),
    city VARCHAR(100),
    state VARCHAR(100),
    county VARCHAR(150),
    zipcode VARCHAR(20),
    region VARCHAR(50),
    address VARCHAR(500),
    employee_count INT,
    size_range VARCHAR(50),
    revenue_range VARCHAR(50),
    annual_revenue BIGINT,
    founded_year SMALLINT,
    company_type VARCHAR(50) DEFAULT 'Private',
    linkedin_url VARCHAR(500),
    twitter_url VARCHAR(500),
    facebook_url VARCHAR(500),
    tech_stack JSON,
    funding_total BIGINT DEFAULT 0,
    funding_stage VARCHAR(50),
    description TEXT,
    sic_code VARCHAR(20),
    naics_code VARCHAR(20),
    data_source VARCHAR(100) DEFAULT 'manual',
    last_verified DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_industry (industry),
    KEY idx_country (country),
    KEY idx_state (state),
    KEY idx_county (county),
    KEY idx_zipcode (zipcode),
    KEY idx_region (region),
    KEY idx_domain (domain),
    KEY idx_source (data_source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── CONTACTS TABLE ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) AS (CONCAT(COALESCE(first_name,''),' ',COALESCE(last_name,''))) STORED,
    job_title VARCHAR(200),
    seniority VARCHAR(50),
    department VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    personal_email VARCHAR(255),
    phone VARCHAR(50),
    mobile_phone VARCHAR(50),
    linkedin_url VARCHAR(500),
    twitter_url VARCHAR(500),
    city VARCHAR(100),
    state VARCHAR(100),
    county VARCHAR(150),
    zipcode VARCHAR(20),
    region VARCHAR(50),
    country VARCHAR(100),
    timezone VARCHAR(50),
    languages JSON,
    education VARCHAR(300),
    employment_history JSON,
    skills JSON,
    verified_steps TINYINT DEFAULT 0,
    verification_status ENUM('verified','partial','unverified','bounced') DEFAULT 'unverified',
    email_verified TINYINT(1) DEFAULT 0,
    phone_verified TINYINT(1) DEFAULT 0,
    gdpr_compliant TINYINT(1) DEFAULT 1,
    confidence_score DECIMAL(5,2) DEFAULT 0,
    data_source VARCHAR(100) DEFAULT 'manual',
    last_verified DATETIME,
    next_verify_at DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_email (email),
    KEY idx_status (verification_status),
    KEY idx_company (company_id),
    KEY idx_seniority (seniority),
    KEY idx_dept (department),
    KEY idx_country (country),
    KEY idx_state (state),
    KEY idx_county (county),
    KEY idx_zipcode (zipcode),
    KEY idx_region (region),
    KEY idx_active (is_active),
    KEY idx_score (confidence_score),
    KEY idx_source (data_source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── USERS TABLE ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role ENUM('superadmin','admin','user') DEFAULT 'user',
    plan ENUM('free','starter','pro','enterprise') DEFAULT 'free',
    status ENUM('active','suspended','pending') DEFAULT 'active',
    credits INT DEFAULT 25,
    credits_used INT DEFAULT 0,
    credits_reset DATETIME,
    stripe_customer_id VARCHAR(100),
    stripe_subscription_id VARCHAR(100),
    stripe_price_id VARCHAR(100),
    subscription_status VARCHAR(50) DEFAULT 'inactive',
    subscription_ends_at DATETIME,
    gdpr_consent TINYINT(1) DEFAULT 0,
    last_login DATETIME,
    login_count INT DEFAULT 0,
    api_key VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_email (email),
    KEY idx_role (role),
    KEY idx_plan (plan),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── EXPORTS TABLE ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS exports (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    record_count INT DEFAULT 0,
    credits_deducted INT DEFAULT 0,
    export_type VARCHAR(50) DEFAULT 'csv',
    filters_used JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user (user_id),
    KEY idx_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── REFRESH LOG ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS refresh_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    triggered_by INT UNSIGNED,
    contacts_checked INT DEFAULT 0,
    contacts_updated INT DEFAULT 0,
    contacts_bounced INT DEFAULT 0,
    duration_ms INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── RATE LIMITS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rate_limits (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(64) NOT NULL,
    action VARCHAR(50) NOT NULL,
    attempts INT DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_ip_action (ip, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── STRIPE EVENTS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS stripe_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(100) UNIQUE,
    event_type VARCHAR(100),
    payload JSON,
    processed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── INGEST LOG ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ingest_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(100) NOT NULL,
    run_started DATETIME,
    run_finished DATETIME,
    raw_fetched INT DEFAULT 0,
    passed_verify INT DEFAULT 0,
    failed_verify INT DEFAULT 0,
    skipped_dup INT DEFAULT 0,
    inserted INT DEFAULT 0,
    error_msg TEXT,
    status ENUM('running','done','failed') DEFAULT 'running',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_source (source),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── CRAWL QUEUE ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS crawl_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(100) NOT NULL,
    payload JSON NOT NULL,
    priority TINYINT DEFAULT 5,
    status ENUM('pending','processing','done','failed') DEFAULT 'pending',
    attempts TINYINT DEFAULT 0,
    max_attempts TINYINT DEFAULT 3,
    worker_id VARCHAR(64),
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_status_priority (status, priority DESC),
    KEY idx_source (source),
    KEY idx_worker (worker_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── CRAWL DOMAINS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS crawl_domains (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) UNIQUE NOT NULL,
    company_name VARCHAR(300),
    industry VARCHAR(150),
    country VARCHAR(100),
    mx_valid TINYINT(1) DEFAULT 0,
    contacts_found INT DEFAULT 0,
    last_crawled DATETIME,
    next_crawl DATETIME,
    crawl_status ENUM('pending','crawled','failed','skipped') DEFAULT 'pending',
    source VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_status (crawl_status),
    KEY idx_next (next_crawl),
    KEY idx_mx (mx_valid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── REFRESH SCHEDULE ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS refresh_schedule (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contact_id INT UNSIGNED NOT NULL,
    scheduled_at DATETIME NOT NULL,
    status ENUM('pending','done','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_scheduled (scheduled_at, status),
    KEY idx_contact (contact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── PIPELINE STATS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pipeline_stats (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE UNIQUE,
    total_contacts BIGINT DEFAULT 0,
    total_verified BIGINT DEFAULT 0,
    total_domains INT DEFAULT 0,
    domains_pending INT DEFAULT 0,
    queue_pending BIGINT DEFAULT 0,
    queue_done BIGINT DEFAULT 0,
    refresh_pending INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── PLAN PRICES ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS plan_prices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_key VARCHAR(50) UNIQUE,
    display_name VARCHAR(100),
    monthly_price DECIMAL(10,2),
    stripe_price_id VARCHAR(100),
    credits INT,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── EXTRACTION JOBS (NEW) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS extraction_jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    triggered_by INT UNSIGNED NOT NULL,
    job_type ENUM('full_extract','source_extract','geo_extract','refresh') DEFAULT 'full_extract',
    sources_target JSON COMMENT 'Which sources to run',
    status ENUM('queued','running','paused','completed','failed','cancelled') DEFAULT 'queued',
    progress_pct DECIMAL(5,2) DEFAULT 0,
    current_source VARCHAR(100),
    total_sources INT DEFAULT 0,
    completed_sources INT DEFAULT 0,
    total_raw_fetched BIGINT DEFAULT 0,
    total_verified BIGINT DEFAULT 0,
    total_inserted BIGINT DEFAULT 0,
    total_duplicates BIGINT DEFAULT 0,
    total_failed BIGINT DEFAULT 0,
    source_results JSON COMMENT 'Per-source breakdown',
    started_at DATETIME,
    finished_at DATETIME,
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_status (status),
    KEY idx_user (triggered_by),
    KEY idx_type (job_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── GEO REGIONS REFERENCE TABLE (NEW) ─────────────────────────────
CREATE TABLE IF NOT EXISTS geo_regions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    country_name VARCHAR(100) NOT NULL,
    state_code VARCHAR(10),
    state_name VARCHAR(150),
    county_name VARCHAR(150),
    zipcode VARCHAR(20),
    city VARCHAR(100),
    region VARCHAR(50) COMMENT 'Americas, Europe, APAC, Middle East, Africa',
    sub_region VARCHAR(100),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    KEY idx_country (country_code),
    KEY idx_state (country_code, state_code),
    KEY idx_county (country_name, state_name, county_name),
    KEY idx_zip (country_code, zipcode),
    KEY idx_region (region),
    KEY idx_city (city),
    UNIQUE KEY uk_geo (country_code, state_code, county_name, zipcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── SOURCE REGISTRY (NEW) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS source_registry (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_key VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    source_type ENUM('api','scraper','database','manual') DEFAULT 'api',
    is_active TINYINT(1) DEFAULT 1,
    requires_api_key TINYINT(1) DEFAULT 0,
    rate_limit_per_hour INT DEFAULT 100,
    estimated_contacts BIGINT DEFAULT 0,
    last_run DATETIME,
    total_extracted BIGINT DEFAULT 0,
    total_verified BIGINT DEFAULT 0,
    icon_class VARCHAR(50) DEFAULT 'bi-globe',
    color VARCHAR(20) DEFAULT '#00d4ff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_active (is_active),
    KEY idx_type (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ================================================================
--  SEED DATA
-- ================================================================

-- ── Seed Plan Prices ──────────────────────────────────────────────
INSERT IGNORE INTO plan_prices (plan_key, display_name, monthly_price, stripe_price_id, credits) VALUES
    ('free',       'Free',       0,    '',                          25),
    ('starter',    'Starter',    49,   'price_YOUR_STARTER_ID',     1000),
    ('pro',        'Pro',        149,  'price_YOUR_PRO_ID',         10000),
    ('enterprise', 'Enterprise', 399,  'price_YOUR_ENTERPRISE_ID',  -1);

-- ── Seed Source Registry ──────────────────────────────────────────
INSERT IGNORE INTO source_registry (source_key, display_name, description, source_type, is_active, requires_api_key, rate_limit_per_hour, estimated_contacts, icon_class, color) VALUES
    ('hunter',        'Hunter.io',           'Email finder API — domain-based contact search',          'api',      1, 1, 100,    5000000,  'bi-envelope-check', '#ff6b35'),
    ('github',        'GitHub',              'Public org members and developer profiles',                'api',      1, 0, 5000,   10000000, 'bi-github',         '#24292e'),
    ('opencorp',      'OpenCorporates',      'Global company registry — officers and directors',        'api',      1, 0, 100,    15000000, 'bi-building',       '#2d7d9a'),
    ('whois',         'WHOIS/RDAP',          'Domain registration data + executive email patterns',     'scraper',  1, 0, 500,    8000000,  'bi-globe',          '#00c6ff'),
    ('sec_edgar',     'SEC EDGAR',           'US public company filings — officers and directors',      'api',      1, 0, 500,    5000000,  'bi-bank',           '#003366'),
    ('uk_companies',  'UK Companies House',  'UK company directors and officers (free API)',             'api',      1, 0, 600,    8000000,  'bi-flag',           '#c8102e'),
    ('eu_opendata',   'EU Open Data',        'European company registries and officer data',             'api',      1, 0, 200,    10000000, 'bi-stars',          '#003399'),
    ('domain_crawl',  'Domain Crawl',        'Crawl company websites for contact pages',                'scraper',  1, 0, 300,    20000000, 'bi-search',         '#8b5cf6'),
    ('google_maps',   'Google Maps',         'Business directory — local business contacts',             'api',      1, 1, 100,    25000000, 'bi-geo-alt',        '#4285f4'),
    ('gov_contracts', 'Gov Contracts',       'Government contract awards — company contacts',            'api',      1, 0, 300,    10000000, 'bi-award',          '#bf0a30'),
    ('academic',      'Academic (ORCID)',     'Academic researchers and institution contacts',            'api',      1, 0, 500,    8000000,  'bi-mortarboard',    '#a6ce39'),
    ('press_release', 'Press Releases',      'PR newswire company announcements and contacts',           'scraper',  1, 0, 200,    5000000,  'bi-newspaper',      '#ff9800'),
    ('patent',        'Patents (USPTO)',      'Patent inventor and assignee contact data',                'api',      1, 0, 300,    3000000,  'bi-lightbulb',      '#f59e0b'),
    ('conference',    'Conference Data',      'Tech conference speaker and organizer contacts',           'scraper',  1, 0, 100,    2000000,  'bi-calendar-event', '#ec4899'),
    ('builtwith',     'BuiltWith',           'Technology profiling — websites and tech stacks',           'api',      1, 1, 100,    15000000, 'bi-cpu',            '#059669'),
    ('crunchbase',    'Crunchbase (Open)',    'Startup database — founders, executives, funding',         'api',      1, 1, 200,    5000000,  'bi-rocket-takeoff', '#0288d1');

-- ── Seed Worldwide Geo Regions (sample — US states, UK regions, major countries) ──
INSERT IGNORE INTO geo_regions (country_code, country_name, state_code, state_name, region, sub_region) VALUES
    -- United States
    ('US','United States','AL','Alabama','Americas','North America'),
    ('US','United States','AK','Alaska','Americas','North America'),
    ('US','United States','AZ','Arizona','Americas','North America'),
    ('US','United States','AR','Arkansas','Americas','North America'),
    ('US','United States','CA','California','Americas','North America'),
    ('US','United States','CO','Colorado','Americas','North America'),
    ('US','United States','CT','Connecticut','Americas','North America'),
    ('US','United States','DE','Delaware','Americas','North America'),
    ('US','United States','FL','Florida','Americas','North America'),
    ('US','United States','GA','Georgia','Americas','North America'),
    ('US','United States','HI','Hawaii','Americas','North America'),
    ('US','United States','ID','Idaho','Americas','North America'),
    ('US','United States','IL','Illinois','Americas','North America'),
    ('US','United States','IN','Indiana','Americas','North America'),
    ('US','United States','IA','Iowa','Americas','North America'),
    ('US','United States','KS','Kansas','Americas','North America'),
    ('US','United States','KY','Kentucky','Americas','North America'),
    ('US','United States','LA','Louisiana','Americas','North America'),
    ('US','United States','ME','Maine','Americas','North America'),
    ('US','United States','MD','Maryland','Americas','North America'),
    ('US','United States','MA','Massachusetts','Americas','North America'),
    ('US','United States','MI','Michigan','Americas','North America'),
    ('US','United States','MN','Minnesota','Americas','North America'),
    ('US','United States','MS','Mississippi','Americas','North America'),
    ('US','United States','MO','Missouri','Americas','North America'),
    ('US','United States','MT','Montana','Americas','North America'),
    ('US','United States','NE','Nebraska','Americas','North America'),
    ('US','United States','NV','Nevada','Americas','North America'),
    ('US','United States','NH','New Hampshire','Americas','North America'),
    ('US','United States','NJ','New Jersey','Americas','North America'),
    ('US','United States','NM','New Mexico','Americas','North America'),
    ('US','United States','NY','New York','Americas','North America'),
    ('US','United States','NC','North Carolina','Americas','North America'),
    ('US','United States','ND','North Dakota','Americas','North America'),
    ('US','United States','OH','Ohio','Americas','North America'),
    ('US','United States','OK','Oklahoma','Americas','North America'),
    ('US','United States','OR','Oregon','Americas','North America'),
    ('US','United States','PA','Pennsylvania','Americas','North America'),
    ('US','United States','RI','Rhode Island','Americas','North America'),
    ('US','United States','SC','South Carolina','Americas','North America'),
    ('US','United States','SD','South Dakota','Americas','North America'),
    ('US','United States','TN','Tennessee','Americas','North America'),
    ('US','United States','TX','Texas','Americas','North America'),
    ('US','United States','UT','Utah','Americas','North America'),
    ('US','United States','VT','Vermont','Americas','North America'),
    ('US','United States','VA','Virginia','Americas','North America'),
    ('US','United States','WA','Washington','Americas','North America'),
    ('US','United States','WV','West Virginia','Americas','North America'),
    ('US','United States','WI','Wisconsin','Americas','North America'),
    ('US','United States','WY','Wyoming','Americas','North America'),
    ('US','United States','DC','District of Columbia','Americas','North America'),
    -- United Kingdom
    ('GB','United Kingdom','ENG','England','Europe','Northern Europe'),
    ('GB','United Kingdom','SCT','Scotland','Europe','Northern Europe'),
    ('GB','United Kingdom','WLS','Wales','Europe','Northern Europe'),
    ('GB','United Kingdom','NIR','Northern Ireland','Europe','Northern Europe'),
    -- Canada
    ('CA','Canada','ON','Ontario','Americas','North America'),
    ('CA','Canada','QC','Quebec','Americas','North America'),
    ('CA','Canada','BC','British Columbia','Americas','North America'),
    ('CA','Canada','AB','Alberta','Americas','North America'),
    ('CA','Canada','MB','Manitoba','Americas','North America'),
    ('CA','Canada','SK','Saskatchewan','Americas','North America'),
    ('CA','Canada','NS','Nova Scotia','Americas','North America'),
    ('CA','Canada','NB','New Brunswick','Americas','North America'),
    -- Australia
    ('AU','Australia','NSW','New South Wales','APAC','Oceania'),
    ('AU','Australia','VIC','Victoria','APAC','Oceania'),
    ('AU','Australia','QLD','Queensland','APAC','Oceania'),
    ('AU','Australia','WA','Western Australia','APAC','Oceania'),
    ('AU','Australia','SA','South Australia','APAC','Oceania'),
    ('AU','Australia','TAS','Tasmania','APAC','Oceania'),
    -- Germany
    ('DE','Germany','BY','Bavaria','Europe','Western Europe'),
    ('DE','Germany','NW','North Rhine-Westphalia','Europe','Western Europe'),
    ('DE','Germany','BW','Baden-Württemberg','Europe','Western Europe'),
    ('DE','Germany','NI','Lower Saxony','Europe','Western Europe'),
    ('DE','Germany','HE','Hesse','Europe','Western Europe'),
    ('DE','Germany','BE','Berlin','Europe','Western Europe'),
    ('DE','Germany','HH','Hamburg','Europe','Western Europe'),
    -- France
    ('FR','France','IDF','Île-de-France','Europe','Western Europe'),
    ('FR','France','ARA','Auvergne-Rhône-Alpes','Europe','Western Europe'),
    ('FR','France','NAQ','Nouvelle-Aquitaine','Europe','Western Europe'),
    ('FR','France','OCC','Occitanie','Europe','Western Europe'),
    ('FR','France','PAC','Provence-Alpes-Côte d''Azur','Europe','Western Europe'),
    -- India
    ('IN','India','MH','Maharashtra','APAC','South Asia'),
    ('IN','India','KA','Karnataka','APAC','South Asia'),
    ('IN','India','DL','Delhi','APAC','South Asia'),
    ('IN','India','TN','Tamil Nadu','APAC','South Asia'),
    ('IN','India','TG','Telangana','APAC','South Asia'),
    ('IN','India','GJ','Gujarat','APAC','South Asia'),
    ('IN','India','UP','Uttar Pradesh','APAC','South Asia'),
    ('IN','India','WB','West Bengal','APAC','South Asia'),
    ('IN','India','RJ','Rajasthan','APAC','South Asia'),
    ('IN','India','KL','Kerala','APAC','South Asia'),
    -- Japan
    ('JP','Japan','13','Tokyo','APAC','East Asia'),
    ('JP','Japan','27','Osaka','APAC','East Asia'),
    ('JP','Japan','23','Aichi','APAC','East Asia'),
    ('JP','Japan','14','Kanagawa','APAC','East Asia'),
    -- Singapore
    ('SG','Singapore','SG','Singapore','APAC','Southeast Asia'),
    -- UAE
    ('AE','UAE','DXB','Dubai','Middle East','Gulf States'),
    ('AE','UAE','AUH','Abu Dhabi','Middle East','Gulf States'),
    -- Israel
    ('IL','Israel','TA','Tel Aviv','Middle East','Levant'),
    ('IL','Israel','JM','Jerusalem','Middle East','Levant'),
    -- Brazil
    ('BR','Brazil','SP','São Paulo','Americas','South America'),
    ('BR','Brazil','RJ','Rio de Janeiro','Americas','South America'),
    ('BR','Brazil','MG','Minas Gerais','Americas','South America'),
    -- Mexico
    ('MX','Mexico','CMX','Mexico City','Americas','Central America'),
    ('MX','Mexico','JAL','Jalisco','Americas','Central America'),
    ('MX','Mexico','NLE','Nuevo León','Americas','Central America'),
    -- South Africa
    ('ZA','South Africa','GP','Gauteng','Africa','Southern Africa'),
    ('ZA','South Africa','WC','Western Cape','Africa','Southern Africa'),
    -- Nigeria
    ('NG','Nigeria','LA','Lagos','Africa','West Africa'),
    ('NG','Nigeria','AB','Abuja','Africa','West Africa'),
    -- Kenya
    ('KE','Kenya','NBO','Nairobi','Africa','East Africa'),
    -- China
    ('CN','China','BJ','Beijing','APAC','East Asia'),
    ('CN','China','SH','Shanghai','APAC','East Asia'),
    ('CN','China','GD','Guangdong','APAC','East Asia'),
    ('CN','China','ZJ','Zhejiang','APAC','East Asia'),
    -- South Korea
    ('KR','South Korea','11','Seoul','APAC','East Asia'),
    ('KR','South Korea','26','Busan','APAC','East Asia'),
    -- Netherlands
    ('NL','Netherlands','NH','North Holland','Europe','Western Europe'),
    ('NL','Netherlands','ZH','South Holland','Europe','Western Europe'),
    -- Sweden
    ('SE','Sweden','AB','Stockholm','Europe','Northern Europe'),
    -- Switzerland
    ('CH','Switzerland','ZH','Zürich','Europe','Western Europe'),
    ('CH','Switzerland','GE','Geneva','Europe','Western Europe'),
    -- Ireland
    ('IE','Ireland','D','Dublin','Europe','Northern Europe'),
    -- Italy
    ('IT','Italy','25','Lombardy','Europe','Southern Europe'),
    ('IT','Italy','12','Lazio','Europe','Southern Europe'),
    -- Spain
    ('ES','Spain','MD','Madrid','Europe','Southern Europe'),
    ('ES','Spain','CT','Catalonia','Europe','Southern Europe');

-- ================================================================
--  MIGRATION: Add new columns to existing tables (if upgrading)
-- ================================================================
-- Run these ALTER statements ONLY if you already have the old schema:
--
-- ALTER TABLE contacts ADD COLUMN county VARCHAR(150) AFTER state;
-- ALTER TABLE contacts ADD COLUMN zipcode VARCHAR(20) AFTER county;
-- ALTER TABLE contacts ADD COLUMN region VARCHAR(50) AFTER zipcode;
-- ALTER TABLE contacts ADD KEY idx_county (county);
-- ALTER TABLE contacts ADD KEY idx_zipcode (zipcode);
-- ALTER TABLE contacts ADD KEY idx_region (region);
-- ALTER TABLE contacts ADD KEY idx_source (data_source);
--
-- ALTER TABLE companies ADD COLUMN county VARCHAR(150) AFTER state;
-- ALTER TABLE companies ADD COLUMN zipcode VARCHAR(20) AFTER county;
-- ALTER TABLE companies ADD COLUMN region VARCHAR(50) AFTER zipcode;
-- ALTER TABLE companies ADD KEY idx_state (state);
-- ALTER TABLE companies ADD KEY idx_county (county);
-- ALTER TABLE companies ADD KEY idx_zipcode (zipcode);
-- ALTER TABLE companies ADD KEY idx_region (region);
-- ALTER TABLE companies ADD KEY idx_source (data_source);
-- ================================================================