 CREATE TABLE `foreign_jobs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_mb_id` varchar(50) NOT NULL,
`title` varchar(200) NOT NULL,
`description_ko` text DEFAULT NULL,
`description_en` text DEFAULT NULL,
`visa_support` tinyint(1) DEFAULT 0,
`location` varchar(100) DEFAULT NULL,
`salary_min` int(11) DEFAULT NULL,
`salary_max` int(11) DEFAULT NULL,
`employment_type` enum('full_time','part_time','contract','temporary') DEFAULT 'full_time',
`required_skills` text DEFAULT NULL,
`status` enum('active','filled','closed') DEFAULT 'active',
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_company` (`company_mb_id`),
KEY `idx_status` (`status`),
KEY `idx_location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `foreign_level_sync_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mb_id` varchar(50) NOT NULL,
`from_level` int(11) DEFAULT NULL,
`to_level` int(11) DEFAULT NULL,
`reason` varchar(200) DEFAULT NULL,
`processed` tinyint(1) DEFAULT 0,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_mb_id` (`mb_id`),
KEY `idx_processed` (`processed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `foreign_matches` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`resume_id` int(11) NOT NULL,
`job_id` int(11) NOT NULL,
`status` enum('proposed','contacted','interview','offer','placed','paid1','paid2','closed') DEFAULT 'proposed',
`admin_note` text DEFAULT NULL,
`fee_total` decimal(10,2) DEFAULT NULL,
`fee_stage1` decimal(10,2) DEFAULT NULL,
`fee_stage2` decimal(10,2) 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`),
KEY `resume_id` (`resume_id`),
KEY `job_id` (`job_id`),
KEY `idx_status` (`status`),
KEY `idx_created` (`created_at`),
CONSTRAINT `foreign_matches_ibfk_1` FOREIGN KEY (`resume_id`) REFERENCES `foreign_resumes` (`id`) ON DELETE CASCADE,
CONSTRAINT `foreign_matches_ibfk_2` FOREIGN KEY (`job_id`) REFERENCES `foreign_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `foreign_resumes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mb_id` varchar(50) NOT NULL,
`title` varchar(200) NOT NULL,
`content_ko` text DEFAULT NULL,
`content_en` text DEFAULT NULL,
`content_vi` text DEFAULT NULL,
`content_cn` text DEFAULT NULL,
`visa_type` varchar(20) DEFAULT NULL,
`work_experience` text DEFAULT NULL,
`skills` text DEFAULT NULL,
`job_category_1` varchar(50) DEFAULT NULL,
`job_category_2` varchar(50) DEFAULT NULL,
`region` varchar(50) DEFAULT NULL,
`skill_level` varchar(20) DEFAULT NULL,
`keywords` text DEFAULT NULL,
`location_preference` varchar(100) DEFAULT NULL,
`salary_expectation` int(11) DEFAULT NULL,
`status` enum('active','matched','inactive') DEFAULT 'active',
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_mb_id` (`mb_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

;


CREATE TABLE `u1_member` (
`mb_no` int(11) NOT NULL AUTO_INCREMENT,
`mb_id` varchar(20) NOT NULL DEFAULT '',
`mb_password` varchar(255) NOT NULL DEFAULT '',
`mb_name` varchar(255) NOT NULL DEFAULT '',
`mb_nick` varchar(255) NOT NULL DEFAULT '',
`mb_nick_date` date NOT NULL DEFAULT '0000-00-00',
`mb_email` varchar(255) NOT NULL DEFAULT '',
`mb_homepage` varchar(255) NOT NULL DEFAULT '',
`mb_level` tinyint(4) NOT NULL DEFAULT 0,
`mb_sex` char(1) NOT NULL DEFAULT '',
`mb_birth` varchar(255) NOT NULL DEFAULT '',
`mb_tel` varchar(255) NOT NULL DEFAULT '',
`mb_hp` varchar(255) NOT NULL DEFAULT '',
`mb_certify` varchar(20) NOT NULL DEFAULT '',
`mb_adult` tinyint(4) NOT NULL DEFAULT 0,
`mb_dupinfo` varchar(255) NOT NULL DEFAULT '',
`mb_zip1` char(3) NOT NULL DEFAULT '',
`mb_zip2` char(3) NOT NULL DEFAULT '',
`mb_addr1` varchar(255) NOT NULL DEFAULT '',
`mb_addr2` varchar(255) NOT NULL DEFAULT '',
`mb_addr3` varchar(255) NOT NULL DEFAULT '',
`mb_addr_jibeon` varchar(255) NOT NULL DEFAULT '',
`mb_signature` text NOT NULL,
`mb_recommend` varchar(255) NOT NULL DEFAULT '',
`mb_point` int(11) NOT NULL DEFAULT 0,
`mb_today_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_login_ip` varchar(255) NOT NULL DEFAULT '',
`mb_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_ip` varchar(255) NOT NULL DEFAULT '',
`mb_leave_date` varchar(8) NOT NULL DEFAULT '',
`mb_intercept_date` varchar(8) NOT NULL DEFAULT '',
`mb_email_certify` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_email_certify2` varchar(255) NOT NULL DEFAULT '',
`mb_memo` text NOT NULL,
`mb_lost_certify` varchar(255) NOT NULL,
`mb_mailling` tinyint(4) NOT NULL DEFAULT 0,
`mb_mailling_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_sms` tinyint(4) NOT NULL DEFAULT 0,
`mb_sms_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_open` tinyint(4) NOT NULL DEFAULT 0,
`mb_open_date` date NOT NULL DEFAULT '0000-00-00',
`mb_profile` text NOT NULL,
`mb_memo_call` varchar(255) NOT NULL DEFAULT '',
`mb_memo_cnt` int(11) NOT NULL DEFAULT 0,
`mb_scrap_cnt` int(11) NOT NULL DEFAULT 0,
`mb_marketing_agree` tinyint(1) NOT NULL DEFAULT 0,
`mb_marketing_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_thirdparty_agree` tinyint(1) NOT NULL DEFAULT 0,
`mb_thirdparty_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`mb_agree_log` text NOT NULL,
`mb_board_post` tinyint(1) NOT NULL DEFAULT 0,
`mb_board_reply` tinyint(1) NOT NULL DEFAULT 0,
`mb_board_comment` tinyint(1) NOT NULL DEFAULT 0,
`mb_board_recomment` tinyint(1) NOT NULL DEFAULT 0,
`mb_1` varchar(255) NOT NULL DEFAULT '',
`mb_2` varchar(255) NOT NULL DEFAULT '',
`mb_3` varchar(255) NOT NULL DEFAULT '',
`mb_4` varchar(255) NOT NULL DEFAULT '',
`mb_5` varchar(255) NOT NULL DEFAULT '',
`mb_6` varchar(255) NOT NULL DEFAULT '',
`mb_7` varchar(255) NOT NULL DEFAULT '',
`mb_8` varchar(255) NOT NULL DEFAULT '',
`mb_9` varchar(255) NOT NULL DEFAULT '',
`mb_10` varchar(255) NOT NULL DEFAULT '',
`resume_limit` int(11) DEFAULT 1 COMMENT '이력서 작성 개수 제한 (기본 1개)',
PRIMARY KEY (`mb_no`),
UNIQUE KEY `mb_id` (`mb_id`),
KEY `mb_today_login` (`mb_today_login`),
KEY `mb_datetime` (`mb_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;











