/**
 * Database Schema for [Your Application's Name]
 * 
 * This script defines the schema for a series of tables critical to the application's functionality. It includes:
 * - `api_key`: Manages API keys with associated usage credits and types.
 * - `candidates`: Tracks candidates sourced for recruitment, including various metadata and status indicators.
 * - `client`: Stores client information, vital for managing client interactions and data.
 * - `requests`: Details requests made by or for clients, encompassing job details and assignment tracking.
 * - `user`: Manages user accounts, roles, and authentication within the application.
 * 
 * Each table is designed with a focus on extensibility, security, and robust data management, ensuring that the application
 * can scale and adapt to future requirements.
 * 
 * Author: RapidMod
 * Website: https://rapidmod.io/
 * 
 * Note: Before executing this script, ensure that your MySQL/MariaDB server is running and accessible. Modify the script as necessary
 * to align with your specific application logic and database naming conventions.
 */


| api_key | CREATE TABLE `api_key` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `guid` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `active` tinyint DEFAULT NULL,
  `credits` int DEFAULT NULL,
  `type` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'api_key',
  `public_key` varchar(248) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `valid_until` datetime DEFAULT NULL,
  `extra_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guid` (`guid`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


| candidates | CREATE TABLE `candidates` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `guid` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sourced_by` bigint DEFAULT NULL,
  `recruiter_id` bigint DEFAULT NULL,
  `request_id` bigint DEFAULT NULL,
  `has_dripify` tinyint DEFAULT NULL,
  `has_rf` tinyint DEFAULT NULL,
  `date_loaded` datetime DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `linkedin_profile_link_status` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linkedin_profile_link` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recruiter_notes` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `sourcer_notes` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guid` (`guid`),
  KEY `sourced_by` (`sourced_by`),
  KEY `recruiter_id` (`recruiter_id`),
  KEY `request_id` (`request_id`),
  CONSTRAINT `candidates_ibfk_1` FOREIGN KEY (`recruiter_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `candidates_ibfk_2` FOREIGN KEY (`sourced_by`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `candidates_ibfk_3` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| client | CREATE TABLE `client` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `guid` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| requests | CREATE TABLE `requests` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `guid` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
  `job_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `job_city` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
  `search_area` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
  `search_priority` int NOT NULL,
  `client_id` bigint DEFAULT NULL,
  `submitted_by` bigint DEFAULT NULL,
  `assigned_to` bigint DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guid` (`guid`),
  KEY `assigned_to` (`assigned_to`),
  KEY `submitted_by` (`submitted_by`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`assigned_to`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`submitted_by`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| user  | CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `api_key_id` bigint DEFAULT NULL,
  `contact_id` bigint DEFAULT NULL,
  `role` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'client',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'this should be email address',
  `email_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `auth_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local',
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `last_login` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contact_id` (`contact_id`),
  KEY `guid` (`guid`),
  KEY `api_key_id` (`api_key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT |