PriceForge/migrations/026_add_partnumber_books.sql

-- Partnumber book snapshots for QuoteForge integration.
-- PriceForge writes, QuoteForge reads (SELECT only).

-- Add is_primary_pn to lot_partnumbers: marks which PN drives qty in vendor BOM.
ALTER TABLE lot_partnumbers
    ADD COLUMN IF NOT EXISTS is_primary_pn TINYINT(1) NOT NULL DEFAULT 1 AFTER lot_name;

-- Versioned snapshot headers.
CREATE TABLE IF NOT EXISTS qt_partnumber_books (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    version    VARCHAR(30)  NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) NOT NULL DEFAULT '',
    is_active  TINYINT(1)   NOT NULL DEFAULT 0,
    UNIQUE KEY uq_qt_partnumber_books_version (version),
    INDEX idx_qt_partnumber_books_is_active (is_active),
    INDEX idx_qt_partnumber_books_created_at (created_at)
);

-- Mapping rows for each snapshot.
-- Bundles are expanded: one partnumber may produce multiple rows (one per LOT component).
CREATE TABLE IF NOT EXISTS qt_partnumber_book_items (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    book_id       BIGINT UNSIGNED NOT NULL,
    partnumber    VARCHAR(255)    NOT NULL,
    lot_name      VARCHAR(255)    NOT NULL,
    is_primary_pn TINYINT(1)      NOT NULL DEFAULT 1,
    INDEX idx_qt_partnumber_book_items_book_id (book_id),
    INDEX idx_qt_partnumber_book_items_partnumber (partnumber),
    CONSTRAINT fk_qt_partnumber_book_items_book
        FOREIGN KEY (book_id) REFERENCES qt_partnumber_books (id) ON DELETE CASCADE
);