-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: 04 أكتوبر 2025 الساعة 01:49
-- إصدار الخادم: 10.11.14-MariaDB
-- PHP Version: 8.3.14

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `gulftriangle_demo`
--

-- --------------------------------------------------------

--
-- بنية الجدول `members`
--

CREATE TABLE `members` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `member_code` char(7) NOT NULL,
  `branch_id` bigint(20) UNSIGNED NOT NULL,
  `status` enum('new','active','inactive') NOT NULL DEFAULT 'new',
  `id_type` enum('national_id','iqama','passport','driving_license','work_card') DEFAULT NULL,
  `id_number` varchar(20) DEFAULT NULL,
  `id_issue_greg` date DEFAULT NULL,
  `id_expiry_greg` date DEFAULT NULL,
  `id_issue_hijri` varchar(20) DEFAULT NULL,
  `id_expiry_hijri` varchar(20) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `gender` enum('male','female') DEFAULT NULL,
  `nationality_id` bigint(20) UNSIGNED DEFAULT NULL,
  `birth_greg` date DEFAULT NULL,
  `birth_hijri` varchar(20) DEFAULT NULL,
  `age` smallint(5) UNSIGNED DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `po_box` varchar(50) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `avatar_path` varchar(255) DEFAULT NULL,
  `id_image_path` varchar(255) DEFAULT NULL,
  `work_card_image_path` varchar(255) DEFAULT NULL,
  `how_heard` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`how_heard`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ;

--
-- إرجاع أو استيراد بيانات الجدول `members`
--

INSERT INTO `members` (`id`, `member_code`, `branch_id`, `status`, `id_type`, `id_number`, `id_issue_greg`, `id_expiry_greg`, `id_issue_hijri`, `id_expiry_hijri`, `name`, `gender`, `nationality_id`, `birth_greg`, `birth_hijri`, `age`, `phone`, `email`, `po_box`, `postal_code`, `address`, `avatar_path`, `id_image_path`, `work_card_image_path`, `how_heard`, `created_at`, `updated_at`) VALUES
(1, '0400001', 4, 'new', NULL, NULL, NULL, NULL, NULL, NULL, 'Test Member', 'female', 1, NULL, NULL, 28, '+1 (955) 965-2423', 'gycyv@mailinator.com', 'P.O. Box 88296', '12345', 'Quos labore voluptat', NULL, NULL, NULL, NULL, '2025-10-03 22:39:00', '2025-10-03 22:39:00');

--
-- القوادح `members`
--
DELIMITER $$
CREATE TRIGGER `trg_members_before_insert` BEFORE INSERT ON `members` FOR EACH ROW BEGIN
  DECLARE v_branch_code VARCHAR(20);
  DECLARE v_prefix CHAR(2);
  DECLARE v_last_seq INT DEFAULT 0;

  IF NEW.member_code IS NULL OR NEW.member_code = '' THEN
    -- احصل على كود الفرع
    SELECT branch_code INTO v_branch_code
    FROM branches
    WHERE id = NEW.branch_id
    FOR UPDATE;

    -- آخر رقمين (إن لم يوجد نستخدم "00")
    SET v_prefix = LPAD(RIGHT(COALESCE(v_branch_code,'00'), 2), 2, '0');

    -- أعلى تسلسل لنفس البادئة
    SELECT COALESCE(MAX(CAST(SUBSTRING(member_code, 3) AS UNSIGNED)), 0)
      INTO v_last_seq
    FROM members
    WHERE member_code LIKE CONCAT(v_prefix, '%')
    FOR UPDATE;

    SET NEW.member_code = CONCAT(v_prefix, LPAD(v_last_seq + 1, 5, '0'));  -- BB + SSSSS
  END IF;
END
$$
DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `members`
--
ALTER TABLE `members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `members_member_code_unique` (`member_code`),
  ADD UNIQUE KEY `members_id_number_unique` (`id_number`),
  ADD KEY `members_branch_id_idx` (`branch_id`),
  ADD KEY `members_status_idx` (`status`),
  ADD KEY `members_nationality_id_idx` (`nationality_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `members`
--
ALTER TABLE `members`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- قيود الجداول المُلقاة.
--

--
-- قيود الجداول `members`
--
ALTER TABLE `members`
  ADD CONSTRAINT `members_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `members_nationality_id_foreign` FOREIGN KEY (`nationality_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
