package main

import (
	"bytes"
	"fmt"
	"github.com/tealeg/xlsx"
	"io/ioutil"
	_ "os"
	_ "strconv"
	"strings"
)

func main() {
	excelFileName := "SVP-表定义(ORACLE)_FACT_采购.xlsx"

	xlFile, err := xlsx.OpenFile(excelFileName)
	if err != nil {
		panic(err)
	}
	for _, sheet := range xlFile.Sheets {

		//DataTable := make(map[int]map[int]map[string]string)
		DataTable := make(map[int]map[string]string)
		header := make(map[int]string)

		index := 0
		var pk []string
		for rIndex, row := range sheet.Rows {

			cellTip := make(map[string]string)
			for i, cell := range row.Cells {

				if rIndex == 1 {
					header[i] = strings.Trim(cell.Value, " ")
					continue
				}

				if len(header) == 0 {
					continue
				} else {
					cellTip[header[i]] = strings.Trim(cell.Value, " ")
				}

				//fmt.Printf("%s\n", cell.String())
				//fmt.Printf("%s\n", cell.Value)
			}
			//fmt.Println(cellTip)
			if len(cellTip) != 0 {
				DataTable[index] = cellTip
				index++
			}

		}

		//fmt.Println(DataTable[0])

		buf := bytes.NewBufferString("--创建表脚本")
		buf.WriteString("\r\n")
		buf.WriteString("create table ")
		buf.WriteString(sheet.Name)
		buf.WriteString("\r\n")
		buf.WriteString("(")
		buf.WriteString("\r\n")

		rowCount := 0
		for _, Values := range DataTable {

			buf.WriteString("   ")
			buf.WriteString(Values["字段 物理名"])
			buf.WriteString(" ")
			buf.WriteString(Values["数据类型"])
			if strings.ToUpper(Values["数据类型"]) == "VARCHAR2" {
				buf.WriteString("(")
				buf.WriteString(Values["最大长度"])
				buf.WriteString(")")
			}
			buf.WriteString(" ")

			if strings.Trim(Values["缺省值"], " ") != "" {

				buf.WriteString("default")
				buf.WriteString(" ")
				if strings.ToUpper(Values["数据类型"]) == "VARCHAR2" {

					buf.WriteString("'")
					buf.WriteString(Values["缺省值"])
					buf.WriteString("'")

				} else {
					buf.WriteString(Values["缺省值"])
				}
			}
			buf.WriteString(" ")

			if strings.ToUpper(Values[`IS
NULL`]) == "N" {
				buf.WriteString("NOT NULL ")

			}

			if rowCount != len(DataTable)-1 {
				buf.WriteString(",")
			}

			buf.WriteString("\r\n")

			if strings.ToUpper(Values[`主键
(PK)`]) == "○" {
				pk = append(pk, Values["字段 物理名"])

			}

			rowCount++

		}

		buf.WriteString(")\r\n")
		buf.WriteString(`
tablespace ELINK11
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );`)

		if len(pk) > 0 {

			buf.WriteString("\r\n")
			buf.WriteString("-- Create/Recreate primary, unique and foreign key constraints ")
			buf.WriteString("\r\n")
			buf.WriteString("alter table ")
			buf.WriteString(sheet.Name)
			buf.WriteString("\r\n")
			buf.WriteString("add constraint PK_")
			buf.WriteString(sheet.Name)
			buf.WriteString(" primary key (")
			for j, v := range pk {
				buf.WriteString(v)
				if j != len(pk)-1 {
					buf.WriteString(",")
				}
			}
			buf.WriteString(")")
			buf.WriteString("\r\n")
			buf.WriteString(`  using index 
  tablespace ELINK11
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );`)

		}

		//fmt.Println(buf.String())

		err := ioutil.WriteFile("sql/"+sheet.Name+".txt", []byte(buf.String()), 0666)

		if err != nil {
			fmt.Println(err)
		}

	}
}