CREATE DATABASE IF NOT EXISTS foreign_agent_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE foreign_agent_db;

CREATE TABLE IF NOT EXISTS agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) NOT NULL,
    nid_no VARCHAR(100) NOT NULL,
    applicant_photo VARCHAR(255) NOT NULL,
    applicant_nid_file VARCHAR(255) NOT NULL,
    org_type ENUM('Recruiting Agency', 'Individual', 'Social Organization') NOT NULL,
    license_no VARCHAR(100) NULL,
    country_city VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    address TEXT NOT NULL,
    email VARCHAR(255) NULL,
    
    family_member_1_name VARCHAR(255) NOT NULL,
    family_member_1_relation VARCHAR(50) NOT NULL,
    family_member_1_phone VARCHAR(50) NOT NULL,
    family_member_1_nid VARCHAR(100) NOT NULL,
    family_member_1_nid_file VARCHAR(255) NOT NULL,
    
    family_member_2_name VARCHAR(255) NOT NULL,
    family_member_2_relation VARCHAR(50) NOT NULL,
    family_member_2_phone VARCHAR(50) NOT NULL,
    family_member_2_nid VARCHAR(100) NOT NULL,
    family_member_2_nid_file VARCHAR(255) NOT NULL,
    
    family_member_3_name VARCHAR(255) NOT NULL,
    family_member_3_relation VARCHAR(50) NOT NULL,
    family_member_3_phone VARCHAR(50) NOT NULL,
    family_member_3_nid VARCHAR(100) NOT NULL,
    family_member_3_nid_file VARCHAR(255) NOT NULL,
    
    sectors TEXT NULL, -- Stored as comma-separated values or JSON
    experience_years VARCHAR(100) NULL,
    total_placed VARCHAR(100) NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);