
USE `helga`;

-- assets
CREATE TABLE `assets` (
	`asset_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`asset_name` VARCHAR(255) NOT NULL,
	`type_id` INT NOT NULL,
	`due_date` INT(10) NULL
);
CREATE TABLE `asset_types` (
	`type_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`type_name` VARCHAR(255) NOT NULL
);
INSERT INTO `asset_types` ( `type_id`, `type_name` ) VALUES
( NULL, 'show' ),
( NULL, 'shot' );
CREATE TABLE `attrs` (
	`attr_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`asset_id` INT NOT NULL,
	`attr_key` VARCHAR(255) NOT NULL,
	`attr_val` TEXT NULL
);
CREATE TABLE `lists` (
	`list_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`parent_id` INT NULL,
	`list_type` ENUM('global','show','user'),
	`list_label` VARCHAR(255)
);

-- log stuff
CREATE TABLE `logs` (
	`entry_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`asset_id` INT NOT NULL,
	`timestamp` INT(10) NOT NULL,
	`text` TEXT NULL,
	`entry_type` ENUM('user','system'),
	`user_id` INT NOT NULL
);
CREATE TABLE `attachments` (
	`attachment_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`entry_id` INT NOT NULL,
	`file_path` VARCHAR(255) NOT NULL
);

-- user stuff
CREATE TABLE `users` (
	`user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`user_name` VARCHAR(255) NOT NULL,
	`password` VARCHAR(32) NULL,
	`email` VARCHAR(255) NULL,
	`cell_num` VARCHAR(255) NULL,
	`cell_carrier` INT NULL
);
CREATE TABLE `assignments` (
	`assignment_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`user_id` INT NOT NULL,
	`asset_id` INT NOT NULL,
	`due_date` INT(10) NULL,
	`description` TEXT NULL
);
CREATE TABLE `notifications` (
	`notification_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`user_id` INT NOT NULL,
	`asset_id` INT NOT NULL,
	`notify_for` ENUM('user','system','both'),
	`notify_via` ENUM('email','sms','both')
);
CREATE TABLE `permissions` (
	`permission_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`asset_id` INT NOT NULL,
	`user_id` INT NOT NULL,
	`permission_level` ENUM('blocked','guest','basic','admin')
);
CREATE TABLE `sms` (
	`sms_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`carrier_name` VARCHAR(255) NOT NULL,
	`carrier_email` VARCHAR(255) NOT NULL
);

-- scripting stuff
CREATE TABLE `scripts` (
	`script_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`script_name` VARCHAR(255) NOT NULL,
	`script_path` VARCHAR(255) NOT NULL
);
