-- =========================
-- MASTER: LOCATIONS
-- =========================
CREATE TABLE locations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    type ENUM('WAREHOUSE','STORE','TRANSIT') DEFAULT 'WAREHOUSE',
    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,

    category VARCHAR(100),
    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,

    INDEX idx_category (category)
);

-- =========================
-- ITEM BARCODES
-- =========================
CREATE TABLE item_barcodes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,

    barcode VARCHAR(150) NOT NULL UNIQUE,
    type ENUM('EAN13','QR','CODE128','OTHER') DEFAULT 'CODE128',

    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),
    INDEX idx_item (item_id)
);

-- =========================
-- 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,
    reserved_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),
    CHECK (reserved_quantity >= 0)
);
ALTER TABLE inventories
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP;

-- =========================
-- 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),
    FOREIGN KEY (created_by) REFERENCES users(id),

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

-- =========================
-- USERS
-- =========================
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    google_id VARCHAR(255) UNIQUE,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NULL,
    name VARCHAR(150),
    avatar TEXT,

    role ENUM(
        'ADMIN',
        'CHECKER',
        'SUPERVISOR',
        'OPERATOR'
    ) DEFAULT 'OPERATOR',

    is_active BOOLEAN DEFAULT TRUE,

    last_login_at TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE 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',

    remarks TEXT,

    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),
    INDEX idx_status (status)
);

-- =========================
-- 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),

    CHECK (requested_qty >= 0),
    CHECK (shipped_qty >= 0),
    CHECK (received_qty >= 0),

    INDEX idx_sto (sto_id),
    INDEX idx_item (item_id)
);

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

    sto_id BIGINT NOT NULL,
    shipment_number VARCHAR(100),

    shipped_by BIGINT,
    received_by BIGINT,

    shipped_at TIMESTAMP NULL,
    received_at TIMESTAMP NULL,

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

    notes TEXT,

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

    INDEX idx_sto (sto_id)
);

-- =========================
-- STO SHIPMENT ITEMS (IMPORTANT)
-- =========================
CREATE TABLE stock_transfer_shipment_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    shipment_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,

    quantity DECIMAL(18,4) NOT NULL,

    FOREIGN KEY (shipment_id) REFERENCES stock_transfer_shipments(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id),

    CHECK (quantity >= 0),

    INDEX idx_shipment (shipment_id),
    INDEX idx_item (item_id)
);

-- =========================
-- STO APPROVALS
-- =========================
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),
    INDEX idx_sto (sto_id)
);

-- =========================
-- STOCK RESERVATIONS
-- =========================
CREATE TABLE stock_reservations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    item_id BIGINT NOT NULL,
    location_id BIGINT NOT NULL,

    reference_type VARCHAR(50),
    reference_id BIGINT,

    quantity DECIMAL(18,4) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

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


CREATE TABLE item_dimensions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    item_id BIGINT NOT NULL UNIQUE,

    length_cm DECIMAL(18,4),
    width_cm DECIMAL(18,4),
    height_cm DECIMAL(18,4),
    weight_gr DECIMAL(18,4),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE item_unit_conversions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    item_id BIGINT NOT NULL,

    from_unit VARCHAR(50) NOT NULL,
    to_unit VARCHAR(50) NOT NULL,

    conversion_ratio DECIMAL(18,6) NOT NULL,

    level INT NOT NULL, 
    -- 2,3,4,5 sesuai satuan_2 - satuan_5

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

    INDEX idx_item (item_id)
);

CREATE TABLE item_pricing (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    item_id BIGINT NOT NULL,

    price_type ENUM('BUY','SELL') NOT NULL,

    unit VARCHAR(50),

    price DECIMAL(18,4) NOT NULL,

    level INT DEFAULT 1,
    -- untuk def_hrg_jual_satuan_1 - 5

    effective_date DATE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

    INDEX idx_item (item_id),
    INDEX idx_type (price_type)
);

CREATE TABLE item_taxes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    item_id BIGINT NOT NULL,

    ppn_rate DECIMAL(5,2),
    ppnbm_rate DECIMAL(5,2),

    tax_base_percent DECIMAL(5,2),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

//category
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,

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

    INDEX idx_name (name)
);
ALTER TABLE items
ADD COLUMN category_id BIGINT NULL AFTER description;

ALTER TABLE items
ADD CONSTRAINT fk_items_category
FOREIGN KEY (category_id)
REFERENCES categories(id);

INSERT INTO categories (code, name)
SELECT DISTINCT
    UPPER(REPLACE(TRIM(category), ' ', '_')) AS code,
    TRIM(category) AS name
FROM items
WHERE category IS NOT NULL
AND category <> '';

UPDATE items i
JOIN categories c
    ON TRIM(LOWER(i.category)) = TRIM(LOWER(c.name))
SET i.category_id = c.id;



CREATE TABLE units (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,

    category ENUM(
        'PIECE',
        'LENGTH',
        'AREA',
        'WEIGHT',
        'VOLUME',
        'PACK',
        'INDUSTRIAL',
        'OTHER'
    ) DEFAULT 'OTHER',

    symbol VARCHAR(20),

    is_base BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,

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

PCS
SET
UNT
ROL
(blank)
MTR
BTG
UN
DRUM
TIN
PAIR
PVC
LBR
FTF
Ltr
YA
PAIL
M2
UT

INSERT INTO units (code, name, category, symbol, is_base) VALUES

-- PIECE / UNIT
('PCS','Pieces','PIECE','pcs',1),
('SET','Set','PIECE','set',0),
('UNT','Unit','PIECE','unit',0),
('UN','Unit','PIECE','unit',0),
('UT','Unit','PIECE','unit',0),
('PAIR','Pair','PIECE','pair',0),

-- LENGTH
('MTR','Meter','LENGTH','m',1),
('FTF','Feet','LENGTH','ft',0),

-- AREA
('M2','Square Meter','AREA','m²',1),

-- PACKAGING / INDUSTRIAL
('ROL','Roll','PACK','roll',0),
('DRUM','Drum','INDUSTRIAL','drum',0),
('TIN','Tin','INDUSTRIAL','tin',0),
('PAIL','Pail','INDUSTRIAL','pail',0),
('BTG','Batang','INDUSTRIAL','btg',0),

-- VOLUME
('LTR','Liter','VOLUME','L',1),
('LBR','Lembar','PIECE','lbr',0),

-- UNKNOWN / LEGACY
('PVC','PVC','OTHER','pcs',0),
('YA','Ya','OTHER',NULL,0);

ALTER TABLE items
ADD units VARCHAR(20) NULL AFTER unit;

UPDATE items
SET units = UPPER(TRIM(unit));

UPDATE items
SET units = CASE

WHEN UPPER(unit) IN ('UN','UT','UNT') THEN 'UNT'
WHEN UPPER(unit) = 'PCS' THEN 'PCS'
WHEN UPPER(unit) = 'SET' THEN 'SET'
WHEN UPPER(unit) = 'ROL' THEN 'ROL'
WHEN UPPER(unit) = 'MTR' THEN 'MTR'
WHEN UPPER(unit) = 'M2' THEN 'M2'
WHEN UPPER(unit) = 'LTR' THEN 'LTR'
WHEN UPPER(unit) = 'LBR' THEN 'LBR'
WHEN UPPER(unit) = 'PAIR' THEN 'PAIR'
WHEN UPPER(unit) = 'DRUM' THEN 'DRUM'
WHEN UPPER(unit) = 'TIN' THEN 'TIN'
WHEN UPPER(unit) = 'PAIL' THEN 'PAIL'
WHEN UPPER(unit) = 'BTG' THEN 'BTG'
WHEN UPPER(unit) = 'PVC' THEN 'PVC'
WHEN UPPER(unit) = 'FTF' THEN 'FTF'
WHEN UPPER(unit) = 'YA' THEN 'YA'
WHEN UPPER(unit) = 'M2' THEN 'M2'

ELSE 'UNT'
END;


SELECT DISTINCT units
FROM items
WHERE units NOT IN (SELECT code FROM units);

CREATE INDEX idx_items_units ON items(units);


CREATE TABLE activity_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,

  entity VARCHAR(50),       -- LOCATION, ITEM, STO
  entity_id BIGINT,

  action VARCHAR(50),       -- CREATE, UPDATE, DELETE

  description TEXT,

  created_by BIGINT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



CREATE TABLE suppliers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    code VARCHAR(50) UNIQUE,
    name VARCHAR(255) NOT NULL UNIQUE,

    contact_person VARCHAR(150),
    phone VARCHAR(50),
    email VARCHAR(150),

    address TEXT,
    city VARCHAR(100),

    is_active BOOLEAN DEFAULT TRUE,

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

    INDEX idx_name (name)
);
ALTER TABLE items
ADD COLUMN supplier_id BIGINT NULL AFTER category_id;

ALTER TABLE items
ADD CONSTRAINT fk_items_supplier
FOREIGN KEY (supplier_id)
REFERENCES suppliers(id);


INSERT INTO suppliers (code, name)
SELECT DISTINCT
    LEFT(
        UPPER(
            REGEXP_REPLACE(
                REPLACE(TRIM(pemasok_utama), ' ', '_'),
                '[^A-Z0-9_]',
                ''
            )
        ),
        50
    ) AS code,

    TRIM(pemasok_utama) AS name

FROM staging_inventory_raw
WHERE pemasok_utama IS NOT NULL
AND TRIM(pemasok_utama) <> '';



UPDATE items i
JOIN staging_inventory_raw s
    ON s.kode_barang = i.sku
JOIN suppliers sp
    ON TRIM(sp.name) = TRIM(s.pemasok_utama)
SET i.supplier_id = sp.id
WHERE s.pemasok_utama IS NOT NULL
AND TRIM(s.pemasok_utama) <> '';



-- ==========================================
-- UPGRADE TABEL users YANG SUDAH ADA
-- Tambah kolom yang kurang saja
-- ==========================================

ALTER TABLE users
ADD COLUMN google_id VARCHAR(255) NULL UNIQUE AFTER id,
ADD COLUMN avatar TEXT NULL AFTER name,
ADD COLUMN last_login_at TIMESTAMP NULL AFTER is_active,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
    AFTER created_at;

-- ==========================================
-- OPTIONAL:
-- kalau login hanya Google, password boleh nullable
-- ==========================================

ALTER TABLE users
MODIFY COLUMN password VARCHAR(255) NULL;

-- ==========================================
-- OPTIONAL INDEX tambahan
-- ==========================================

CREATE INDEX idx_users_google_id ON users(google_id);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_active ON users(is_active);