-- ============================================================
--  Nursing Guru Portal - Complete Database Schema v2.0
--  Generated: 2026-06-28
-- ============================================================
--
--  ADMIN LOGIN CREDENTIALS
--  URL:      /admin/login
--  Email:    admin@nursingguru.in
--  Password: Admin@123
--
--  DATABASE CONFIG (config/database.php)
--  DB Host:  localhost
--  DB Name:  nurseportal
--  DB User:  root
--  DB Pass:  root@123
--
--  IMPORT STEPS:
--  1. Open phpMyAdmin
--  2. Create database named: nurseportal
--  3. Click Import tab > choose this file > Go
--  4. Copy portal files to htdocs/ng/
--  5. Visit: http://localhost/ng/public/admin/login
-- ============================================================

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

CREATE DATABASE IF NOT EXISTS `nurseportal` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `nurseportal`;

-- =============================================
-- SETTINGS & CONFIGURATION
-- =============================================
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) DEFAULT 'general',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_group`) VALUES
('site_name', 'Nursing Guru', 'general'),
('site_tagline', 'Connecting Healthcare Professionals', 'general'),
('site_email', 'admin@nursingguru.in', 'general'),
('site_phone', '', 'general'),
('site_address', '', 'general'),
('site_logo', '', 'general'),
('currency_symbol', '₹', 'general'),
('currency_code', 'INR', 'general'),
-- SMTP
('smtp_host', '', 'email'),
('smtp_port', '587', 'email'),
('smtp_user', '', 'email'),
('smtp_pass', '', 'email'),
('smtp_from_name', 'Nursing Guru', 'email'),
('smtp_from_email', 'no-reply@nursingguru.in', 'email'),
('smtp_encryption', 'tls', 'email'),
-- Payment
('payment_gateway', 'razorpay', 'payment'),
('razorpay_key_id', '', 'payment'),
('razorpay_key_secret', '', 'payment'),
('razorpay_mode', 'test', 'payment'),
('stripe_publishable_key', '', 'payment'),
('stripe_secret_key', '', 'payment'),
('stripe_mode', 'test', 'payment'),
-- Email Templates
('email_notify_apply', '1', 'notifications'),
('email_notify_shortlist', '1', 'notifications'),
('email_notify_employer_apply', '1', 'notifications'),
-- SEO
('meta_description', 'Find nursing jobs in hospitals, corporate, home care and overseas', 'seo'),
('meta_keywords', 'nursing jobs, hospital jobs, overseas nursing', 'seo'),
('google_analytics', '', 'seo'),
('maintenance_mode', '0', 'general'),
('jobs_per_page', '12', 'general'),
('candidates_per_page', '12', 'general');

-- =============================================
-- USERS (ADMIN + AUTH BASE)
-- =============================================
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('superadmin','admin') DEFAULT 'admin',
  `avatar` varchar(255) DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin: admin@nursingguru.in / Admin@123
INSERT INTO `admins` (`name`, `email`, `password`, `role`) VALUES
('Super Admin', 'admin@nursingguru.in', '$2y$10$O6TRoMuN7lOjYjQzAWLK3uYa1xf4kMSUaFywb/uIJN1w1pqvZxJBy', 'superadmin');

-- =============================================
-- PLANS
-- =============================================
CREATE TABLE `plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `type` enum('candidate','employer') NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `duration_days` int(11) NOT NULL DEFAULT 30,
  `max_applications` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `max_resume_views` int(11) DEFAULT NULL COMMENT 'For employer: how many resumes they can view',
  `max_job_posts` int(11) DEFAULT NULL COMMENT 'For employer',
  `can_view_full_profile` tinyint(1) DEFAULT 0,
  `can_view_contact` tinyint(1) DEFAULT 0,
  `can_download_resume` tinyint(1) DEFAULT 0,
  `featured_profile` tinyint(1) DEFAULT 0,
  `featured_job` tinyint(1) DEFAULT 0,
  `priority_listing` tinyint(1) DEFAULT 0,
  `email_alerts` tinyint(1) DEFAULT 1,
  `description` text DEFAULT NULL,
  `features` json DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `plans` (`name`, `slug`, `type`, `price`, `duration_days`, `max_applications`, `max_resume_views`, `max_job_posts`, `can_view_full_profile`, `can_view_contact`, `can_download_resume`, `featured_profile`, `featured_job`, `description`, `status`) VALUES
-- Candidate Plans
('Free', 'candidate-free', 'candidate', 0.00, 365, 3, NULL, NULL, 0, 0, 0, 0, 0, 'Basic plan to get started', 1),
('Basic', 'candidate-basic', 'candidate', 499.00, 90, 20, NULL, NULL, 1, 1, 1, 0, 0, 'Apply for more jobs with full access', 1),
('Premium', 'candidate-premium', 'candidate', 999.00, 180, NULL, NULL, NULL, 1, 1, 1, 1, 0, 'Unlimited applications with featured profile', 1),
-- Employer Plans
('Starter', 'employer-starter', 'employer', 999.00, 30, NULL, 10, 3, 0, 0, 0, 0, 0, 'Post up to 3 jobs, view 10 resumes', 1),
('Professional', 'employer-professional', 'employer', 2499.00, 60, NULL, 50, 10, 1, 1, 1, 0, 1, 'Post 10 jobs with featured listings', 1),
('Enterprise', 'employer-enterprise', 'employer', 4999.00, 90, NULL, NULL, NULL, 1, 1, 1, 0, 1, 'Unlimited everything with priority support', 1);

-- =============================================
-- CANDIDATES
-- =============================================
CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(20) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `nationality` varchar(100) DEFAULT NULL,
  `headline` varchar(255) DEFAULT NULL,
  `bio` text DEFAULT NULL,
  -- Location
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `pincode` varchar(20) DEFAULT NULL,
  -- Professional
  `category` varchar(100) DEFAULT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `academic_level` varchar(100) DEFAULT NULL,
  `experience_years` decimal(4,1) DEFAULT 0.0,
  `current_salary` decimal(10,2) DEFAULT NULL,
  `expected_salary` decimal(10,2) DEFAULT NULL,
  `notice_period` varchar(50) DEFAULT NULL,
  `availability` enum('immediate','1_week','2_weeks','1_month','negotiable') DEFAULT 'immediate',
  `languages` json DEFAULT NULL,
  -- Nursing Specific
  `nursing_council_reg` varchar(100) DEFAULT NULL,
  `passport_number` varchar(50) DEFAULT NULL,
  `passport_expiry` date DEFAULT NULL,
  `ielts_score` decimal(3,1) DEFAULT NULL,
  `oet_score` varchar(20) DEFAULT NULL,
  -- Resume & Documents
  `resume` varchar(255) DEFAULT NULL,
  `resume_updated_at` datetime DEFAULT NULL,
  -- Preferences
  `preferred_locations` json DEFAULT NULL,
  `preferred_job_types` json DEFAULT NULL,
  `open_to_overseas` tinyint(1) DEFAULT 0,
  -- Status
  `profile_completion` int(3) DEFAULT 0,
  `is_verified` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified` tinyint(1) DEFAULT 0,
  `email_verify_token` varchar(100) DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_expires` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `last_active` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CANDIDATE EDUCATION
-- =============================================
CREATE TABLE `candidate_education` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `degree` varchar(200) NOT NULL,
  `institution` varchar(200) NOT NULL,
  `year_from` year(4) DEFAULT NULL,
  `year_to` year(4) DEFAULT NULL,
  `grade` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `candidate_id` (`candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CANDIDATE EXPERIENCE
-- =============================================
CREATE TABLE `candidate_experience` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `job_title` varchar(200) NOT NULL,
  `employer_name` varchar(200) NOT NULL,
  `location` varchar(200) DEFAULT NULL,
  `employment_type` varchar(50) DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `is_current` tinyint(1) DEFAULT 0,
  `description` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `candidate_id` (`candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CANDIDATE CERTIFICATIONS
-- =============================================
CREATE TABLE `candidate_certifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `issuing_org` varchar(200) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `credential_id` varchar(100) DEFAULT NULL,
  `credential_url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `candidate_id` (`candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CANDIDATE CUSTOM FIELD VALUES
-- =============================================
CREATE TABLE `candidate_field_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `candidate_field` (`candidate_id`, `field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- EMPLOYERS
-- =============================================
CREATE TABLE `employers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(20) NOT NULL,
  `company_name` varchar(200) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `banner` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  -- Company Info
  `industry` varchar(100) DEFAULT NULL,
  `company_size` varchar(50) DEFAULT NULL,
  `founded_year` year(4) DEFAULT NULL,
  `registration_number` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `specializations` json DEFAULT NULL,
  -- Location
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `pincode` varchar(20) DEFAULT NULL,
  -- Social
  `linkedin` varchar(255) DEFAULT NULL,
  `facebook` varchar(255) DEFAULT NULL,
  `twitter` varchar(255) DEFAULT NULL,
  -- Status
  `is_verified` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified` tinyint(1) DEFAULT 0,
  `email_verify_token` varchar(100) DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_expires` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- EMPLOYER CUSTOM FIELD VALUES
-- =============================================
CREATE TABLE `employer_field_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employer_field` (`employer_id`, `field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CUSTOM FIELDS
-- =============================================
CREATE TABLE `custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_name` varchar(100) NOT NULL,
  `field_label` varchar(200) NOT NULL,
  `field_type` enum('text','textarea','select','multiselect','radio','checkbox','date','file','number','email','url') NOT NULL DEFAULT 'text',
  `field_for` enum('candidate','employer') NOT NULL,
  `options` json DEFAULT NULL COMMENT 'For select/radio/checkbox types',
  `placeholder` varchar(255) DEFAULT NULL,
  `default_value` varchar(255) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT 0,
  `is_visible_public` tinyint(1) DEFAULT 1,
  `is_searchable` tinyint(1) DEFAULT 0,
  `category` varchar(100) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- JOBS
-- =============================================
CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_code` varchar(20) NOT NULL,
  `employer_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(300) NOT NULL,
  `category` enum('Hospital','Corporate','Home Nurse','Overseas') NOT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `academic_level` varchar(100) DEFAULT NULL,
  `job_type` enum('full_time','part_time','contract','temporary','internship') DEFAULT 'full_time',
  `experience_min` decimal(4,1) DEFAULT 0.0,
  `experience_max` decimal(4,1) DEFAULT NULL,
  `salary_min` decimal(10,2) DEFAULT NULL,
  `salary_max` decimal(10,2) DEFAULT NULL,
  `salary_type` enum('monthly','yearly','hourly','fixed') DEFAULT 'monthly',
  `hide_salary` tinyint(1) DEFAULT 0,
  `vacancies` int(11) DEFAULT 1,
  -- Location
  `location` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `is_remote` tinyint(1) DEFAULT 0,
  -- Content
  `description` longtext NOT NULL,
  `requirements` longtext DEFAULT NULL,
  `benefits` text DEFAULT NULL,
  `skills` json DEFAULT NULL,
  `qualifications` json DEFAULT NULL,
  -- Overseas specific
  `visa_assistance` tinyint(1) DEFAULT 0,
  `accommodation` tinyint(1) DEFAULT 0,
  `air_ticket` tinyint(1) DEFAULT 0,
  `food_allowance` tinyint(1) DEFAULT 0,
  `medical_insurance` tinyint(1) DEFAULT 0,
  -- Status
  `status` enum('draft','active','paused','expired','closed') DEFAULT 'draft',
  `is_featured` tinyint(1) DEFAULT 0,
  `is_urgent` tinyint(1) DEFAULT 0,
  `application_deadline` date DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `views` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `job_code` (`job_code`),
  UNIQUE KEY `slug` (`slug`),
  KEY `employer_id` (`employer_id`),
  KEY `category` (`category`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- JOB APPLICATIONS
-- =============================================
CREATE TABLE `job_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `employer_id` int(11) NOT NULL,
  `cover_letter` text DEFAULT NULL,
  `resume_at_apply` varchar(255) DEFAULT NULL,
  `status` enum('applied','viewed','shortlisted','interview','offered','rejected','withdrawn') DEFAULT 'applied',
  `employer_notes` text DEFAULT NULL,
  `interview_date` datetime DEFAULT NULL,
  `interview_mode` enum('online','offline','phone') DEFAULT NULL,
  `interview_link` varchar(500) DEFAULT NULL,
  `offer_salary` decimal(10,2) DEFAULT NULL,
  `applied_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status_updated_at` timestamp NULL DEFAULT NULL,
  `notification_sent` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_application` (`job_id`, `candidate_id`),
  KEY `candidate_id` (`candidate_id`),
  KEY `employer_id` (`employer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- WISHLIST / SAVED JOBS
-- =============================================
CREATE TABLE `wishlists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `saved_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_wishlist` (`candidate_id`, `job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- SAVED CANDIDATES (by employer)
-- =============================================
CREATE TABLE `saved_candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `notes` text DEFAULT NULL,
  `saved_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_saved` (`employer_id`, `candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- RESUME VIEWS (track employer views)
-- =============================================
CREATE TABLE `resume_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `viewed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_view` (`employer_id`, `candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- SUBSCRIPTIONS / PAYMENTS
-- =============================================
CREATE TABLE `subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscription_code` varchar(30) NOT NULL,
  `user_type` enum('candidate','employer') NOT NULL,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'INR',
  `payment_gateway` varchar(50) DEFAULT NULL,
  `gateway_order_id` varchar(255) DEFAULT NULL,
  `gateway_payment_id` varchar(255) DEFAULT NULL,
  `gateway_signature` varchar(500) DEFAULT NULL,
  `gateway_response` json DEFAULT NULL,
  `status` enum('pending','active','failed','cancelled','expired') DEFAULT 'pending',
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `applications_used` int(11) DEFAULT 0,
  `resume_views_used` int(11) DEFAULT 0,
  `job_posts_used` int(11) DEFAULT 0,
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_code` (`subscription_code`),
  KEY `user_id_type` (`user_id`, `user_type`),
  KEY `plan_id` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- EMAIL NOTIFICATIONS LOG
-- =============================================
CREATE TABLE `email_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` varchar(150) NOT NULL,
  `to_name` varchar(100) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `body` longtext DEFAULT NULL,
  `status` enum('sent','failed','pending') DEFAULT 'pending',
  `error` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `sent_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- EMAIL TEMPLATES
-- =============================================
CREATE TABLE `email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_key` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` longtext NOT NULL,
  `variables` json DEFAULT NULL COMMENT 'Available template variables',
  `status` tinyint(1) DEFAULT 1,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `template_key` (`template_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `email_templates` (`template_key`, `subject`, `body`, `variables`) VALUES
('candidate_welcome', 'Welcome to {{site_name}}!', '<h2>Welcome, {{name}}!</h2><p>Your account has been created. Please verify your email to get started.</p><p><a href="{{verify_link}}">Verify Email</a></p>', '["name","site_name","verify_link"]'),
('employer_welcome', 'Welcome to {{site_name}} - Employer Account', '<h2>Welcome, {{company_name}}!</h2><p>Your employer account is ready. <a href="{{verify_link}}">Verify Email</a></p>', '["company_name","site_name","verify_link"]'),
('job_applied_candidate', 'Application Submitted - {{job_title}}', '<h2>Hi {{name}},</h2><p>You have successfully applied for <strong>{{job_title}}</strong> at <strong>{{company_name}}</strong>.</p><p>Application ID: {{application_id}}</p>', '["name","job_title","company_name","application_id"]'),
('job_applied_employer', 'New Application for {{job_title}}', '<h2>New Application Received</h2><p><strong>{{candidate_name}}</strong> has applied for <strong>{{job_title}}</strong>.</p><p><a href="{{view_link}}">View Application</a></p>', '["candidate_name","job_title","view_link"]'),
('candidate_shortlisted', 'Great News! You have been Shortlisted', '<h2>Congratulations {{name}}!</h2><p>You have been shortlisted for <strong>{{job_title}}</strong> at <strong>{{company_name}}</strong>.</p>', '["name","job_title","company_name"]'),
('interview_scheduled', 'Interview Scheduled - {{job_title}}', '<h2>Interview Scheduled</h2><p>Your interview for <strong>{{job_title}}</strong> is scheduled on <strong>{{interview_date}}</strong>.</p><p>Mode: {{interview_mode}}</p>{{interview_link}}', '["name","job_title","interview_date","interview_mode","interview_link"]'),
('password_reset', 'Reset Your Password', '<h2>Password Reset Request</h2><p>Click the link below to reset your password. This link expires in 1 hour.</p><p><a href="{{reset_link}}">Reset Password</a></p>', '["reset_link"]'),
('payment_success', 'Payment Successful - {{plan_name}}', '<h2>Payment Confirmed!</h2><p>Your subscription to <strong>{{plan_name}}</strong> is now active until <strong>{{expires_at}}</strong>.</p><p>Amount Paid: {{amount}}</p>', '["plan_name","expires_at","amount"]');

-- =============================================
-- NOTIFICATIONS (in-app)
-- =============================================
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_type` enum('candidate','employer','admin') NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `link` varchar(500) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_type`, `user_id`),
  KEY `is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- WORDPRESS IMPORT LOG
-- =============================================
CREATE TABLE `import_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `import_type` enum('candidate','employer') NOT NULL,
  `wp_user_id` int(11) DEFAULT NULL,
  `wp_post_id` int(11) DEFAULT NULL,
  `local_id` int(11) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `status` enum('success','failed','skipped') DEFAULT 'success',
  `notes` text DEFAULT NULL,
  `imported_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CATEGORIES / SPECIALIZATIONS
-- =============================================
CREATE TABLE `specializations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `slug` varchar(220) DEFAULT NULL,
  `category` enum('Hospital','Corporate','Home Nurse','Overseas') DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `specializations` (`name`, `category`) VALUES
('General Nurse (GNM)', 'Hospital'), ('Staff Nurse BSc', 'Hospital'), ('ICU Nurse', 'Hospital'),
('OT Nurse', 'Hospital'), ('Emergency/Casualty Nurse', 'Hospital'), ('Pediatric Nurse', 'Hospital'),
('Oncology Nurse', 'Hospital'), ('Cardiac Nurse', 'Hospital'), ('Midwife', 'Hospital'),
('Dialysis Nurse', 'Hospital'), ('Neonatal Nurse', 'Hospital'), ('Psychiatric Nurse', 'Hospital'),
('Occupational Health Nurse', 'Corporate'), ('Corporate Wellness Nurse', 'Corporate'),
('Factory/Plant Nurse', 'Corporate'), ('School/College Nurse', 'Corporate'),
('Home Care Nurse', 'Home Nurse'), ('Elderly Care Nurse', 'Home Nurse'),
('Post-operative Home Nurse', 'Home Nurse'), ('Palliative Care Nurse', 'Home Nurse'),
('Infant Care Nurse', 'Home Nurse'), ('Wound Care Nurse', 'Home Nurse'),
('IELTS Qualified Nurse', 'Overseas'), ('OET Qualified Nurse', 'Overseas'),
('UK Nurse (NMC)', 'Overseas'), ('US Nurse (NCLEX)', 'Overseas'),
('Gulf Nurse (DHA/HAAD)', 'Overseas'), ('Canada Nurse', 'Overseas'),
('Australia Nurse (AHPRA)', 'Overseas');

-- =============================================
-- PAGES (CMS)
-- =============================================
CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `show_in_footer` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `pages` (`title`, `slug`, `content`) VALUES
('About Us', 'about', '<h2>About Nursing Guru</h2><p>We connect nursing professionals with top healthcare employers across India and abroad.</p>'),
('Privacy Policy', 'privacy-policy', '<h2>Privacy Policy</h2><p>Your privacy is important to us...</p>'),
('Terms of Service', 'terms', '<h2>Terms of Service</h2><p>By using Nursing Guru, you agree to these terms...</p>'),
('Contact Us', 'contact', '');

-- Foreign Keys
ALTER TABLE `candidate_experience` ADD CONSTRAINT `fk_exp_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE;
ALTER TABLE `candidate_field_values` ADD CONSTRAINT `fk_cfv_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE;
ALTER TABLE `jobs` ADD CONSTRAINT `fk_job_employer` FOREIGN KEY (`employer_id`) REFERENCES `employers` (`id`) ON DELETE CASCADE;
ALTER TABLE `job_applications` ADD CONSTRAINT `fk_app_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE;
ALTER TABLE `wishlists` ADD CONSTRAINT `fk_wish_job` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE;

-- =============================================
-- CONTACT MESSAGES
-- =============================================
CREATE TABLE `contact_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `user_type` enum('candidate','employer','other') DEFAULT 'other',
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `replied_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- JOB ALERTS (for candidates)
-- =============================================
CREATE TABLE `job_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `keyword` varchar(150) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `job_type` varchar(50) DEFAULT NULL,
  `frequency` enum('daily','weekly') DEFAULT 'daily',
  `is_active` tinyint(1) DEFAULT 1,
  `last_sent` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `candidate_id` (`candidate_id`),
  CONSTRAINT `fk_alert_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- IMPORT BATCH LOG (summary per import run)
-- =============================================
CREATE TABLE IF NOT EXISTS `import_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `import_type` varchar(50) NOT NULL COMMENT 'candidates | employers | mixed | wordpress',
  `candidates_imported` int(11) DEFAULT 0,
  `employers_imported` int(11) DEFAULT 0,
  `updated` int(11) DEFAULT 0,
  `skipped` int(11) DEFAULT 0,
  `status` enum('success','partial','failed') DEFAULT 'success',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- Add location alias column to employers
-- =============================================
UPDATE `employers` SET `location` = `city` WHERE `location` IS NULL AND `city` IS NOT NULL;

UPDATE `candidates` SET `location` = `city` WHERE `location` IS NULL AND `city` IS NOT NULL;

-- =============================================
-- SEO SETTINGS (additional)
-- =============================================
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `setting_group`) VALUES
('site_title_suffix',     ' | Nursing Guru — Jobs for Nurses', 'seo'),
('footer_copyright_text', '© {year} Nursing Guru. All rights reserved.', 'seo'),
('footer_tagline',        'Made with ❤️ for Indian Nurses', 'seo'),
('social_facebook',       '', 'social'),
('social_instagram',      '', 'social'),
('social_linkedin',       '', 'social'),
('social_twitter',        '', 'social'),
('social_youtube',        '', 'social'),
('google_analytics',      '', 'seo'),
('google_tag_manager',    '', 'seo'),
('robots_txt',            "User-agent: *\nAllow: /", 'seo'),
('schema_org_enabled',    '1', 'seo'),
('sitemap_enabled',       '1', 'seo'),
('og_image',              '', 'seo'),
('twitter_card',          'summary_large_image', 'seo'),
('twitter_site',          '', 'seo'),
('canonical_url',         '', 'seo');