-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `horizonhire`
--

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

--
-- Table structure for table `applications`
--

CREATE TABLE `applications` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `status` enum('applied','under_review','interview_requested','interview_submitted','offer_made','offer_accepted','rejected','hired') NOT NULL DEFAULT 'applied',
  `task_unlock_at` datetime DEFAULT NULL,
  `cover_letter` text DEFAULT NULL,
  `resume_path` varchar(255) DEFAULT NULL,
  `terms_accepted` tinyint(1) NOT NULL DEFAULT 0,
  `offer_letter_path` varchar(255) DEFAULT NULL,
  `offer_issued_at` datetime DEFAULT NULL,
  `offer_accepted_at` datetime DEFAULT NULL,
  `hired_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `applications`
--

INSERT INTO `applications` (`id`, `job_id`, `employee_id`, `status`, `task_unlock_at`, `cover_letter`, `resume_path`, `terms_accepted`, `offer_letter_path`, `offer_issued_at`, `offer_accepted_at`, `hired_at`, `created_at`, `updated_at`) VALUES
(3, 14, 9, '', '2025-08-18 01:48:36', 'I am a professional in this field.', 'resumes/f_68a243bf84cf57.18782195.docx', 1, NULL, NULL, '2025-08-19 01:29:30', '2025-08-19 01:21:22', '2025-08-17 22:03:59', '2025-08-19 01:29:30'),
(4, 14, 10, '', '2025-08-19 07:34:34', 'tuu guyu gu', 'resumes/f_68a418aeb3bb47.43729534.docx', 1, NULL, '2025-08-19 07:29:34', '2025-08-19 07:32:34', NULL, '2025-08-19 07:24:46', '2025-08-19 07:34:34'),
(5, 13, 10, 'applied', NULL, 'kjiuytr hi', 'resumes/f_68a41aa10fa853.59929398.docx', 1, NULL, NULL, NULL, NULL, '2025-08-19 07:33:05', '2025-08-19 07:33:05');

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

--
-- Table structure for table `chat_reads`
--

CREATE TABLE `chat_reads` (
  `application_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `last_read_message_id` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `chat_reads`
--

INSERT INTO `chat_reads` (`application_id`, `user_id`, `last_read_message_id`) VALUES
(1, 1, 32),
(3, 1, 36),
(3, 9, 36),
(2, 1, 37);

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

--
-- Table structure for table `chat_typing`
--

CREATE TABLE `chat_typing` (
  `application_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `last_typing_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `failed_logins`
--

CREATE TABLE `failed_logins` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `occurred_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_logins`
--

INSERT INTO `failed_logins` (`id`, `email`, `ip`, `occurred_at`) VALUES
(1, 'employer@example.com', '::1', '2025-08-16 18:55:36'),
(2, 'employer@example.com', '::1', '2025-08-16 18:56:07'),
(3, 'employer@example.com', '::1', '2025-08-16 19:41:59'),
(4, 'employer@example.com', '::1', '2025-08-17 04:08:50'),
(5, 'hrmanager@horizonhire.world', '::1', '2025-08-18 01:24:29'),
(6, 'admin@horizonhire.test', '::1', '2025-08-19 04:11:30'),
(7, 'admin@horizonhire.test', '::1', '2025-08-19 04:11:34'),
(8, 'hrmanager@horizonhire.world', '::1', '2025-08-19 04:19:03'),
(9, 'hrmanager@horizonhire.test', '::1', '2025-08-19 04:19:13'),
(10, 'hrmanager@horizonhire.test', '::1', '2025-08-19 04:20:56'),
(11, 'hrmanager@horizonhire.test', '::1', '2025-08-19 04:21:15'),
(12, 'hrmanager@horizonhire.test', '::1', '2025-08-19 04:21:34'),
(13, 'employer@horizonhire.test', '::1', '2025-08-19 04:21:59'),
(14, 'hrmanager@horizonhire.test', '::1', '2025-08-19 04:22:38'),
(15, 'hrmanager@horizonhire.world', '::1', '2025-08-19 04:24:44');

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

--
-- Table structure for table `interview_answers`
--

CREATE TABLE `interview_answers` (
  `id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_text` text DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `interview_answers`
--

INSERT INTO `interview_answers` (`id`, `application_id`, `question_id`, `answer_text`, `file_path`, `created_at`) VALUES
(13, 3, 3, 'Jamm jamm', '', '2025-08-17 22:06:46'),
(14, 3, 4, 'usa', '', '2025-08-17 22:06:46'),
(15, 3, 5, '', 'interviews/3/5_20250817_220646_befb5678.jpg', '2025-08-17 22:06:46'),
(16, 3, 6, 'Yes', '', '2025-08-17 22:06:46'),
(17, 3, 7, 'Yes', '', '2025-08-17 22:06:46'),
(18, 4, 3, 'kh hioyioh', '', '2025-08-19 07:25:56'),
(19, 4, 4, 'hyiuyi tiutut ut ui', '', '2025-08-19 07:25:56'),
(20, 4, 5, '', 'interviews/4/5_20250819_072556_5a8f5570.docx', '2025-08-19 07:25:56'),
(21, 4, 6, 'Yes', '', '2025-08-19 07:25:56'),
(22, 4, 7, 'Yes', '', '2025-08-19 07:25:56');

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

--
-- Table structure for table `interview_questions`
--

CREATE TABLE `interview_questions` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `qtype` enum('text','file','yesno') NOT NULL DEFAULT 'text',
  `required` tinyint(1) NOT NULL DEFAULT 1,
  `position` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `interview_questions`
--

INSERT INTO `interview_questions` (`id`, `job_id`, `question_text`, `qtype`, `required`, `position`) VALUES
(1, 1, 'Tell us about a complex project you shipped. What was your role?', 'text', 1, 0),
(2, 1, 'Upload a sample (PDF/ZIP) of your code or portfolio.', 'file', 0, 0),
(3, 14, 'What is your name?', 'text', 1, 20),
(4, 14, 'Where are you from?', 'text', 1, 30),
(5, 14, 'And What ID do you have, please provide?', 'file', 1, 40),
(6, 14, 'DO YOU LOVE?', 'yesno', 1, 10),
(7, 14, 'do you like what yousee?', 'yesno', 1, 41);

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

--
-- Table structure for table `interview_submissions`
--

CREATE TABLE `interview_submissions` (
  `id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `answers_json` longtext NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `review_note` text DEFAULT NULL,
  `admin_note` text DEFAULT NULL,
  `submitted_at` datetime NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` datetime DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `interview_submissions`
--

INSERT INTO `interview_submissions` (`id`, `application_id`, `employee_id`, `answers_json`, `status`, `review_note`, `admin_note`, `submitted_at`, `reviewed_at`, `reviewed_by`) VALUES
(2, 3, 9, '', 'approved', 'Thanks for Joining Us... HR Manager', NULL, '2025-08-17 22:06:46', '2025-08-18 01:43:53', NULL),
(3, 4, 10, '', 'approved', '', NULL, '2025-08-19 07:25:56', '2025-08-19 07:29:34', NULL);

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

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL,
  `employer_id` int(11) NOT NULL,
  `title` varchar(160) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  `rate_per_hour` decimal(10,2) NOT NULL DEFAULT 35.25,
  `weekly_hours_min` int(11) NOT NULL DEFAULT 20,
  `weekly_hours_max` int(11) NOT NULL DEFAULT 45,
  `is_remote` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `updated_at` datetime DEFAULT NULL,
  `company_terms` text DEFAULT NULL,
  `job_terms` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--

INSERT INTO `jobs` (`id`, `employer_id`, `title`, `category`, `description`, `rate_per_hour`, `weekly_hours_min`, `weekly_hours_max`, `is_remote`, `status`, `updated_at`, `company_terms`, `job_terms`, `created_at`) VALUES
(1, 1, 'Software Engineer', 'Software', 'Develop innovative software solutions remotely at $35.25/hr. Flexible hours (20–45/wk).', 35.25, 20, 45, 1, 'open', NULL, 'You must accept Horizonhire company terms & privacy.', 'You must follow job-specific guidelines and instructions.', '2025-08-16 04:56:46'),
(2, 1, 'Marketing Manager', 'Marketing', 'Lead our marketing team and drive business growth (remote, flexible).', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(3, 1, 'Data Entry Clerk', 'Data Entry', 'Accurately enter data into our database.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(4, 1, 'Customer Support Representative', 'Support', 'Provide exceptional customer service.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(5, 1, 'Sales Representative', 'Sales', 'Drive sales growth and build relationships.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(6, 1, 'Call Center Director/Manager', 'Call Center', 'Lead and optimize call center operations.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(7, 1, 'Clerical Admin', 'Administration', 'General office/clerical tasks.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(8, 1, 'Administrative Clerk/Assistant', 'Administration', 'Assist with admin tasks and scheduling.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(9, 1, 'Medical Billing and Coding', 'Healthcare', 'Handle billing and coding remotely.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(10, 1, 'Payroll Clerk', 'Finance', 'Process payroll accurately and on time.', 35.25, 20, 45, 1, 'open', '2025-08-19 05:55:42', NULL, NULL, '2025-08-16 04:56:46'),
(11, 1, 'Bookkeeping', 'Finance', 'Maintain financial records and reconciliations.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(12, 1, 'Dispatcher', 'Logistics', 'Coordinate dispatch operations.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(13, 1, 'Medical Biller/Coder', 'Healthcare', 'Process medical bills and codes.', 35.25, 20, 45, 1, 'open', NULL, NULL, NULL, '2025-08-16 04:56:46'),
(14, 1, 'Typist Clerk', 'Administration', 'Fast and accurate typing tasks.', 40.00, 20, 45, 1, 'open', '2025-08-19 04:04:48', NULL, NULL, '2025-08-16 04:56:46');

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

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `message_text` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`id`, `application_id`, `sender_id`, `message_text`, `created_at`) VALUES
(33, 3, 1, 'Hello', '2025-08-18 01:16:32'),
(34, 3, 9, 'how are you doing?', '2025-08-18 01:31:44'),
(35, 3, 9, 'how are you', '2025-08-19 01:31:45'),
(36, 3, 1, 'I\'m fine and you?', '2025-08-19 04:06:06');

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('credit','debit') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `source` varchar(50) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `title` varchar(160) NOT NULL,
  `description` text NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `expires_after_hours` int(11) DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tasks`
--

INSERT INTO `tasks` (`id`, `job_id`, `title`, `description`, `amount`, `expires_after_hours`, `position`) VALUES
(1, 1, 'Onboarding & Environment Setup', 'Follow the checklist and submit a zip of your setup notes/screens.', 50.00, 168, 1),
(2, 1, 'Ship Feature X', 'Implement the specified feature and submit a zip or link for review.', 80.00, 168, 2),
(4, 14, 'Typing tutor', 'I want you to type like 500 page and sumbitted it now. thanks now', 40.00, 2, 1);

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

--
-- Table structure for table `task_assignments`
--

CREATE TABLE `task_assignments` (
  `id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `assigned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `can_start_after` datetime NOT NULL,
  `started_at` datetime DEFAULT NULL,
  `deadline_at` datetime DEFAULT NULL,
  `status` enum('locked','started','submitted','reviewing','approved','rejected') NOT NULL DEFAULT 'locked',
  `submission_path` varchar(255) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `review_note` text DEFAULT NULL,
  `reviewer_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `task_assignments`
--

INSERT INTO `task_assignments` (`id`, `task_id`, `application_id`, `assigned_at`, `can_start_after`, `started_at`, `deadline_at`, `status`, `submission_path`, `submitted_at`, `approved_at`, `review_note`, `reviewer_id`, `amount`) VALUES
(3, 4, 3, '2025-08-18 01:49:04', '0000-00-00 00:00:00', NULL, NULL, 'approved', 'tasks/3/20250818_014959_58d59d67.docx', '2025-08-18 01:49:59', '2025-08-18 01:55:13', 'Thanks for completing the tasks', NULL, 35.50),
(4, 4, 4, '2025-08-19 07:34:58', '0000-00-00 00:00:00', NULL, NULL, 'approved', 'tasks/4/20250819_073510_a6944917.docx', '2025-08-19 07:35:10', '2025-08-19 07:36:25', 'Good Job to you my dear..', NULL, 40.00);

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

--
-- Table structure for table `task_attachments`
--

CREATE TABLE `task_attachments` (
  `id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_path` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `task_questions`
--

CREATE TABLE `task_questions` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `qtype` enum('text','file','yesno') NOT NULL DEFAULT 'text',
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `wallets`
--

CREATE TABLE `wallets` (
  `user_id` int(11) NOT NULL,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `wallets`
--

INSERT INTO `wallets` (`user_id`, `balance`) VALUES
(1, 0.00),
(9, 5.00),
(10, 20.00);

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

--
-- Table structure for table `wallet_transactions`
--

CREATE TABLE `wallet_transactions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'misc',
  `direction` enum('credit','debit') NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `amount_cents` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `ref` varchar(190) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `wallet_transactions`
--

INSERT INTO `wallet_transactions` (`id`, `user_id`, `type`, `direction`, `amount`, `ref_type`, `ref_id`, `note`, `amount_cents`, `description`, `ref`, `created_at`) VALUES
(14, 9, 'withdraw_request', 'debit', 10.00, 'withdrawal', 10, 'Requested payout (pre-hold)', 0, '', '', '2025-08-18 01:59:49'),
(15, 9, 'withdraw_request', 'debit', 5.50, 'withdrawal', 11, 'Requested payout (pre-hold)', 0, '', '', '2025-08-18 02:27:29'),
(16, 9, 'withdraw_request', 'debit', 10.00, 'withdrawal', 12, 'Requested payout (pre-hold)', 0, '', '', '2025-08-18 21:02:14'),
(17, 9, 'withdraw_request', 'debit', 5.00, 'withdrawal', 13, 'Requested payout (pre-hold)', 0, '', '', '2025-08-19 01:32:12'),
(18, 10, 'withdraw_request', 'debit', 20.00, 'withdrawal', 14, 'Requested payout (pre-hold)', 0, '', '', '2025-08-19 07:37:06');

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

--
-- Table structure for table `withdrawals`
--

CREATE TABLE `withdrawals` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `bank_name` varchar(120) NOT NULL DEFAULT 'Earnwise FCU',
  `account_number` varchar(40) NOT NULL,
  `status` enum('pending','paid','failed') NOT NULL DEFAULT 'pending',
  `requested_at` datetime NOT NULL DEFAULT current_timestamp(),
  `decided_at` datetime DEFAULT NULL,
  `decided_by` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `bank` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `withdrawals`
--

INSERT INTO `withdrawals` (`id`, `user_id`, `amount`, `bank_name`, `account_number`, `status`, `requested_at`, `decided_at`, `decided_by`, `note`, `created_at`, `bank`) VALUES
(10, 9, 10.00, 'Earnwise FCU', '1234567890', '', '2025-08-18 01:59:49', '2025-08-18 02:02:08', NULL, NULL, '2025-08-18 01:59:49', 'earnwisefcu'),
(11, 9, 5.50, 'Earnwise FCU', '1234567890', '', '2025-08-18 02:27:28', '2025-08-18 02:28:23', NULL, NULL, '2025-08-18 02:27:28', 'earnwisefcu'),
(12, 9, 10.00, 'Earnwise FCU', '1234567890', '', '2025-08-18 21:02:14', '2025-08-18 21:03:27', NULL, NULL, '2025-08-18 21:02:14', 'earnwisefcu'),
(13, 9, 5.00, 'Earnwise FCU', '1234567897', '', '2025-08-19 01:32:12', '2025-08-19 04:09:14', NULL, NULL, '2025-08-19 01:32:12', 'earnwisefcu'),
(14, 10, 20.00, 'Earnwise FCU', '1234567890', '', '2025-08-19 07:37:06', '2025-08-19 07:37:19', NULL, NULL, '2025-08-19 07:37:06', 'earnwisefcu');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `applications`
--
ALTER TABLE `applications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_app_employee` (`employee_id`),
  ADD KEY `idx_app_job` (`job_id`),
  ADD KEY `idx_app_status` (`status`);

--
-- Indexes for table `chat_reads`
--
ALTER TABLE `chat_reads`
  ADD PRIMARY KEY (`application_id`,`user_id`),
  ADD KEY `last_read_message_id` (`last_read_message_id`);

--
-- Indexes for table `chat_typing`
--
ALTER TABLE `chat_typing`
  ADD PRIMARY KEY (`application_id`,`user_id`),
  ADD KEY `last_typing_at` (`last_typing_at`);

--
-- Indexes for table `failed_logins`
--
ALTER TABLE `failed_logins`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_failed_email_time` (`email`,`occurred_at`),
  ADD KEY `idx_failed_ip_time` (`ip`,`occurred_at`);

--
-- Indexes for table `interview_answers`
--
ALTER TABLE `interview_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `application_id` (`application_id`),
  ADD KEY `question_id` (`question_id`);

--
-- Indexes for table `interview_questions`
--
ALTER TABLE `interview_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `job_id` (`job_id`);

--
-- Indexes for table `interview_submissions`
--
ALTER TABLE `interview_submissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `application_id` (`application_id`),
  ADD KEY `status` (`status`),
  ADD KEY `employee_id` (`employee_id`),
  ADD KEY `reviewed_by` (`reviewed_by`);

--
-- Indexes for table `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_jobs_status` (`status`),
  ADD KEY `idx_jobs_employer` (`employer_id`);

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender_id` (`sender_id`),
  ADD KEY `idx_msg_app` (`application_id`),
  ADD KEY `idx_msg_created` (`created_at`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `job_id` (`job_id`);

--
-- Indexes for table `task_assignments`
--
ALTER TABLE `task_assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `task_id` (`task_id`),
  ADD KEY `idx_ta_app` (`application_id`),
  ADD KEY `idx_ta_status_time` (`status`,`can_start_after`);

--
-- Indexes for table `task_attachments`
--
ALTER TABLE `task_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `task_id` (`task_id`);

--
-- Indexes for table `task_questions`
--
ALTER TABLE `task_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `job_id` (`job_id`);

--
-- Indexes for table `wallets`
--
ALTER TABLE `wallets`
  ADD PRIMARY KEY (`user_id`);

--
-- Indexes for table `wallet_transactions`
--
ALTER TABLE `wallet_transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_user_ref` (`user_id`,`ref_type`,`ref_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_wallet_user_created` (`user_id`,`created_at`);

--
-- Indexes for table `withdrawals`
--
ALTER TABLE `withdrawals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_withdrawals_user` (`user_id`),
  ADD KEY `idx_withdrawals_status` (`status`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `applications`
--
ALTER TABLE `applications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `failed_logins`
--
ALTER TABLE `failed_logins`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `interview_answers`
--
ALTER TABLE `interview_answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `interview_questions`
--
ALTER TABLE `interview_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `interview_submissions`
--
ALTER TABLE `interview_submissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `task_assignments`
--
ALTER TABLE `task_assignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `task_attachments`
--
ALTER TABLE `task_attachments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `task_questions`
--
ALTER TABLE `task_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `wallet_transactions`
--
ALTER TABLE `wallet_transactions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `withdrawals`
--
ALTER TABLE `withdrawals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `applications`
--
ALTER TABLE `applications`
  ADD CONSTRAINT `applications_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`),
  ADD CONSTRAINT `applications_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `interview_answers`
--
ALTER TABLE `interview_answers`
  ADD CONSTRAINT `interview_answers_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`),
  ADD CONSTRAINT `interview_answers_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `interview_questions` (`id`);

--
-- Constraints for table `interview_questions`
--
ALTER TABLE `interview_questions`
  ADD CONSTRAINT `interview_questions_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`);

--
-- Constraints for table `interview_submissions`
--
ALTER TABLE `interview_submissions`
  ADD CONSTRAINT `fk_is_app` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_is_employee` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_is_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `jobs`
--
ALTER TABLE `jobs`
  ADD CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`employer_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`),
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `tasks`
--
ALTER TABLE `tasks`
  ADD CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`);

--
-- Constraints for table `task_assignments`
--
ALTER TABLE `task_assignments`
  ADD CONSTRAINT `task_assignments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`),
  ADD CONSTRAINT `task_assignments_ibfk_2` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`);

--
-- Constraints for table `task_questions`
--
ALTER TABLE `task_questions`
  ADD CONSTRAINT `fk_tq_job` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `wallets`
--
ALTER TABLE `wallets`
  ADD CONSTRAINT `wallets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `withdrawals`
--
ALTER TABLE `withdrawals`
  ADD CONSTRAINT `withdrawals_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
COMMIT;

