SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'contacts'
      AND index_name = 'idx_contacts_tenant_owner_deleted'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE contacts ADD INDEX idx_contacts_tenant_owner_deleted (tenant_id, owner_user_id, deleted_at, id)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'contacts'
      AND index_name = 'idx_contacts_tenant_updated'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE contacts ADD INDEX idx_contacts_tenant_updated (tenant_id, updated_at, id)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'contacts'
      AND index_name = 'idx_contacts_tenant_last_interaction'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE contacts ADD INDEX idx_contacts_tenant_last_interaction (tenant_id, last_interaction_at)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'contact_tasks'
      AND index_name = 'idx_contact_tasks_tenant_status_due'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE contact_tasks ADD INDEX idx_contact_tasks_tenant_status_due (tenant_id, status, due_at, id)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ts_precision := (
    SELECT COALESCE(DATETIME_PRECISION, 0)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'contacts'
      AND column_name = 'updated_at'
    LIMIT 1
);
SET @sql := IF(@ts_precision < 6, 'ALTER TABLE contacts MODIFY COLUMN updated_at TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS contact_dedupe_scan_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    requested_by BIGINT UNSIGNED NULL,
    status ENUM('queued', 'running', 'finished', 'failed') NOT NULL DEFAULT 'queued',
    scan_count INT UNSIGNED NOT NULL DEFAULT 0,
    error_message VARCHAR(255) NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_dedupe_jobs_tenant_status (tenant_id, status, created_at),
    CONSTRAINT fk_contact_dedupe_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_dedupe_jobs_user FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_idempotency_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    idempotency_key VARCHAR(120) NOT NULL,
    endpoint VARCHAR(120) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    response_json JSON NOT NULL,
    status_code SMALLINT UNSIGNED NOT NULL DEFAULT 200,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    UNIQUE KEY uq_api_idempotency_key (tenant_id, endpoint, idempotency_key),
    INDEX idx_api_idempotency_expires (tenant_id, expires_at),
    CONSTRAINT fk_api_idempotency_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_rate_limit_counters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    key_hash CHAR(64) NOT NULL,
    route_key VARCHAR(120) NOT NULL,
    window_start DATETIME NOT NULL,
    request_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_api_rate_limit_window (tenant_id, key_hash, route_key, window_start),
    INDEX idx_api_rate_limit_lookup (tenant_id, route_key, window_start),
    CONSTRAINT fk_api_rate_limit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
