-- =========================
-- MASTER: LOCATIONS
-- =========================
CREATE TABLE locations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =========================
-- MASTER: ITEMS
-- =========================
CREATE TABLE items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    unit VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =========================
-- BARCODES (MULTI)
-- =========================
CREATE TABLE item_barcodes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    barcode VARCHAR(150) NOT NULL UNIQUE,
    is_primary BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_barcode (barcode)
);

-- =========================
-- INVENTORY
-- =========================
CREATE TABLE inventories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    location_id BIGINT NOT NULL,
    quantity DECIMAL(18,4) NOT NULL DEFAULT 0,

    UNIQUE KEY unique_inventory (item_id, location_id),

    FOREIGN KEY (item_id) REFERENCES items(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),

    CHECK (quantity >= 0)
);

-- =========================
-- STOCK MOVEMENTS (AUDIT)
-- =========================
CREATE TABLE stock_movements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    location_id BIGINT NOT NULL,

    movement_type ENUM(
        'IN',
        'OUT',
        'TRANSFER_IN',
        'TRANSFER_OUT',
        'ADJUSTMENT'
    ) NOT NULL,

    quantity DECIMAL(18,4) NOT NULL,
    reference_type VARCHAR(50),
    reference_id BIGINT,

    notes TEXT,
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (item_id) REFERENCES items(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),

    INDEX idx_reference (reference_type, reference_id),
    INDEX idx_item_location (item_id, location_id)
);

-- =========================
-- USERS
-- =========================
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255),
    role ENUM('ADMIN','CHECKER','SUPERVISOR','OPERATOR') DEFAULT 'OPERATOR',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- STO (HEADER)
-- =========================
CREATE TABLE stock_transfer_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sto_number VARCHAR(100) NOT NULL UNIQUE,

    from_location_id BIGINT NOT NULL,
    to_location_id BIGINT NOT NULL,

    status ENUM(
        'DRAFT',
        'CHECKED',
        'APPROVED',
        'IN_TRANSIT',
        'COMPLETED',
        'CANCELLED'
    ) DEFAULT 'DRAFT',

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

    FOREIGN KEY (from_location_id) REFERENCES locations(id),
    FOREIGN KEY (to_location_id) REFERENCES locations(id),
    FOREIGN KEY (created_by) REFERENCES users(id),

    CHECK (from_location_id <> to_location_id),

    INDEX idx_sto_number (sto_number)
);

-- =========================
-- STO ITEMS
-- =========================
CREATE TABLE stock_transfer_order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sto_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,

    requested_qty DECIMAL(18,4) NOT NULL,
    shipped_qty DECIMAL(18,4) DEFAULT 0,
    received_qty DECIMAL(18,4) DEFAULT 0,

    FOREIGN KEY (sto_id) REFERENCES stock_transfer_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id),

    INDEX idx_sto (sto_id)
);

-- =========================
-- STO SHIPMENTS
-- =========================
CREATE TABLE stock_transfer_shipments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sto_id BIGINT NOT NULL,

    shipped_by BIGINT,
    received_by BIGINT,

    shipped_at TIMESTAMP NULL,
    received_at TIMESTAMP NULL,

    status ENUM('PENDING','SHIPPED','RECEIVED') DEFAULT 'PENDING',

    FOREIGN KEY (sto_id) REFERENCES stock_transfer_orders(id),
    FOREIGN KEY (shipped_by) REFERENCES users(id),
    FOREIGN KEY (received_by) REFERENCES users(id)
);

-- =========================
-- STO APPROVALS (DUAL CONTROL)
-- =========================
CREATE TABLE stock_transfer_approvals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sto_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 (sto_id) REFERENCES stock_transfer_orders(id),
    FOREIGN KEY (user_id) REFERENCES users(id),

    UNIQUE KEY unique_role_per_sto (sto_id, role)
);