OX

-- 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;

-- Optional: auto-created by resource if missing. Plain PK only (no FK) so it works on any ox_core DB.
-- Wizard fields (Savings/Checking, Personal/Business) — ox `accounts.type` stays personal|shared|group.

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;

-- ============================================================================
-- finals_banking — ox_core banker job + society / company grade flags
-- ============================================================================
-- QB / QBX: `sql/qb/qb_shared_jobs_example.lua` (paste into `shared/jobs.lua`).
-- ESX: `sql/esx/esx_jobs_example.sql` (`jobs` / `job_grades` on the es_extended DB).
-- ============================================================================
-- Run on the **same database** ox_core uses. Tables `ox_groups` and `ox_group_grades`
-- are created by ox_core; this file only **alters** and **seeds**.
--
-- Setup:
--   1. Run this SQL once (if a step errors with “Duplicate column”, skip that ALTER).
--   2. Set `BankerConfig.JobName` in `banker_config.lua` to the same name as below (`banker`).
--   3. Assign the job in-game (`character_groups`), e.g. grade `5` for full manager.
--
-- Company account (Config.SocietyBanking): any grade with **`isBoss = 1`** or **`bankAuth = 1`**
-- can open that job’s company balance in the banking UI (see `server/society/society_accounts_ox.lua`).
-- ============================================================================

-- Boss + bank-auth flags (company / society tab). Run separately so one can succeed if the other already exists.
ALTER TABLE `ox_group_grades`
  ADD COLUMN `isBoss` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'finals_banking: company account access' AFTER `label`;

ALTER TABLE `ox_group_grades`
  ADD COLUMN `bankAuth` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'finals_banking: company / bank management access' AFTER `isBoss`;

-- Banker job (matches `BankerConfig.JobName` = `'banker'`)
INSERT INTO `ox_groups` (`name`, `label`, `type`, `colour`, `hasAccount`)
VALUES ('banker', 'Bank', 'job', NULL, 0)
ON DUPLICATE KEY UPDATE
  `label` = VALUES(`label`),
  `type` = VALUES(`type`),
  `colour` = VALUES(`colour`),
  `hasAccount` = VALUES(`hasAccount`);

-- Grades: `accountRole` NULL (no `account_roles` row required for this job).
-- Tweak isBoss/bankAuth per rank; Assistant Manager gets `bankAuth` only; Branch Manager gets both.
INSERT INTO `ox_group_grades` (`group`, `grade`, `label`, `accountRole`, `isBoss`, `bankAuth`) VALUES
  ('banker', 0, 'Trainee', NULL, 0, 0),
  ('banker', 1, 'Officer', NULL, 0, 0),
  ('banker', 2, 'Senior Officer', NULL, 0, 0),
  ('banker', 3, 'Head', NULL, 0, 0),
  ('banker', 4, 'Assistant Manager', NULL, 0, 1),
  ('banker', 5, 'Branch Manager', NULL, 1, 1)
ON DUPLICATE KEY UPDATE
  `label` = VALUES(`label`),
  `isBoss` = VALUES(`isBoss`),
  `bankAuth` = VALUES(`bankAuth`);

-- Example: give character id1 the banker job at manager grade (replace `1` with real `charId`)
-- INSERT INTO `character_groups` (`charId`, `name`, `grade`, `isPrimary`)
-- VALUES (1, 'banker', 5, 1)
-- ON DUPLICATE KEY UPDATE `grade` = VALUES(`grade`), `isPrimary` = VALUES(`isPrimary`);

-- Sample below was disabled — edit char id / columns for your `character_groups` schema before uncommenting.
-- INSERT INTO character_groups (charID, name, grade, isActive)
-- VALUES (2, 'banker', 3, 1);