CREATE TABLE IF NOT EXISTS `supplier_portal_invites` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED DEFAULT 1,
  `invited_by` INT UNSIGNED DEFAULT NULL,
  `token` VARCHAR(64) NOT NULL,
  `status` ENUM('pending','activated','submitted','expired') NOT NULL DEFAULT 'pending',
  `expires_at` DATETIME NOT NULL,
  `activated_at` DATETIME DEFAULT NULL,
  `submitted_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_token` (`token`),
  KEY `idx_supplier` (`supplier_id`),
  CONSTRAINT `fk_invite_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `supplier_profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_portal_submissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invite_id` INT UNSIGNED NOT NULL,
  `supplier_id` INT UNSIGNED NOT NULL,
  `company_name` VARCHAR(255) DEFAULT NULL,
  `factory_name` VARCHAR(255) DEFAULT NULL,
  `factory_address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `postal_code` VARCHAR(20) DEFAULT NULL,
  `latitude` DECIMAL(10,7) DEFAULT NULL,
  `longitude` DECIMAL(10,7) DEFAULT NULL,
  `contact_person` VARCHAR(255) DEFAULT NULL,
  `contact_email` VARCHAR(255) DEFAULT NULL,
  `contact_phone` VARCHAR(50) DEFAULT NULL,
  `contact_designation` VARCHAR(100) DEFAULT NULL,
  `year_established` YEAR DEFAULT NULL,
  `facility_size_sqft` INT UNSIGNED DEFAULT NULL,
  `total_employees` INT UNSIGNED DEFAULT NULL,
  `annual_revenue_usd` DECIMAL(15,2) DEFAULT NULL,
  `num_sewing_lines` INT UNSIGNED DEFAULT NULL,
  `num_cutting_tables` INT UNSIGNED DEFAULT NULL,
  `num_finishing_lines` INT UNSIGNED DEFAULT NULL,
  `monthly_capacity` INT UNSIGNED DEFAULT NULL,
  `capacity_utilization` DECIMAL(5,2) DEFAULT NULL,
  `lead_time_days` INT UNSIGNED DEFAULT NULL,
  `machinery_details` JSON DEFAULT NULL,
  `departments` JSON DEFAULT NULL,
  `compliance_details` JSON DEFAULT NULL,
  `environmental_details` JSON DEFAULT NULL,
  `chemical_management` JSON DEFAULT NULL,
  `warehouse_details` JSON DEFAULT NULL,
  `progress_pct` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('draft','submitted','under_review','approved','rejected') NOT NULL DEFAULT 'draft',
  `reviewer_notes` TEXT DEFAULT NULL,
  `reviewed_by` INT UNSIGNED DEFAULT NULL,
  `reviewed_at` DATETIME 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 `idx_invite` (`invite_id`),
  KEY `idx_supplier` (`supplier_id`),
  CONSTRAINT `fk_sub_invite` FOREIGN KEY (`invite_id`) REFERENCES `supplier_portal_invites` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sub_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `supplier_profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_portal_documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `submission_id` INT UNSIGNED NOT NULL,
  `doc_category` ENUM('iso_9001','iso_14001','wrap','bsci','sedex','sa8000','oeko_tex','gots','grs','higg','zdhc','fire_certificate','building_license','factory_license','insurance','calibration','environmental_report','msds','training_records','payroll','other') NOT NULL DEFAULT 'other',
  `doc_name` VARCHAR(255) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `expiry_date` DATE DEFAULT NULL,
  `is_expired` TINYINT(1) NOT NULL DEFAULT 0,
  `ai_status` ENUM('pending','valid','expiring_soon','expired','unreadable','wrong_file') NOT NULL DEFAULT 'pending',
  `ai_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_submission` (`submission_id`),
  CONSTRAINT `fk_doc_submission` FOREIGN KEY (`submission_id`) REFERENCES `supplier_portal_submissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_portal_photos` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `submission_id` INT UNSIGNED NOT NULL,
  `photo_category` ENUM('factory_entrance','production_floor','warehouse','chemical_room','fire_safety','dormitory','canteen','packing','loading_area','office','laboratory','waste_area','solar','other') NOT NULL DEFAULT 'other',
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `caption` VARCHAR(255) DEFAULT NULL,
  `gps_lat` DECIMAL(10,7) DEFAULT NULL,
  `gps_lng` DECIMAL(10,7) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_submission` (`submission_id`),
  CONSTRAINT `fk_photo_submission` FOREIGN KEY (`submission_id`) REFERENCES `supplier_portal_submissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
