-- Run this once in phpMyAdmin if you cannot run Laravel migrations on shared hosting.
-- If the status column already exists on savings_transactions, skip the ALTER TABLE block.

ALTER TABLE savings_transactions
    ADD status VARCHAR(30) NOT NULL DEFAULT 'posted' AFTER method;

CREATE INDEX savings_transactions_status_index ON savings_transactions(status);

UPDATE savings_accounts
SET status = 'active',
    opened_at = COALESCE(opened_at, created_at),
    approved_at = COALESCE(approved_at, opened_at, created_at)
WHERE status = 'pending';

INSERT INTO investment_transactions (
    tenant_id,
    investment_account_id,
    customer_id,
    created_by,
    type,
    amount,
    balance_before,
    balance_after,
    reference,
    method,
    status,
    description,
    transacted_at,
    created_at,
    updated_at
)
SELECT
    ia.tenant_id,
    ia.id,
    ia.customer_id,
    ia.approved_by,
    'deposit',
    ia.amount,
    0,
    0,
    CONCAT('INVDEP-MIG-', ia.id),
    'cash',
    'pending',
    'Initial investment deposit awaiting approval',
    NOW(),
    NOW(),
    NOW()
FROM investment_accounts ia
WHERE ia.status = 'pending'
    AND ia.amount > 0
    AND NOT EXISTS (
        SELECT 1
        FROM investment_transactions it
        WHERE it.investment_account_id = ia.id
            AND it.type = 'deposit'
    );

UPDATE investment_accounts
SET status = 'active',
    amount = 0,
    balance = 0,
    expected_return = 0,
    opened_at = COALESCE(opened_at, created_at),
    approved_at = COALESCE(approved_at, opened_at, created_at)
WHERE status = 'pending';
