-- Life Goals Onboarding Prototype - MySQL schema
-- Applied automatically by app/public/setup.php via PDO. See
-- design docs/prototype_design_decisions.md for the reasoning behind this
-- shape (deliberately simpler than the full backend design doc).

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_active_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    brief_count INT UNSIGNED NOT NULL DEFAULT 0,
    detail_count INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS life_areas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    area_key VARCHAR(64) NOT NULL,
    label VARCHAR(255) NOT NULL,
    review_status VARCHAR(32) NOT NULL DEFAULT 'not_reviewed',
        -- not_reviewed | strong | stable | needs_attention | important_opportunity | skipped
    attention_level VARCHAR(32),
        -- a_lot | some | very_little | not_sure | skip
    notes TEXT,
    last_reviewed_at DATETIME NULL,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_user_area (user_id, area_key),
    CONSTRAINT fk_life_areas_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS goals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(32),
        -- repair | improve | build | maintain | explore | later
    life_area_id INT UNSIGNED NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
        -- draft | explore | ready | active | maintain | paused | completed | closed
    priority VARCHAR(32),
        -- major | supporting | maintenance | later
    why_it_matters TEXT,
    target_date VARCHAR(64),
    progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
    source VARCHAR(32) NOT NULL DEFAULT 'onboarding',
        -- onboarding | normal
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_goals_user_status (user_id, status),
    CONSTRAINT fk_goals_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_goals_life_area FOREIGN KEY (life_area_id) REFERENCES life_areas(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS actions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id INT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'open',
        -- open | completed
    due_date VARCHAR(64),
    sort_order INT NOT NULL DEFAULT 0,
    is_next TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    KEY idx_actions_goal (goal_id),
    CONSTRAINT fk_actions_goal FOREIGN KEY (goal_id) REFERENCES goals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS accomplishments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    type VARCHAR(32),
    related_goal_id INT UNSIGNED NULL,
    related_step_key VARCHAR(64),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_accomplishments_user (user_id, created_at),
    CONSTRAINT fk_accomplishments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_accomplishments_goal FOREIGN KEY (related_goal_id) REFERENCES goals(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per user: where they currently are in the onboarding flow.
CREATE TABLE IF NOT EXISTS onboarding_state (
    user_id INT UNSIGNED PRIMARY KEY,
    current_stage_key VARCHAR(64) NOT NULL DEFAULT 'get_started',
    current_step_key VARCHAR(64) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'in_progress',
        -- in_progress | sufficiently_complete
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_onboarding_state_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per stage/step a user has completed or skipped, plus any answers.
CREATE TABLE IF NOT EXISTS onboarding_progress (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    stage_key VARCHAR(64) NOT NULL,
    step_key VARCHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'completed',
        -- completed | skipped
    answers_json TEXT,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_stage_step (user_id, stage_key, step_key),
    CONSTRAINT fk_onboarding_progress_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;