-- FAMILY360 Phase 2 migration. Additive only: existing data is not changed.
-- Back up the database first (phpMyAdmin > Export), then import this file.

SET NAMES utf8mb4;

-- Already exists from Phase 1; created here only if missing.
CREATE TABLE IF NOT EXISTS budgets (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id  INT UNSIGNED  NOT NULL,
    category_id   INT UNSIGNED  NOT NULL,
    period        ENUM('weekly','monthly') NOT NULL DEFAULT 'monthly',
    amount        DECIMAL(14,2) NOT NULL,
    starts_on     DATE          NOT NULL,
    ends_on       DATE          NULL,
    created_by    INT UNSIGNED  NOT NULL,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_budget_household (household_id),
    CONSTRAINT fk_budget_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_budget_category  FOREIGN KEY (category_id) REFERENCES categories (id),
    CONSTRAINT fk_budget_user      FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS savings_goals (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id       INT UNSIGNED  NOT NULL,
    name               VARCHAR(120)  NOT NULL,
    description        VARCHAR(500)  NULL,
    target_amount      DECIMAL(14,2) NOT NULL,
    deadline           DATE          NULL,
    priority           ENUM('high','medium','low') NOT NULL DEFAULT 'medium',
    frequency          ENUM('weekly','monthly','once') NOT NULL DEFAULT 'monthly',
    assigned_user_id   INT UNSIGNED  NULL,
    status             ENUM('active','paused','completed','cancelled') NOT NULL DEFAULT 'active',
    created_by         INT UNSIGNED  NOT NULL,
    created_at         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_goal_household (household_id, status),
    CONSTRAINT fk_goal_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_goal_assigned  FOREIGN KEY (assigned_user_id) REFERENCES users (id),
    CONSTRAINT fk_goal_created   FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS goal_contributions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id         INT UNSIGNED  NOT NULL,
    household_id    INT UNSIGNED  NOT NULL,
    amount          DECIMAL(14,2) NOT NULL,
    contributed_on  DATE          NOT NULL,
    recorded_by     INT UNSIGNED  NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_contrib_goal (goal_id),
    CONSTRAINT fk_contrib_goal      FOREIGN KEY (goal_id) REFERENCES savings_goals (id),
    CONSTRAINT fk_contrib_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_contrib_user      FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rent_plans (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id    INT UNSIGNED  NOT NULL,
    monthly_rent    DECIMAL(14,2) NOT NULL,
    annual_target   DECIMAL(14,2) NOT NULL,
    landlord_name   VARCHAR(120)  NULL,
    property_name   VARCHAR(160)  NULL,
    due_day         TINYINT UNSIGNED NOT NULL DEFAULT 1,
    cycle_end       DATE          NOT NULL,
    updated_by      INT UNSIGNED  NOT NULL,
    updated_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_rent_household (household_id),
    CONSTRAINT fk_rent_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_rent_user      FOREIGN KEY (updated_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rent_reserve_entries (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id  INT UNSIGNED  NOT NULL,
    amount        DECIMAL(14,2) NOT NULL,
    entry_date    DATE          NOT NULL,
    note          VARCHAR(255)  NULL,
    recorded_by   INT UNSIGNED  NOT NULL,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_rent_res_household (household_id, entry_date),
    CONSTRAINT fk_rent_res_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_rent_res_user      FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;