-- FAMILY360 Phase 3 migration.
-- Run ONCE, after migrate_phase2.sql. Back up the database first.
-- Note: the two ALTER TABLE statements at the bottom will fail if re-run.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS projects (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id    INT UNSIGNED  NOT NULL,
    title           VARCHAR(160)  NOT NULL,
    description     VARCHAR(500)  NULL,
    estimated_cost  DECIMAL(14,2) NOT NULL,
    start_date      DATE          NULL,
    target_date     DATE          NULL,
    priority        ENUM('high','medium','low') NOT NULL DEFAULT 'medium',
    status          ENUM('planned','active','on_hold','completed','cancelled') NOT NULL DEFAULT 'planned',
    created_by      INT UNSIGNED  NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_project_household (household_id, status),
    CONSTRAINT fk_project_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_project_user      FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS project_milestones (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id  INT UNSIGNED NOT NULL,
    title       VARCHAR(160) NOT NULL,
    due_date    DATE         NULL,
    done        TINYINT(1)   NOT NULL DEFAULT 0,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_milestone_project (project_id),
    CONSTRAINT fk_milestone_project FOREIGN KEY (project_id) REFERENCES projects (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rent_payments (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id    INT UNSIGNED  NOT NULL,
    for_month       DATE          NOT NULL,
    amount          DECIMAL(14,2) NOT NULL,
    paid_on         DATE          NOT NULL,
    payment_method  VARCHAR(40)   NULL,
    transaction_id  BIGINT UNSIGNED NULL,
    recorded_by     INT UNSIGNED  NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_rent_pay_household (household_id, for_month),
    CONSTRAINT fk_rent_pay_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_rent_pay_tx        FOREIGN KEY (transaction_id) REFERENCES transactions (id),
    CONSTRAINT fk_rent_pay_user      FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS electricity_purchases (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id    INT UNSIGNED  NOT NULL,
    purchased_on    DATE          NOT NULL,
    amount          DECIMAL(14,2) NOT NULL,
    units           DECIMAL(12,2) NULL,
    token           VARCHAR(40)   NULL,
    meter_number    VARCHAR(40)   NULL,
    reference       VARCHAR(60)   NULL,
    transaction_id  BIGINT UNSIGNED NULL,
    recorded_by     INT UNSIGNED  NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_elec_token (household_id, token),
    UNIQUE KEY uq_elec_reference (household_id, reference),
    KEY idx_elec_date (household_id, purchased_on),
    CONSTRAINT fk_elec_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_elec_tx        FOREIGN KEY (transaction_id) REFERENCES transactions (id),
    CONSTRAINT fk_elec_user      FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS electricity_readings (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id    INT UNSIGNED  NOT NULL,
    read_on         DATE          NOT NULL,
    units_remaining DECIMAL(12,2) NOT NULL,
    recorded_by     INT UNSIGNED  NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_elec_read (household_id, read_on),
    CONSTRAINT fk_read_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_read_user      FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    token_hash  CHAR(64)     NOT NULL,
    expires_at  DATETIME     NOT NULL,
    used_at     DATETIME     NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_reset_hash (token_hash),
    CONSTRAINT fk_reset_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extend existing tables (run once only)
ALTER TABLE rent_plans ADD COLUMN rent_start_month DATE NULL AFTER cycle_end;

ALTER TABLE transactions
    ADD COLUMN project_id INT UNSIGNED NULL AFTER private_owner_id,
    ADD KEY idx_tx_project (project_id),
    ADD CONSTRAINT fk_tx_project FOREIGN KEY (project_id) REFERENCES projects (id);