-- =========================================================
-- KING LIQUOR TELEGRAM BOT — SCHEMA DATABASE (versi menu penuh)
-- Import file ini lewat phpMyAdmin di cPanel Anda.
-- Kalau Anda SUDAH pernah import versi lama, JANGAN pakai file ini —
-- pakai migration_v2.sql biar data lama tidak hilang.
-- =========================================================

CREATE TABLE IF NOT EXISTS produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(30) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    sku VARCHAR(10) DEFAULT NULL,
    harga_modal DECIMAL(12,2) NOT NULL,
    ongkir_per_botol DECIMAL(12,2) NOT NULL DEFAULT 5417,
    harga_eceran DECIMAL(12,2) NOT NULL,
    harga_grosir DECIMAL(12,2) DEFAULT NULL,
    grosir_min_qty INT NOT NULL DEFAULT 6,
    stok INT NOT NULL DEFAULT 0,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    chat_id BIGINT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    role ENUM('owner','staff') NOT NULL DEFAULT 'staff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transaksi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    produk_id INT NOT NULL,
    jumlah INT NOT NULL,
    tipe_harga ENUM('eceran','grosir','custom') NOT NULL DEFAULT 'eceran',
    harga_satuan DECIMAL(12,2) NOT NULL,
    ongkir_ke_pelanggan DECIMAL(12,2) NOT NULL DEFAULT 0,
    diskon DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_modal DECIMAL(12,2) NOT NULL,
    total_jual DECIMAL(12,2) NOT NULL,
    margin DECIMAL(12,2) NOT NULL,
    status_bayar ENUM('lunas','bon') NOT NULL DEFAULT 'lunas',
    nama_pengutang VARCHAR(100) DEFAULT NULL,
    tanggal_lunas TIMESTAMP NULL DEFAULT NULL,
    status ENUM('aktif','batal') NOT NULL DEFAULT 'aktif',
    dicatat_oleh_chat_id BIGINT NOT NULL,
    waktu TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (produk_id) REFERENCES produk(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pembelian (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supplier VARCHAR(100) NOT NULL,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    status_bayar ENUM('lunas','belum') NOT NULL DEFAULT 'belum',
    status_kirim ENUM('dipesan','dikirim','sampai') NOT NULL DEFAULT 'dipesan',
    tanggal_order TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tanggal_sampai TIMESTAMP NULL DEFAULT NULL,
    tanggal_bayar TIMESTAMP NULL DEFAULT NULL,
    dicatat_oleh_chat_id BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pembelian_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pembelian_id INT NOT NULL,
    produk_id INT NOT NULL,
    jumlah INT NOT NULL,
    harga_modal DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (pembelian_id) REFERENCES pembelian(id),
    FOREIGN KEY (produk_id) REFERENCES produk(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pengeluaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    deskripsi VARCHAR(255) NOT NULL,
    jumlah DECIMAL(12,2) NOT NULL,
    tanggal DATE NOT NULL,
    dicatat_oleh_chat_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_state (
    chat_id BIGINT PRIMARY KEY,
    step VARCHAR(40) NOT NULL,
    data_json TEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;