-- User-owned meal journal. Photos are deliberately not stored in these rows.
-- Apply after migrations 001-004 with a verified database backup.

CREATE TABLE meal_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    client_meal_uuid CHAR(36) NOT NULL,
    name VARCHAR(180) NOT NULL,
    meal_type ENUM('breakfast', 'lunch', 'dinner', 'snack') NOT NULL,
    calories DECIMAL(8,2) NOT NULL,
    protein_grams DECIMAL(7,2) NOT NULL DEFAULT 0,
    carbohydrate_grams DECIMAL(7,2) NOT NULL DEFAULT 0,
    fat_grams DECIMAL(7,2) NOT NULL DEFAULT 0,
    consumed_at DATETIME NOT NULL,
    source ENUM('manual', 'photo_ai') NOT NULL DEFAULT 'manual',
    notes VARCHAR(1000) NOT NULL DEFAULT '',
    revision INT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_meal_user_client_uuid (user_id, client_meal_uuid),
    INDEX idx_meals_user_consumed (user_id, consumed_at DESC),
    CONSTRAINT fk_meals_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CHECK (calories >= 0 AND calories <= 10000),
    CHECK (protein_grams >= 0 AND protein_grams <= 1000),
    CHECK (carbohydrate_grams >= 0 AND carbohydrate_grams <= 1000),
    CHECK (fat_grams >= 0 AND fat_grams <= 1000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;