CREATE TABLE block (
	block_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	block_height INT UNSIGNED, -- blocks not in blockchain have NULL
	block_length INT UNSIGNED NOT NULL,
	block_hash VARBINARY(32) NOT NULL,
	block_work VARBINARY(32) NOT NULL,

	-- header
	block_version INT UNSIGNED NOT NULL,
	block_parent_block_id INT UNSIGNED, -- genesis has NULL
	block_merkle_root VARBINARY(32) NOT NULL,
	block_timestamp INT UNSIGNED NOT NULL,
	block_bits VARBINARY(4) NOT NULL,
	block_nonce VARBINARY(4) NOT NULL,

	UNIQUE ( block_hash ),
	UNIQUE ( block_height )
);

-- extra indirection between blocks and txns is needed to 
-- enforce the ORDER of txns in a block
CREATE TABLE block2txn (
	block2txn_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	block_id INT UNSIGNED,
	txn_id BIGINT UNSIGNED NOT NULL,
	txn_idx INT UNSIGNED NOT NULL,

	UNIQUE ( block_id, txn_id ) -- each transaction can only happen once in the blockchain (thus also in a block)
);
CREATE INDEX block2txn0 ON block2txn ( block_id );
CREATE INDEX block2txn1 ON block2txn ( txn_id );

CREATE TABLE txn (
	txn_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	txn_length INT UNSIGNED NOT NULL,
	txn_hash VARBINARY(32) NOT NULL,

	-- header
	txn_version INT UNSIGNED NOT NULL,
	txn_lock_time INT UNSIGNED NOT NULL,

	UNIQUE ( txn_hash )
);

CREATE TABLE txin (
	txin_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	
	txn2txout_id BIGINT UNSIGNED NOT NULL, -- coinbase scripts have 0
	txin_script_id BIGINT UNSIGNED NOT NULL,
	txin_sequence INT UNSIGNED NOT NULL,
	UNIQUE ( txn2txout_id, txin_script_id, txin_sequence ) 
);
CREATE INDEX txin0 ON txin ( txn2txout_id );

CREATE TABLE txn2txin (
	txn2txin_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	txn_id BIGINT UNSIGNED NOT NULL,
	txin_id BIGINT UNSIGNED NOT NULL,
	txin_idx INT UNSIGNED NOT NULL,
	UNIQUE ( txn_id, txin_id ) -- each txin can only be used once
);
CREATE INDEX txn2txin0 ON txn2txin ( txn_id );
CREATE INDEX txn2txin1 ON txn2txin ( txin_id );

CREATE TABLE txout (
	txout_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	-- ouput value + script
	txout_value BIGINT NOT NULL,
	txout_script_id BIGINT UNSIGNED NOT NULL,
	UNIQUE (txout_value, txout_script_id )
);

CREATE TABLE txn2txout (
	txn2txout_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	txn_id BIGINT UNSIGNED NOT NULL,
	txout_id BIGINT UNSIGNED NOT NULL,
	txout_idx INT UNSIGNED NOT NULL,

	-- a txout can in principle occur more than
	-- once in the same transaction...
	-- but not with the same idx
	UNIQUE ( txn_id, txout_id, txout_idx )
);
CREATE INDEX txn2txout0 ON txn2txout ( txn_id );
CREATE INDEX txn2txout1 ON txn2txout ( txout_id );
CREATE INDEX txn2txout2 ON txn2txout ( txn_id, txout_idx );

CREATE TABLE script (
	script_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	script_hash VARBINARY(20),
	script_type ENUM ( 'unparsed', 'pk', 'pk_hash' ) NOT NULL,
	script_data BLOB NOT NULL,

	UNIQUE (script_hash)
);

CREATE TABLE pk (
	pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	pk_hash VARBINARY(20) NOT NULL,
	pk_long VARBINARY(65),
	
	UNIQUE (pk_hash)
);

CREATE TABLE script2pk (
	script2pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

	script_id BIGINT UNSIGNED NOT NULL,
	pk_id BIGINT UNSIGNED NOT NULL,
	pk_idx INT UNSIGNED NOT NULL,

	UNIQUE (script_id, pk_id, pk_idx)
);
CREATE INDEX script2pk0 ON script2pk ( script_id );
CREATE INDEX script2pk1 ON script2pk ( pk_id );

-- and now the famous genesis block, it is included with all implementations
-- of the bitcoin daemon, pre-inclusion in the database eliminates a special case
-- in the code

-- the hash of this block is also embedded in the C code, this enables us to
-- check the integrity of the complete blockchain in the database
INSERT INTO `block` VALUES (1,0,285,0x6FE28C0AB6F1B372C1A6A246AE63F74F931E8365E15A089C68D6190000000000,0x0100010001,
		1,NULL,0x3BA3EDFD7A7B12B27AC72C3E67768F617FC81BC3888A51323A9FB8AA4B1E5E4A,1231006505,0xFFFF001D,0x1DAC2B7C);
INSERT INTO `block2txn` VALUES (1,1,1,0);
INSERT INTO `pk` VALUES (1,0x62E907B15CBF27D5425399EBF6F0FB50EBB88F18,
		0x04678AFDB0FE5548271967F1A67130B7105CD6A828E03909A67962E0EA1F61DEB649F6BC3F4CEF38C4F35504E51EC112DE5C384DF7BA0B8D578A4C702B6BF11D5F);
INSERT INTO `script` VALUES
	(1,0x99E6D226387B7C47D1E7AA0702349D99B20AD38C,'unparsed',
		0x04FFFF001D0104455468652054696D65732030332F4A616E2F32303039204368616E63656C6C6F72206F6E206272696E6B206F66207365636F6E64206261696C6F757420666F722062616E6B73),
	(2,0x8424E7542477EF1A76CBAB88D4B177D2FB5A96C1,'pk','');
INSERT INTO `script2pk` VALUES (1,2,1,0);
INSERT INTO `txin` VALUES (1,0,1,4294967295);
INSERT INTO `txn` VALUES (1,204,0x3BA3EDFD7A7B12B27AC72C3E67768F617FC81BC3888A51323A9FB8AA4B1E5E4A,1,0);
INSERT INTO `txn2txin` VALUES (1,1,1,0);
INSERT INTO `txn2txout` VALUES (1,1,1,0);
INSERT INTO `txout` VALUES (1,5000000000,2);
