/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE TABLE IF NOT EXISTS `application_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `application_id` int(11) NOT NULL,
  `poster_id` int(11) NOT NULL,
  `post_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `admin_only` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `poster_id` (`poster_id`),
  KEY `application_id` (`application_id`),
  CONSTRAINT `application_comments_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`),
  CONSTRAINT `application_comments_ibfk_2` FOREIGN KEY (`poster_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `app_version` int(11) NOT NULL,
  `app_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`app_data`)),
  `submitted_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `denied_at` datetime DEFAULT NULL,
  `app_status` varchar(20) GENERATED ALWAYS AS (case when `approved_at` is not null then 'Accepted' when `denied_at` is not null then 'Denied' else 'Pending' end) STORED,
  `decision_at` datetime GENERATED ALWAYS AS (coalesce(`approved_at`,`denied_at`)) STORED,
  `approved_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `FK_applications_members` (`approved_by`),
  CONSTRAINT `FK_applications_members` FOREIGN KEY (`approved_by`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_app_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `chk_json_valid` CHECK (json_valid(`app_data`)),
  CONSTRAINT `chk_app_version` CHECK (`app_version` >= 1),
  CONSTRAINT `chk_exclusive_decision` CHECK (`approved_at` is null or `denied_at` is null)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `arma_maps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `world_name` varchar(200) DEFAULT NULL,
  `display_name` varchar(200) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `workshop_id` varchar(100) DEFAULT NULL,
  `author` varchar(50) DEFAULT NULL,
  `world_name_original` varchar(50) DEFAULT NULL,
  `world_size` float DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of Arma3 Maps';

CREATE TABLE IF NOT EXISTS `awards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext DEFAULT NULL,
  `short_name` longtext DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `type` longtext DEFAULT NULL,
  `footprint` longtext DEFAULT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updated_at` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
  `image_url` longtext DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime(3) DEFAULT NULL,
  `is_revocable` tinyint(1) NOT NULL DEFAULT 0,
  `is_stackable` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_awards_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of Awards for the unit.';

CREATE TABLE IF NOT EXISTS `calendar_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `color` char(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '#000000',
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `creator` int(11) DEFAULT NULL,
  `cancelled` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `full_day` tinyint(1) DEFAULT NULL,
  `guilded_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guilded_channel_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_creator` (`creator`),
  CONSTRAINT `FK_calendar_events_members` FOREIGN KEY (`creator`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3390 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `calendar_events_signups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `status` enum('not_attending','attending','maybe') COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `guilded_member_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guilded_event_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id_event_id` (`member_id`,`event_id`),
  UNIQUE KEY `guilded_member_id_guilded_event_id` (`guilded_member_id`,`guilded_event_id`),
  KEY `fk_signup_event` (`event_id`),
  CONSTRAINT `fk_signup_event` FOREIGN KEY (`event_id`) REFERENCES `calendar_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_signup_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=99449 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `course_attendee_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='Changed from course_attendee_type to event_attendee_type';

CREATE TABLE IF NOT EXISTS `course_attendees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `passed_bookwork` tinyint(1) NOT NULL DEFAULT 0,
  `passed_qual` tinyint(1) NOT NULL DEFAULT 0,
  `attendee_id` int(11) NOT NULL,
  `course_event_id` int(11) NOT NULL,
  `attendee_role_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `remarks` text DEFAULT NULL,
  `expired` tinyint(1) NOT NULL DEFAULT 0,
  `expired_reason` tinytext DEFAULT NULL,
  `expired_date` datetime DEFAULT NULL,
  `credited` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `attendee_id_course_event_id` (`attendee_id`,`course_event_id`),
  KEY `courseInstanceId` (`course_event_id`) USING BTREE,
  KEY `fk_CourseInstancesMembers_CoureseAttendeeType_id` (`attendee_role_id`) USING BTREE,
  CONSTRAINT `fk_course_event_member_coures_attendee_type_id` FOREIGN KEY (`attendee_role_id`) REFERENCES `course_attendee_roles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_course_event_members_coures_event_id` FOREIGN KEY (`course_event_id`) REFERENCES `course_events` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_course_event_members_members_id` FOREIGN KEY (`attendee_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5017 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `course_category` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `course_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) DEFAULT NULL,
  `event_type` int(11) DEFAULT NULL,
  `event_date` datetime NOT NULL,
  `guilded_event_id` varchar(50) DEFAULT NULL,
  `guilded_report_id` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(4) DEFAULT 0,
  `report_url` varchar(2048) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `hasBookwork` tinyint(1) DEFAULT NULL,
  `hasQual` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_course_events_event_type_id` (`event_type`) USING BTREE,
  KEY `courseId` (`course_id`) USING BTREE,
  KEY `fk_course_events_created_by` (`created_by`),
  CONSTRAINT `fk_coures_events_course_id` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_course_events_created_by` FOREIGN KEY (`created_by`) REFERENCES `members` (`id`),
  CONSTRAINT `fk_course_events_event_type_id` FOREIGN KEY (`event_type`) REFERENCES `event_types` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1037 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `course_qualifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `category` tinytext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `active` tinyint(4) NOT NULL DEFAULT 1,
  `is_revocable` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `course_qualified_trainers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `qualified` tinyint(1) DEFAULT NULL,
  `instance_qualified_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `memberId_courseId` (`member_id`,`course_id`) USING BTREE,
  KEY `fk_coures_qualified_trainers_coures_id` (`course_id`) USING BTREE,
  KEY `fk_CourseTrainers_CourseInstance_id` (`instance_qualified_id`) USING BTREE,
  CONSTRAINT `fk_coures_qualified_trainers_coures_events_id` FOREIGN KEY (`instance_qualified_id`) REFERENCES `course_events` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_coures_qualified_trainers_coures_id` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_coures_qualified_trainers_mebers_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Contains a linked group of qualified trainers';

CREATE TABLE IF NOT EXISTS `course_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) DEFAULT NULL COMMENT 'What training is requested',
  `requester_id` int(11) DEFAULT NULL COMMENT 'Who Requetsed the training',
  `requested_availablity` tinytext DEFAULT NULL COMMENT 'Freeform Request Time Frame',
  `aditional_info` tinytext DEFAULT NULL COMMENT 'Aditional info provided by requester',
  `request_status` enum('New Request','Awaiting Scheduling','Scheduled','Fulfilled','Did Not Attend','Ignore') DEFAULT NULL COMMENT 'Allow Training Staff to keep track of state of request',
  `requested_date` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Date the Request was put in',
  `fufilled_by_id` int(11) DEFAULT NULL COMMENT 'Who Fufilled the Request',
  `fufilled_date` datetime DEFAULT NULL COMMENT 'What date the request was fufilled',
  `request_notes` tinytext DEFAULT NULL COMMENT 'Notes for the Training Staff to put on the request (Internal)',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Database Value',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Database Value',
  PRIMARY KEY (`id`),
  KEY `FK__members` (`requester_id`),
  KEY `FK__courses` (`course_id`),
  KEY `FK__members_2` (`fufilled_by_id`),
  CONSTRAINT `FK__courses` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK__members` FOREIGN KEY (`requester_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK__members_2` FOREIGN KEY (`fufilled_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of Course requests that members wish to make.';

CREATE TABLE IF NOT EXISTS `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `short_name` varchar(10) NOT NULL,
  `category` varchar(100) NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) DEFAULT 0,
  `prereq_id` int(11) DEFAULT NULL,
  `hasBookwork` tinyint(1) NOT NULL DEFAULT 0,
  `hasQual` tinyint(1) NOT NULL DEFAULT 0,
  `award_on_completion_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `shortName` (`short_name`) USING BTREE,
  KEY `FK_courses_awards` (`award_on_completion_id`),
  CONSTRAINT `FK_courses_awards` FOREIGN KEY (`award_on_completion_id`) REFERENCES `awards` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `courses_sme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL DEFAULT 0,
  `member_id` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_course_sme_course_id` (`course_id`),
  KEY `fk_course_sme_member_id` (`member_id`),
  CONSTRAINT `fk_course_sme_course_id` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_course_sme_member_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `event_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(100) DEFAULT NULL,
  `event_category` varchar(100) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `guilded_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_type` int(11) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) DEFAULT NULL,
  `url` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `calendar_id_event_id` (`channel_id`,`event_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leave_of_absences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `filed_date` datetime NOT NULL COMMENT 'Date that the LOA was filed',
  `start_date` datetime NOT NULL COMMENT 'Date that the LOA should Start',
  `end_date` datetime NOT NULL COMMENT 'Date that the LOA should End',
  `extended_till` datetime DEFAULT NULL,
  `extended_count` int(11) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `reason` text DEFAULT NULL COMMENT 'Reason a person has put in the LOA',
  `created_by_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `expired` tinyint(1) DEFAULT NULL COMMENT 'If a LOA is Expired or not',
  `closed_by` int(11) DEFAULT NULL COMMENT 'Who Closed the LOA if it was manually Closed',
  `ended_at` date DEFAULT NULL,
  `closed` tinyint(1) DEFAULT NULL COMMENT 'If a LOA was closed early or ',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Database Record Creation Date',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Database Record Modified Date',
  PRIMARY KEY (`id`),
  KEY `fk_leave_of_absesnse_members_id` (`member_id`) USING BTREE,
  KEY `FK_leave_of_absences_leave_of_absences_types` (`type_id`),
  KEY `c` (`created_by_id`),
  CONSTRAINT `FK_leave_of_absences_leave_of_absences_types` FOREIGN KEY (`type_id`) REFERENCES `leave_of_absences_types` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `c` FOREIGN KEY (`created_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_leave_of_absesnse_members_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `leave_of_absences_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `max_length_days` int(11) NOT NULL DEFAULT 0,
  `extendable` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `timezone` varchar(5) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `website` varchar(240) DEFAULT NULL,
  `guilded_id` varchar(10) DEFAULT NULL,
  `steam_id_64` varchar(17) DEFAULT NULL,
  `teamspeak_uid` varchar(32) DEFAULT NULL,
  `steam_profile_name` varchar(32) DEFAULT NULL,
  `discord_id` varchar(20) DEFAULT NULL,
  `discord_username` varchar(32) DEFAULT NULL,
  `aliases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`aliases`)),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `authentik_sub` varchar(255) DEFAULT NULL,
  `authentik_issuer` varchar(255) DEFAULT NULL,
  `state` enum('guest','applicant','member','retired','banned','denied') NOT NULL DEFAULT 'guest',
  `displayName` varchar(50) DEFAULT NULL,
  `last_activity` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `steamId64` (`steam_id_64`) USING BTREE,
  UNIQUE KEY `discordId` (`discord_id`) USING BTREE,
  UNIQUE KEY `guilded_id` (`guilded_id`),
  UNIQUE KEY `uk_authentik_identity` (`authentik_issuer`,`authentik_sub`)
) ENGINE=InnoDB AUTO_INCREMENT=3372 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `members_awards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `awards_id` int(11) DEFAULT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `revoked` tinyint(1) DEFAULT 0,
  `revoked_reason` tinytext DEFAULT NULL,
  `revoke_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_members_awards_membres_id` (`member_id`),
  KEY `fk_members_awards_awards_id` (`awards_id`),
  KEY `FK_members_awards_members` (`recorded_by`),
  CONSTRAINT `FK_members_awards_members` FOREIGN KEY (`recorded_by`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_members_awards_awards_id` FOREIGN KEY (`awards_id`) REFERENCES `awards` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_members_awards_membres_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1386 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `members_qualifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT 0,
  `qualification_id` int(11) DEFAULT 0,
  `event_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_members_qualifications_member_id` (`member_id`),
  KEY `fk_members_qualifications_qualifications_id` (`qualification_id`),
  CONSTRAINT `fk_members_qualifications_member_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_members_qualifications_qualifications_id` FOREIGN KEY (`qualification_id`) REFERENCES `qualifications` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `members_ranks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `rank_id` int(11) NOT NULL,
  `authorized_by_id` int(11) DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `reason` varchar(50) DEFAULT NULL,
  `start_date_og` datetime DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date_og` datetime DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `batch` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_members_ranks_members_id` (`member_id`),
  KEY `fk_members_ranks_rank_id` (`rank_id`),
  KEY `FK_members_ranks_members_2` (`authorized_by_id`),
  KEY `FK_members_ranks_members_3` (`created_by_id`),
  CONSTRAINT `FK_members_ranks_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_ranks_members_2` FOREIGN KEY (`authorized_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_ranks_members_3` FOREIGN KEY (`created_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_members_ranks_rank_id` FOREIGN KEY (`rank_id`) REFERENCES `ranks` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `members_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id_role_id` (`member_id`,`role_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `members_roles_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `members_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `members_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `authorized_by_id` int(11) DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `reason` varchar(50) DEFAULT NULL,
  `start_date_og` datetime DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date_og` datetime DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_members_statuses_status_id` (`status_id`),
  KEY `fk_members_statuses_member_id` (`member_id`),
  KEY `FK_members_statuses_ranger_unit_tracker.members` (`authorized_by_id`),
  KEY `FK_members_statuses_ranger_unit_tracker.members_2` (`created_by_id`),
  CONSTRAINT `FK_members_statuses_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_statuses_ranger_unit_tracker.members` FOREIGN KEY (`authorized_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_statuses_ranger_unit_tracker.members_2` FOREIGN KEY (`created_by_id`) REFERENCES `members` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_members_statuses_status_id` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `members_unit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  `authorized_by_id` int(11) NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `reason` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `start_date_og` datetime DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date_og` datetime DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `FK_members_unit_units` (`unit_id`),
  KEY `FK_members_unit_members` (`member_id`),
  KEY `FK_members_unit_members_2` (`authorized_by_id`),
  KEY `FK_members_unit_members_3` (`created_by_id`),
  CONSTRAINT `FK_members_unit_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_unit_members_2` FOREIGN KEY (`authorized_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_unit_members_3` FOREIGN KEY (`created_by_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_members_unit_units` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `operation_campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `createdAt` datetime DEFAULT current_timestamp(),
  `updatedAt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_campaigns_members` (`author_id`) USING BTREE,
  CONSTRAINT `FK_campaigns_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of Campagins';

CREATE TABLE IF NOT EXISTS `operation_ingame_assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) DEFAULT NULL,
  `category` enum('APC','Rotary','Truck','Boats','MRAP','TiltRotor','Cars','Fixed-Wing','Artillery','Armor','Primary weapons','Optics','Secondary weapons','Launchers') NOT NULL,
  `friendly_name` varchar(100) DEFAULT NULL,
  `description` tinytext DEFAULT NULL,
  `image_name` varchar(250) DEFAULT NULL,
  `currently_approved` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `operation_ingame_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `role_name` (`name`) USING BTREE,
  UNIQUE KEY `short_name` (`short_name`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of in game roles that can be run.';

CREATE TABLE IF NOT EXISTS `operation_orbat_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('BATTALION','COMPANY','PLATOON','SQUAD','TEAM','SECTION','ELEMENT','OTHER','MECHANIZED','AIR','WATERCRAFT','SUPPORT') NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  `enabled` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `operation_orbat_group_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `operation_orbat_group` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `operation_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `active` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `operations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `description` text DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `map_id` int(11) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `calendar_event_id` int(11) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `aarURL` varchar(512) DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedAt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_campaign_id` (`name`,`campaign_id`),
  KEY `FK_operations_arma_maps` (`map_id`),
  KEY `FK_operations_operation_campaigns` (`campaign_id`),
  KEY `FK_operations_members` (`author_id`),
  KEY `FK_operations_operation_type` (`type_id`),
  KEY `FK_operations_calendar_events` (`calendar_event_id`),
  CONSTRAINT `FK_operations_arma_maps` FOREIGN KEY (`map_id`) REFERENCES `arma_maps` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_calendar_events` FOREIGN KEY (`calendar_event_id`) REFERENCES `calendar_events` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_operation_campaigns` FOREIGN KEY (`campaign_id`) REFERENCES `operation_campaigns` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_operation_type` FOREIGN KEY (`type_id`) REFERENCES `operation_type` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `operations_attendees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `orbat_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `secondary_role_id` int(11) DEFAULT NULL,
  `callsign` varchar(50) DEFAULT NULL,
  `join_time` datetime DEFAULT NULL,
  `leave_time` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `notes` text DEFAULT NULL,
  `finalized` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `operation_id_member_id_role_id` (`operation_id`,`member_id`,`role_id`),
  KEY `FK_operations_attendees_members` (`member_id`),
  KEY `FK_operations_attendees_operation_orbat_group` (`orbat_id`),
  KEY `FK_operations_attendees_operation_ingame_roles` (`role_id`),
  KEY `FK_operations_attendees_operation_ingame_roles_2` (`secondary_role_id`),
  CONSTRAINT `FK_operations_attendees_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_attendees_operation_ingame_roles` FOREIGN KEY (`role_id`) REFERENCES `operation_ingame_roles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_attendees_operation_ingame_roles_2` FOREIGN KEY (`secondary_role_id`) REFERENCES `operation_ingame_roles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_attendees_operation_orbat_group` FOREIGN KEY (`orbat_id`) REFERENCES `operation_orbat_group` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_operations_attendees_operations` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `qualifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `short_name` varchar(10) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `classification` varchar(100) DEFAULT NULL,
  `footprint` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `image_url` varchar(250) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Contains a list of Member Qualifications for the unit.';

CREATE TABLE IF NOT EXISTS `ranks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext DEFAULT NULL,
  `short_name` varchar(70) NOT NULL,
  `category` varchar(100) NOT NULL,
  `sort_id` int(11) NOT NULL DEFAULT 0,
  `image_url` varchar(240) DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime(3) DEFAULT NULL,
  `discord_role_id` varchar(50) DEFAULT NULL,
  `teamspeak_role_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shortName` (`short_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `color` varchar(9) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '#000000',
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `site_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `discord_guild_id` int(11) DEFAULT NULL,
  `application_unit_on_accept` int(11) DEFAULT NULL,
  `application_rank_on_accept` int(11) DEFAULT NULL,
  `application_status_on_accept` int(11) DEFAULT NULL,
  `loa_consecutive_gap_days` int(11) DEFAULT 0,
  `loa_consecutive_count` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `FK_site_config_units` (`application_unit_on_accept`),
  KEY `FK_site_config_ranks` (`application_rank_on_accept`),
  KEY `FK_site_config_statuses` (`application_status_on_accept`),
  CONSTRAINT `FK_site_config_ranks` FOREIGN KEY (`application_rank_on_accept`) REFERENCES `ranks` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_site_config_statuses` FOREIGN KEY (`application_status_on_accept`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_site_config_units` FOREIGN KEY (`application_unit_on_accept`) REFERENCES `units` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE PROCEDURE IF NOT EXISTS `sp_accept_new_recruit_validation`(
	IN `p_site_config_id` INT,
	IN `p_member_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT
)
BEGIN
    #-------------------------------------------------------------------
    # VARIABLE DECLARATIONS
    #-------------------------------------------------------------------
    DECLARE v_application_unit_on_accept INT;
    DECLARE v_application_rank_on_accept INT;
    DECLARE v_application_status_on_accept INT;

    DECLARE v_cfg_exists INT DEFAULT 0;
    DECLARE v_member_exists INT DEFAULT 0;
    DECLARE v_auth_exists INT DEFAULT 0;
    DECLARE v_created_exists INT DEFAULT 0;

    DECLARE v_msg TEXT;

    #-------------------------------------------------------------------
    # VALIDATE INPUT PARAMETERS
    #-------------------------------------------------------------------

    IF p_site_config_id IS NULL THEN
        SET v_msg = 'ERROR 1001: p_site_config_id cannot be NULL';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF p_member_id IS NULL THEN
        SET v_msg = 'ERROR 1002: p_member_id cannot be NULL';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF p_authorized_by_id IS NULL THEN
        SET v_msg = 'ERROR 1003: p_authorized_by_id cannot be NULL';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF p_created_by_id IS NULL THEN
        SET v_msg = 'ERROR 1004: p_created_by_id cannot be NULL';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    #-------------------------------------------------------------------
    # VALIDATE FOREIGN KEYS FOR MEMBERS
    #-------------------------------------------------------------------

    SELECT COUNT(*) INTO v_member_exists
    FROM members WHERE id = p_member_id;

    IF v_member_exists = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1101: p_member_id ', p_member_id, ' does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    SELECT COUNT(*) INTO v_auth_exists
    FROM members WHERE id = p_authorized_by_id;

    IF v_auth_exists = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1102: p_authorized_by_id ', p_authorized_by_id, ' does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    SELECT COUNT(*) INTO v_created_exists
    FROM members WHERE id = p_created_by_id;

    IF v_created_exists = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1103: p_created_by_id ', p_created_by_id, ' does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    #-------------------------------------------------------------------
    # FETCH SITE CONFIG
    #-------------------------------------------------------------------

    SELECT application_unit_on_accept,
           application_rank_on_accept,
           application_status_on_accept
    INTO   v_application_unit_on_accept,
           v_application_rank_on_accept,
           v_application_status_on_accept
    FROM site_config
    WHERE id = p_site_config_id
    LIMIT 1;

    SELECT ROW_COUNT() INTO v_cfg_exists;
    
    # Debugging: check the fetched values (you can remove this later)
    SELECT v_application_unit_on_accept, v_application_rank_on_accept, v_application_status_on_accept;

    IF v_cfg_exists = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1201: p_site_config_id ', p_site_config_id, ' not found');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    #-------------------------------------------------------------------
    # VALIDATE THAT CONFIG VALUES ARE NOT NULL
    #-------------------------------------------------------------------

    IF v_application_unit_on_accept IS NULL THEN
        SET v_msg = 'ERROR 1202: v_application_unit_on_accept is NULL in site_config';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF v_application_rank_on_accept IS NULL THEN
        SET v_msg = 'ERROR 1203: v_application_rank_on_accept is NULL in site_config';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF v_application_status_on_accept IS NULL THEN
        SET v_msg = 'ERROR 1204: v_application_status_on_accept is NULL in site_config';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    #-------------------------------------------------------------------
    # VALIDATE CONFIG FOREIGN KEY REFERENCES
    #-------------------------------------------------------------------

    IF (SELECT COUNT(*) FROM units WHERE id = v_application_unit_on_accept) = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1301: v_application_unit_on_accept (',
                            v_application_unit_on_accept, ') does not reference a valid unit');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF (SELECT COUNT(*) FROM ranks WHERE id = v_application_rank_on_accept) = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1302: v_application_rank_on_accept (',
                            v_application_rank_on_accept, ') does not reference a valid rank');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    IF (SELECT COUNT(*) FROM statuses WHERE id = v_application_status_on_accept) = 0 THEN
        SET v_msg = CONCAT_WS('', 'ERROR 1303: v_application_status_on_accept (',
                            v_application_status_on_accept, ') does not reference a valid status');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_msg;
    END IF;

    #-------------------------------------------------------------------
    # CALL SUB-PROCEDURES
    #-------------------------------------------------------------------

    CALL sp_update_member_rank(
        p_member_id,
        v_application_rank_on_accept,
        p_authorized_by_id,
        p_created_by_id,
        'Application Accepted',
        NOW()
    );

    CALL sp_update_member_status(
        p_member_id,
        v_application_status_on_accept,
        p_authorized_by_id,
        p_created_by_id,
        'Application Accepted',
        NOW()
    );

    CALL sp_update_member_unit(
        p_member_id,
        v_application_unit_on_accept,
        p_authorized_by_id,
        p_created_by_id,
        'Application Accepted',
        NOW()
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_end_member_rank`(
	IN `p_member_id` INT,
	IN `p_end_date` DATETIME
)
BEGIN

    -- Update only the most recent active rank record for the member
    UPDATE members_ranks
    SET 
        end_date = p_end_date,        -- Full datetime version
        updated_at = NOW()
    WHERE id = (
        SELECT id FROM (
            SELECT id
            FROM members_ranks
            WHERE member_id = p_member_id
              AND end_date IS NULL
            ORDER BY start_date DESC, created_at DESC
            LIMIT 1
        ) AS x
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_end_member_status`(
	IN `p_member_id` INT,
	IN `p_end_date` DATETIME
)
BEGIN

    -- Update only the most recent active status record for the member
    UPDATE members_statuses
    SET
        end_date = p_end_date,
        updated_at = NOW()
    WHERE id = (
        SELECT id FROM (
            SELECT id
            FROM members_statuses
            WHERE member_id = p_member_id
              AND end_date IS NULL
            ORDER BY start_date DESC, created_at DESC
            LIMIT 1
        ) AS x
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_end_member_unit`(
	IN `p_member_id` INT,
	IN `p_end_date` DATETIME
)
BEGIN

    -- Update only the most recent active unit record for the member
    UPDATE members_unit
    SET
        end_date = p_end_date,
        updated_at = NOW()
    WHERE id = (
        SELECT id FROM (
            SELECT id
            FROM members_unit
            WHERE member_id = p_member_id
              AND end_date IS NULL
            ORDER BY start_date DESC, created_at DESC
            LIMIT 1
        ) AS x
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_GetCalendarEventSignups`(
	IN `p_event_id` INT
)
BEGIN
    SELECT 
        s.member_id,
        s.status,
        m.name AS member_name,
        u.name AS unit_name
    FROM calendar_events_signups s
    LEFT JOIN members m 
        ON s.member_id = m.id

    -- Keep member status logic
    LEFT JOIN members_statuses memStat 
        ON memStat.member_id = s.member_id
        AND memStat.created_at = (
            SELECT MAX(ms.created_at)
            FROM members_statuses ms
            WHERE ms.member_id = s.member_id
        )

    -- Get member's current unit
    LEFT JOIN members_unit mu
        ON mu.member_id = s.member_id
        AND mu.end_date IS NULL

    LEFT JOIN units u
        ON u.id = mu.unit_id

    WHERE s.event_id = p_event_id;
END;


CREATE PROCEDURE IF NOT EXISTS `sp_manual_update_report`(
	IN `p_member_guilded_id_createdBy` VARCHAR(10),
	IN `p_guilded_report_id` VARCHAR(50)
)
BEGIN
    DECLARE v_member_id INT;

    /* Get member ID from guilded_id */
    SELECT id
    INTO v_member_id
    FROM members
    WHERE guilded_id = p_member_guilded_id_createdBy
    LIMIT 1;

    /* Safety check */
    IF v_member_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Member with provided guilded_id not found';
    END IF;

    /* Update course events */
    UPDATE course_events ce
    JOIN courses c
        ON ce.course_id = c.id
    SET
        ce.created_by = v_member_id,
        ce.hasBookwork = c.hasBookwork,
        ce.hasQual = c.hasQual,
        ce.guilded_report_id = p_guilded_report_id
    WHERE ce.guilded_report_id IS NULL
      AND ce.deleted = 0;
END;

DROP PROCEDURE IF EXISTS `sp_update_member_rank`;
CREATE PROCEDURE IF NOT EXISTS `sp_update_member_rank`(
	IN `p_member_id` INT,
	IN `p_rank_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
sp: BEGIN
    DECLARE v_existing_rank_id INT;        -- Rank ID of the current record
    DECLARE v_existing_record_id INT;      -- Record ID
    DECLARE v_is_active INT;               -- 1 if active, 0 if closed

    -- ============================================================
    -- 1) Get the latest record using:
    --    ORDER BY start_date DESC, created_at DESC
    -- ============================================================
    SELECT id, rank_id, (end_date IS NULL) AS is_active
    INTO v_existing_record_id, v_existing_rank_id, v_is_active
    FROM members_ranks
    WHERE member_id = p_member_id
    ORDER BY start_date DESC, created_at DESC
    LIMIT 1;

    -- ============================================================
    -- 2) If the existing rank matches the incoming rank → do nothing
    -- ============================================================
    IF v_existing_rank_id = p_rank_id AND v_is_active = 1 THEN
        LEAVE sp;
    END IF;


    -- ============================================================
    -- 3) Rank is changing → close the previous active record
    -- ============================================================
    IF v_is_active = 1 THEN
        CALL sp_end_member_rank(
            p_member_id,       -- Same member
            p_start_date       -- The new rank's start_date becomes the previous rank's end_date
        );
    END IF;

    -- ============================================================
    -- 4) Insert the new rank record
    -- ============================================================
    INSERT INTO members_ranks (
        member_id,
        rank_id,
        authorized_by_id,
        created_by_id,
        reason,
        start_date,
        created_at,
        updated_at
    ) VALUES (
        p_member_id,
        p_rank_id,
        p_authorized_by_id,
        p_created_by_id,
        p_reason,
        DATE(p_start_date),
        NOW(),
        NOW()
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_update_member_rank_Backup_1-27-2026`(
	IN `p_member_id` INT,
	IN `p_rank_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
BEGIN
    DECLARE existing_rank_id INT DEFAULT NULL;
    DECLARE existing_end_date DATETIME DEFAULT NULL;

    -- Get the latest record for the member_id, if it exists
    SELECT `rank_id`, `end_date`
    INTO existing_rank_id, existing_end_date
    FROM `members_ranks`
    WHERE `member_id` = p_member_id
    ORDER BY `start_date` DESC
    LIMIT 1;

    -- If no existing record is found, or the rank_id is different, proceed to insert a new record
    IF existing_rank_id IS NULL OR existing_rank_id != p_rank_id OR existing_end_date IS NOT NULL THEN
        -- Update the end_date of the latest record if its end_date is NULL (i.e., it's the active record)
        IF existing_rank_id IS NOT NULL THEN
            UPDATE `members_ranks`
            SET `end_date` = p_start_date
            WHERE `member_id` = p_member_id AND `end_date` IS NULL;
        END IF;

        -- Insert a new record for the rank_id
        INSERT INTO `members_ranks` (`member_id`, `rank_id`, `authorized_by_id`, `created_by_id`, `reason`, `start_date`, `created_at`, `updated_at`)
        VALUES (p_member_id, p_rank_id, p_authorized_by_id, p_created_by_id, p_reason, p_start_date, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
    END IF;
END;

DROP PROCEDURE IF EXISTS `sp_update_member_status`;
CREATE PROCEDURE IF NOT EXISTS `sp_update_member_status`(
	IN `p_member_id` INT,
	IN `p_status_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
sp: BEGIN
    DECLARE v_existing_record_id INT;
    DECLARE v_existing_status_id INT;
    DECLARE v_is_active INT;

    -- ============================================================
    -- 1) Get the latest record using:
    --    ORDER BY start_date DESC, created_at DESC
    -- ============================================================
    SELECT id, status_id, (end_date IS NULL) AS is_active
    INTO v_existing_record_id, v_existing_status_id, v_is_active
    FROM members_statuses
    WHERE member_id = p_member_id
    ORDER BY start_date DESC, created_at DESC
    LIMIT 1;

    -- ============================================================
    -- 2) If the existing status matches the incoming status → do nothing
    -- ============================================================
    IF v_existing_status_id = p_status_id AND v_is_active = 1 THEN
        LEAVE sp;
    END IF;

    -- ============================================================
    -- 3) Status is changing → close the previous active record
    -- ============================================================
    IF v_is_active = 1 THEN
        CALL sp_end_member_status(
            p_member_id,
            p_start_date
        );
    END IF;

    -- ============================================================
    -- 4) Insert the new status record
    -- ============================================================
    INSERT INTO members_statuses (
        member_id,
        status_id,
        authorized_by_id,
        created_by_id,
        reason,
        start_date,
        created_at,
        updated_at
    ) VALUES (
        p_member_id,
        p_status_id,
        p_authorized_by_id,
        p_created_by_id,
        p_reason,
        p_start_date,
        NOW(),
        NOW()
    );


END;


CREATE PROCEDURE IF NOT EXISTS `sp_update_member_status_Backup_1-27-2026`(
	IN `p_member_id` INT,
	IN `p_status_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
BEGIN
    DECLARE existing_status_id INT DEFAULT NULL;
    DECLARE existing_end_date DATETIME DEFAULT NULL;

    -- Get the latest record for the member_id, if it exists
    SELECT `status_id`, `end_date`
    INTO existing_status_id, existing_end_date
    FROM `members_statuses`
    WHERE `member_id` = p_member_id
    ORDER BY `start_date` DESC
    LIMIT 1;

    -- If no existing record is found, or the status_id is different, proceed to insert a new record
    IF existing_status_id IS NULL OR existing_status_id != p_status_id OR existing_end_date IS NOT NULL THEN
        -- Update the end_date of the latest record if its end_date is NULL (i.e., it's the active record)
        IF existing_status_id IS NOT NULL THEN
            UPDATE `members_statuses`
            SET `end_date` = p_start_date
            WHERE `member_id` = p_member_id AND `end_date` IS NULL;
        END IF;

        -- Insert a new record for the status_id
        INSERT INTO `members_statuses` (`member_id`, `status_id`, `authorized_by_id`, `created_by_id`, `reason`, `start_date`, `created_at`, `updated_at`)
        VALUES (p_member_id, p_status_id, p_authorized_by_id, p_created_by_id, p_reason, p_start_date, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
    END IF;
END;

DROP PROCEDURE IF EXISTS `sp_update_member_unit`;
CREATE PROCEDURE IF NOT EXISTS `sp_update_member_unit`(
	IN `p_member_id` INT,
	IN `p_unit_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
sp: BEGIN
    DECLARE v_existing_record_id INT;
    DECLARE v_existing_unit_id INT;
    DECLARE v_is_active INT;

    -- ============================================================
    -- 1) Get the latest record using:
    --    ORDER BY start_date DESC, created_at DESC
    -- ============================================================
    SELECT id, unit_id, (end_date IS NULL) AS is_active
    INTO v_existing_record_id, v_existing_unit_id, v_is_active
    FROM members_unit
    WHERE member_id = p_member_id
    ORDER BY start_date DESC, created_at DESC
    LIMIT 1;

    -- ============================================================
    -- 2) If the existing unit matches the incoming unit → do nothing
    -- ============================================================
    IF v_existing_unit_id = p_unit_id AND v_is_active = 1 THEN
        LEAVE sp;
    END IF;

    -- ============================================================
    -- 3) Unit is changing → close the previous active record
    -- ============================================================
    IF v_is_active = 1 THEN
        CALL sp_end_member_unit(
            p_member_id,
            p_start_date
        );
    END IF;

    -- ============================================================
    -- 4) Insert the new unit record
    -- ============================================================
    INSERT INTO members_unit (
        member_id,
        unit_id,
        authorized_by_id,
        created_by_id,
        reason,
        start_date,
        created_at,
        updated_at
    ) VALUES (
        p_member_id,
        p_unit_id,
        p_authorized_by_id,
        p_created_by_id,
        p_reason,
        p_start_date,
        NOW(),
        NOW()
    );

END;


CREATE PROCEDURE IF NOT EXISTS `sp_update_member_unit_Backup_1-27-2026`(
	IN `p_member_id` INT,
	IN `p_unit_id` INT,
	IN `p_authorized_by_id` INT,
	IN `p_created_by_id` INT,
	IN `p_reason` VARCHAR(50),
	IN `p_start_date` DATETIME
)
BEGIN
    DECLARE existing_unit_id INT DEFAULT NULL;
    DECLARE existing_end_date DATETIME DEFAULT NULL;

    -- Get the latest record for the member_id, if it exists
    SELECT `unit_id`, `end_date`
    INTO existing_unit_id, existing_end_date
    FROM `members_unit`
    WHERE `member_id` = p_member_id
    ORDER BY `start_date` DESC
    LIMIT 1;

    -- If no existing record is found, or the unit_id is different, proceed to insert a new record
    IF existing_unit_id IS NULL OR existing_unit_id != p_unit_id OR existing_end_date IS NOT NULL THEN
        -- Update the end_date of the latest record if its end_date is NULL (i.e., it's the active record)
        IF existing_unit_id IS NOT NULL THEN
            UPDATE `members_unit`
            SET `end_date` = p_start_date
            WHERE `member_id` = p_member_id AND `end_date` IS NULL;
        END IF;

        -- Insert a new record for the unit_id
        INSERT INTO `members_unit` (`member_id`, `unit_id`, `authorized_by_id`, `created_by_id`, `reason`, `start_date`, `created_at`, `updated_at`)
        VALUES (p_member_id, p_unit_id, p_authorized_by_id, p_created_by_id, p_reason, p_start_date, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
    END IF;
END;

CREATE TABLE IF NOT EXISTS `status_change_requests` (
  `id` int(11) NOT NULL,
  `member_id` int(11) DEFAULT NULL,
  `current_status` int(11) DEFAULT NULL,
  `next_status` int(11) DEFAULT NULL,
  `next_rank` int(11) DEFAULT NULL,
  `approval_from` int(11) DEFAULT NULL,
  `approval_from_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `approval_to` int(11) DEFAULT NULL,
  `approval_to_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `request_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `next_status` (`next_status`),
  KEY `next_rank` (`next_rank`),
  KEY `approval_from` (`approval_from`),
  KEY `approval_to` (`approval_to`),
  KEY `current_status` (`current_status`),
  CONSTRAINT `status_change_requests_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  CONSTRAINT `status_change_requests_ibfk_2` FOREIGN KEY (`next_status`) REFERENCES `statuses` (`id`),
  CONSTRAINT `status_change_requests_ibfk_3` FOREIGN KEY (`next_rank`) REFERENCES `ranks` (`id`),
  CONSTRAINT `status_change_requests_ibfk_4` FOREIGN KEY (`approval_from`) REFERENCES `members` (`id`),
  CONSTRAINT `status_change_requests_ibfk_5` FOREIGN KEY (`approval_to`) REFERENCES `members` (`id`),
  CONSTRAINT `status_change_requests_ibfk_6` FOREIGN KEY (`current_status`) REFERENCES `statuses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(4) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `statuses_reasons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted` tinyint(4) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains a list of Reasons that a Status Change occured. This helps determine Promotions, Demotions, Transfers, Joins, Leaves.';

CREATE TABLE IF NOT EXISTS `units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` enum('Company','Status','External') DEFAULT NULL,
  `is_internal` tinyint(4) NOT NULL,
  `description` text DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT 1,
  `color` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE OR REPLACE VIEW `view_member_rank_unit_status_latest` AS select `m`.`id` AS `member_id`,`m`.`name` AS `member_name`,`m`.`displayName` AS `displayName`,`m`.`state` AS `member_state`,`r`.`id` AS `rank_id`,`r`.`name` AS `rank`,`r`.`short_name` AS `rank_abv`,`mr`.`start_date` AS `rank_date`,`u`.`id` AS `unit_id`,`u`.`name` AS `unit`,`mu`.`start_date` AS `unit_date`,`s`.`name` AS `status`,`ms`.`start_date` AS `status_date`,`loa`.`loa_until` AS `loa_until` from (((((((`members` `m` left join (select `x`.`id` AS `id`,`x`.`member_id` AS `member_id`,`x`.`rank_id` AS `rank_id`,`x`.`authorized_by_id` AS `authorized_by_id`,`x`.`created_by_id` AS `created_by_id`,`x`.`reason` AS `reason`,`x`.`start_date` AS `start_date`,`x`.`end_date` AS `end_date`,`x`.`created_at` AS `created_at`,`x`.`updated_at` AS `updated_at`,`x`.`batch` AS `batch` from (`members_ranks` `x` join (select `members_ranks`.`member_id` AS `member_id`,max(`members_ranks`.`created_at`) AS `max_created` from `members_ranks` where `members_ranks`.`end_date` is null group by `members_ranks`.`member_id`) `y` on(`x`.`member_id` = `y`.`member_id` and `x`.`created_at` = `y`.`max_created`))) `mr` on(`mr`.`member_id` = `m`.`id`)) left join `ranks` `r` on(`r`.`id` = `mr`.`rank_id`)) left join (select `x`.`id` AS `id`,`x`.`member_id` AS `member_id`,`x`.`unit_id` AS `unit_id`,`x`.`authorized_by_id` AS `authorized_by_id`,`x`.`created_by_id` AS `created_by_id`,`x`.`reason` AS `reason`,`x`.`start_date` AS `start_date`,`x`.`end_date` AS `end_date`,`x`.`created_at` AS `created_at`,`x`.`updated_at` AS `updated_at` from (`members_unit` `x` join (select `members_unit`.`member_id` AS `member_id`,max(`members_unit`.`created_at`) AS `max_created` from `members_unit` where `members_unit`.`end_date` is null group by `members_unit`.`member_id`) `y` on(`x`.`member_id` = `y`.`member_id` and `x`.`created_at` = `y`.`max_created`))) `mu` on(`mu`.`member_id` = `m`.`id`)) left join `units` `u` on(`u`.`id` = `mu`.`unit_id`)) left join (select `x`.`id` AS `id`,`x`.`member_id` AS `member_id`,`x`.`status_id` AS `status_id`,`x`.`authorized_by_id` AS `authorized_by_id`,`x`.`created_by_id` AS `created_by_id`,`x`.`reason` AS `reason`,`x`.`start_date` AS `start_date`,`x`.`end_date` AS `end_date`,`x`.`created_at` AS `created_at`,`x`.`updated_at` AS `updated_at` from (`members_statuses` `x` join (select `members_statuses`.`member_id` AS `member_id`,max(`members_statuses`.`created_at`) AS `max_created` from `members_statuses` where `members_statuses`.`end_date` is null group by `members_statuses`.`member_id`) `y` on(`x`.`member_id` = `y`.`member_id` and `x`.`created_at` = `y`.`max_created`))) `ms` on(`ms`.`member_id` = `m`.`id`)) left join `statuses` `s` on(`s`.`id` = `ms`.`status_id`)) left join (select `x`.`id` AS `id`,`x`.`member_id` AS `member_id`,`x`.`filed_date` AS `filed_date`,`x`.`start_date` AS `start_date`,`x`.`end_date` AS `end_date`,`x`.`extended_till` AS `extended_till`,`x`.`type_id` AS `type_id`,`x`.`reason` AS `reason`,`x`.`created_by_id` AS `created_by_id`,`x`.`deleted` AS `deleted`,`x`.`expired` AS `expired`,`x`.`closed_by` AS `closed_by`,`x`.`ended_at` AS `ended_at`,`x`.`closed` AS `closed`,`x`.`created_at` AS `created_at`,`x`.`updated_at` AS `updated_at`,greatest(cast(`x`.`end_date` as date),cast(coalesce(`x`.`extended_till`,`x`.`end_date`) as date)) AS `loa_until` from (`leave_of_absences` `x` join (select `leave_of_absences`.`member_id` AS `member_id`,max(`leave_of_absences`.`created_at`) AS `max_created` from `leave_of_absences` where (`leave_of_absences`.`deleted` = 0 or `leave_of_absences`.`deleted` is null) and (`leave_of_absences`.`closed` = 0 or `leave_of_absences`.`closed` is null) group by `leave_of_absences`.`member_id`) `y` on(`x`.`member_id` = `y`.`member_id` and `x`.`created_at` = `y`.`max_created`))) `loa` on(`loa`.`member_id` = `m`.`id`)) order by `m`.`displayName`
;

CREATE OR REPLACE VIEW `view_member_settings` AS select `m`.`id` AS `id`,`m`.`displayName` AS `displayName` from `members` `m`
;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
