-- Database Schema for Proxnum Bot V2
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `chat_id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `balance` decimal(15,2) DEFAULT 0.00,
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_blocked` tinyint(1) DEFAULT 0,
  `invited_by` bigint(20) DEFAULT NULL,
  `referral_earnings` decimal(15,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chat_id` (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `proxnum_id` varchar(255) DEFAULT NULL,
  `proxnum_activation_id` varchar(255) DEFAULT NULL,
  `service_code` varchar(50) NOT NULL,
  `country_code` int(11) NOT NULL,
  `phone_number` varchar(50) DEFAULT NULL,
  `otp_code` varchar(50) DEFAULT NULL,
  `price_usd` decimal(10,4) NOT NULL,
  `price_irr` decimal(15,2) NOT NULL,
  `status` enum('pending','completed','canceled','refunded','expired') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(15,2) NOT NULL, 
  `type` enum('deposit','purchase','refund') NOT NULL,
  `description` text DEFAULT NULL,
  `authority` varchar(255) DEFAULT NULL,
  `status` enum('pending','success','failed') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key_name` varchar(50) NOT NULL,
  `value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`key_name`, `value`) VALUES
('profit_margin_percent', '20'),
('referral_percent', '5'),
('usd_price_buffer', '1000'),
('channel_id', '@your_channel_username'),
('admin_chat_id', '8215217430');

COMMIT;
