-- ============================================================================
-- Migration: monthly meal totals -> daily meal on/off chart
-- Run this ONCE on your existing database (via phpMyAdmin > SQL tab) if you
-- already imported the old schema.sql before this update.
--
-- Safe to run even if the `meals` table has data: it renames the old table
-- to `meals_old_backup` instead of deleting it, then creates the new
-- `meal_entries` table used by the redesigned meal chart pages.
-- ============================================================================

-- NOTE: this file only needs to run ONCE. If you already ran it before and
-- get "Table 'meals' doesn't exist", that just means this step is already
-- done — skip straight to migration_v3_features.sql.
RENAME TABLE meals TO meals_old_backup;

CREATE TABLE IF NOT EXISTS meal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    meal_date DATE NOT NULL,
    breakfast TINYINT(1) NOT NULL DEFAULT 0,
    lunch TINYINT(1) NOT NULL DEFAULT 0,
    dinner TINYINT(1) NOT NULL DEFAULT 0,
    total_meals TINYINT GENERATED ALWAYS AS (breakfast+lunch+dinner) STORED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_member_meal_date (member_id, meal_date),
    KEY idx_mess_date (mess_id, meal_date)
) ENGINE=InnoDB;

-- Once you've confirmed everything works, you may optionally delete the old
-- backup table:
-- DROP TABLE meals_old_backup;