CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(64) NOT NULL,
    title VARCHAR(191) NOT NULL,
    body TEXT NULL,
    url VARCHAR(500) NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notifications_user_read (user_id, read_at, created_at),
    KEY idx_notifications_type_created (type, created_at),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(191) NOT NULL,
    subject VARCHAR(191) NULL,
    message TEXT NOT NULL,
    locale VARCHAR(16) NOT NULL DEFAULT 'ar',
    status VARCHAR(32) NOT NULL DEFAULT 'open',
    admin_notes TEXT NULL,
    ip_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_contact_messages_status_created (status, created_at),
    KEY idx_contact_messages_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO settings (`key`, value, type, is_secret, created_at, updated_at)
VALUES
    ('membership.registration_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('membership.require_email_verification', '0', 'boolean', 0, NOW(), NOW()),
    ('maintenance.enabled', '0', 'boolean', 0, NOW(), NOW()),
    ('maintenance.message', '', 'string', 0, NOW(), NOW()),
    ('contact.enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.database_enabled', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.email_enabled', '0', 'boolean', 0, NOW(), NOW()),
    ('notifications.admin_email', '', 'string', 0, NOW(), NOW()),
    ('notifications.telegram_enabled', '0', 'boolean', 0, NOW(), NOW()),
    ('notifications.telegram_bot_token', '', 'string', 1, NOW(), NOW()),
    ('notifications.telegram_chat_id', '', 'string', 1, NOW(), NOW()),
    ('notifications.new_question', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.new_report', '1', 'boolean', 0, NOW(), NOW()),
    ('notifications.contact_message', '1', 'boolean', 0, NOW(), NOW());
