CREATE TABLE IF NOT EXISTS languages (
    code VARCHAR(16) NOT NULL PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    native_name VARCHAR(120) NOT NULL,
    direction VARCHAR(3) NOT NULL DEFAULT 'ltr',
    fallback_locale VARCHAR(16) NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_languages_enabled_sort (is_enabled, sort_order, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translation_overrides (
    locale VARCHAR(16) NOT NULL,
    translation_key VARCHAR(191) NOT NULL,
    value TEXT NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (locale, translation_key),
    KEY idx_translation_overrides_updated_by (updated_by),
    CONSTRAINT fk_translation_overrides_locale FOREIGN KEY (locale) REFERENCES languages (code) ON DELETE CASCADE,
    CONSTRAINT fk_translation_overrides_user FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_preferences (
    user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    admin_locale VARCHAR(16) NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_preferences_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO languages (code, name, native_name, direction, fallback_locale, is_enabled, sort_order, created_at, updated_at)
VALUES
    ('ar', 'Arabic', 'العربية', 'rtl', 'en', 1, 10, NOW(), NOW()),
    ('en', 'English', 'English', 'ltr', NULL, 1, 20, NOW(), NOW());

INSERT IGNORE INTO settings (`key`, value, type, is_secret, created_at, updated_at)
VALUES
    ('site.default_locale', 'ar', 'string', 0, NOW(), NOW()),
    ('admin.default_locale', 'ar', 'string', 0, NOW(), NOW()),
    ('site.language_switcher_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('site.browser_locale_detection', '0', 'boolean', 0, NOW(), NOW());
