-- Dokumentasi Database Schema - ATK Inventory System
-- Sistem Inventory Alat Tulis Kantor (ATK) Jurusan Teknik Elektro
-- Politeknik Negeri Ambon

-- Table: users (User Management & Authentication)
-- Menyimpan data user dengan role-based access
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,      -- Username untuk login
    email VARCHAR(255) UNIQUE NOT NULL,         -- Email user
    password VARCHAR(255) NOT NULL,             -- Password hash (bcrypt)
    name VARCHAR(255) NOT NULL,                 -- Nama lengkap user
    role ENUM('operator', 'admin') DEFAULT 'operator', -- Role user
    is_active BOOLEAN DEFAULT TRUE,             -- Status aktif/nonaktif
    last_login TIMESTAMP NULL,                  -- Waktu login terakhir
    last_logout TIMESTAMP NULL,                 -- Waktu logout terakhir
    login_duration INT DEFAULT 0,               -- Durasi session dalam detik
    ip_address VARCHAR(45),                     -- IP address saat login
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_last_login (last_login)
);

-- Table: login_history (Tracking History Login)
-- Mencatat setiap aktivitas login/logout
CREATE TABLE login_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    duration_seconds INT DEFAULT 0,             -- Durasi login dalam detik
    ip_address VARCHAR(45),                     -- IP address saat login
    user_agent VARCHAR(500),                    -- Browser/client info
    login_status ENUM('success', 'failed') DEFAULT 'success',
    failure_reason VARCHAR(255),                -- Alasan gagal login (jika ada)
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_login (user_id, login_time),
    INDEX idx_login_time (login_time)
);

-- Table: atk_items (Master ATK Items)
-- Menyimpan data master Alat Tulis Kantor
CREATE TABLE atk_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,          -- Kode unik ATK (ATK-001, ATK-002, dst)
    name VARCHAR(255) NOT NULL,                 -- Nama ATK
    category VARCHAR(100),                      -- Kategori ATK (Pensil, Kertas, Pena, dll)
    description TEXT,                           -- Deskripsi ATK
    unit VARCHAR(50) NOT NULL,                  -- Satuan (pcs, rim, box, dll)
    stock INT DEFAULT 0,                        -- Jumlah stok saat ini
    min_stock INT DEFAULT 10,                   -- Minimum stok untuk alert
    reorder_point INT DEFAULT 20,               -- Titik pemesanan ulang
    average_monthly_usage INT DEFAULT 0,        -- Rata-rata penggunaan per bulan (AI calculated)
    lifespan_days INT DEFAULT 0,                -- Estimasi berapa hari barang akan habis
    last_stockin DATE,                          -- Tanggal stok masuk terakhir
    last_stockout DATE,                         -- Tanggal stok keluar terakhir
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table: products (untuk backward compatibility)
-- Alias/referensi dari atk_items
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    description TEXT,
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 10,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


-- Table: stock_movements
-- Mencatat setiap gerakan stok ATK (masuk, keluar, penyesuaian)
CREATE TABLE stock_movements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    atk_id INT NOT NULL,
    type ENUM('in', 'out', 'adjust', 'damage', 'return') NOT NULL, -- Tipe gerakan
    quantity INT NOT NULL,                      -- Jumlah unit
    reason VARCHAR(100),                        -- Alasan (restock, penggunaan rutin, dll)
    notes VARCHAR(500),                         -- Catatan gerakan
    recorded_by VARCHAR(100),                   -- Siapa yang catat
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(atk_id) REFERENCES atk_items(id)
);

-- Table: atk_usage (Tracking Penggunaan ATK)
-- Mencatat penggunaan ATK sehari-hari
CREATE TABLE atk_usage (
    id INT PRIMARY KEY AUTO_INCREMENT,
    atk_id INT NOT NULL,
    quantity_used INT NOT NULL,                 -- Jumlah yang digunakan
    used_by VARCHAR(100),                       -- Siapa yang menggunakan (nama/NIP)
    purpose VARCHAR(255),                       -- Keperluan penggunaan
    date_used DATE NOT NULL,                    -- Tanggal penggunaan
    time_used TIME,                             -- Jam penggunaan
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(atk_id) REFERENCES atk_items(id),
    INDEX idx_usage_date (date_used)
);

-- Table: stock_in_records
-- Detail pencatatan barang masuk
CREATE TABLE stock_in_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    date_in DATE NOT NULL,
    total_items_in INT,
    supplier_notes VARCHAR(255),
    recorded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date_in (date_in)
);

-- Table: transactions (DEPRECATED - untuk referensi legacy)
-- Mencatat semua transaksi bisnis (tidak lagi digunakan untuk ATK)
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    transaction_type ENUM('purchase', 'sale', 'return', 'damage') NOT NULL,
    notes VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(product_id) REFERENCES products(id)
);

-- Table: ai_predictions
-- Menyimpan hasil prediksi AI untuk setiap ATK
CREATE TABLE ai_predictions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    atk_id INT NOT NULL,
    
    -- Analisis Penggunaan
    monthly_avg_usage DECIMAL(10, 2),           -- Rata-rata penggunaan per bulan
    usage_trend VARCHAR(50),                    -- Trend: increasing, decreasing, stable
    
    -- Analisis Pemakaian
    fast_moving BOOLEAN DEFAULT FALSE,          -- TRUE jika barang cepat habis (high usage)
    slow_moving BOOLEAN DEFAULT FALSE,          -- TRUE jika barang jarang dipakai (low usage)
    
    -- Prediksi Stok
    estimated_days_to_empty INT,                -- Estimasi berapa hari stok akan habis
    recommended_reorder_qty INT,                -- Jumlah rekon pemesanan ulang
    confidence_level DECIMAL(3, 2),             -- Tingkat kepercayaan (0-1)
    
    -- Rekomendasi
    recommendation VARCHAR(255),                -- Rekomendasi sistem
    priority_level VARCHAR(20),                 -- Priority: high, medium, low
    
    -- Data Analisis
    analysis_data JSON,                         -- Data analisis detail (trend history, dll)
    
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY(atk_id) REFERENCES atk_items(id)
);

-- Table: alerts
-- Menyimpan alert/notifikasi sistem
CREATE TABLE alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    atk_id INT NOT NULL,
    alert_type VARCHAR(100),                    -- Tipe alert (LOW_STOCK, FAST_MOVING, SLOW_MOVING, REORDER_NEEDED)
    severity VARCHAR(20) DEFAULT 'medium',     -- Tingkat keparahan: critical, high, medium, low
    message VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(atk_id) REFERENCES atk_items(id),
    INDEX idx_alert_type (alert_type),
    INDEX idx_severity (severity)
);

-- INDEXES untuk performa query
CREATE INDEX idx_atk_code ON atk_items(code);
CREATE INDEX idx_atk_category ON atk_items(category);

-- Table: system_settings (System Configuration)
-- Menyimpan konfigurasi sistem seperti IP whitelist setting
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_name VARCHAR(100) UNIQUE NOT NULL, -- Nama setting (e.g., IP_WHITELIST_ENABLED)
    setting_value VARCHAR(255),                -- Nilai setting
    setting_type VARCHAR(50),                  -- Tipe (boolean, string, integer, json)
    description TEXT,                          -- Deskripsi setting
    updated_by INT,                            -- User yang mengupdate setting
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_setting_name (setting_name)
);

-- Table: ip_whitelist (IP Whitelist for Login Access Control)
-- Menyimpan daftar IP yang diizinkan untuk login
CREATE TABLE ip_whitelist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ip_address VARCHAR(45) UNIQUE NOT NULL,    -- IP address (IPv4 atau IPv6)
    description VARCHAR(255),                  -- Deskripsi IP (e.g., "Office Admin PC")
    is_active BOOLEAN DEFAULT TRUE,            -- Status aktif/nonaktif
    added_by INT,                              -- User ID yang menambahkan IP
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY(added_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_ip_address (ip_address),
    INDEX idx_is_active (is_active)
);
CREATE INDEX idx_atk_stock ON atk_items(stock);

CREATE INDEX idx_movement_atk ON stock_movements(atk_id);
CREATE INDEX idx_movement_type ON stock_movements(type);
CREATE INDEX idx_movement_date ON stock_movements(recorded_at);

CREATE INDEX idx_usage_atk ON atk_usage(atk_id);
CREATE INDEX idx_usage_date ON atk_usage(date_used);
CREATE INDEX idx_usage_user ON atk_usage(used_by);

CREATE INDEX idx_prediction_atk ON ai_predictions(atk_id);
CREATE INDEX idx_prediction_fast_moving ON ai_predictions(fast_moving);
CREATE INDEX idx_prediction_slow_moving ON ai_predictions(slow_moving);

CREATE INDEX idx_alert_atk ON alerts(atk_id);
CREATE INDEX idx_alert_read ON alerts(is_read);