SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nama VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB;

-- Simpan hasil perhitungan HPP (header)
CREATE TABLE IF NOT EXISTS hpp_calculations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NULL,

  nama_produk VARCHAR(200) NOT NULL,
  jumlah_produksi DECIMAL(20,6) NOT NULL DEFAULT 0,

  -- komponen biaya yang dipakai di Index.html
  bbb DECIMAL(20,2) NOT NULL DEFAULT 0,
  btkl DECIMAL(20,2) NOT NULL DEFAULT 0,

  bop_variabel DECIMAL(20,2) NOT NULL DEFAULT 0,
  bop_tetap DECIMAL(20,2) NOT NULL DEFAULT 0,

  total_biaya DECIMAL(20,2) NOT NULL DEFAULT 0,
  hpp_per_unit DECIMAL(20,6) NOT NULL DEFAULT 0,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (id),
  KEY idx_hpp_user (user_id),
  CONSTRAINT fk_hpp_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Simpan rincian detail biaya (baris-baris) dari rincian.html
-- Struktur: setiap perhitungan dapat memiliki banyak detail untuk bbb/tkl/foh.
CREATE TABLE IF NOT EXISTS hpp_details (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  calculation_id BIGINT UNSIGNED NOT NULL,

  jenis ENUM('BBB','TKL','FOH') NOT NULL,
  nama_detail VARCHAR(200) NOT NULL,
  nominal DECIMAL(20,2) NOT NULL DEFAULT 0,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_detail_calc (calculation_id),

  CONSTRAINT fk_detail_calc
    FOREIGN KEY (calculation_id) REFERENCES hpp_calculations(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

-- View ringkas opsional (tidak wajib)
CREATE OR REPLACE VIEW v_hpp_history AS
SELECT
  c.id AS calculation_id,
  c.created_at,
  c.nama_produk,
  c.jumlah_produksi,
  c.total_biaya,
  c.hpp_per_unit,
  u.email AS user_email
FROM hpp_calculations c
LEFT JOIN users u ON u.id = c.user_id;