-- FinPulse Database Schema — MySQL
-- Import this file via phpMyAdmin: select docuralk_finpulse → Import → choose this file

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ============================================================
-- AUTH
-- ============================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id`             VARCHAR(191) NOT NULL,
  `name`           VARCHAR(191)          NULL,
  `email`          VARCHAR(191) NOT NULL,
  `emailVerified`  DATETIME(3)           NULL,
  `image`          VARCHAR(191)          NULL,
  `passwordHash`   VARCHAR(191)          NULL,
  `mfaEnabled`     TINYINT(1)   NOT NULL DEFAULT 0,
  `mfaSecret`      VARCHAR(191)          NULL,
  `mfaBackupCodes` JSON         NOT NULL,
  `currency`       VARCHAR(191) NOT NULL DEFAULT 'USD',
  `timezone`       VARCHAR(191) NOT NULL DEFAULT 'UTC',
  `createdAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_key` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `accounts` (
  `id`                VARCHAR(191) NOT NULL,
  `userId`            VARCHAR(191) NOT NULL,
  `type`              VARCHAR(191) NOT NULL,
  `provider`          VARCHAR(191) NOT NULL,
  `providerAccountId` VARCHAR(191) NOT NULL,
  `refresh_token`     TEXT                  NULL,
  `access_token`      TEXT                  NULL,
  `expires_at`        INT                   NULL,
  `token_type`        VARCHAR(191)          NULL,
  `scope`             VARCHAR(191)          NULL,
  `id_token`          TEXT                  NULL,
  `session_state`     VARCHAR(191)          NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_provider_providerAccountId_key` (`provider`, `providerAccountId`),
  INDEX `accounts_userId_fkey` (`userId`),
  CONSTRAINT `accounts_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
  `id`           VARCHAR(191) NOT NULL,
  `sessionToken` VARCHAR(191) NOT NULL,
  `userId`       VARCHAR(191) NOT NULL,
  `expires`      DATETIME(3)  NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sessions_sessionToken_key` (`sessionToken`),
  INDEX `sessions_userId_fkey` (`userId`),
  CONSTRAINT `sessions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `verification_tokens` (
  `identifier` VARCHAR(191) NOT NULL,
  `token`      VARCHAR(191) NOT NULL,
  `expires`    DATETIME(3)  NOT NULL,
  UNIQUE KEY `verification_tokens_token_key` (`token`),
  UNIQUE KEY `verification_tokens_identifier_token_key` (`identifier`, `token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BANK ACCOUNTS
-- ============================================================

CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id`                VARCHAR(191)                                           NOT NULL,
  `userId`            VARCHAR(191)                                           NOT NULL,
  `name`              VARCHAR(191)                                           NOT NULL,
  `type`              ENUM('CHECKING','SAVINGS','CASH','WALLET','INVESTMENT','OTHER') NOT NULL,
  `balanceCents`      INT          NOT NULL DEFAULT 0,
  `currency`          VARCHAR(191) NOT NULL DEFAULT 'USD',
  `institutionName`   VARCHAR(191)          NULL,
  `lastFourDigits`    CHAR(4)               NULL,
  `color`             VARCHAR(191)          NULL,
  `icon`              VARCHAR(191)          NULL,
  `isArchived`        TINYINT(1)   NOT NULL DEFAULT 0,
  `includeInNetWorth` TINYINT(1)   NOT NULL DEFAULT 1,
  `createdAt`         DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`         DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `bank_accounts_userId_idx` (`userId`),
  CONSTRAINT `bank_accounts_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CATEGORIES
-- ============================================================

CREATE TABLE IF NOT EXISTS `categories` (
  `id`         VARCHAR(191)           NOT NULL,
  `userId`     VARCHAR(191)               NULL,
  `name`       VARCHAR(191)           NOT NULL,
  `type`       ENUM('INCOME','EXPENSE') NOT NULL,
  `icon`       VARCHAR(191)               NULL,
  `color`      VARCHAR(191)               NULL,
  `parentId`   VARCHAR(191)               NULL,
  `isSystem`   TINYINT(1)   NOT NULL DEFAULT 0,
  `isArchived` TINYINT(1)   NOT NULL DEFAULT 0,
  `sortOrder`  INT          NOT NULL DEFAULT 0,
  `createdAt`  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `categories_userId_idx` (`userId`),
  INDEX `categories_type_idx` (`type`),
  INDEX `categories_parentId_fkey` (`parentId`),
  CONSTRAINT `categories_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `categories_parentId_fkey` FOREIGN KEY (`parentId`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CREDIT CARDS
-- ============================================================

CREATE TABLE IF NOT EXISTS `credit_cards` (
  `id`                      VARCHAR(191) NOT NULL,
  `userId`                  VARCHAR(191) NOT NULL,
  `name`                    VARCHAR(191) NOT NULL,
  `lastFourDigits`          CHAR(4)               NULL,
  `creditLimitCents`        INT          NOT NULL,
  `statementDayOfMonth`     INT          NOT NULL,
  `paymentDueDayOffset`     INT          NOT NULL DEFAULT 21,
  `minimumPaymentPct`       DOUBLE       NOT NULL DEFAULT 0.02,
  `minimumPaymentFlatCents` INT          NOT NULL DEFAULT 2500,
  `interestRateBps`         INT          NOT NULL,
  `currency`                VARCHAR(191) NOT NULL DEFAULT 'USD',
  `color`                   VARCHAR(191)          NULL,
  `isArchived`              TINYINT(1)   NOT NULL DEFAULT 0,
  `createdAt`               DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`               DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `credit_cards_userId_idx` (`userId`),
  CONSTRAINT `credit_cards_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `credit_card_statements` (
  `id`              VARCHAR(191) NOT NULL,
  `creditCardId`    VARCHAR(191) NOT NULL,
  `statementDate`   DATETIME(3)  NOT NULL,
  `dueDate`         DATETIME(3)  NOT NULL,
  `openingBalance`  INT          NOT NULL,
  `closingBalance`  INT          NOT NULL,
  `minimumPayment`  INT          NOT NULL,
  `isPaid`          TINYINT(1)   NOT NULL DEFAULT 0,
  `paidAt`          DATETIME(3)           NULL,
  `paidAmountCents` INT                   NULL,
  `createdAt`       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `credit_card_statements_creditCardId_idx` (`creditCardId`),
  CONSTRAINT `credit_card_statements_creditCardId_fkey` FOREIGN KEY (`creditCardId`) REFERENCES `credit_cards` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOANS
-- ============================================================

CREATE TABLE IF NOT EXISTS `loans` (
  `id`               VARCHAR(191)                                              NOT NULL,
  `userId`           VARCHAR(191)                                              NOT NULL,
  `name`             VARCHAR(191)                                              NOT NULL,
  `type`             ENUM('PERSONAL','HOME_MORTGAGE','AUTO','STUDENT','BUSINESS','OTHER') NOT NULL,
  `lenderName`       VARCHAR(191)          NULL,
  `principalCents`   INT          NOT NULL,
  `outstandingCents` INT          NOT NULL,
  `annualRateBps`    INT          NOT NULL,
  `termMonths`       INT          NOT NULL,
  `emiDayOfMonth`    INT          NOT NULL,
  `startDate`        DATETIME(3)  NOT NULL,
  `firstEmiDate`     DATETIME(3)  NOT NULL,
  `currency`         VARCHAR(191) NOT NULL DEFAULT 'USD',
  `notes`            TEXT                  NULL,
  `isActive`         TINYINT(1)   NOT NULL DEFAULT 1,
  `createdAt`        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `loans_userId_idx` (`userId`),
  CONSTRAINT `loans_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loan_payments` (
  `id`                  VARCHAR(191) NOT NULL,
  `loanId`              VARCHAR(191) NOT NULL,
  `installmentNumber`   INT          NOT NULL,
  `dueDate`             DATETIME(3)  NOT NULL,
  `emiCents`            INT          NOT NULL,
  `principalCents`      INT          NOT NULL,
  `interestCents`       INT          NOT NULL,
  `openingBalanceCents` INT          NOT NULL,
  `closingBalanceCents` INT          NOT NULL,
  `isPaid`              TINYINT(1)   NOT NULL DEFAULT 0,
  `paidAt`              DATETIME(3)           NULL,
  `paidAmountCents`     INT                   NULL,
  `createdAt`           DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`           DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_payments_loanId_installmentNumber_key` (`loanId`, `installmentNumber`),
  INDEX `loan_payments_loanId_idx` (`loanId`),
  INDEX `loan_payments_dueDate_idx` (`dueDate`),
  CONSTRAINT `loan_payments_loanId_fkey` FOREIGN KEY (`loanId`) REFERENCES `loans` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RECURRING TRANSACTIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `recurring_transactions` (
  `id`            VARCHAR(191)                                                     NOT NULL,
  `userId`        VARCHAR(191)                                                     NOT NULL,
  `bankAccountId` VARCHAR(191)                                                     NOT NULL,
  `categoryId`    VARCHAR(191)                                                         NULL,
  `type`          ENUM('INCOME','EXPENSE','TRANSFER')                              NOT NULL,
  `amountCents`   INT          NOT NULL,
  `currency`      VARCHAR(191) NOT NULL DEFAULT 'USD',
  `description`   VARCHAR(191) NOT NULL,
  `notes`         TEXT                  NULL,
  `frequency`     ENUM('DAILY','WEEKLY','BIWEEKLY','MONTHLY','QUARTERLY','YEARLY') NOT NULL,
  `startDate`     DATETIME(3)  NOT NULL,
  `endDate`       DATETIME(3)           NULL,
  `nextDueDate`   DATETIME(3)  NOT NULL,
  `lastProcessed` DATETIME(3)           NULL,
  `isActive`      TINYINT(1)   NOT NULL DEFAULT 1,
  `icon`          VARCHAR(191)          NULL,
  `color`         VARCHAR(191)          NULL,
  `merchantName`  VARCHAR(191)          NULL,
  `billingUrl`    VARCHAR(191)          NULL,
  `reminderDays`  INT          NOT NULL DEFAULT 0,
  `createdAt`     DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`     DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `recurring_transactions_userId_idx` (`userId`),
  INDEX `recurring_transactions_userId_isActive_idx` (`userId`, `isActive`),
  INDEX `recurring_transactions_nextDueDate_isActive_idx` (`nextDueDate`, `isActive`),
  INDEX `recurring_transactions_bankAccountId_fkey` (`bankAccountId`),
  INDEX `recurring_transactions_categoryId_fkey` (`categoryId`),
  CONSTRAINT `recurring_transactions_userId_fkey`        FOREIGN KEY (`userId`)        REFERENCES `users`         (`id`) ON DELETE CASCADE  ON UPDATE CASCADE,
  CONSTRAINT `recurring_transactions_bankAccountId_fkey` FOREIGN KEY (`bankAccountId`) REFERENCES `bank_accounts` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `recurring_transactions_categoryId_fkey`    FOREIGN KEY (`categoryId`)    REFERENCES `categories`    (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRANSACTIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `transactions` (
  `id`                     VARCHAR(191)                                                          NOT NULL,
  `userId`                 VARCHAR(191)                                                          NOT NULL,
  `bankAccountId`          VARCHAR(191)                                                          NOT NULL,
  `categoryId`             VARCHAR(191)                                                              NULL,
  `type`                   ENUM('INCOME','EXPENSE','TRANSFER')                                   NOT NULL,
  `amountCents`            INT          NOT NULL,
  `currency`               VARCHAR(191) NOT NULL DEFAULT 'USD',
  `description`            VARCHAR(191) NOT NULL,
  `notes`                  TEXT                  NULL,
  `date`                   DATETIME(3)  NOT NULL,
  `isRecurring`            TINYINT(1)   NOT NULL DEFAULT 0,
  `recurrenceId`           VARCHAR(191)          NULL,
  `recurrenceFreq`         ENUM('DAILY','WEEKLY','BIWEEKLY','MONTHLY','QUARTERLY','YEARLY')      NULL,
  `recurrenceEnd`          DATETIME(3)           NULL,
  `nextOccurrence`         DATETIME(3)           NULL,
  `recurringTransactionId` VARCHAR(191)          NULL,
  `transferToAccountId`    VARCHAR(191)          NULL,
  `creditCardId`           VARCHAR(191)          NULL,
  `tags`                   JSON         NOT NULL,
  `attachmentUrl`          VARCHAR(191)          NULL,
  `isVerified`             TINYINT(1)   NOT NULL DEFAULT 0,
  `deletedAt`              DATETIME(3)           NULL,
  `createdAt`              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `transactions_userId_idx` (`userId`),
  INDEX `transactions_userId_date_idx` (`userId`, `date`),
  INDEX `transactions_userId_type_idx` (`userId`, `type`),
  INDEX `transactions_recurrenceId_idx` (`recurrenceId`),
  INDEX `transactions_recurringTransactionId_idx` (`recurringTransactionId`),
  INDEX `transactions_bankAccountId_idx` (`bankAccountId`),
  INDEX `transactions_categoryId_fkey` (`categoryId`),
  INDEX `transactions_creditCardId_fkey` (`creditCardId`),
  CONSTRAINT `transactions_userId_fkey`                 FOREIGN KEY (`userId`)                 REFERENCES `users`                  (`id`) ON DELETE CASCADE  ON UPDATE CASCADE,
  CONSTRAINT `transactions_bankAccountId_fkey`          FOREIGN KEY (`bankAccountId`)          REFERENCES `bank_accounts`          (`id`) ON UPDATE CASCADE,
  CONSTRAINT `transactions_categoryId_fkey`             FOREIGN KEY (`categoryId`)             REFERENCES `categories`             (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transactions_creditCardId_fkey`           FOREIGN KEY (`creditCardId`)           REFERENCES `credit_cards`           (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transactions_recurringTransactionId_fkey` FOREIGN KEY (`recurringTransactionId`) REFERENCES `recurring_transactions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BUDGETS
-- ============================================================

CREATE TABLE IF NOT EXISTS `budgets` (
  `id`         VARCHAR(191) NOT NULL,
  `userId`     VARCHAR(191) NOT NULL,
  `name`       VARCHAR(191) NOT NULL,
  `startMonth` INT          NOT NULL,
  `startYear`  INT          NOT NULL,
  `isRolling`  TINYINT(1)   NOT NULL DEFAULT 1,
  `currency`   VARCHAR(191) NOT NULL DEFAULT 'USD',
  `createdAt`  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `budgets_userId_idx` (`userId`),
  CONSTRAINT `budgets_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `budget_periods` (
  `id`        VARCHAR(191)           NOT NULL,
  `budgetId`  VARCHAR(191)           NOT NULL,
  `month`     INT                    NOT NULL,
  `year`      INT                    NOT NULL,
  `status`    ENUM('ACTIVE','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  `createdAt` DATETIME(3)            NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3)            NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `budget_periods_budgetId_month_year_key` (`budgetId`, `month`, `year`),
  INDEX `budget_periods_budgetId_idx` (`budgetId`),
  CONSTRAINT `budget_periods_budgetId_fkey` FOREIGN KEY (`budgetId`) REFERENCES `budgets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `budget_lines` (
  `id`             VARCHAR(191) NOT NULL,
  `budgetId`       VARCHAR(191) NOT NULL,
  `categoryId`     VARCHAR(191) NOT NULL,
  `allocatedCents` INT          NOT NULL,
  `alertAt80`      TINYINT(1)   NOT NULL DEFAULT 1,
  `alertAt100`     TINYINT(1)   NOT NULL DEFAULT 1,
  `createdAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `budget_lines_budgetId_categoryId_key` (`budgetId`, `categoryId`),
  INDEX `budget_lines_budgetId_idx` (`budgetId`),
  INDEX `budget_lines_categoryId_fkey` (`categoryId`),
  CONSTRAINT `budget_lines_budgetId_fkey`   FOREIGN KEY (`budgetId`)   REFERENCES `budgets`    (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `budget_lines_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `budget_alerts` (
  `id`             VARCHAR(191)                             NOT NULL,
  `budgetPeriodId` VARCHAR(191)                             NOT NULL,
  `categoryId`     VARCHAR(191)                             NOT NULL,
  `threshold`      ENUM('EIGHTY_PERCENT','ONE_HUNDRED_PERCENT') NOT NULL,
  `triggeredAt`    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `acknowledgedAt` DATETIME(3)           NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `budget_alerts_budgetPeriodId_categoryId_threshold_key` (`budgetPeriodId`, `categoryId`, `threshold`),
  INDEX `budget_alerts_budgetPeriodId_fkey` (`budgetPeriodId`),
  CONSTRAINT `budget_alerts_budgetPeriodId_fkey` FOREIGN KEY (`budgetPeriodId`) REFERENCES `budget_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- IOU LEDGER
-- ============================================================

CREATE TABLE IF NOT EXISTS `ious` (
  `id`                VARCHAR(191)                                           NOT NULL,
  `userId`            VARCHAR(191)                                           NOT NULL,
  `direction`         ENUM('I_OWE','THEY_OWE')                              NOT NULL,
  `counterpartyName`  VARCHAR(191)                                           NOT NULL,
  `counterpartyEmail` VARCHAR(191)                                               NULL,
  `description`       VARCHAR(191)                                           NOT NULL,
  `principalCents`    INT          NOT NULL,
  `remainingCents`    INT          NOT NULL,
  `currency`          VARCHAR(191) NOT NULL DEFAULT 'USD',
  `dueDate`           DATETIME(3)           NULL,
  `status`            ENUM('PENDING','PARTIALLY_PAID','SETTLED','WRITTEN_OFF') NOT NULL DEFAULT 'PENDING',
  `notes`             TEXT                  NULL,
  `settledAt`         DATETIME(3)           NULL,
  `deletedAt`         DATETIME(3)           NULL,
  `createdAt`         DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`         DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ious_userId_idx` (`userId`),
  INDEX `ious_userId_status_idx` (`userId`, `status`),
  CONSTRAINT `ious_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iou_payments` (
  `id`          VARCHAR(191) NOT NULL,
  `iouId`       VARCHAR(191) NOT NULL,
  `amountCents` INT          NOT NULL,
  `paidAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `notes`       VARCHAR(191)          NULL,
  `createdAt`   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `iou_payments_iouId_idx` (`iouId`),
  CONSTRAINT `iou_payments_iouId_fkey` FOREIGN KEY (`iouId`) REFERENCES `ious` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iou_reminders` (
  `id`             VARCHAR(191) NOT NULL,
  `iouId`          VARCHAR(191) NOT NULL,
  `userId`         VARCHAR(191) NOT NULL,
  `scheduledAt`    DATETIME(3)  NOT NULL,
  `sentAt`         DATETIME(3)           NULL,
  `recipientEmail` VARCHAR(191) NOT NULL,
  `reminderType`   VARCHAR(191) NOT NULL DEFAULT 'DUE_DATE',
  `isSent`         TINYINT(1)   NOT NULL DEFAULT 0,
  `createdAt`      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `iou_reminders_iouId_idx` (`iouId`),
  INDEX `iou_reminders_scheduledAt_isSent_idx` (`scheduledAt`, `isSent`),
  INDEX `iou_reminders_userId_fkey` (`userId`),
  CONSTRAINT `iou_reminders_iouId_fkey`  FOREIGN KEY (`iouId`)  REFERENCES `ious`  (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `iou_reminders_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ASSETS
-- ============================================================

CREATE TABLE IF NOT EXISTS `assets` (
  `id`                 VARCHAR(191)                                                                                                   NOT NULL,
  `userId`             VARCHAR(191)                                                                                                   NOT NULL,
  `name`               VARCHAR(191)                                                                                                   NOT NULL,
  `type`               ENUM('REAL_ESTATE','VEHICLE','INVESTMENT','RETIREMENT','SAVINGS_BOND','CRYPTO','BUSINESS_EQUITY','PERSONAL_PROPERTY','OTHER') NOT NULL,
  `currentValueCents`  INT          NOT NULL,
  `currency`           VARCHAR(191) NOT NULL DEFAULT 'USD',
  `purchasePriceCents` INT                   NULL,
  `purchaseDate`       DATETIME(3)           NULL,
  `notes`              VARCHAR(191)          NULL,
  `includeInNetWorth`  TINYINT(1)   NOT NULL DEFAULT 1,
  `lastUpdated`        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `createdAt`          DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt`          DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `assets_userId_idx` (`userId`),
  CONSTRAINT `assets_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;