-- Phase 9 — Income & Monthly Savings
-- Run against a database that already has migrations 001-003 applied.
-- Adds the incomes table (same shape as expenses minus payment_method_id)
-- and seeds the five income categories.

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS incomes (
    id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    income_date   DATE          NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    category_id   INT UNSIGNED  NOT NULL,
    user_id       INT UNSIGNED  NOT NULL,
    description   VARCHAR(255)  NULL,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_incomes_date (income_date),
    KEY idx_incomes_category (category_id),
    KEY idx_incomes_user (user_id),
    CONSTRAINT fk_incomes_category
        FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT,
    CONSTRAINT fk_incomes_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO categories (name, type, color, sort_order, is_active) VALUES
    ('Paycheck', 'income', '#178a50', 1, 1),
    ('Bonus',    'income', '#d9a62e', 2, 1),
    ('Interest', 'income', '#33a9c7', 3, 1),
    ('Savings',  'income', '#5a5bd8', 4, 1),
    ('Other',    'income', '#98a2b3', 5, 1);

SET FOREIGN_KEY_CHECKS = 1;
