-- ============================================
-- GALTech Admin Panel - Database Schema
-- Generated from Laravel Migrations
-- ============================================

-- Set charset
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- Table: users
-- ============================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin user (password: admin123)
INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, 'Admin', 'admin@galtech.org', NOW(), '$2y$12$YWJjZGVmMTIzNDU2Nzg5MGFic2RlZjEyMzQ1Njc4OTA', NULL, NOW(), NOW());

-- ============================================
-- Table: cache
-- ============================================
DROP TABLE IF EXISTS `cache`;
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: cache_locks
-- ============================================
DROP TABLE IF EXISTS `cache_locks`;
CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: jobs (Laravel queue jobs)
-- ============================================
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: job_batches
-- ============================================
DROP TABLE IF EXISTS `job_batches`;
CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: failed_jobs
-- ============================================
DROP TABLE IF EXISTS `failed_jobs`;
CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: careers
-- ============================================
DROP TABLE IF EXISTS `careers`;
CREATE TABLE `careers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `responsibilities` text DEFAULT NULL,
  `requirements` text DEFAULT NULL,
  `benefits` text DEFAULT NULL,
  `location` varchar(255) NOT NULL,
  `job_location` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `employment_type` enum('full-time','part-time','contract','internship','remote') NOT NULL DEFAULT 'full-time',
  `education` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `skills` varchar(255) DEFAULT NULL,
  `industry` varchar(255) DEFAULT NULL,
  `working_hours` varchar(255) DEFAULT NULL,
  `salary_range` varchar(255) DEFAULT NULL,
  `base_salary` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `date_posted` date DEFAULT NULL,
  `valid_through` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `careers_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample career listings
INSERT INTO `careers` (`id`, `title`, `slug`, `description`, `responsibilities`, `requirements`, `benefits`, `location`, `job_location`, `department`, `employment_type`, `education`, `contact_email`, `contact_phone`, `skills`, `industry`, `working_hours`, `salary_range`, `base_salary`, `is_active`, `date_posted`, `valid_through`, `created_at`, `updated_at`) VALUES
(1, 'Senior SEO Analyst', 'senior-seo-analyst', '<p>We are looking for an experienced Senior SEO Analyst to join our digital marketing team. The ideal candidate will have a strong background in search engine optimization, analytics, and content strategy.</p><p class=\"font-semibold mt-4\">Roles and Responsibilities</p><p>1. Develop and implement effective SEO strategies to improve organic search rankings.</p><p>2. Conduct keyword research, competitor analysis, and site audits.</p><p>3. Monitor and report on SEO performance using analytics tools.</p><p>4. Collaborate with content and development teams to optimize website structure.</p><p>5. Stay updated with the latest SEO trends and algorithm changes.</p><p class=\"font-semibold mt-4\">Required Skills and Experience</p><p>1. 3+ years of experience in SEO and digital marketing.</p><p>2. Proficiency in tools like Google Analytics, SEMrush, Ahrefs, and Google Search Console.</p><p>3. Strong understanding of on-page, off-page, and technical SEO.</p><p>4. Excellent analytical and problem-solving skills.</p><p>5. Strong communication and project management abilities.</p><p class=\"font-semibold mt-4\">Qualifications</p><p>1. Bachelor''s degree in Marketing, Communications, or a related field.</p><p>2. Google Analytics certification preferred.</p><p>3. Experience with e-commerce SEO is a plus.</p><p>If this opportunity aligns with your career goals, kindly share your updated resume with us at <a href=\"mailto:hr@galtech.org\" class=\"text-accent hover:underline\">hr@galtech.org</a></p>', NULL, NULL, NULL, 'Koratty, Thrissur', 'Koratty, Thrissur, Thrissur, KERALA, 680308, India', 'Digital Marketing', 'full-time', 'Bachelor''s degree', 'hr@galtech.org', '+91 6282845368', 'SEO,Google Analytics,SEMrush,Ahrefs,Content Strategy', 'Information Technology', '9.00 AM TO 6.00 PM', 'INR35000-INR50000 Per month', NULL, 1, '2026-01-23', '2026-03-20', NOW(), NOW()),
(2, 'Digital Marketing Interns', 'digital-marketing-interns', '<p>We are seeking enthusiastic Digital Marketing Interns to join our growing team. This is an excellent opportunity for freshers who want to kickstart their career in digital marketing.</p><p class=\"font-semibold mt-4\">Roles and Responsibilities</p><p>1. Assist in managing social media accounts and creating content.</p><p>2. Support SEO and SEM campaigns.</p><p>3. Help with email marketing campaigns and newsletters.</p><p>4. Analyze marketing data and prepare reports.</p><p>5. Participate in brainstorming sessions for marketing strategies.</p><p class=\"font-semibold mt-4\">Required Skills</p><p>1. Basic knowledge of digital marketing concepts.</p><p>2. Familiarity with social media platforms.</p><p>3. Good written and verbal communication skills.</p><p>4. Eagerness to learn and adapt.</p><p>5. Basic understanding of analytics tools is a plus.</p><p class=\"font-semibold mt-4\">Qualifications</p><p>1. Any graduate or postgraduate.</p><p>2. Certification in Digital Marketing is preferred.</p><p>If this opportunity aligns with your career goals, kindly share your updated resume with us at <a href=\"mailto:hr@galtech.org\" class=\"text-accent hover:underline\">hr@galtech.org</a></p>', NULL, NULL, NULL, 'Koratty, Thrissur', 'Koratty, Thrissur, Thrissur, KERALA, 680308, India', 'Digital Marketing', 'internship', 'Any graduate', 'hr@galtech.org', '+91 6282845368', 'Social Media,SEO,Content Writing,Email Marketing', 'Information Technology', '9.00 AM TO 6.00 PM', 'INR10000-INR15000 Per month', NULL, 1, '2026-01-23', '2026-03-20', NOW(), NOW()),
(3, 'Video Editor Intern', 'video-editor-intern', '<p>We are looking for a creative Video Editor Intern to join our content team. If you have a passion for visual storytelling, this role is for you.</p><p class=\"font-semibold mt-4\">Roles and Responsibilities</p><p>1. Edit and produce video content for social media and marketing campaigns.</p><p>2. Add effects, transitions, and audio to enhance video quality.</p><p>3. Collaborate with the creative team on video concepts.</p><p>4. Ensure all videos align with brand guidelines.</p><p class=\"font-semibold mt-4\">Required Skills</p><p>1. Knowledge of video editing software (Premiere Pro, After Effects, etc.).</p><p>2. Creative mindset with attention to detail.</p><p>3. Basic understanding of motion graphics.</p><p>4. Ability to work in a fast-paced environment.</p><p class=\"font-semibold mt-4\">Qualifications</p><p>1. Any graduate or diploma holder.</p><p>2. Portfolio of previous work is preferred.</p><p>If this opportunity aligns with your career goals, kindly share your updated resume with us at <a href=\"mailto:hr@galtech.org\" class=\"text-accent hover:underline\">hr@galtech.org</a></p>', NULL, NULL, NULL, 'Koratty, Thrissur', 'Koratty, Thrissur, Thrissur, KERALA, 680308, India', 'Creative', 'internship', 'Any graduate', 'hr@galtech.org', '+91 6282845368', 'Premiere Pro,After Effects,Video Editing,Motion Graphics', 'Information Technology', '9.00 AM TO 6.00 PM', 'INR10000-INR15000 Per month', NULL, 1, '2026-01-23', '2026-03-20', NOW(), NOW()),
(4, 'HR Intern', 'hr-intern', '<p>We are looking for an HR Intern to support our human resources department. This is a great opportunity for someone looking to build a career in HR.</p><p class=\"font-semibold mt-4\">Roles and Responsibilities</p><p>1. Assist in recruitment processes and candidate screening.</p><p>2. Help with onboarding and documentation.</p><p>3. Support employee engagement activities.</p><p>4. Maintain HR records and databases.</p><p class=\"font-semibold mt-4\">Required Skills</p><p>1. Good communication and interpersonal skills.</p><p>2. Basic knowledge of HR functions.</p><p>3. Proficiency in MS Office.</p><p>4. Attention to detail and organization skills.</p><p class=\"font-semibold mt-4\">Qualifications</p><p>1. Graduate or pursuing MBA in HR.</p><p>If this opportunity aligns with your career goals, kindly share your updated resume with us at <a href=\"mailto:hr@galtech.org\" class=\"text-accent hover:underline\">hr@galtech.org</a></p>', NULL, NULL, NULL, 'Koratty, Thrissur', 'Koratty, Thrissur, Thrissur, KERALA, 680308, India', 'Human Resources', 'internship', 'Any graduate', 'hr@galtech.org', '+91 6282845368', 'Communication,MS Office,Recruitment,Organization', 'Information Technology', '9.00 AM TO 6.00 PM', 'INR10000-INR15000 Per month', NULL, 1, '2026-01-23', '2026-03-21', NOW(), NOW()),
(5, 'Senior PHP Developer', 'senior-php-developer', '<p>We are looking for an experienced Senior PHP Developer to join our development team. The ideal candidate will have strong expertise in PHP frameworks and web application development.</p><p class=\"font-semibold mt-4\">Roles and Responsibilities</p><p>1. Design, develop, and maintain PHP-based web applications.</p><p>2. Write clean, scalable, and maintainable code.</p><p>3. Collaborate with front-end developers and designers.</p><p>4. Troubleshoot and debug applications.</p><p>5. Participate in code reviews and mentor junior developers.</p><p class=\"font-semibold mt-4\">Required Skills and Experience</p><p>1. 3+ years of experience in PHP development.</p><p>2. Strong knowledge of Laravel framework.</p><p>3. Experience with MySQL, REST APIs, and third-party integrations.</p><p>4. Familiarity with front-end technologies (HTML, CSS, JavaScript).</p><p>5. Understanding of Git version control.</p><p class=\"font-semibold mt-4\">Qualifications</p><p>1. Bachelor''s degree in Computer Science or related field.</p><p>2. Relevant certifications are a plus.</p><p>If this opportunity aligns with your career goals, kindly share your updated resume with us at <a href=\"mailto:hr@galtech.org\" class=\"text-accent hover:underline\">hr@galtech.org</a></p>', NULL, NULL, NULL, 'Koratty, Thrissur', 'Koratty, Thrissur, Thrissur, KERALA, 680308, India', 'Development', 'full-time', 'BTech, MCA', 'hr@galtech.org', '+91 6282845368', 'PHP,Laravel,MySQL,REST API,JavaScript', 'Information Technology', '9.00 AM TO 6.00 PM', 'INR40000-INR60000 Per month', NULL, 1, '2026-01-23', '2026-04-30', NOW(), NOW());

-- ============================================
-- Table: job_applications
-- ============================================
DROP TABLE IF EXISTS `job_applications`;
CREATE TABLE `job_applications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `career_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `resume_path` varchar(255) DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_applications_career_id_foreign` (`career_id`),
  CONSTRAINT `job_applications_career_id_foreign` FOREIGN KEY (`career_id`) REFERENCES `careers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: personal_access_tokens
-- ============================================
DROP TABLE IF EXISTS `personal_access_tokens`;
CREATE TABLE `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` text NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: password_reset_tokens
-- ============================================
DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_reset_tokens_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: sessions
-- ============================================
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================
-- NOTES:
-- ============================================
-- 1. Admin credentials:
--    Email: admin@galtech.org
--    Password: admin123
--
--    IMPORTANT: The password hash above is a placeholder.
--    Run `php artisan db:seed --class=AdminUserSeeder` after migration
--    to create the admin user with a properly hashed password.
--
-- 2. To set up the database with Laravel:
--    php artisan migrate
--    php artisan db:seed --class=AdminUserSeeder
--
-- 3. Or import this SQL file directly:
--    mysql -u username -p database_name < galtech_database.sql
-- ============================================