CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `balance` DECIMAL(12, 2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `services` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `provider_service_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `min` INT NOT NULL,
  `max` INT NOT NULL,
  `status` ENUM('Active', 'Inactive') DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  `target` VARCHAR(255) NOT NULL,
  `quantity` INT NOT NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `provider_order_id` INT DEFAULT NULL,
  `status` ENUM('Pending', 'Processing', 'Completed', 'Partial', 'Error', 'Canceled') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `deposits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `reference_id` VARCHAR(100) NOT NULL UNIQUE,
  `gateway_trx_id` VARCHAR(100) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(12, 2) NOT NULL,
  `status` ENUM('Pending', 'Success', 'Failed', 'Expired') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `settings` (
  `option_name` VARCHAR(50) PRIMARY KEY,
  `option_value` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Data Awal
INSERT INTO `users` (`username`, `balance`) VALUES ('demouser', 50000.00);

INSERT INTO `settings` (`option_name`, `option_value`) VALUES
('markup_percentage', '10'),
('markup_fixed', '500')
ON DUPLICATE KEY UPDATE `option_name`=`option_name`;
