;+
; $Id: mysqlinsert2createtable.pro,v 1.4 2021/03/11 18:06:04 nathan Exp $
;
; Project     : SO SOLOHI
;                   
; Name        : mysqlinsert2cratetable
;               
; Purpose     : To convert MySQL insert statement to MySQL table definition 
;               
; Use         : IDL> mysqlinsert2cratetable, scrf
;    
; Inputs      : scrf		; mysql script file with insert statement(s)
;               
; Outputs     : file in current directory
;               
; Calls       : 
;
; Common      : None.
;               
; Restrictions: Must have write permission for the current directory.
;               
; Side effects: Creates one file in the current directory named:
;		<table name>_tabledef.sql	;MySQL table definition script
;               
; Category    : Database, Pipeline 
;               
; Prev. Hist. : None.
;
; Written     : Nathan Rich, March 2020
;               
; Modified    :
;
; $Log: mysqlinsert2createtable.pro,v $
; Revision 1.4  2021/03/11 18:06:04  nathan
; use grt for unique key
;
; Revision 1.3  2020/04/01 18:38:39  nathan
; fix bugs
;
; Revision 1.2  2020/04/01 18:20:58  nathan
; complete first draft
;
; Revision 1.1  2020/03/31 21:37:11  nathan
; unfinished draft
;
;-            


PRO mysqlinsert2createtable, scrf

COMMON cpp_common, idl_out, in

r=''
openr,1,scrf
readf,1,r   ;Use solohi_hk;
readf,1,r   ;start transaction;
readf,1,r   ;insert ignore xftlm(grnd_time,Pckt_time,unix_time,CMDCNT,ERRCNT,EXECNT,currEngFileSize,...
readf,1,r	; 2nd insert line

a=strsplit(r,',()',/extract)
na=n_elements(a)
nc=(na-3)/2
a0=strsplit(a[0],/extract)
s_name=a0[2]
s_tags=a[1:nc]
values=a[nc+2:na-2]

; fix seqcnt missing
IF s_tags[3] NE 'seqcnt' THEN BEGIN
	s_tags=[s_tags[0:2],'seqcnt',s_tags[3:*]]
	values=[values[0:2],'12345' ,values[3:*]]
ENDIF

   n=n_elements(s_tags)
   out_file_idl = s_name + '_struct.sql'
   PRINT, '%%: Creating output file: ', out_file_idl

      OPENW, IDL_OUT, out_file_idl, /GET_LUN
  
   today = SYSTIME()
   printf, IDL_OUT, '/*  $Id: mysqlinsert2createtable.pro,v 1.4 2021/03/11 18:06:04 nathan Exp $'
   PRINTF, IDL_OUT, ' *  MySQL table definition created on ' + today
   PRINTF, IDL_OUT, ' */'
   printf, IDL_OUT, 'CREATE TABLE ',s_name,' ('

for i=0,n-1 do begin
    value=values[i]
    isnum = is_number(value)
    valen = strlen(value)
    IF (isnum) THEN BEGIN
    	
    	IF valen LE 2 THEN typ='BYT' ELSE $
    	IF valen LE 6 THEN typ='UIN' ELSE $
    	typ='ULN'
    	IF strpos(value,'.') GT 0 THEN typ='FLO'
    	IF strpos(value,'-') GT 0 THEN typ='INT'

    ENDIF ELSE typ='STR'
    
    case (typ) of
	'BYT': mnval="SMALLINT UNSIGNED "
	'LON': mnval="INT "
	'FLO': mnval="FLOAT "
	'DOU': mnval="DOUBLE "
	'ULN': mnval="INT UNSIGNED "
	'L64': mnval="BIGINT "
	'U64': mnval="BIGINT UNSIGNED "
	'INT': mnval="SMALLINT "
	'UIN': mnval="SMALLINT UNSIGNED "
	'STR': mnval="VARCHAR("+trim(valen)+") "
	else:   mnval="SMALLINT "
    endcase

    IF strpos(strupcase(s_tags[i]),'TIME') gt 0 THEN mnval="DATETIME(3) "
    
    printf,IDL_OUT,' '+s_tags[i]+' '+mnval+'NOT NULL,'
endfor

printf,IDL_OUT,'UNIQUE KEY '+s_name+'_index (grnd_time,seqcnt)'
printf,IDL_OUT,');'

   CLOSE, IDL_OUT & FREE_LUN, IDL_OUT

END