-- MoneyTime full schema
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_id` VARCHAR(40) UNIQUE NOT NULL,
  `first_name` VARCHAR(100),
  `last_name` VARCHAR(100),
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `phone` VARCHAR(40),
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('customer','admin') DEFAULT 'customer',
  `status` ENUM('active','suspended') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_number` VARCHAR(50) UNIQUE NOT NULL,
  `company` VARCHAR(200),
  `contact_name` VARCHAR(200),
  `email` VARCHAR(150),
  `phone` VARCHAR(50),
  `address` VARCHAR(255),
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `package_id` VARCHAR(60) UNIQUE NOT NULL,
  `client_id` INT NOT NULL,
  `tracking_number` VARCHAR(120),
  `weight` DECIMAL(10,2) DEFAULT 0,
  `length` DECIMAL(10,2) DEFAULT 0,
  `width` DECIMAL(10,2) DEFAULT 0,
  `height` DECIMAL(10,2) DEFAULT 0,
  `declared_value` DECIMAL(10,2) DEFAULT 0,
  `shipping_type` ENUM('regular','express','super') DEFAULT 'regular',
  `status` ENUM('pending','received','ready','consolidated','shipped','delivered') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `shipments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `shipment_id` VARCHAR(60) UNIQUE NOT NULL,
  `client_id` INT NOT NULL,
  `address` VARCHAR(255),
  `total_weight` DECIMAL(10,2) DEFAULT 0,
  `shipping_cost` DECIMAL(10,2) DEFAULT 0,
  `tax_amount` DECIMAL(10,2) DEFAULT 0,
  `total_cost` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('pending','approved','shipped','delivered','cancelled') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) UNIQUE NOT NULL,
  `setting_value` TEXT,
  `setting_type` ENUM('text','number','boolean','json') DEFAULT 'text',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_id` INT,
  `type` VARCHAR(50),
  `amount` DECIMAL(12,2),
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`,`setting_value`,`setting_type`) VALUES
('shipping_regular_price','45','number'),
('shipping_express_price','75','number'),
('shipping_super_price','120','number'),
('handling_fee','25','number'),
('vat_rate','17','number'),
('free_storage_days','60','number'),
('storage_fee_per_day','5','number')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);