CREATE TABLE [db_version] (
	id INTEGER PRIMARY KEY
	,version_maj INTEGER NOT NULL
	,version_min INTEGER NOT NULL
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
);

CREATE TABLE [at_setlist] (
	uuid NCHAR(56) PRIMARY KEY
	,title NVARCHAR(128) DEFAULT ''
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
);

CREATE TABLE [at_song] (
	uuid NCHAR(56) PRIMARY KEY
	,ccli INTEGER
	,title NVARCHAR(128) DEFAULT ''
	,arrangement NVARCHAR(128) DEFAULT ''
	,beats_per_minute INTEGER
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
);

CREATE TABLE [at_setlist_song] (
	setlist_uuid NCHAR(56)
	,song_uuid NCHAR(56)
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
	,PRIMARY KEY (setlist_uuid, song_uuid)
);

CREATE TABLE [at_section] (
	song_uuid NCHAR(56)
	,sequence INTEGER
	,section_type INTEGER
	,beats_per_bar INTEGER
	,bar_count INTEGER
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
);

CREATE TABLE [ext_source] (
	id INTEGER PRIMARY KEY
	,title NVARCHAR(255) NOT NULL
);

CREATE TABLE [ext_mapping] (
	source_id INTEGER
	,id_external_integer INTEGER NOT NULL DEFAULT 0
	,uuid NCHAR(56) NOT NULL
	,is_deleted INTEGER NOT NULL DEFAULT 0
	,creation_dt INTEGER DEFAULT (unixepoch('now'))
	,modification_dt INTEGER DEFAULT (unixepoch('now'))
	,PRIMARY KEY (source_id, id_external_integer)
);

INSERT INTO [ext_source](id,title) VALUES(1,'Planning Center Services');
INSERT INTO [db_version](id,version_maj,version_min) VALUES(0,1,0);