CREATE TABLE Market(
	@Id INTEGER IDENTITY PRIMARY KEY,
	@Name VARCHAR NOT NULL
);

CREATE TABLE Brand(
	@Id INTEGER IDENTITY PRIMARY KEY,
	@Code INTEGER NOT NULL,
	@MarketId INTEGER REFERENCES Market(@Id),
	@Name VARCHAR NOT NULL,
	UNIQUE(@Code, @MarketId)
);

CREATE TABLE Price(
	@BrandId INTEGER NOT NULL REFERENCES Brand(@Id),
	@Date DATETIME NOT NULL,
	@Open INTEGER NOT NULL,
	@High INTEGER NOT NULL,
	@Low INTEGER NOT NULL,
	@Close INTEGER NOT NULL,
	@Volume DOUBLE NOT NULL,
	PRIMARY KEY(@BrandId, @Date)
);

CREATE INDEX IndexPriceDate ON Price(@Date);

CREATE TABLE Split(
	@Code INTEGER NOT NULL,
	@Date DATETIME NOT NULL,
	@Ratio DOUBLE NOT NULL,
	PRIMARY KEY(@Code, @Date)
);

CREATE TABLE System(
	@Id INTEGER IDENTITY PRIMARY KEY,
	@File VARCHAR NOT NULL,
	@Mode INTEGER NOT NULL DEFAULT 0, -- Daily:0 Weekly:1
	UNIQUE(@File, @Mode)
);

CREATE TABLE SystemLog(
	@SystemId INTEGER NOT NULL REFERENCES System(@Id),
	@BrandId INTEGER NOT NULL REFERENCES Brand(@Id),
	@Date DATETIME NOT NULL,
	@Price INTEGER NOT NULL,
	@Number INTEGER NOT NULL,
	@Order INTEGER NOT NULL -- Buy:0 Sell:1
);

CREATE INDEX IndexSystemLog ON SystemLog(@SystemId, @BrandId);

CREATE TABLE BrandList(
	@Id INTEGER IDENTITY PRIMARY KEY,
	@Name VARCHAR NOT NULL
);

CREATE TABLE BrandList_Brand(
	@BrandListId INTEGER NOT NULL REFERENCES BrandList(@Id),
	@BrandId INTEGER NOT NULL REFERENCES Brand(@Id),
	PRIMARY KEY(@BrandListId, @BrandId)
);

INSERT INTO Market(@Name) VALUES('東証１部');
INSERT INTO Market(@Name) VALUES('東証２部');
INSERT INTO Market(@Name) VALUES('マザーズ');
INSERT INTO Market(@Name) VALUES('東証外国部');
INSERT INTO Market(@Name) VALUES('大証１部');
INSERT INTO Market(@Name) VALUES('大証２部');
INSERT INTO Market(@Name) VALUES('ヘラクレス');
INSERT INTO Market(@Name) VALUES('名証１部');
INSERT INTO Market(@Name) VALUES('名証２部');
INSERT INTO Market(@Name) VALUES('Jasdaq');
