CREATE TABLE location_bins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    location_id BIGINT NOT NULL,

    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,

    type ENUM(
        'RACK',
        'BIN',
        'AREA',
        'SHELF',
        'PALLET'
    ) DEFAULT 'RACK',

    is_active BOOLEAN DEFAULT TRUE,

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

    UNIQUE KEY uniq_bin (location_id, code),

    FOREIGN KEY (location_id)
        REFERENCES locations(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

   // bin_id BIGINT NULL,
    //-- jika opname hanya Rak 1
CREATE TABLE stock_opnames (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_number VARCHAR(100) UNIQUE NOT NULL,

    location_id BIGINT NOT NULL,


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

    notes TEXT,

    created_by BIGINT,
    checked_by BIGINT,
    approved_by BIGINT,

    counted_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 (bin_id) REFERENCES location_bins(id),

    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (checked_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)

) ENGINE=InnoDB;

CREATE TABLE stock_opname_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,

    bin_id BIGINT NULL,
    -- item ditemukan di rak mana

    batch_number VARCHAR(100) NULL,
    expired_date DATE NULL,

    system_qty DECIMAL(18,4) DEFAULT 0,
    counted_qty DECIMAL(18,4) DEFAULT 0,
    variance_qty DECIMAL(18,4) DEFAULT 0,

    notes TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

    FOREIGN KEY (item_id)
        REFERENCES items(id),

    FOREIGN KEY (bin_id)
        REFERENCES location_bins(id),

    UNIQUE KEY uniq_opname_item_bin (
        opname_id,
        item_id,
        bin_id,
        batch_number
    )

) ENGINE=InnoDB;

CREATE TABLE stock_opname_item_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    opname_id BIGINT NOT NULL,
    opname_item_id BIGINT NULL,

    item_id BIGINT NOT NULL,
    bin_id BIGINT NULL,

    action_type ENUM(
        'ADD',
        'UPDATE',
        'ADJUST',
        'DELETE'
    ) DEFAULT 'ADD',

    qty_before DECIMAL(18,4) DEFAULT 0,
    qty_change DECIMAL(18,4) DEFAULT 0,
    qty_after DECIMAL(18,4) DEFAULT 0,

    system_qty DECIMAL(18,4) DEFAULT 0,
    variance_qty DECIMAL(18,4) DEFAULT 0,

    batch_number VARCHAR(100) NULL,
    expired_date DATE NULL,

    notes TEXT NULL,

    created_by BIGINT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

    FOREIGN KEY (opname_item_id)
        REFERENCES stock_opname_items(id)
        ON DELETE SET NULL,

    FOREIGN KEY (item_id)
        REFERENCES items(id),

    FOREIGN KEY (bin_id)
        REFERENCES location_bins(id),

    FOREIGN KEY (created_by)
        REFERENCES users(id),

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

ALTER TABLE stock_opname_item_logs
ADD COLUMN checked_by BIGINT NULL AFTER created_by;

ALTER TABLE stock_opname_item_logs
ADD CONSTRAINT fk_stock_opname_checked_by
FOREIGN KEY (checked_by)
REFERENCES users(id)
ON DELETE SET NULL;


//CREATE TABLE stock_opname_snapshots (
// 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,
//  counted_qty DECIMAL(18,4) NOT NULL,
//  variance_qty DECIMAL(18,4) NOT NULL,
//  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
//  UNIQUE KEY uniq_snapshot (opname_id, item_id)
//);

//ALTER TABLE stock_opname_snapshots
//ADD CONSTRAINT fk_snapshot_opname
//FOREIGN KEY (opname_id)
//REFERENCES stock_opnames(id)
//ON DELETE CASCADE;