-- Life Analytics - production schema update for MySQL 8 / OVH.
-- Target: the schema represented by baptisvlife.sql before the 2026-07-24 update.
--
-- IMPORTANT:
-- 1. Back up the production database before importing this file.
-- 2. Import this file only once.
-- 3. Then import 2026_07_24_adapt_production_data.sql.
-- 4. DDL statements auto-commit in MySQL and cannot be rolled back as one unit.

ALTER TABLE users
    ADD COLUMN locale VARCHAR(5) NOT NULL DEFAULT 'en' AFTER email,
    ADD COLUMN timezone VARCHAR(64) NOT NULL DEFAULT 'UTC' AFTER locale;

ALTER TABLE scoring_settings
    ADD COLUMN active_hours_target DECIMAL(5, 2) NOT NULL DEFAULT 8.00 AFTER user_id,
    ADD COLUMN goals_share_percent TINYINT UNSIGNED NOT NULL DEFAULT 30 AFTER active_hours_target;

CREATE TABLE activity_social_contexts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    multiplier DECIMAL(5, 2) NOT NULL DEFAULT 1.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY activity_social_contexts_user_id_slug_unique (user_id, slug),
    CONSTRAINT activity_social_contexts_user_id_foreign
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE activity_sessions
    ADD COLUMN activity_social_context_id BIGINT UNSIGNED NULL AFTER with_friend_or_family,
    ADD KEY activity_sessions_activity_social_context_id_foreign (activity_social_context_id),
    ADD CONSTRAINT activity_sessions_activity_social_context_id_foreign
        FOREIGN KEY (activity_social_context_id)
        REFERENCES activity_social_contexts (id)
        ON DELETE SET NULL;

ALTER TABLE goals
    ADD COLUMN activity_social_context_id BIGINT UNSIGNED NULL AFTER activity_type_id,
    ADD KEY goals_activity_social_context_id_foreign (activity_social_context_id),
    ADD CONSTRAINT goals_activity_social_context_id_foreign
        FOREIGN KEY (activity_social_context_id)
        REFERENCES activity_social_contexts (id)
        ON DELETE SET NULL;

CREATE TABLE goal_schedule_days (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    goal_id BIGINT UNSIGNED NOT NULL,
    period_day TINYINT UNSIGNED NOT NULL,
    expected_progress DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY goal_schedule_days_goal_id_period_day_unique (goal_id, period_day),
    CONSTRAINT goal_schedule_days_goal_id_foreign
        FOREIGN KEY (goal_id) REFERENCES goals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE daily_score_results
    MODIFY COLUMN final_score DECIMAL(10, 2) NULL DEFAULT NULL;

CREATE TABLE manual_database_updates (
    update_key VARCHAR(191) NOT NULL,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (update_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mark the equivalent Laravel migrations as applied so a later `artisan migrate`
-- does not try to recreate the manually installed schema.
SET @manual_migration_batch := (
    SELECT COALESCE(MAX(batch), 0) + 1
    FROM migrations
);

INSERT IGNORE INTO migrations (migration, batch) VALUES
    ('2026_07_24_000002_normalize_daily_scoring_and_add_social_contexts', @manual_migration_batch),
    ('2026_07_24_000003_add_locale_and_timezone_to_users_table', @manual_migration_batch);
