-- WA Status App — database schema
-- ===========================================================
-- Create database first:
--   CREATE DATABASE wa_status_app
--     CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Then USE wa_status_app and run this file.
-- ===========================================================
-- Schema is intentionally identical to WaSeen's `devices` table
-- (same column names, types, indexes) so server-side logic from
-- WA Tracker can be reused without translation.

CREATE TABLE `devices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(250) DEFAULT NULL,
  `rc_id` varchar(40) DEFAULT NULL,
  `user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `invite_code` varchar(50) DEFAULT NULL,
  `wa_phone` varchar(20) DEFAULT NULL COMMENT 'Connected WhatsApp number',
  `device_token` varchar(255) NOT NULL DEFAULT '',
  `push_token` varchar(255) DEFAULT NULL,
  `platform` enum('ios','android') DEFAULT 'android',
  `country_code` varchar(4) DEFAULT NULL,
  `app_version` varchar(20) DEFAULT NULL,
  `is_apk_connected` tinyint(1) NOT NULL DEFAULT '0',
  `is_premium` tinyint(1) NOT NULL DEFAULT '0',
  `user_type` tinyint DEFAULT '0' COMMENT '0=free, 1=plus, 2=pro_p1, 3=pro_p2',
  `subscription_type` varchar(20) DEFAULT NULL COMMENT 'monthly, yearly, lifetime',
  `subscription_start` bigint DEFAULT '0',
  `subscription_end` bigint DEFAULT '0',
  `has_canceled` tinyint(1) DEFAULT '0',
  `revenuecat_id` varchar(100) DEFAULT NULL,
  `referral_bonus_days` int DEFAULT '0',
  `referral_friends_count` int DEFAULT '0',
  `referred_by` varchar(50) DEFAULT NULL,
  `temp_is_premium` tinyint(1) DEFAULT '0' COMMENT '0=none, 1=active, 2=expired',
  `temp_premium_start` bigint DEFAULT '0',
  `temp_premium_end` bigint DEFAULT '0',
  `referred_price` varchar(10) DEFAULT NULL,
  `last_event_at` bigint DEFAULT '0',
  `last_event_type` varchar(50) DEFAULT NULL,
  `notification_count` int DEFAULT '0',
  `created_at` bigint NOT NULL DEFAULT '0',
  `updated_at` bigint NOT NULL DEFAULT '0',
  `plan_type_cst` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `device_id` (`device_id`),
  UNIQUE KEY `invite_code` (`invite_code`),
  UNIQUE KEY `uniq_devices_rcid` (`rc_id`),
  KEY `wa_phone` (`wa_phone`),
  KEY `is_apk_connected` (`is_apk_connected`),
  KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- Optional referrals log table (for future referral system)
CREATE TABLE `referrals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `referrer_user_id` int NOT NULL,
  `referred_user_id` int NOT NULL,
  `invite_code`      varchar(50) NOT NULL,
  `bonus_days`       int DEFAULT '0',
  `created_at`       bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_referrer` (`referrer_user_id`),
  KEY `idx_referred` (`referred_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
