CREATE TABLE tbltool_tracking_tags(
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tag_code VARCHAR(255) NOT NULL,
    purchase_price DECIMAL NOT NULL,
    sell_price DECIMAL NOT NULL,
    status ENUM('in_stock', 'sold_to_client', 'lost', 'damaged', 'test_usage') NOT NULL DEFAULT 'in_stock',
    sold_to_client_id BIGINT DEFAULT NULL,
    added_on DATETIME NOT NULL
);
CREATE TABLE tbltools(
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT NOT NULL,
    name VARCHAR(500) NOT NULL,
    number BIGINT NOT NULL,
    prefix VARCHAR(50) NOT NULL,
    number_format BIGINT NOT NULL,
    tool_number_text VARCHAR(191) NOT NULL,
    photo VARCHAR(500) DEFAULT NULL,
    price DECIMAL DEFAULT NULL,
    tag_code VARCHAR(255) DEFAULT NULL,
    tag_assigned_on DATETIME DEFAULT NULL,
    tool_type ENUM('single_tool', 'tool_set') DEFAULT 'single_tool',
    note TEXT DEFAULT NULL,
    status VARCHAR(191) DEFAULT 'in_stock' COMMENT 'in_use, in_stock',
    in_use_with BIGINT DEFAULT NULL,
    in_use_since DATETIME DEFAULT NULL,
    added_by BIGINT DEFAULT NULL,
    added_on DATETIME
);
CREATE TABLE tbltool_set_items(
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT NOT NULL,
    tool_id BIGINT NOT NULL,
    name VARCHAR(500) NOT NULL,
    quantity INT NOT NULL,
    added_by BIGINT DEFAULT NULL,
    added_on DATETIME
);
CREATE TABLE tbltool_tags_allocation_history(
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tag_code VARCHAR(255) NOT NULL,
    tool_id BIGINT NOT NULL,
    added_by BIGINT DEFAULT NULL,
    added_on DATETIME
);
INSERT INTO tbloptions_static (name, value, autoload) VALUES ('tool_prefix', 'TL-', '1');
INSERT INTO tbloptions_static (name, value, autoload) VALUES ('next_tool_number', '1', '1');
INSERT INTO tbloptions_static (name, value, autoload) VALUES ('tool_format', '1', '1');
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'tool_prefix', 'TL-', '1' FROM tbltenants;
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'next_tool_number', '1', '1' FROM tbltenants;
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'tool_format', '1', '1' FROM tbltenants;
CREATE TABLE tbltools_usage_data (
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    tenant_id BIGINT NOT NULL,
    staff_id BIGINT NOT NULL,
    tool_id BIGINT NOT NULL,
    checkout_datetime DATETIME NOT NULL,
    checkout_latitude VARCHAR(191) DEFAULT NULL,
    checkout_longitude VARCHAR(191) DEFAULT NULL,
    checkin_datetime DATETIME DEFAULT NULL,
    checkin_latitude VARCHAR(191) DEFAULT NULL,
    checkin_longitude VARCHAR(191) DEFAULT NULL,
    status VARCHAR(191) DEFAULT 'checked_out' COMMENT 'checked_in, checked_out',
    added_by BIGINT NOT NULL,
    added_on DATETIME NOT NULL
);

-- 2021-02-11 - Akash
ALTER TABLE `tbltools` CHANGE `status` `status` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'in_stock' COMMENT 'in_use, in_stock, lost, damaged';

INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'tools' as feature, 'create' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'tools' as feature, 'edit' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'tools' as feature, 'checkin_checkout' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'tools' as feature, 'receive_notification' as capability FROM tblstaff WHERE is_tenant = 0;