-- Bin Location Setup
CREATE TABLE IF NOT EXISTS bin_locations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_bin_location_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed standard bin locations
INSERT IGNORE INTO bin_locations (name, description) VALUES
('A-01', 'Aisle A, Shelf 1'), ('A-02', 'Aisle A, Shelf 2'), ('A-03', 'Aisle A, Shelf 3'),
('A-04', 'Aisle A, Shelf 4'), ('A-05', 'Aisle A, Shelf 5'), ('B-01', 'Aisle B, Shelf 1'),
('B-02', 'Aisle B, Shelf 2'), ('B-03', 'Aisle B, Shelf 3'), ('B-04', 'Aisle B, Shelf 4'),
('B-05', 'Aisle B, Shelf 5'), ('C-01', 'Aisle C, Shelf 1'), ('C-02', 'Aisle C, Shelf 2'),
('C-03', 'Aisle C, Shelf 3'), ('C-04', 'Aisle C, Shelf 4'), ('C-05', 'Aisle C, Shelf 5'),
('D-01', 'Aisle D, Shelf 1'), ('D-02', 'Aisle D, Shelf 2'), ('D-03', 'Aisle D, Shelf 3'),
('E-01', 'Aisle E, Shelf 1'), ('E-02', 'Aisle E, Shelf 2'), ('F-01', 'Aisle F, Shelf 1'),
('F-02', 'Aisle F, Shelf 2'), ('G-01', 'Aisle G, Shelf 1'), ('G-02', 'Aisle G, Shelf 2'),
('H-01', 'Aisle H, Shelf 1'), ('H-02', 'Aisle H, Shelf 2');

-- Add bin_location_id to parts
ALTER TABLE parts ADD COLUMN bin_location_id BIGINT UNSIGNED NULL AFTER supplier_name;
ALTER TABLE parts ADD CONSTRAINT fk_part_bin_location FOREIGN KEY (bin_location_id) REFERENCES bin_locations(id) ON DELETE SET NULL;

-- Permissions
INSERT IGNORE INTO permissions (code, description) VALUES
('bin_location.view', 'View bin locations'),
('bin_location.create', 'Create bin locations'),
('bin_location.edit', 'Edit bin locations'),
('bin_location.delete', 'Delete bin locations');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE p.code IN ('bin_location.view','bin_location.create','bin_location.edit','bin_location.delete');