-- ============================================================
-- CRB Status Checker – Database Schema
-- Run this once in cPanel → phpMyAdmin → SQL tab
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- Users
CREATE TABLE IF NOT EXISTS `users` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `full_name`   VARCHAR(120) NOT NULL,
  `national_id` VARCHAR(20)  NOT NULL UNIQUE,
  `phone`       VARCHAR(20)  NOT NULL,
  `email`       VARCHAR(180) NOT NULL UNIQUE,
  `password`    VARCHAR(255) NOT NULL,
  `created_at`  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CRB check records
CREATE TABLE IF NOT EXISTS `crb_checks` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT UNSIGNED NOT NULL,
  `national_id`  VARCHAR(20)  NOT NULL,
  `full_name`    VARCHAR(120) NOT NULL,
  `credit_score` SMALLINT     NOT NULL DEFAULT 0,
  `status`       VARCHAR(30)  NOT NULL DEFAULT 'unknown',
  `category`     VARCHAR(60)  NOT NULL DEFAULT '',
  `paid`         TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`   DATETIME     DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payments
CREATE TABLE IF NOT EXISTS `payments` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `check_id`       INT UNSIGNED NOT NULL,
  `user_id`        INT UNSIGNED NOT NULL,
  `mpesa_message`  TEXT         NOT NULL,
  `transaction_id` VARCHAR(20)  NOT NULL DEFAULT '',
  `amount`         DECIMAL(10,2) NOT NULL DEFAULT 0,
  `status`         VARCHAR(20)  NOT NULL DEFAULT 'pending',   -- pending|verified|rejected
  `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`check_id`) REFERENCES `crb_checks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Clearance requests
CREATE TABLE IF NOT EXISTS `clearances` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `check_id`    INT UNSIGNED NOT NULL,
  `user_id`     INT UNSIGNED NOT NULL,
  `status`      VARCHAR(20)  NOT NULL DEFAULT 'under_review',  -- under_review|approved|rejected
  `notes`       TEXT,
  `created_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`check_id`) REFERENCES `crb_checks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App settings (editable from admin panel)
CREATE TABLE IF NOT EXISTS `settings` (
  `key_name`  VARCHAR(80)  PRIMARY KEY,
  `value`     TEXT         NOT NULL,
  `updated_at` DATETIME    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default settings
INSERT INTO `settings` (`key_name`, `value`) VALUES
  ('till_number',            '1234567'),
  ('till_name',              'CRB Services'),
  ('amount',                 '500'),
  ('pangle_app_id',          'YOUR_PANGLE_APP_ID'),
  ('pangle_banner_slot',     'YOUR_BANNER_SLOT_ID'),
  ('pangle_native_slot',     'YOUR_NATIVE_SLOT_ID'),
  ('pangle_interstitial_slot','YOUR_INTERSTITIAL_SLOT_ID'),
  ('app_version',            '1.0.0'),
  ('maintenance_mode',       '0')
ON DUPLICATE KEY UPDATE `key_name` = `key_name`;

SET FOREIGN_KEY_CHECKS = 1;