-- Phase 3: Consumables, Licenses, Accessories, Components

CREATE TABLE IF NOT EXISTS `licenses` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `license_key` TEXT NULL,
    `license_email` VARCHAR(255) NULL,
    `seats` INT(11) DEFAULT 1,
    `company_id` INT(11) NULL,
    `manufacturer_id` INT(11) NULL,
    `category_id` INT(11) NULL,
    `order_number` VARCHAR(255) NULL,
    `purchase_date` DATE NULL,
    `purchase_cost` DECIMAL(13,2) NULL,
    `expiration_date` DATE NULL,
    `notes` TEXT NULL,
    `enabled` TINYINT(1) DEFAULT 1,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `owner` VARCHAR(255) NULL,
    `modified_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `license_seats` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `license_id` INT(11) NOT NULL,
    `assigned_to` VARCHAR(255) NULL, -- Employee ID
    `asset_id` INT(11) NULL, -- Asset ID if assigned to asset
    `notes` TEXT NULL,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `assigned_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`license_id`) REFERENCES `licenses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `accessories` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `company_id` INT(11) NULL,
    `category_id` INT(11) NULL,
    `manufacturer_id` INT(11) NULL,
    `location_id` INT(11) NULL,
    `model_number` VARCHAR(255) NULL,
    `qty` INT(11) DEFAULT 0,
    `min_amt` INT(11) DEFAULT 0,
    `purchase_date` DATE NULL,
    `purchase_cost` DECIMAL(13,2) NULL,
    `order_number` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `image` TEXT NULL,
    `enabled` TINYINT(1) DEFAULT 1,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `owner` VARCHAR(255) NULL,
    `modified_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `accessory_checkouts` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `accessory_id` INT(11) NOT NULL,
    `assigned_to` VARCHAR(255) NOT NULL, -- Employee ID
    `note` TEXT NULL,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `assigned_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`accessory_id`) REFERENCES `accessories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `consumables` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `company_id` INT(11) NULL,
    `category_id` INT(11) NULL,
    `manufacturer_id` INT(11) NULL,
    `location_id` INT(11) NULL,
    `model_number` VARCHAR(255) NULL,
    `item_no` VARCHAR(255) NULL,
    `qty` INT(11) DEFAULT 0,
    `min_amt` INT(11) DEFAULT 0,
    `purchase_date` DATE NULL,
    `purchase_cost` DECIMAL(13,2) NULL,
    `order_number` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `image` TEXT NULL,
    `enabled` TINYINT(1) DEFAULT 1,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `owner` VARCHAR(255) NULL,
    `modified_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `consumable_checkouts` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `consumable_id` INT(11) NOT NULL,
    `assigned_to` VARCHAR(255) NOT NULL, -- Employee ID
    `note` TEXT NULL,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `assigned_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`consumable_id`) REFERENCES `consumables`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `components` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `company_id` INT(11) NULL,
    `category_id` INT(11) NULL,
    `location_id` INT(11) NULL,
    `serial` VARCHAR(255) NULL,
    `qty` INT(11) DEFAULT 0,
    `min_amt` INT(11) DEFAULT 0,
    `purchase_date` DATE NULL,
    `purchase_cost` DECIMAL(13,2) NULL,
    `order_number` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `image` TEXT NULL,
    `enabled` TINYINT(1) DEFAULT 1,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `owner` VARCHAR(255) NULL,
    `modified_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `component_assets` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `component_id` INT(11) NOT NULL,
    `asset_id` INT(11) NOT NULL,
    `note` TEXT NULL,
    `creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `assigned_by` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`component_id`) REFERENCES `components`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE consumable_checkouts ADD COLUMN asset_id INT NULL AFTER assigned_to;