CREATE TABLE `users` (
  `uuid` CHAR(36) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `google_id` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `profile_picture` VARCHAR(255) DEFAULT NULL,
  `role` ENUM('admin','employer','jobseeker','staff') DEFAULT 'jobseeker',
  `is_verified` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` ENUM('active','inactive') DEFAULT 'active',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `admin_profiles` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `full_name` VARCHAR(100) DEFAULT NULL,
  `contact_number` VARCHAR(20) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `employers` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `company_name` VARCHAR(150) DEFAULT NULL,
  `contact_number` VARCHAR(20) DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `industry` VARCHAR(100) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `company_logo` VARCHAR(255) DEFAULT NULL,
  `about_company` TEXT DEFAULT NULL,
  `profile_completed` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `job_seekers` (
  `uuid` CHAR(36) NOT NULL PRIMARY KEY,
  `user_uuid` CHAR(36) NOT NULL,
  `fullName` VARCHAR(100) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `gender` ENUM('male','female') DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `professional_title` VARCHAR(100) DEFAULT NULL,
  `skills` TEXT DEFAULT NULL,
  `education` TEXT DEFAULT NULL,
  `resume_file` VARCHAR(255) DEFAULT NULL,
  `profile_completed` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `industries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `job_seeker_industries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job_seeker_uuid` CHAR(36) NOT NULL,
  `industry_id` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `countries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `job_posts` (
  `uuid` CHAR(36) NOT NULL,
  `employer_uuid` CHAR(36) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `job_level` VARCHAR(100) DEFAULT NULL,
  `job_description` TEXT NOT NULL,
  `job_type` VARCHAR(100) DEFAULT NULL,
  `industry` INT DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `salary_range` VARCHAR(100) DEFAULT NULL,
  `additional_information` TEXT DEFAULT NULL,
  `requirements_qualifications` TEXT DEFAULT NULL,
  `status` ENUM('open','closed') DEFAULT 'open',
  `application_deadline` DATE DEFAULT NULL,
  `country_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `applications` (
  `uuid` CHAR(36) NOT NULL,
  `job_uuid` CHAR(36) NOT NULL,
  `job_seeker_uuid` CHAR(36) NOT NULL,
  `cover_letter` TEXT DEFAULT NULL,
  `resume_file` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','reviewed') DEFAULT 'pending',
  `applied_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `cv_revamp_requests` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `cv_file` VARCHAR(255) DEFAULT NULL,
  `user_notes` TEXT DEFAULT NULL,
  `status` ENUM('pending','viewed','completed') DEFAULT 'pending',
  `submitted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `email_verifications` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_used` TINYINT(1) DEFAULT 0,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `password_resets` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_used` TINYINT(1) DEFAULT 0,
  `used_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `saved_jobs` (
  `uuid` CHAR(36) NOT NULL,
  `job_uuid` CHAR(36) NOT NULL,
  `job_seeker_uuid` CHAR(36) NOT NULL,
  `saved_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `shared_cvs` (
  `uuid` CHAR(36) NOT NULL,
  `user_uuid` CHAR(36) NOT NULL,
  `cv_file` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `personal_notes` TEXT DEFAULT NULL,
  `is_public` ENUM('true','false') DEFAULT 'false',
  `status` ENUM('active','inactive') DEFAULT 'active',
  `shared_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_accessed` TIMESTAMP NULL DEFAULT NULL,
  `access_count` INT DEFAULT 0,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Foreign Key Constraints
ALTER TABLE `admin_profiles` ADD CONSTRAINT `fk_admin_profiles_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `employers` ADD CONSTRAINT `fk_employers_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `job_seekers` ADD CONSTRAINT `fk_job_seekers_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `job_posts` ADD CONSTRAINT `fk_job_posts_employer` FOREIGN KEY (`employer_uuid`) REFERENCES `employers`(`uuid`);
ALTER TABLE `job_posts` ADD CONSTRAINT `fk_job_posts_industry` FOREIGN KEY (`industry`) REFERENCES `industries`(`id`);
ALTER TABLE `job_posts` ADD CONSTRAINT `fk_job_posts_country` FOREIGN KEY (`country_id`) REFERENCES `countries`(`id`);
ALTER TABLE `job_seeker_industries` ADD CONSTRAINT `fk_job_seeker_industries_job_seeker` FOREIGN KEY (`job_seeker_uuid`) REFERENCES `job_seekers`(`uuid`);
ALTER TABLE `job_seeker_industries` ADD CONSTRAINT `fk_job_seeker_industries_industry` FOREIGN KEY (`industry_id`) REFERENCES `industries`(`id`);
ALTER TABLE `applications` ADD CONSTRAINT `fk_applications_job` FOREIGN KEY (`job_uuid`) REFERENCES `job_posts`(`uuid`);
ALTER TABLE `applications` ADD CONSTRAINT `fk_applications_job_seeker` FOREIGN KEY (`job_seeker_uuid`) REFERENCES `job_seekers`(`uuid`);
ALTER TABLE `cv_revamp_requests` ADD CONSTRAINT `fk_cv_revamp_requests_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `email_verifications` ADD CONSTRAINT `fk_email_verifications_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `password_resets` ADD CONSTRAINT `fk_password_resets_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);
ALTER TABLE `saved_jobs` ADD CONSTRAINT `fk_saved_jobs_job` FOREIGN KEY (`job_uuid`) REFERENCES `job_posts`(`uuid`);
ALTER TABLE `saved_jobs` ADD CONSTRAINT `fk_saved_jobs_job_seeker` FOREIGN KEY (`job_seeker_uuid`) REFERENCES `job_seekers`(`uuid`);
ALTER TABLE `shared_cvs` ADD CONSTRAINT `fk_shared_cvs_user` FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`);