SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS audit_logs,arrival_checkins,location_updates,job_offers,applications,assignments,financial_ledger,payment_records,payout_requests,refunds,disputes,complaints,reviews,notifications,notification_preferences,worker_availability,preferred_work_zones,worker_skills,skills,job_requirements,full_time_applications,training_enrollments,training_courses,risk_flags,routing_cache,platform_settings,worker_verifications,employer_verifications,jobs,job_categories,worker_profiles,employer_profiles,user_roles,permissions,roles,users,migration_versions;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,phone VARCHAR(25) NOT NULL UNIQUE,email VARCHAR(190) NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('super_admin','operations_admin','verification_officer','finance_admin','support_officer','employer','worker') NOT NULL,status ENUM('active','restricted','suspended','disabled') NOT NULL DEFAULT 'active',preferred_language VARCHAR(5) NOT NULL DEFAULT 'bn',last_login_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE roles(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,role_key VARCHAR(60) NOT NULL UNIQUE,label_bn VARCHAR(100) NOT NULL);
CREATE TABLE permissions(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,permission_key VARCHAR(100) NOT NULL UNIQUE,label_bn VARCHAR(150) NOT NULL);
CREATE TABLE user_roles(user_id BIGINT UNSIGNED NOT NULL,role_id INT UNSIGNED NOT NULL,PRIMARY KEY(user_id,role_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE);
CREATE TABLE worker_profiles(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL UNIQUE,bio TEXT NULL,experience_years DECIMAL(4,1) NOT NULL DEFAULT 0,minimum_pay DECIMAL(12,2) NULL,available TINYINT(1) NOT NULL DEFAULT 1,availability_json JSON NULL,preferred_categories_json JSON NULL,preferred_zones_json JSON NULL,latitude DECIMAL(10,7) NULL,longitude DECIMAL(10,7) NULL,location_accuracy_m DECIMAL(10,2) NULL,location_updated_at DATETIME NULL,location_consent TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,INDEX idx_worker_location(latitude,longitude),INDEX idx_worker_available(available));
CREATE TABLE employer_profiles(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL UNIQUE,organization_name VARCHAR(190) NOT NULL,business_category VARCHAR(100) NULL,address TEXT NULL,city VARCHAR(100) NULL,verification_status ENUM('unverified','pending','verified','restricted','suspended') NOT NULL DEFAULT 'unverified',verified_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE employer_verifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,employer_id BIGINT UNSIGNED NOT NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',document_path VARCHAR(500) NULL,reviewed_by BIGINT UNSIGNED NULL,reviewed_at DATETIME NULL,notes TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(employer_id) REFERENCES employer_profiles(id),FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE worker_verifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,worker_id BIGINT UNSIGNED NOT NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',verification_type VARCHAR(60) NOT NULL,private_document_path VARCHAR(500) NULL,reviewed_by BIGINT UNSIGNED NULL,reviewed_at DATETIME NULL,notes TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id),FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE skills(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL UNIQUE,is_active TINYINT(1) NOT NULL DEFAULT 1);
CREATE TABLE worker_skills(worker_id BIGINT UNSIGNED NOT NULL,skill_id BIGINT UNSIGNED NOT NULL,level ENUM('beginner','intermediate','advanced','expert') NOT NULL DEFAULT 'beginner',PRIMARY KEY(worker_id,skill_id),FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE,FOREIGN KEY(skill_id) REFERENCES skills(id));
CREATE TABLE job_categories(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL UNIQUE,slug VARCHAR(140) NOT NULL UNIQUE,is_active TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0);
CREATE TABLE jobs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,employer_id BIGINT UNSIGNED NOT NULL,category_id INT UNSIGNED NULL,title VARCHAR(190) NOT NULL,description TEXT NOT NULL,city VARCHAR(100) NOT NULL,area VARCHAR(150) NOT NULL,latitude DECIMAL(10,7) NULL,longitude DECIMAL(10,7) NULL,work_date DATE NULL,start_time TIME NULL,end_time TIME NULL,workers_needed INT UNSIGNED NOT NULL DEFAULT 1,pay_amount DECIMAL(12,2) NOT NULL,pay_unit ENUM('hourly','daily','shift','task','monthly') NOT NULL DEFAULT 'shift',required_verification ENUM('none','basic','enhanced') NOT NULL DEFAULT 'none',status ENUM('draft','pending_review','approved','published','partially_filled','filled','in_progress','completed','cancelled','rejected','under_investigation') NOT NULL DEFAULT 'pending_review',geofence_radius_m INT UNSIGNED NOT NULL DEFAULT 150,reviewed_by BIGINT UNSIGNED NULL,reviewed_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(employer_id) REFERENCES employer_profiles(id),FOREIGN KEY(category_id) REFERENCES job_categories(id) ON DELETE SET NULL,FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_job_status_date(status,work_date),INDEX idx_job_city_area(city,area),INDEX idx_job_location(latitude,longitude));
CREATE TABLE job_requirements(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,skill_id BIGINT UNSIGNED NULL,requirement_text VARCHAR(255) NOT NULL,required TINYINT(1) NOT NULL DEFAULT 1,FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE,FOREIGN KEY(skill_id) REFERENCES skills(id) ON DELETE SET NULL);
CREATE TABLE job_offers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,worker_id BIGINT UNSIGNED NOT NULL,distance_km DECIMAL(8,2) NULL,status ENUM('pending','accepted','declined','expired','cancelled') NOT NULL DEFAULT 'pending',expires_at DATETIME NOT NULL,responded_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_offer_job_worker(job_id,worker_id),INDEX idx_offer_worker_status(worker_id,status),FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE);
CREATE TABLE applications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,worker_id BIGINT UNSIGNED NOT NULL,status ENUM('applied','under_review','shortlisted','interview','selected','rejected','withdrawn') NOT NULL DEFAULT 'applied',cover_note TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_application(job_id,worker_id),FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE);
CREATE TABLE assignments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,worker_id BIGINT UNSIGNED NOT NULL,status ENUM('assigned','in_progress','completed','cancelled','disputed','no_show') NOT NULL DEFAULT 'assigned',assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,started_at DATETIME NULL,completed_at DATETIME NULL,cancelled_at DATETIME NULL,UNIQUE KEY uq_job_worker_assignment(job_id,worker_id),INDEX idx_assignment_status(status),FOREIGN KEY(job_id) REFERENCES jobs(id),FOREIGN KEY(worker_id) REFERENCES worker_profiles(id));
CREATE TABLE worker_availability(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,worker_id BIGINT UNSIGNED NOT NULL,weekday TINYINT UNSIGNED NOT NULL,start_time TIME NOT NULL,end_time TIME NOT NULL,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE);
CREATE TABLE preferred_work_zones(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,worker_id BIGINT UNSIGNED NOT NULL,city VARCHAR(100) NOT NULL,area VARCHAR(150) NULL,latitude DECIMAL(10,7) NULL,longitude DECIMAL(10,7) NULL,radius_km DECIMAL(6,2) NULL,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE);
CREATE TABLE location_updates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,worker_id BIGINT UNSIGNED NOT NULL,assignment_id BIGINT UNSIGNED NULL,latitude DECIMAL(10,7) NOT NULL,longitude DECIMAL(10,7) NOT NULL,accuracy_m DECIMAL(10,2) NULL,consent TINYINT(1) NOT NULL DEFAULT 0,recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_location_worker_time(worker_id,recorded_at),FOREIGN KEY(worker_id) REFERENCES worker_profiles(id) ON DELETE CASCADE,FOREIGN KEY(assignment_id) REFERENCES assignments(id) ON DELETE SET NULL);
CREATE TABLE arrival_checkins(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,assignment_id BIGINT UNSIGNED NOT NULL,latitude DECIMAL(10,7) NULL,longitude DECIMAL(10,7) NULL,distance_km DECIMAL(8,3) NULL,result ENUM('inside','outside','uncertain') NOT NULL,checked_at DATETIME NOT NULL,employer_confirmed TINYINT(1) NOT NULL DEFAULT 0,FOREIGN KEY(assignment_id) REFERENCES assignments(id) ON DELETE CASCADE);
CREATE TABLE notifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,channel ENUM('in_app','push','email','sms','whatsapp') NOT NULL DEFAULT 'in_app',title VARCHAR(190) NOT NULL,body TEXT NOT NULL,status ENUM('queued','sent','failed','read') NOT NULL DEFAULT 'queued',dedupe_key VARCHAR(190) NULL,scheduled_at DATETIME NULL,sent_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_notification_dedupe(dedupe_key),INDEX idx_notification_user(user_id,status),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE notification_preferences(user_id BIGINT UNSIGNED PRIMARY KEY,in_app TINYINT(1) NOT NULL DEFAULT 1,push TINYINT(1) NOT NULL DEFAULT 0,email TINYINT(1) NOT NULL DEFAULT 0,sms TINYINT(1) NOT NULL DEFAULT 0,marketing TINYINT(1) NOT NULL DEFAULT 0,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE reviews(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,assignment_id BIGINT UNSIGNED NOT NULL,reviewer_user_id BIGINT UNSIGNED NOT NULL,reviewee_user_id BIGINT UNSIGNED NOT NULL,rating TINYINT UNSIGNED NOT NULL,comment TEXT NULL,status ENUM('visible','hidden','under_review') NOT NULL DEFAULT 'visible',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_review_assignment_reviewer(assignment_id,reviewer_user_id),FOREIGN KEY(assignment_id) REFERENCES assignments(id),FOREIGN KEY(reviewer_user_id) REFERENCES users(id),FOREIGN KEY(reviewee_user_id) REFERENCES users(id),CHECK(rating BETWEEN 1 AND 5));
CREATE TABLE complaints(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,created_by BIGINT UNSIGNED NOT NULL,assignment_id BIGINT UNSIGNED NULL,subject VARCHAR(190) NOT NULL,description TEXT NOT NULL,status ENUM('open','investigating','resolved','rejected','appealed') NOT NULL DEFAULT 'open',assigned_to BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(created_by) REFERENCES users(id),FOREIGN KEY(assignment_id) REFERENCES assignments(id) ON DELETE SET NULL,FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE disputes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,assignment_id BIGINT UNSIGNED NOT NULL,opened_by BIGINT UNSIGNED NOT NULL,reason TEXT NOT NULL,status ENUM('open','investigating','resolved','rejected','appealed') NOT NULL DEFAULT 'open',resolution TEXT NULL,resolved_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(assignment_id) REFERENCES assignments(id),FOREIGN KEY(opened_by) REFERENCES users(id),FOREIGN KEY(resolved_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE payment_records(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,assignment_id BIGINT UNSIGNED NULL,payer_user_id BIGINT UNSIGNED NOT NULL,payee_user_id BIGINT UNSIGNED NULL,amount DECIMAL(12,2) NOT NULL,currency CHAR(3) NOT NULL DEFAULT 'BDT',status ENUM('unpaid','payment_pending','payment_submitted','payment_confirmed','payout_pending','paid','refund_pending','refunded','disputed') NOT NULL DEFAULT 'unpaid',provider VARCHAR(50) NOT NULL DEFAULT 'manual',transaction_reference VARCHAR(190) NULL,confirmed_by BIGINT UNSIGNED NULL,confirmed_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(job_id) REFERENCES jobs(id),FOREIGN KEY(assignment_id) REFERENCES assignments(id) ON DELETE SET NULL,FOREIGN KEY(payer_user_id) REFERENCES users(id),FOREIGN KEY(payee_user_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(confirmed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE financial_ledger(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,transaction_group VARCHAR(80) NOT NULL,account_code VARCHAR(50) NOT NULL,entry_type ENUM('debit','credit') NOT NULL,amount DECIMAL(12,2) NOT NULL,currency CHAR(3) NOT NULL DEFAULT 'BDT',reference_type VARCHAR(50) NOT NULL,reference_id BIGINT UNSIGNED NOT NULL,description VARCHAR(255) NOT NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_ledger_ref(reference_type,reference_id),FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE payout_requests(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,worker_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(12,2) NOT NULL,status ENUM('pending','approved','paid','rejected','disputed') NOT NULL DEFAULT 'pending',payment_reference VARCHAR(190) NULL,reviewed_by BIGINT UNSIGNED NULL,reviewed_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(worker_id) REFERENCES worker_profiles(id),FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE refunds(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,payment_record_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(12,2) NOT NULL,reason TEXT NOT NULL,status ENUM('pending','approved','refunded','rejected') NOT NULL DEFAULT 'pending',processed_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(payment_record_id) REFERENCES payment_records(id),FOREIGN KEY(processed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE platform_settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value TEXT NOT NULL,updated_by BIGINT UNSIGNED NULL,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE routing_cache(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,origin_hash CHAR(64) NOT NULL,destination_hash CHAR(64) NOT NULL,provider VARCHAR(50) NOT NULL,road_distance_m BIGINT UNSIGNED NULL,duration_seconds BIGINT UNSIGNED NULL,response_status VARCHAR(30) NOT NULL,expires_at DATETIME NOT NULL,UNIQUE KEY uq_route(origin_hash,destination_hash,provider));
CREATE TABLE risk_flags(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,job_id BIGINT UNSIGNED NULL,flag_type VARCHAR(80) NOT NULL,severity ENUM('low','medium','high') NOT NULL DEFAULT 'low',evidence_summary TEXT NULL,status ENUM('open','reviewing','cleared','actioned') NOT NULL DEFAULT 'open',reviewed_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE SET NULL,FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE audit_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,actor_user_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(80) NOT NULL,entity_id BIGINT UNSIGNED NULL,details TEXT NULL,ip_hash CHAR(64) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_audit_entity(entity_type,entity_id),INDEX idx_audit_actor(actor_user_id,created_at),FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE training_courses(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,description TEXT NULL,provider VARCHAR(190) NULL,course_url VARCHAR(500) NULL,status ENUM('draft','published','disabled') NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE training_enrollments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,course_id BIGINT UNSIGNED NOT NULL,worker_id BIGINT UNSIGNED NOT NULL,status ENUM('enrolled','in_progress','completed','withdrawn') NOT NULL DEFAULT 'enrolled',progress_percent DECIMAL(5,2) NOT NULL DEFAULT 0,certificate_reference VARCHAR(190) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_course_worker(course_id,worker_id),FOREIGN KEY(course_id) REFERENCES training_courses(id),FOREIGN KEY(worker_id) REFERENCES worker_profiles(id));
CREATE TABLE full_time_applications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,worker_id BIGINT UNSIGNED NOT NULL,cv_private_path VARCHAR(500) NULL,status ENUM('applied','under_review','shortlisted','interview_scheduled','selected','rejected','withdrawn') NOT NULL DEFAULT 'applied',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_fulltime_application(job_id,worker_id),FOREIGN KEY(job_id) REFERENCES jobs(id),FOREIGN KEY(worker_id) REFERENCES worker_profiles(id));
CREATE TABLE migration_versions(version VARCHAR(50) PRIMARY KEY,applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
INSERT INTO roles(role_key,label_bn) VALUES('super_admin','সুপার অ্যাডমিন'),('operations_admin','অপারেশন অ্যাডমিন'),('verification_officer','যাচাই কর্মকর্তা'),('finance_admin','ফাইন্যান্স অ্যাডমিন'),('support_officer','সাপোর্ট ও ডিসপিউট কর্মকর্তা'),('employer','নিয়োগদাতা'),('worker','কর্মী');
INSERT INTO job_categories(name,slug,sort_order) VALUES('দোকান সহকারী','shop-assistant',1),('রেস্টুরেন্ট ও হসপিটালিটি','hospitality',2),('ডেলিভারি ও লজিস্টিকস','delivery-logistics',3),('গুদাম ও প্যাকেজিং','warehouse-packaging',4),('ইভেন্ট ম্যানেজমেন্ট','event-management',5),('প্রমোশন ও ব্র্যান্ড অ্যাক্টিভেশন','promotion',6),('কাস্টমার সাপোর্ট','customer-support',7),('অফিস সহকারী','office-assistant',8),('ক্লিনিং ও মেইনটেন্যান্স','cleaning-maintenance',9),('নির্মাণ ও কারিগরি কাজ','construction-trades',10),('টেকনিক্যাল ও ডিজিটাল সার্ভিস','technical-digital',11),('রিটেইল সেলস','retail-sales',12),('অস্থায়ী প্রশাসনিক কাজ','temporary-admin',13),('ফুল-টাইম চাকরি','full-time',14),('ট্রেনিং ও ইন্টার্নশিপ','training-internship',15);
INSERT INTO platform_settings(setting_key,setting_value) VALUES('worker_fee_percent','10'),('employer_fee_percent','10'),('matching_radius_km','25'),('candidate_batch_size','10'),('offer_window_minutes','60'),('geofence_radius_m','150'),('location_freshness_minutes','30'),('location_retention_days','30');