-- ═══════════════════════════════════════════════════════════════
-- Migration 003 — Owner Payout System
-- ═══════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ─── Payout Accounts (one per owner) ────────────────────────────
CREATE TABLE IF NOT EXISTS payout_accounts (
    id                       CHAR(36)      NOT NULL DEFAULT (UUID()),
    tenant_id                CHAR(36)      NOT NULL,
    user_id                  CHAR(36)      NOT NULL,
    account_holder_name      VARCHAR(255)  NULL,
    bank_name                VARCHAR(255)  NULL,
    routing_number           VARCHAR(9)    NULL,
    account_number_encrypted TEXT          NULL,
    account_number_last4     VARCHAR(4)    NULL,
    account_type             ENUM('checking','savings') NOT NULL DEFAULT 'checking',
    w9_status                ENUM('not_submitted','pending_review','approved','rejected') NOT NULL DEFAULT 'not_submitted',
    w9_file_path             VARCHAR(500)  NULL,
    w9_submitted_at          DATETIME      NULL,
    w9_reviewed_at           DATETIME      NULL,
    w9_reviewed_by           CHAR(36)      NULL,
    w9_reject_reason         TEXT          NULL,
    pending_balance          DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_earned             DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_paid_out           DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at               DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_payout_accounts_user (user_id),
    INDEX idx_pa_tenant (tenant_id),
    INDEX idx_pa_w9 (w9_status),
    CONSTRAINT fk_pa_user   FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Payouts (each admin disbursement) ──────────────────────────
CREATE TABLE IF NOT EXISTS payouts (
    id             CHAR(36)      NOT NULL DEFAULT (UUID()),
    tenant_id      CHAR(36)      NOT NULL,
    user_id        CHAR(36)      NOT NULL,
    amount         DECIMAL(12,2) NOT NULL,
    status         ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
    bank_name      VARCHAR(255)  NULL,
    account_last4  VARCHAR(4)    NULL,
    notes          TEXT          NULL,
    processed_by   CHAR(36)      NULL,
    requested_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at   DATETIME      NULL,
    PRIMARY KEY (id),
    INDEX idx_payouts_user   (user_id),
    INDEX idx_payouts_status (status),
    INDEX idx_payouts_tenant (tenant_id),
    CONSTRAINT fk_payouts_user   FOREIGN KEY (user_id)   REFERENCES users(id),
    CONSTRAINT fk_payouts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Alter payments: add owner_id to payout records ─────────────
ALTER TABLE payments
    ADD COLUMN IF NOT EXISTS owner_id CHAR(36) NULL AFTER payer_id,
    ADD INDEX IF NOT EXISTS idx_payments_owner (owner_id);

SET foreign_key_checks = 1;
