package api

import (
	"log"
	"strings"
)

var initSQLString string = `create table users(
	id serial primary key,
	username varchar(30) not null unique,
	password varchar(64) not null,
	balance money not null default 0,
	role integer not null default 1,
	location point not null default '(0, 0)',
	registerd_time timestamp not null default now()
);

create table market (
	id serial primary key,
	name varchar(100) not null,
	description text not null,
	location point not null
)

create table tag (
	id serial primary key,
	name varchar(30) not null
);

create table goods (
	id serial primary key,
	name varchar(100) not null,
	description text not null,
	create_time timestamp not null default now(),
	supplier_id integer not null references users(id),
	market_id integer not null references market(id),
	quantity numeric not null check (quantity >= 0),
	price money not null,
	data jsonb
);

create table tags_on_goods(
	tag_id integer not null references tag(id),
	goods_id integer not null references goods(id),
	primary key (tag_id, goods_id)
);

create table purchase (
	id serial primary key,
	user_id integer not null references users(id),
	goods_id integer not null references goods(id),
	quantity numeric not null default 1,
	purchased_time timestamp not null default now()
);

insert into users (username, password) values ('a', 'a');
`

func install() {
	sqls := strings.Split(initSQLString, "\n\n")
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	for _, sql := range sqls {
		log.Println("Installing table with SQL", sql)
		_, err = tx.Exec(sql)
		if err != nil {
			tx.Rollback()
			log.Fatal(err)
		}
	}
	tx.Commit()
	log.Println("Successfully install all tables")
}