crawler/model/company.go

package model

import (
	"crawler/config"
	"crawler/service"
	"database/sql"
	"encoding/json"
	"log"
	"strings"
)

// 영화사 목록 검색 변수
type SearchCompanyListParams struct {
	Key           string `form:"key" url:"key" binding:"required"`
	CurPage       int    `form:"curPage" url:"curPage,omitempty"`
	ItemPerPage   int    `form:"itemPerPage" url:"itemPerPage,omitempty"`
	CompanyNm     string `form:"companyNm" url:"companyNm,omitempty"`
	CeoNm         string `form:"ceoNm" url:"ceoNm,omitempty"`
	CompanyPartCd string `form:"companyPartCd" url:"companyPartCd,omitempty"`
}

// 영화사 목록 응답 변수
type SearchCompanyList struct {
	CompanyListResult struct {
		TotCnt      int               `json:"totCnt"`
		Source      string            `json:"source"`
		CompanyList []CompanyListInfo `json:"companyList"`
	} `json:"companyListResult"`
}

type CompanyListInfo struct {
	CompanyCd        string `json:"companyCd"`
	CompanyNm        string `json:"companyNm"`
	CompanyNmEn      string `json:"companyNmEn"`
	CompanyPartNames string `json:"companyPartNames"`
	CeoNm            string `json:"ceoNm"`
	FilmoNames       string `json:"filmoNames"`
}

// 영화사 상세 검색 변수
type SearchCompanyInfoParams struct {
	Key       string `form:"key" url:"key" binding:"required"`
	CompanyCd string `form:"companyCd" url:"companyCd" binding:"required"`
}

// 영화사 상세 응답 변수
type SearchCompanyInfo struct {
	CompanyInfoResult struct {
		Source      string      `json:"source"`
		CompanyInfo CompanyInfo `json:"companyInfo"`
	} `json:"companyInfoResult"`
}

type CompanyInfo struct {
	CompanyCd   string         `json:"companyCd"`
	CompanyNm   string         `json:"companyNm"`
	CompanyNmEn string         `json:"companyNmEn"`
	CeoNm       string         `json:"ceoNm"`
	Parts       []CompanyPart  `json:"parts"`
	Filmos      []CompanyFilmo `json:"filmos"`
}

type CompanyPart struct {
	CompanyPartNm string `json:"companyPartNm"`
}

type CompanyFilmo struct {
	MovieCd       string `json:"movieCd"`
	MovieNm       string `json:"movieNm"`
	CompanyPartNm string `json:"companyPartNm"`
}

// ---- DB 계층 (tb_company / tb_company_filmo) ----

type CompanyModel struct{}

// tb_company 로우
type CompanyTable struct {
	CompanyID        int
	CompanyCd        string
	CompanyNm        *string
	CompanyNmEn      *string
	CeoNm            *string
	CompanyPartNames *string
	Parts            *string
	FilmoNames       *string
	UpdatedAt        *string
	CreatedAt        string
}

func (this *CompanyModel) Total() int {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = "SELECT COUNT(*) FROM tb_company;"
		total = 0
	)

	err := conn.QueryRow(query).Scan(&total)
	if err != nil {
		db.SetErrorLog(err, query)
		return total
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select total company")
	return total
}

func (this *CompanyModel) IsExists(companyCd string) bool {
	var (
		db     = service.DB_MOVIEW
		conn   = db.SQLDB
		query  = "SELECT IF(COUNT(*) <= 0, 0, 1) AS `exists` FROM tb_company WHERE company_cd = ?;"
		exists = false
	)

	err := conn.QueryRow(query, companyCd).Scan(&exists)
	if err != nil {
		db.SetErrorLog(err, query)
		return exists
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select exists company")
	return exists
}

// 목록 API 결과 upsert (company_part_names, filmo_names 채움)
func (this *CompanyModel) Insert(list []CompanyListInfo) error {
	if len(list) <= 0 {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_company (
				company_cd, company_nm, company_nm_en, ceo_nm,
			    company_part_names, filmo_names, updated_at, created_at
			)
			VALUES
		`
		vals = []interface{}{}
	)

	for _, row := range list {
		query += `(?, ?, ?, ?, ?, ?, NULL, NOW()),`
		vals = append(vals,
			row.CompanyCd, nullStr(row.CompanyNm), nullStr(row.CompanyNmEn), nullStr(row.CeoNm),
			nullStr(row.CompanyPartNames), nullStr(row.FilmoNames))
	}

	query = query[0 : len(query)-1]
	query += `
		ON DUPLICATE KEY UPDATE
			company_nm = VALUES(company_nm), company_nm_en = VALUES(company_nm_en), ceo_nm = VALUES(ceo_nm),
			company_part_names = VALUES(company_part_names), filmo_names = VALUES(filmo_names), updated_at = NOW();
	`

	stmt, err := conn.Prepare(query)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(vals...)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}

	db.SetGeneralLog(config.GL_ACTION_WRITE, query, "insert company list")
	return nil
}

// 상세 API 결과 upsert (parts JSON 채움)
func (this *CompanyModel) InsertInfo(info CompanyInfo) error {
	if info.CompanyCd == "" {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_company (
				company_cd, company_nm, company_nm_en, ceo_nm, parts, updated_at, created_at
			)
			VALUES (?, ?, ?, ?, ?, NULL, NOW())
			ON DUPLICATE KEY UPDATE
				company_nm = VALUES(company_nm), company_nm_en = VALUES(company_nm_en),
				ceo_nm = VALUES(ceo_nm), parts = VALUES(parts), updated_at = NOW();
		`
		parts interface{}
	)

	if len(info.Parts) > 0 {
		b, _ := json.Marshal(info.Parts)
		parts = string(b)
	} else {
		parts = nil
	}

	_, err := conn.Exec(query,
		info.CompanyCd, nullStr(info.CompanyNm), nullStr(info.CompanyNmEn), nullStr(info.CeoNm), parts)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}

	db.SetGeneralLog(config.GL_ACTION_WRITE, query, "insert company info")
	return nil
}

// 필모그래피 upsert (tb_company_filmo)
func (this *CompanyModel) InsertFilmos(companyCd string, filmos []CompanyFilmo) error {
	if companyCd == "" || len(filmos) <= 0 {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_company_filmo (company_cd, movie_cd, movie_nm, company_part_nm, created_at)
			VALUES
		`
		vals = []interface{}{}
	)

	for _, f := range filmos {
		if f.MovieCd == "" {
			continue
		}
		query += `(?, ?, ?, ?, NOW()),`
		vals = append(vals, companyCd, f.MovieCd, nullStr(f.MovieNm), nullStr(f.CompanyPartNm))
	}

	if len(vals) == 0 {
		return nil
	}

	query = query[0 : len(query)-1]
	query += `
		ON DUPLICATE KEY UPDATE movie_nm = VALUES(movie_nm);
	`

	stmt, err := conn.Prepare(query)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(vals...)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}

	db.SetGeneralLog(config.GL_ACTION_WRITE, query, "insert company filmo")
	return nil
}

// 상세(parts) 미수집 영화사 코드 조회
func (this *CompanyModel) CompanyInfoExcludeCodes() []string {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `SELECT company_cd FROM tb_company WHERE parts IS NULL ORDER BY id ASC;`
		code  []string
	)

	rows, err := conn.Query(query)
	if err != nil && err != sql.ErrNoRows {
		db.SetErrorLog(err, query)
		return code
	}
	defer rows.Close()

	for rows.Next() {
		var cd string
		if err := rows.Scan(&cd); err != nil {
			log.Println(err)
			continue
		}
		code = append(code, cd)
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select company_cd exclude codes")
	return code
}

func nullStr(s string) interface{} {
	if strings.TrimSpace(s) == "" {
		return nil
	}
	return s
}