-- Create `accounts` table
CREATE TABLE IF NOT EXISTS `accounts` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
  	`login_name` varchar(50) NOT NULL,
  	`user_name` varchar(50) NOT NULL,
  	`password` varchar(255) NOT NULL,
  	`email` varchar(100) NOT NULL,
  	`admin` TINYINT NOT NULL DEFAULT '0',
  	`lang` CHAR(3) NOT NULL DEFAULT 'eng',
  	`disabled` INT NOT NULL DEFAULT '0',
  	`last_login` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE `accounts` ADD UNIQUE `unique_login_name`(`login_name`);

-- Insert initial admin account if it doesn't exist
INSERT INTO `accounts` (`id`, `login_name`, `user_name`, `password`, `email`, `admin`, `lang`, `last_login`)
SELECT 1, 'admin', 'My name', '$2y$10$SfhYIDtn.iOuCW7zfoFLuuZHX6lja4lF4XA4JqNmpiH/.P3zB8JCa', 'admin@mysite.com', 1, 'eng', '0000-00-00 00:00:00'
WHERE NOT EXISTS (SELECT 1 FROM `accounts` WHERE `id` = 1);

-- Create `user_permission` table
CREATE TABLE IF NOT EXISTS `user_permission` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `function` VARCHAR(50),
    `permission_lvl` INT NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`, `function`)
);

-- Insert admin permissions
INSERT INTO `user_permission` (`id`, `function`, `permission_lvl`)
SELECT 1, 'fyrk_project', 100
WHERE NOT EXISTS (SELECT 1 FROM `user_permission` WHERE `id` = 1 AND `function` = 'fyrk_project');

-- Create `fyrk_projects` table
CREATE TABLE IF NOT EXISTS `fyrk_projects` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `owner` INT NOT NULL,
    `project_status` INT NOT NULL DEFAULT '0',
    `edited` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

-- Create `fyrk_project_tasks` table
CREATE TABLE IF NOT EXISTS `fyrk_project_tasks` (
    `task_id` INT NOT NULL AUTO_INCREMENT,
    `project_id` INT NOT NULL,
    `name` VARCHAR(60) NOT NULL,
    `owner` INT NOT NULL,
    `end_date` DATE NOT NULL,
    `status` INT NOT NULL DEFAULT '0',
    `description` TEXT NOT NULL,
    `options` TEXT,
    `reminder_active` TINYINT NOT NULL DEFAULT '0',
    `reminder_sent` DATE NOT NULL DEFAULT '0000-00-00',
    `changed_by` INT NOT NULL,
    PRIMARY KEY (`task_id`)
);

-- Create `scheduler_log` table
CREATE TABLE IF NOT EXISTS `scheduler_log` (
    `scheduler_log_id` INT NOT NULL AUTO_INCREMENT,
    `datetime` DATETIME NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `info` TEXT NOT NULL,
    PRIMARY KEY (`scheduler_log_id`)
);

-- Create `site_settings` table
CREATE TABLE IF NOT EXISTS `site_settings` (
    `setting_id` INT NOT NULL AUTO_INCREMENT,
    `function` VARCHAR(50) NOT NULL,
    `part` VARCHAR(50) NOT NULL,
    `setting_name` VARCHAR(100) NOT NULL,
    `value1` VARCHAR(255) NOT NULL,
    `value2` VARCHAR(255),
    `array` TEXT,
    `int_value` INT NOT NULL DEFAULT '0',
    PRIMARY KEY (`setting_id`)
);