-- Database untuk BukaOlshop Admin System
-- Create database jika belum ada
CREATE DATABASE IF NOT EXISTS bukaolshop_admin;
USE bukaolshop_admin;

-- Tabel Products Local Cache
CREATE TABLE IF NOT EXISTS products (
    id_barang VARCHAR(50) PRIMARY KEY,
    id_kategori VARCHAR(50) NOT NULL,
    nama_barang VARCHAR(255) NOT NULL,
    deskripsi_singkat TEXT,
    deskripsi_panjang TEXT,
    harga_barang INT NOT NULL,
    harga_barang_asli INT,
    harga_modal INT,
    stok_barang INT NOT NULL,
    berat INT,
    jenis_barang VARCHAR(50),
    kondisi_barang VARCHAR(50),
    merek VARCHAR(100),
    sku VARCHAR(100),
    url_gambar_barang VARCHAR(500),
    tanggal DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    synced_at DATETIME NULL,
    admin_id INT NOT NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_kategori (id_kategori),
    INDEX idx_nama (nama_barang)
);

-- Tabel Upload Queue
CREATE TABLE IF NOT EXISTS upload_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_antrian VARCHAR(100) UNIQUE NOT NULL,
    id_kategori VARCHAR(50) NOT NULL,
    id_lokasi VARCHAR(50) NOT NULL,
    nama_barang VARCHAR(255) NOT NULL,
    harga_barang INT NOT NULL,
    stok_barang INT NOT NULL,
    jenis_barang VARCHAR(50),
    url_gambar_barang_1 VARCHAR(500),
    status VARCHAR(50) DEFAULT 'pending',
    response_data JSON,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    admin_id INT NOT NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_status (status),
    INDEX idx_uploaded_at (uploaded_at)
);

-- Tabel Kategori Cache
CREATE TABLE IF NOT EXISTS kategori (
    id_kategori VARCHAR(50) PRIMARY KEY,
    nama_kategori VARCHAR(255) NOT NULL,
    admin_id INT NOT NULL,
    synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id)
);

-- Tabel Lokasi Cache
CREATE TABLE IF NOT EXISTS lokasi (
    id_lokasi VARCHAR(50) PRIMARY KEY,
    nama_pengirim VARCHAR(255) NOT NULL,
    tipe_lokasi VARCHAR(50),
    admin_id INT NOT NULL,
    synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id)
);

-- Tabel API Response Cache
CREATE TABLE IF NOT EXISTS api_cache (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    cache_key VARCHAR(255) NOT NULL,
    cache_value LONGTEXT NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cache (admin_id, cache_key),
    INDEX idx_expires_at (expires_at)
);

-- Tabel Activity Log
CREATE TABLE IF NOT EXISTS activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    action VARCHAR(100),
    description TEXT,
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_created_at (created_at)
);
