
-- Only add 'item_role' column if it doesn't exist
SET @col_exists := (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'items' 
    AND COLUMN_NAME = 'item_role'
    AND TABLE_SCHEMA = DATABASE()
);

-- Conditionally add the column
SET @stmt := IF(
  @col_exists = 0,
  'ALTER TABLE `items` ADD COLUMN `item_role` ENUM(''stock'', ''material'') NOT NULL DEFAULT ''stock''',
  'SELECT "Column `item_role` already exists"'
);

PREPARE alter_stmt FROM @stmt;
EXECUTE alter_stmt;
DEALLOCATE PREPARE alter_stmt;


CREATE TABLE `bom_master` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `item_id` INT NOT NULL, -- must be a `stock` item
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
);


CREATE TABLE `bom_components` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `bom_id` INT NOT NULL,
  `component_item_id` INT NOT NULL, -- must be a `material` item
  `quantity` DECIMAL(10, 2) NOT NULL, -- quantity needed per unit
  PRIMARY KEY (`id`),
  FOREIGN KEY (`bom_id`) REFERENCES `bom_master`(`id`),
  FOREIGN KEY (`component_item_id`) REFERENCES `items`(`id`)
);

CREATE TABLE `inventory_movements` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `item_id` INT NOT NULL,
  `movement_type` ENUM('in', 'out', 'adjustment') NOT NULL,
  `reference_type` ENUM('purchase', 'sale', 'manual', 'other') DEFAULT 'manual',
  `reference_id` INT DEFAULT NULL,
  `quantity` DECIMAL(10, 2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
);


CREATE TABLE IF NOT EXISTS sup_outstanding (
    id INT NOT NULL AUTO_INCREMENT,
    purchase_id INT NOT NULL,
    vendor_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('unpaid','partial','paid') DEFAULT 'unpaid',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS sup_payments (
    id INT NOT NULL AUTO_INCREMENT,
    vendor_id INT NOT NULL,
    purchase_id INT DEFAULT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    remarks VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id)
);

