src/gspread.rs

//extern crate hyper;
//extern crate hyper_rustls;
//extern crate hyper_util;
extern crate google_sheets4 as sheets4;
use sheets4::{Sheets, hyper_rustls, hyper_util, yup_oauth2};

use serde_json::Value;
use std::collections::HashMap;
use futures::future::join_all;
use hyper_util::client::legacy::connect::HttpConnector;
use hyper_rustls::HttpsConnector;

use inflector::Inflector; // 패키지: id = "0.11" (네이밍 변환용)

pub type SheetsHub = Sheets>;

//#[derive(Default, Debug, Clone, PartialEq)]
pub struct SpreadsheetExporter {
    hub: SheetsHub,
    spreadsheet_id: String,
}

impl SpreadsheetExporter {
    pub fn new(hub: SheetsHub, spreadsheet_id: String) -> Self {
        Self { hub, spreadsheet_id }
    }

    // Value 타입을 String으로 안전하게 변환하는 헬퍼 함수
    fn val_to_string(val: &Value) -> String {
        match val {
            Value::String(s) => s.clone(),
            Value::Number(n) => n.to_string(),
            Value::Bool(b) => b.to_string(),
            _ => val.to_string().replace('"', ""),
        }
    }

    // 시트에서 가져온 Vec>를 정제된 HashMap 리스트로 변환
    fn clean_rows(&self, values: &Vec>) -> Vec> {
        if values.is_empty() { return vec![]; }
        let headers: Vec = values[0].iter().map(Self::val_to_string).collect();
        let mut result = Vec::new();

        for row in values.iter().skip(1) {
            if row.is_empty() || Self::val_to_string(&row[0]).trim().is_empty() { break; }
            let mut row_map = HashMap::new();
            for (i, header) in headers.iter().enumerate() {
                let val = row.get(i).map(Self::val_to_string).unwrap_or_default();
                row_map.insert(header.clone(), val);
            }
            result.push(row_map);
        }
        result
    }

    fn split_column(&self, raw_text: &str) -> String {
        raw_text.split(',').map(|s| format!("`{}`", s.trim())).collect::>().join(", ")
    }

    fn format_sql_value(&self, val: &str) -> String {
        let t = val.trim();
        if t.is_empty() || t.to_uppercase() == "NULL" { return "NULL".to_string(); }
        if t.parse::().is_ok() { t.to_string() } else { format!("'{}'", t.replace("'", "''")) }
    }

    async fn export_schema_unit(&self, row: HashMap) -> String {
        let schema = row.get("SCHEMA").cloned().unwrap_or_default();
        let table_name = row.get("TABLE_NAME").cloned().unwrap_or_default();
        let engine = row.get("ENGINE").cloned().unwrap_or_default();
        let auto_increment = row.get("AUTO_INCREMENT").cloned().unwrap_or_default();
        let charset = row.get("CHARSET").cloned().unwrap_or_default();
        let collate = row.get("COLLATE").cloned().unwrap_or_default();
        let ss_id = match row.get("SPREADSHEET_ID") {
            Some(id) => id,
            None => return format!("-- Error: Missing ID for {}", table_name),
        };

        let result = self.hub.spreadsheets().values_batch_get(ss_id)
            .add_ranges("COLUMN!A:Z")
            .add_ranges("INDEX!A:Z")
            .add_ranges("FOREIGN_KEY!A:Z")
            .doit().await;

        let (_, response) = match result {
            Ok(res) => res,
            Err(e) => return format!("-- API Error in {}: {}\n", table_name, e),
        };

        let v_ranges = response.value_ranges.unwrap_or_default();
        let mut all_defs = Vec::new();

        // 1. COLUMN 처리
        if let Some(vals) = v_ranges.get(0).and_then(|r| r.values.as_ref()) {
            for r in self.clean_rows(vals) {
                let f = r.get("FIELD").cloned().unwrap_or_default();
                let t = r.get("TYPE").cloned().unwrap_or_default();
                let s = r.get("SIGNED").cloned().unwrap_or_default();
                let l = r.get("LENGTH").filter(|s| !s.is_empty()).map_or("".to_string(), |v| format!("({})", v));
                let n = if ["NOT", "NO", "FALSE", "0"].contains(&r.get("NULL").unwrap_or(&"".into()).to_uppercase().as_str()) { "NOT NULL" } else { "" };
                let d = r.get("DEFAULT").filter(|s| !s.is_empty()).map_or("".to_string(), |v| format!("DEFAULT '{}'", v.replace("'", "''")));
                let c = r.get("COMMENT").filter(|s| !s.is_empty()).map_or("".to_string(), |v| format!("COMMENT '{}'", v.replace("'", "''")));
                all_defs.push(format!("  `{}` {} {}{} {} {} {} {}", f.to_snake_case(), t, s, l, n, d, r.get("EXTRA").unwrap_or(&"".into()), c).trim().to_string());
            }
        }

        // 2. INDEX 처리
        if let Some(vals) = v_ranges.get(1).and_then(|r| r.values.as_ref()) {
            for r in self.clean_rows(vals) {
                let itype = r.get("TYPE").unwrap_or(&"".into()).to_uppercase();
                let cols = self.split_column(r.get("COLUMN").unwrap_or(&"".into()));
                if itype == "PRIMARY" { all_defs.push(format!("PRIMARY KEY ({})", cols.to_snake_case())); }
                else {
                    let prefix = if itype == "KEY" { "".into() } else { format!("{} ", itype) };
                    all_defs.push(format!("{}KEY `{}` ({})", prefix, r.get("NAME").unwrap_or(&"".into()).to_snake_case(), cols));
                }
            }
        }

        // 3. FOREIGN KEY 처리
        if let Some(vals) = v_ranges.get(2).and_then(|r| r.values.as_ref()) {
            for r in self.clean_rows(vals) {
                let mut fk = format!("CONSTRAINT `{}` FOREIGN KEY (`{}`) REFERENCES `{}` (`{}`)",
                    r.get("NAME").unwrap_or(&"".to_string()).to_snake_case(),
                    r.get("COLUMN").unwrap_or(&"".to_string()).to_snake_case(),
                    r.get("REF_TABLE").unwrap_or(&"".to_string()).to_plural().to_snake_case(),
                    r.get("REF_COLUMN").unwrap_or(&"".to_string()).to_snake_case());
                if let Some(u) = r.get("ON_UPDATE").filter(|s| !s.is_empty()) { fk.push_str(&format!(" ON UPDATE {}", u)); }
                if let Some(d) = r.get("ON_DELETE").filter(|s| !s.is_empty()) { fk.push_str(&format!(" ON DELETE {}", d)); }
                all_defs.push(fk);
            }
        }
        
        let schema_name = schema.to_snake_case();
        let table_name = table_name.to_plural().to_snake_case();        
        format!("CREATE DATABASE IF NOT EXISTS {};\nUSE {};\nDROP TABLE IF EXISTS `{}`;\nCREATE TABLE `{}` (\n{}\n) ENGINE={} AUTO_INCREMENT={} DEFAULT CHARSET={} COLLATE={};\n",
            schema_name, schema_name, table_name, table_name, all_defs.join(",\n"), engine, auto_increment, charset, collate)
    }

    async fn export_data_unit(&self, row: HashMap) -> String {
        let table_name = row.get("TABLE_NAME").cloned().unwrap_or_default();
        let ss_id = row.get("SPREADSHEET_ID").expect("No ID");

        let result = self.hub.spreadsheets().values_get(ss_id, "DATA!A:Z").doit().await;
        let (_, response) = match result {
            Ok(res) => res,
            Err(_) => return "".into(),
        };

        let values = response.values.unwrap_or_default();
        if values.len() <= 1 { return "".into(); }

        let headers: Vec = values[0].iter().map(Self::val_to_string).collect();
        let mut insert_rows = Vec::new();

        for r in self.clean_rows(&values) {
            let row_vals: Vec = headers.iter()
                .map(|h| self.format_sql_value(r.get(h).unwrap_or(&"".into())))
                .collect();
            insert_rows.push(format!("({})", row_vals.join(", ")));
        }

        let h_sql = headers.iter().map(|h| format!("`{}`", h.to_snake_case())).collect::>().join(", ");
        format!("INSERT INTO `{}` ({})\nVALUES\n{};\n", table_name.to_plural(), h_sql, insert_rows.join(",\n"))
    }

    async fn execute_parallel(&self, unit_func: F) -> String 
    where 
        F: Fn(HashMap) -> Fut,
        Fut: std::future::Future
,
    {
        let result = self.hub.spreadsheets().values_get(&self.spreadsheet_id, "SCHEMA!A:Z").doit().await;
        let (_, response) = match result {
            Ok(res) => res,
            Err(e) => return format!("Critical Error: {}", e),
        };

        let values = response.values.unwrap_or_default();
        if values.len() < 2 { return "No data found.".to_string(); }

        let headers: Vec = values[0].iter().map(Self::val_to_string).collect();
        let mut tasks = Vec::new();
        for row in values.iter().skip(1) {
            if !row.is_empty() && Self::val_to_string(&row[0]).to_uppercase().trim() == "TRUE" {
                let mut row_map = HashMap::new();
                for (i, h) in headers.iter().enumerate() {
                    row_map.insert(h.clone(), row.get(i).map(Self::val_to_string).unwrap_or_default());
                }
                tasks.push(unit_func(row_map));
            }
        }
        
        join_all(tasks).await.join("\n")
    }

    pub async fn export_schema(&self) -> String {
        self.execute_parallel(|r| async { self.export_schema_unit(r).await }).await
    }
    pub async fn export_data(&self) -> String {
        self.execute_parallel(|row| async { self.export_data_unit(row).await }).await
    }
}

// 3. 최신 hyper v1 및 hyper-util 기반 인증 설정
pub async fn create_hub(service_account_key: &str) -> SheetsHub {
  // Get an ApplicationSecret instance by some means. It contains the `client_id` and
  // `client_secret`, among other things.
  //parse_service_account_key
  let secret: yup_oauth2::ServiceAccountKey = yup_oauth2::read_service_account_key(service_account_key)
    .await
    .expect("client secret could not be read");
    
  let connector = hyper_rustls::HttpsConnectorBuilder::new()
    .with_native_roots()
    .unwrap()
    .https_or_http()
    .enable_http1()
    .enable_http2()
    .build();

  let client = hyper_util::client::legacy::Client::builder(
    hyper_util::rt::TokioExecutor::new()
    )
    .build(connector);

  let auth = yup_oauth2::ServiceAccountAuthenticator::builder(
  secret,
    )
    .build()
    .await
    .unwrap();

    Sheets::new(client, auth)
}