//StockOpnames
CREATE TABLE stock_opnames (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_number VARCHAR(100) NOT NULL UNIQUE,

    location_id BIGINT NOT NULL,

    status ENUM(
        'DRAFT',
        'IN_PROGRESS',
        'CHECKED',
        'APPROVED',
        'REJECTED',
        'POSTED',
        'CANCELLED'
    ) DEFAULT 'DRAFT',

    checker_id BIGINT NULL,
    supervisor_id BIGINT NULL,

    notes TEXT,

    created_by BIGINT NULL,

    started_at TIMESTAMP NULL,
    checked_at TIMESTAMP NULL,
    approved_at TIMESTAMP NULL,
    posted_at TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (checker_id) REFERENCES users(id),
    FOREIGN KEY (supervisor_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),

    INDEX idx_opname_number (opname_number),
    INDEX idx_location (location_id),
    INDEX idx_status (status)
);

CREATE TABLE stock_opname_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,

    system_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
    actual_qty DECIMAL(18,4) NOT NULL DEFAULT 0,

    variance DECIMAL(18,4)
        GENERATED ALWAYS AS (actual_qty - system_qty) STORED,

    notes TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (opname_id) REFERENCES stock_opnames(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id),

    INDEX idx_opname (opname_id),
    INDEX idx_item (item_id)
);

CREATE TABLE stock_opname_approvals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,

    role ENUM('CHECKER','SUPERVISOR') NOT NULL,

    status ENUM('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING',

    notes TEXT,

    approved_at TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (opname_id) REFERENCES stock_opnames(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),

    UNIQUE KEY unique_role_per_opname (opname_id, role),

    INDEX idx_opname (opname_id),
    INDEX idx_role (role)
);

CREATE TABLE stock_opname_postings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_id BIGINT NOT NULL,

    item_id BIGINT NOT NULL,
    location_id BIGINT NOT NULL,

    system_qty DECIMAL(18,4) NOT NULL,
    actual_qty DECIMAL(18,4) NOT NULL,
    variance DECIMAL(18,4) NOT NULL,

    movement_id BIGINT NULL,

    posted_by BIGINT NULL,
    posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (opname_id) REFERENCES stock_opnames(id),
    FOREIGN KEY (item_id) REFERENCES items(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (movement_id) REFERENCES stock_movements(id),
    FOREIGN KEY (posted_by) REFERENCES users(id),

    INDEX idx_opname (opname_id),
    INDEX idx_item (item_id)
);