QB/QBX

-- Optional reference: QB banker credit + loans (auto-created by resource on start).
-- finals_banking_credit / finals_banking_loans

CREATE TABLE IF NOT EXISTS `finals_banking_credit` (
  `citizenid` VARCHAR(50) NOT NULL,
  `score` INT NOT NULL DEFAULT 580,
  `loans_completed` INT NOT NULL DEFAULT 0,
  `loans_defaulted` INT NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finals_banking_loans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `borrower_citizenid` VARCHAR(50) NOT NULL,
  `credit_account_id` INT UNSIGNED NULL DEFAULT NULL,
  `banker_citizenid` VARCHAR(50) NOT NULL,
  `lending_society_job` VARCHAR(64) NULL DEFAULT NULL,
  `loan_type` VARCHAR(24) NOT NULL,
  `principal` INT NOT NULL,
  `total_due` INT NOT NULL,
  `balance_remaining` INT NOT NULL,
  `weekly_payment` INT NOT NULL,
  `periods_total` INT NOT NULL,
  `periods_paid` INT NOT NULL DEFAULT 0,
  `annual_rate` DECIMAL(8,4) NOT NULL,
  `missed_payments` INT NOT NULL DEFAULT 0,
  `loan_withdraw_locked` TINYINT(1) NOT NULL DEFAULT 0,
  `loan_release_requested` TINYINT(1) NOT NULL DEFAULT 0,
  `status` VARCHAR(16) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `next_due_unix` BIGINT UNSIGNED NOT NULL,
  `paid_off_unix` BIGINT UNSIGNED NULL DEFAULT NULL,
  `agreement_json` LONGTEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_borrower_status` (`borrower_citizenid`,`status`),
  KEY `idx_banker` (`banker_citizenid`),
  KEY `idx_due` (`status`,`next_due_unix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pending borrower signature before loan disburse (see `server/loan_pending_table.lua` / `banker_qb.lua`).
CREATE TABLE IF NOT EXISTS `finals_banking_loan_pending` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(64) NOT NULL,
  `borrower_citizenid` VARCHAR(64) NOT NULL,
  `banker_citizenid` VARCHAR(64) NOT NULL,
  `lending_society_job` VARCHAR(64) NULL DEFAULT NULL,
  `loan_type` VARCHAR(24) NOT NULL,
  `principal` INT NOT NULL,
  `term_weeks` INT NOT NULL,
  `annual_rate` DECIMAL(8,4) NOT NULL,
  `total_due` INT NOT NULL,
  `weekly_payment` INT NOT NULL,
  `periods_total` INT NOT NULL,
  `officer_name` VARCHAR(128) NOT NULL,
  `officer_signature` LONGTEXT NULL,
  `borrower_signature` LONGTEXT NULL,
  `borrower_signed_unix` BIGINT UNSIGNED NULL,
  `created_unix` BIGINT UNSIGNED NOT NULL,
  `expires_unix` BIGINT UNSIGNED NOT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_borrower` (`borrower_citizenid`,`status`),
  KEY `idx_expires` (`status`,`expires_unix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- QB / QBX: optional tables used by finals_banking when Config.Framework is qb (or auto-detected).
-- Bank balance still lives in `players.money` JSON (`bank`, `cash`) like qb.sql.

CREATE TABLE IF NOT EXISTS `finals_banking_qb_account_index` (
  `accountId` INT UNSIGNED NOT NULL,
  `citizenid` VARCHAR(50) NOT NULL,
  `label` VARCHAR(64) NOT NULL DEFAULT 'Primary',
  PRIMARY KEY (`accountId`),
  UNIQUE KEY `citizenid` (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finals_banking_qb_extra_accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `citizenid` VARCHAR(50) NOT NULL,
  `label` VARCHAR(64) NOT NULL,
  `balance` INT NOT NULL DEFAULT 0,
  `is_shared` TINYINT(1) NOT NULL DEFAULT 0,
  `product_type` VARCHAR(32) NOT NULL DEFAULT 'Savings',
  `purpose` VARCHAR(32) NOT NULL DEFAULT 'Personal',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_qb_extra_citizenid` (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finals_banking_qb_transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `citizenid` VARCHAR(50) NOT NULL,
  `amount` INT NOT NULL,
  `direction` ENUM('in','out') NOT NULL,
  `kind` VARCHAR(32) NOT NULL,
  `message` VARCHAR(255) DEFAULT NULL,
  `counterparty` VARCHAR(50) DEFAULT NULL,
  `balance_after` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cid_created` (`citizenid`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finals_banking_card_atm` (
  `account_id` BIGINT UNSIGNED NOT NULL,
  `atm_blocked` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: auto-created by resource if missing. Plain PK only (no FK).
-- Wizard fields (Savings/Checking, Personal/Business).

CREATE TABLE IF NOT EXISTS `finals_banking_ui` (
  `accountId`   INT UNSIGNED NOT NULL,
  `productType` VARCHAR(32)  NOT NULL DEFAULT 'Savings',
  `purpose`     VARCHAR(32)  NOT NULL DEFAULT 'Personal',
  PRIMARY KEY (`accountId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finals_banking_card_atm` (
  `account_id` BIGINT UNSIGNED NOT NULL,
  `atm_blocked` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    ['banker'] = {
        label = 'Bank',
        defaultDuty = true,
        offDutyPay = false,
        grades = {
            ['0'] = {
                name = 'Trainee',
                payment = 50,
            },
            ['1'] = {
                name = 'Officer',
                payment = 75,
            },
            ['2'] = {
                name = 'Senior Officer',
                payment = 100,
            },
            ['3'] = {
                name = 'Head',
                payment = 125,
            },
            ['4'] = {
                name = 'Assistant Manager',
                bankAuth = true,
                payment = 150,
            },
            ['5'] = {
                name = 'Branch Manager',
                isboss = true,
                bankAuth = true,
                payment = 175,
            },
        },
    },