-- Optional manual schema. The web installer creates these tables automatically.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,email VARCHAR(254) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sequences (owner_id BIGINT UNSIGNED PRIMARY KEY,last_number BIGINT UNSIGNED NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS people (id CHAR(36) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,record_number BIGINT UNSIGNED NOT NULL,name VARCHAR(160) NOT NULL,phone VARCHAR(60) NOT NULL DEFAULT '',email VARCHAR(254) NOT NULL DEFAULT '',category VARCHAR(250) NOT NULL, address TEXT NOT NULL, company VARCHAR(200) NOT NULL DEFAULT '',birthday VARCHAR(10) NOT NULL DEFAULT '',notes TEXT NOT NULL,favorite TINYINT(1) NOT NULL DEFAULT 0,details LONGTEXT NOT NULL,created_at VARCHAR(32) NOT NULL,updated_at VARCHAR(32) NOT NULL, UNIQUE KEY owner_number(owner_id,record_number),KEY owner_updated(owner_id,updated_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS relations (id CHAR(36) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,source_id CHAR(36) NOT NULL,target_id CHAR(36) NOT NULL,kind VARCHAR(250) NOT NULL,KEY owner_rel(owner_id),KEY source_rel(source_id),KEY target_rel(target_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS files (id CHAR(36) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,person_id CHAR(36) NOT NULL,storage_name CHAR(36) NOT NULL,name VARCHAR(200) NOT NULL,kind VARCHAR(20) NOT NULL,content_type VARCHAR(40) NOT NULL,size INT UNSIGNED NOT NULL,created_at VARCHAR(32) NOT NULL,KEY owner_file(owner_id),KEY person_file(person_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS options (id CHAR(36) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,kind VARCHAR(40) NOT NULL,scope VARCHAR(1500) NOT NULL,label VARCHAR(250) NOT NULL,source_label VARCHAR(250) NOT NULL DEFAULT '',hidden TINYINT(1) NOT NULL DEFAULT 0,KEY owner_kind(owner_id,kind)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS preferences (owner_id BIGINT UNSIGNED PRIMARY KEY,value_json TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shares (person_id CHAR(36) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,token CHAR(64) NOT NULL UNIQUE,enabled TINYINT(1) NOT NULL DEFAULT 0,fields TEXT NOT NULL,expires_at VARCHAR(32) NOT NULL DEFAULT '',updated_at VARCHAR(32) NOT NULL,KEY owner_share(owner_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (token_hash CHAR(64) PRIMARY KEY,owner_id BIGINT UNSIGNED NOT NULL,expires_at BIGINT NOT NULL,KEY session_expiry(expires_at)) ENGINE=InnoDB;