إظهار وتصنيف جميع الشركات

CREATE TABLE IF NOT EXISTS dm_directory_upgrades (
                version VARCHAR(100) PRIMARY KEY, checksum CHAR(64) NOT NULL,
                applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dm_business_index (
                entity_kind VARCHAR(12) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL,
                sector VARCHAR(60) NOT NULL, country VARCHAR(2) NOT NULL DEFAULT '',
                city VARCHAR(100) NOT NULL DEFAULT '', employee_range VARCHAR(20) NOT NULL DEFAULT '',
                specialty VARCHAR(120) NOT NULL DEFAULT '', tags_json TEXT NOT NULL,
                indexed_at DATETIME NOT NULL, PRIMARY KEY(entity_kind,entity_id),
                INDEX idx_biz_sector(sector,entity_kind,entity_id),
                INDEX idx_biz_location(country,city), INDEX idx_biz_size(employee_range)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dm_business_terms (
                entity_kind VARCHAR(12) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL,
                dimension VARCHAR(20) NOT NULL, term_key CHAR(40) NOT NULL, label VARCHAR(120) NOT NULL,
                PRIMARY KEY(entity_kind,entity_id,dimension,term_key),
                INDEX idx_biz_facet(dimension,term_key,entity_kind,entity_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dm_business_overrides (
                entity_kind VARCHAR(12) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL,
                fields_json TEXT NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY(entity_kind,entity_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dm_business_build (
                id TINYINT UNSIGNED PRIMARY KEY, phase VARCHAR(12) NOT NULL,
                last_id BIGINT UNSIGNED NOT NULL DEFAULT 0, processed INT UNSIGNED NOT NULL DEFAULT 0,
                started_at DATETIME NOT NULL, completed_at DATETIME NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dm_business_edit_log (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entity_kind VARCHAR(12) NOT NULL,
                entity_id BIGINT UNSIGNED NOT NULL, before_json TEXT NOT NULL, after_json TEXT NOT NULL,
                edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;