CREATE TABLE `members` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` ENUM('worker','company','admin') NOT NULL DEFAULT 'worker' COMMENT '역할',
  `email` VARCHAR(191) NOT NULL COMMENT '이메일',
  `password_hash` VARCHAR(255) NOT NULL COMMENT '비밀번호 해시',
  `name` VARCHAR(100) NOT NULL COMMENT '이름',
  `phone` VARCHAR(30) NOT NULL COMMENT '전화번호',
  `phone_verified` TINYINT(1) DEFAULT 0 COMMENT '전화 인증 여부',
  `company_name` VARCHAR(200) DEFAULT NULL COMMENT '회사명',
  `business_number` VARCHAR(20) DEFAULT NULL COMMENT '사업자번호',
  `company_verified` TINYINT(1) DEFAULT 0 COMMENT '회사 인증 여부',
  `nationality` VARCHAR(50) DEFAULT NULL COMMENT '국적',
  `visa_type` VARCHAR(20) DEFAULT NULL COMMENT '비자 타입 E-9 F-4 F-6 D-2 H-2',
  `visa_expiry` DATE DEFAULT NULL COMMENT '비자 만료일',
  `korean_level` ENUM('none','basic','intermediate','advanced','fluent') DEFAULT 'basic' COMMENT '한국어 수준',
  `status` ENUM('active','suspended','deleted') DEFAULT 'active' COMMENT '상태',
  `last_login_at` DATETIME DEFAULT NULL COMMENT '마지막 로그인',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_phone` (`phone`),
  KEY `idx_role_status` (`role`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` BIGINT UNSIGNED NOT NULL COMMENT '회사 members id',
  `title` VARCHAR(200) NOT NULL COMMENT '공고 제목',
  `description` TEXT NOT NULL COMMENT '상세 설명',
  `visa_types` JSON NOT NULL COMMENT '허용 비자 목록',
  `job_category` VARCHAR(50) NOT NULL COMMENT '직종 factory farm restaurant construction care',
  `sido` VARCHAR(20) NOT NULL COMMENT '시도 경기도 서울특별시',
  `sigungu` VARCHAR(30) NOT NULL COMMENT '시군구 부천시 안산시',
  `detail_address` VARCHAR(200) DEFAULT NULL COMMENT '상세 주소',
  `salary_type` ENUM('monthly','hourly','yearly') DEFAULT 'monthly' COMMENT '급여 타입',
  `salary_min` INT NOT NULL COMMENT '최소 급여',
  `salary_max` INT DEFAULT NULL COMMENT '최대 급여',
  `employment_type` ENUM('full_time','part_time','contract','day') DEFAULT 'full_time' COMMENT '고용 형태',
  `work_days` VARCHAR(50) DEFAULT NULL COMMENT '근무 요일',
  `work_hours` VARCHAR(100) DEFAULT NULL COMMENT '근무 시간',
  `dormitory` TINYINT(1) DEFAULT 0 COMMENT '기숙사 제공 여부',
  `meal` TINYINT(1) DEFAULT 0 COMMENT '식사 제공 여부',
  `korean_required` ENUM('none','basic','intermediate') DEFAULT 'none' COMMENT '한국어 필수 여부',
  `visa_support` TINYINT(1) DEFAULT 0 COMMENT '비자 지원 여부',
  `hiring_count` INT DEFAULT 1 COMMENT '채용 인원',
  `status` ENUM('draft','active','closed','filled') DEFAULT 'active' COMMENT '공고 상태',
  `view_count` INT DEFAULT 0 COMMENT '조회수',
  `is_paid` TINYINT(1) DEFAULT 0 COMMENT '유료 공고 여부',
  `paid_until` DATETIME DEFAULT NULL COMMENT '유료 만료일',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_filter` (`status`,`sido`,`job_category`),
  KEY `idx_created` (`created_at` DESC),
  CONSTRAINT `fk_jobs_company` FOREIGN KEY (`company_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `resumes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `worker_id` BIGINT UNSIGNED NOT NULL COMMENT '구직자 members id',
  `title` VARCHAR(200) NOT NULL COMMENT '이력서 제목',
  `bio` TEXT DEFAULT NULL COMMENT '자기소개',
  `visa_type` VARCHAR(20) NOT NULL COMMENT '보유 비자',
  `job_category_1` VARCHAR(50) NOT NULL COMMENT '희망 직종1',
  `job_category_2` VARCHAR(50) DEFAULT NULL COMMENT '희망 직종2',
  `experience_years` INT DEFAULT 0 COMMENT '경력 년수',
  `skills` JSON DEFAULT NULL COMMENT '보유 기술',
  `sido_preference` VARCHAR(20) DEFAULT NULL COMMENT '희망 지역 시도',
  `salary_expectation` INT DEFAULT NULL COMMENT '희망 급여',
  `korean_level` VARCHAR(20) DEFAULT NULL COMMENT '한국어 수준',
  `available_from` DATE DEFAULT NULL COMMENT '근무 가능일',
  `status` ENUM('active','inactive','hired') DEFAULT 'active' COMMENT '상태',
  `view_count` INT DEFAULT 0 COMMENT '조회수',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
  PRIMARY KEY (`id`),
  KEY `idx_worker` (`worker_id`),
  KEY `idx_search` (`status`,`visa_type`,`job_category_1`,`sido_preference`),
  CONSTRAINT `fk_resumes_worker` FOREIGN KEY (`worker_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `applications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` BIGINT UNSIGNED NOT NULL COMMENT '공고 id',
  `resume_id` BIGINT UNSIGNED NOT NULL COMMENT '이력서 id',
  `worker_id` BIGINT UNSIGNED NOT NULL COMMENT '구직자 id',
  `company_id` BIGINT UNSIGNED NOT NULL COMMENT '회사 id',
  `status` ENUM('applied','viewed','contacted','interview','hired','rejected','closed') DEFAULT 'applied' COMMENT '진행 상태',
  `admin_note` TEXT DEFAULT NULL COMMENT '관리자 메모',
  `hired_at` DATETIME DEFAULT NULL COMMENT '채용 확정일',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_job_resume` (`job_id`,`resume_id`),
  KEY `idx_company_status` (`company_id`,`status`),
  KEY `idx_worker` (`worker_id`),
  CONSTRAINT `fk_app_job` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_app_resume` FOREIGN KEY (`resume_id`) REFERENCES `resumes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` BIGINT UNSIGNED NOT NULL COMMENT '회사 id',
  `job_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '공고 id',
  `type` ENUM('job_post','boost','resume_view') NOT NULL COMMENT '결제 타입',
  `amount` INT NOT NULL COMMENT '결제 금액',
  `method` VARCHAR(30) DEFAULT NULL COMMENT '결제 수단',
  `status` ENUM('pending','paid','failed','refunded') DEFAULT 'pending' COMMENT '결제 상태',
  `paid_at` DATETIME DEFAULT NULL COMMENT '결제일',
  `expires_at` DATETIME DEFAULT NULL COMMENT '만료일',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`),
  CONSTRAINT `fk_pay_company` FOREIGN KEY (`company_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
