-- V4 untuk database yang SUDAH BERJALAN.
ALTER TABLE transactions
  ADD COLUMN subtotal_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER user_id,
  ADD COLUMN discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER subtotal_amount,
  ADD COLUMN discount_note VARCHAR(255) DEFAULT NULL AFTER discount_amount,
  ADD COLUMN offline_user_id INT DEFAULT NULL AFTER client_uuid,
  ADD COLUMN offline_user_name VARCHAR(100) DEFAULT NULL AFTER offline_user_id;
ALTER TABLE transaction_items
  ADD COLUMN cost_price DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER price,
  ADD COLUMN category_name VARCHAR(100) DEFAULT NULL AFTER product_name;
CREATE TABLE IF NOT EXISTS discounts (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,type ENUM('percent','amount') NOT NULL,value DECIMAL(12,2) NOT NULL DEFAULT 0,min_purchase DECIMAL(12,2) NOT NULL DEFAULT 0,starts_at DATETIME DEFAULT NULL,ends_at DATETIME DEFAULT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS inventory_receipts (id INT AUTO_INCREMENT PRIMARY KEY,variant_id INT NOT NULL,qty INT NOT NULL,cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,input_date DATE NOT NULL,note VARCHAR(255) DEFAULT NULL,user_id INT DEFAULT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS expenses (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,expense_date DATE NOT NULL,amount DECIMAL(12,2) NOT NULL DEFAULT 0,note VARCHAR(255) DEFAULT NULL,user_id INT DEFAULT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS store_settings (id TINYINT PRIMARY KEY,store_name VARCHAR(150) DEFAULT 'Kasir Unta Store',address VARCHAR(255) DEFAULT NULL,phone VARCHAR(50) DEFAULT NULL,instagram VARCHAR(100) DEFAULT NULL,tiktok VARCHAR(100) DEFAULT NULL,logo VARCHAR(255) DEFAULT NULL,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
INSERT INTO store_settings (id,store_name) VALUES (1,'Kasir Unta Store') ON DUPLICATE KEY UPDATE id=id;
CREATE INDEX idx_inventory_date ON inventory_receipts(input_date);
CREATE INDEX idx_expenses_date ON expenses(expense_date);