تحديث قاعدة البيانات وإثراء دليل الشركات

017-directory-catalog-v1

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 directory_businesses (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                source_key VARCHAR(191) NOT NULL UNIQUE, slug VARCHAR(160) NOT NULL UNIQUE,
                name VARCHAR(200) NOT NULL, description TEXT NOT NULL,
                industry VARCHAR(120) NOT NULL, country CHAR(2) NOT NULL, city VARCHAR(100) NOT NULL DEFAULT '',
                website_url VARCHAR(1024) NOT NULL DEFAULT '', careers_url VARCHAR(1024) NOT NULL DEFAULT '',
                source_url VARCHAR(1024) NOT NULL, source_kind VARCHAR(20) NOT NULL,
                source_year SMALLINT UNSIGNED NULL, source_page SMALLINT UNSIGNED NULL,
                source_checked_at DATETIME NULL,
                listing_status VARCHAR(20) NOT NULL DEFAULT 'needs_review',
                content_hash CHAR(64) NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_directory_country (listing_status,country),
                INDEX idx_directory_industry (listing_status,industry)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS directory_jobs (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                business_id BIGINT UNSIGNED NOT NULL, source_key VARCHAR(191) NOT NULL UNIQUE,
                slug VARCHAR(180) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL, summary TEXT NOT NULL,
                country CHAR(2) NOT NULL, city VARCHAR(100) NOT NULL DEFAULT '',
                location_type VARCHAR(20) NOT NULL DEFAULT '',
                source_url VARCHAR(1024) NOT NULL, application_url VARCHAR(1024) NOT NULL,
                date_posted DATETIME NULL, deadline DATETIME NULL,
                checked_at DATETIME NOT NULL, expires_at DATETIME NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                content_hash CHAR(64) NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_directory_jobs_open (status,expires_at,country),
                CONSTRAINT fk_directory_job_business FOREIGN KEY (business_id) REFERENCES directory_businesses(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS directory_import_runs (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                dataset_key VARCHAR(100) NOT NULL, source_hash CHAR(64) NOT NULL,
                businesses_processed INT UNSIGNED NOT NULL, jobs_processed INT UNSIGNED NOT NULL,
                jobs_rejected INT UNSIGNED NOT NULL,
                executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

018-directory-review-v1

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 directory_review_log (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                business_id BIGINT UNSIGNED NOT NULL,
                before_data LONGTEXT NOT NULL, after_data LONGTEXT NOT NULL,
                reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_directory_review_business (business_id),
                CONSTRAINT fk_directory_review_business FOREIGN KEY (business_id) REFERENCES directory_businesses(id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

019-directory-bulk-references-v1

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 directory_reference_sources (
                source_id VARCHAR(64) PRIMARY KEY, title VARCHAR(240) NOT NULL,
                filename VARCHAR(240) NOT NULL, file_sha256 CHAR(64) NOT NULL,
                edition_year SMALLINT UNSIGNED NOT NULL, page_count SMALLINT UNSIGNED NOT NULL,
                notes_json TEXT NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS directory_bulk_runs (
                dataset_hash CHAR(64) PRIMARY KEY, total_parts INT UNSIGNED NOT NULL,
                total_occurrences INT UNSIGNED NOT NULL, total_company_groups INT UNSIGNED NOT NULL,
                next_part INT UNSIGNED NOT NULL DEFAULT 0, part_offset INT UNSIGNED NOT NULL DEFAULT 0,
                processed_occurrences INT UNSIGNED NOT NULL DEFAULT 0,
                businesses_created INT UNSIGNED NOT NULL DEFAULT 0,
                businesses_linked INT UNSIGNED NOT NULL DEFAULT 0,
                references_created INT UNSIGNED NOT NULL DEFAULT 0,
                references_existing INT UNSIGNED NOT NULL DEFAULT 0,
                started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                completed_at DATETIME NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS directory_reference_entries (
                reference_id VARCHAR(100) PRIMARY KEY, business_id BIGINT UNSIGNED NOT NULL,
                source_id VARCHAR(64) NOT NULL, physical_page SMALLINT UNSIGNED NOT NULL,
                source_row SMALLINT UNSIGNED NOT NULL, printed_page VARCHAR(20) NOT NULL,
                quality_flags TEXT NOT NULL, details_json LONGTEXT NOT NULL,
                content_hash CHAR(64) NOT NULL,
                imported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_reference_business (business_id),
                INDEX idx_reference_page (source_id, physical_page, source_row),
                CONSTRAINT fk_reference_business FOREIGN KEY (business_id) REFERENCES directory_businesses(id),
                CONSTRAINT fk_reference_source FOREIGN KEY (source_id) REFERENCES directory_reference_sources(source_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

021-business-discovery-v1

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;