-- ============================================================
-- AMCoin Wallet & Store v3 — Complete Schema
-- يُضاف على: segmghlr_pms_db
-- ============================================================

CREATE TABLE IF NOT EXISTS `amcoin_wallets` (
  `id`               int(11) NOT NULL AUTO_INCREMENT,
  `username`         varchar(80)  NOT NULL UNIQUE,
  `email`            varchar(120) NOT NULL UNIQUE,
  `password_hash`    varchar(255) NOT NULL,
  `full_name`        varchar(120) NOT NULL,
  `phone`            varchar(25)  DEFAULT NULL,
  `bio`              varchar(300) DEFAULT NULL,
  `avatar_color`     varchar(7)   DEFAULT '#f5a623',
  `balance`          decimal(18,4) NOT NULL DEFAULT 100.0000,
  `total_earned`     decimal(18,4) DEFAULT 100.0000,
  `total_spent`      decimal(18,4) DEFAULT 0.0000,
  `wallet_address`   varchar(66)  NOT NULL UNIQUE,
  `referral_code`    varchar(12)  NOT NULL UNIQUE,
  `referred_by`      int(11)      DEFAULT NULL,
  `daily_streak`     int(11)      DEFAULT 0,
  `last_login_bonus` date         DEFAULT NULL,
  `is_active`        tinyint(1)   DEFAULT 1,
  `api_token`        varchar(512) DEFAULT NULL,
  `created_at`       timestamp    DEFAULT current_timestamp(),
  `updated_at`       timestamp    DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_transactions` (
  `id`             int(11) NOT NULL AUTO_INCREMENT,
  `wallet_id`      int(11) NOT NULL,
  `type`           enum('credit','debit','refund','bonus','referral','daily','admin','task') NOT NULL,
  `amount`         decimal(18,4) NOT NULL,
  `balance_before` decimal(18,4) NOT NULL,
  `balance_after`  decimal(18,4) NOT NULL,
  `description`    varchar(300) DEFAULT NULL,
  `ref_id`         int(11) DEFAULT NULL,
  `ref_type`       varchar(50) DEFAULT NULL,
  `tx_hash`        varchar(64)  NOT NULL,
  `created_at`     timestamp DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_wallet` (`wallet_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_products` (
  `id`             int(11) NOT NULL AUTO_INCREMENT,
  `name`           varchar(200) NOT NULL,
  `description`    text DEFAULT NULL,
  `category`       varchar(80)  DEFAULT 'عام',
  `emoji`          varchar(10)  DEFAULT '🎁',
  `price_amc`      decimal(18,4) NOT NULL,
  `original_price` decimal(18,4) DEFAULT NULL,
  `stock`          int(11) DEFAULT -1,
  `total_sold`     int(11) DEFAULT 0,
  `is_active`      tinyint(1) DEFAULT 1,
  `is_featured`    tinyint(1) DEFAULT 0,
  `return_allowed` tinyint(1) DEFAULT 1,
  `return_days`    int(11) DEFAULT 3,
  `specs`          text DEFAULT NULL,
  `sort_order`     int(11) DEFAULT 0,
  `created_at`     timestamp DEFAULT current_timestamp(),
  `updated_at`     timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_cart` (
  `id`         int(11) NOT NULL AUTO_INCREMENT,
  `wallet_id`  int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity`   int(11) NOT NULL DEFAULT 1,
  `added_at`   timestamp DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_cart` (`wallet_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_orders` (
  `id`               int(11) NOT NULL AUTO_INCREMENT,
  `order_number`     varchar(30)   NOT NULL UNIQUE,
  `wallet_id`        int(11)       NOT NULL,
  `total_amc`        decimal(18,4) NOT NULL,
  `status`           enum('confirmed','processing','delivered','cancelled','refunded') DEFAULT 'confirmed',
  `delivery_address` text DEFAULT NULL,
  `notes`            text DEFAULT NULL,
  `tx_id`            int(11) DEFAULT NULL,
  `can_return_until` datetime DEFAULT NULL,
  `created_at`       timestamp DEFAULT current_timestamp(),
  `updated_at`       timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_wallet` (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_order_items` (
  `id`            int(11) NOT NULL AUTO_INCREMENT,
  `order_id`      int(11) NOT NULL,
  `product_id`    int(11) NOT NULL,
  `product_name`  varchar(200) NOT NULL,
  `product_emoji` varchar(10)  DEFAULT '🎁',
  `quantity`      int(11) NOT NULL DEFAULT 1,
  `price_amc`     decimal(18,4) NOT NULL,
  `total_amc`     decimal(18,4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amcoin_returns` (
  `id`            int(11) NOT NULL AUTO_INCREMENT,
  `order_id`      int(11) NOT NULL,
  `wallet_id`     int(11) NOT NULL,
  `reason`        text NOT NULL,
  `status`        enum('pending','approved','rejected') DEFAULT 'pending',
  `admin_notes`   text DEFAULT NULL,
  `refund_amount` decimal(18,4) DEFAULT NULL,
  `created_at`    timestamp DEFAULT current_timestamp(),
  `resolved_at`   datetime  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amc_tasks` (
  `id`               int(11) NOT NULL AUTO_INCREMENT,
  `title`            varchar(300) NOT NULL,
  `description`      text DEFAULT NULL,
  `reward_amc`       decimal(18,4) NOT NULL,
  `difficulty`       enum('easy','medium','hard','expert') DEFAULT 'medium',
  `category`         varchar(100) DEFAULT 'عام',
  `pickup_address`   varchar(500) DEFAULT NULL,
  `pickup_lat`       varchar(30)  DEFAULT NULL,
  `pickup_lng`       varchar(30)  DEFAULT NULL,
  `pickup_contact`   varchar(100) DEFAULT NULL,
  `pickup_phone`     varchar(30)  DEFAULT NULL,
  `pickup_items`     text DEFAULT NULL,
  `delivery_address` varchar(500) DEFAULT NULL,
  `delivery_lat`     varchar(30)  DEFAULT NULL,
  `delivery_lng`     varchar(30)  DEFAULT NULL,
  `delivery_contact` varchar(100) DEFAULT NULL,
  `delivery_phone`   varchar(30)  DEFAULT NULL,
  `deadline`         datetime DEFAULT NULL,
  `notes`            text DEFAULT NULL,
  `visible_to`       enum('all','specific') DEFAULT 'all',
  `excluded_wallets` text DEFAULT NULL,
  `status`           enum('open','taken','in_review','completed','cancelled') DEFAULT 'open',
  `max_takers`       int(11) DEFAULT 1,
  `created_at`       timestamp DEFAULT current_timestamp(),
  `updated_at`       timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `amc_task_claims` (
  `id`           int(11) NOT NULL AUTO_INCREMENT,
  `task_id`      int(11) NOT NULL,
  `wallet_id`    int(11) NOT NULL,
  `claimed_at`   timestamp DEFAULT current_timestamp(),
  `submitted_at` datetime DEFAULT NULL,
  `approved_at`  datetime DEFAULT NULL,
  `status`       enum('active','submitted','approved','rejected') DEFAULT 'active',
  `proof_notes`  text DEFAULT NULL,
  `admin_notes`  text DEFAULT NULL,
  `reward_tx_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_claim` (`task_id`,`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample products
INSERT IGNORE INTO `amcoin_products` (`name`,`description`,`category`,`emoji`,`price_amc`,`original_price`,`stock`,`is_featured`,`specs`) VALUES
('لابتوب Dell Inspiron 15','Core i5 | 8GB RAM | 256GB SSD | شاشة 15.6 FHD','لابتوب','💻',100.0,120.0,5,1,'{"المعالج":"Core i5","الذاكرة":"8GB","التخزين":"256GB SSD"}'),
('Samsung Galaxy A55 5G','6.6 بوصة | 8GB | 256GB | كاميرا 50MP','موبايل','📱',80.0,95.0,10,1,'{"الشاشة":"6.6 FHD+","البطارية":"5000mAh"}'),
('سماعة Sony WH-1000XM5','إلغاء ضوضاء | بلوتوث 5.2 | 30 ساعة','إكسسوارات','🎧',40.0,50.0,-1,1,NULL),
('شاحن GaN 100W','شحن 4 أجهزة | USB-C + USB-A','إكسسوارات','⚡',15.0,20.0,-1,0,NULL),
('iPad Air M2','شاشة 11 Liquid Retina | M2 | WiFi 6E','تابلت','📲',120.0,140.0,3,1,NULL);