scripts/afu/.sql_init

-- Initialize the tables for the callbook program

DROP TABLE IF EXISTS `callbook_user`;
DROP TABLE IF EXISTS `callbook_address`;

CREATE TABLE IF NOT EXISTS `callbook_user`(
	`id`		SERIAL,
	`user_id`	INT(10) UNSIGNED NOT NULL DEFAULT 0,
	`call`		VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'amateur radio call sign e.g. OE8ABC',
	`firstname`	VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'forename, given name',
	`surname`	VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'family name, last name',
	`gender`	CHAR(1) NOT NULL DEFAULT '' COMMENT 'gender guessed from the first name',
	`created`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`created_by` INT(10) UNSIGNED NOT NULL DEFAULT 0,
	`modified`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`modified_by` INT(10) UNSIGNED NOT NULL DEFAULT 0,
	`active`	TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'the user is active',
	`inactive`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'old inactive call since...',
	`address_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'link to id of callbook_adress',
	PRIMARY KEY(`id`)
	)	AUTO_INCREMENT=0
		DEFAULT CHARSET=utf8mb4
		COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `callbook_address`(
	`id`			SERIAL,
	`location`		VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'original location from the import',
	`address`		VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'original address from the import',
	`status`		INT(1)	NOT NULL DEFAULT '0' COMMENT 'Status of the address: 0...invalid, -1...uncertain, -2...in progress, 1...computed (valid)',
	`postal_code`	VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'also known as Zip code, "Postleitzahl", computed from location',
	`city`			VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'city, computed from location',
	`street`		VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'street, computed from address',
	`federal_state_code` TINYINT NOT NULL DEFAULT -1
	COMMENT 'Austria: 0...outside, 1...Vienna, 2...Salzburg, 3...Lower Austria, 4...Burgenland, 5...Upper Austria, 6...Styria, 7...Tyrol, 8...Carinthia, 9...Vorarlberg',
	`lat`			DOUBLE NOT NULL DEFAULT 0.0 COMMENT 'latitude',
	`long`			DOUBLE NOT NULL DEFAULT 0.0 COMMENT 'longitude',
	`qth`			VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Maidenhead locator, QTH locator',
	`created`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`created_by` INT(10) UNSIGNED NOT NULL DEFAULT 0,
	`modified`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`modified_by` INT(10) UNSIGNED NOT NULL DEFAULT 0,
	`active`	TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'the address is active',
	`inactive`	DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'old inactive address, since...',
	PRIMARY KEY(`id`)
	)	AUTO_INCREMENT=0
		DEFAULT CHARSET=utf8mb4
		COLLATE=utf8mb4_unicode_ci;