-- =====================================================
-- DroidTester Publisher Panel — Schema
-- Database: panelexploreapps_droidtester (shared with Android)
-- Version : 1.0
-- Run order: top to bottom
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------
-- 1. publisher_user — primary publisher accounts
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_user`;
CREATE TABLE `publisher_user` (
  `id`             INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `uuid`           CHAR(36) NOT NULL UNIQUE,
  `name`           VARCHAR(120) NOT NULL,
  `email`          VARCHAR(190) NOT NULL UNIQUE,
  `phone`          VARCHAR(20) DEFAULT NULL,
  `country_code`   VARCHAR(5) NOT NULL DEFAULT '+91',
  `country`        VARCHAR(80) NOT NULL DEFAULT 'India',
  `password_hash`  VARCHAR(255) NOT NULL,
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `phone_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `profile_pic`    VARCHAR(255) DEFAULT NULL,
  `company_name`   VARCHAR(150) DEFAULT NULL,
  `credits`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status`         TINYINT(1) NOT NULL DEFAULT 1
                   COMMENT '1=active, 0=blocked, 2=deleted',
  `last_login_at`  DATETIME DEFAULT NULL,
  `last_login_ip`  VARCHAR(45) DEFAULT NULL,
  `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- 2. publisher_sessions — DB-backed web sessions
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_sessions`;
CREATE TABLE `publisher_sessions` (
  `id`             INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `publisher_id`   INT(11) UNSIGNED NOT NULL,
  `session_token`  VARCHAR(128) NOT NULL UNIQUE,
  `csrf_token`     VARCHAR(128) NOT NULL,
  `ip_address`     VARCHAR(45) NOT NULL,
  `user_agent`     VARCHAR(255) DEFAULT NULL,
  `expires_at`     DATETIME NOT NULL,
  `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_token` (`session_token`),
  INDEX `idx_publisher` (`publisher_id`),
  INDEX `idx_expires` (`expires_at`),
  CONSTRAINT `fk_sess_pub`
    FOREIGN KEY (`publisher_id`) REFERENCES `publisher_user`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- 3. publisher_otp — OTPs for signup / login / phone
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_otp`;
CREATE TABLE `publisher_otp` (
  `id`              INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `identifier`      VARCHAR(190) NOT NULL,
  `identifier_type` ENUM('email','phone') NOT NULL,
  `purpose`         ENUM('signup','forget','login','phone_verify') NOT NULL,
  `otp_hash`        VARCHAR(255) NOT NULL,
  `attempts`        TINYINT(1) NOT NULL DEFAULT 0,
  `expires_at`      INT(11) NOT NULL,
  `verified`        TINYINT(1) NOT NULL DEFAULT 0,
  `payload`         JSON DEFAULT NULL
                    COMMENT 'temp data: signup name, password hash, etc.',
  `created_at`      INT(11) NOT NULL,
  INDEX `idx_identifier` (`identifier`, `purpose`),
  INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- 4. publisher_password_reset — forget-password tokens
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_password_reset`;
CREATE TABLE `publisher_password_reset` (
  `id`           INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `publisher_id` INT(11) UNSIGNED NOT NULL,
  `token_hash`   VARCHAR(255) NOT NULL,
  `used`         TINYINT(1) NOT NULL DEFAULT 0,
  `expires_at`   DATETIME NOT NULL,
  `ip_address`   VARCHAR(45) DEFAULT NULL,
  `created_at`   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_token` (`token_hash`),
  INDEX `idx_publisher` (`publisher_id`),
  CONSTRAINT `fk_reset_pub`
    FOREIGN KEY (`publisher_id`) REFERENCES `publisher_user`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- 5. publisher_payment — every payment attempt + result
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_payment`;
CREATE TABLE `publisher_payment` (
  `id`                       INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`                 VARCHAR(64) NOT NULL UNIQUE
                             COMMENT 'internal: DT-ORD-YYYYMMDD-XXXXXX',
  `publisher_id`             INT(11) UNSIGNED NOT NULL,
  `pub_purchase_history_id`  INT(11) UNSIGNED DEFAULT NULL,
  `gateway`                  ENUM('cashfree','razorpay','phonepe','stripe','paypal') NOT NULL,
  `gateway_order_id`         VARCHAR(128) DEFAULT NULL,
  `gateway_payment_id`       VARCHAR(128) DEFAULT NULL,
  `gateway_signature`        VARCHAR(255) DEFAULT NULL,
  `currency`                 CHAR(3) NOT NULL DEFAULT 'INR',
  `subtotal`                 DECIMAL(12,2) NOT NULL,
  `gst_amount`               DECIMAL(12,2) NOT NULL DEFAULT 0,
  `platform_fee`             DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_amount`             DECIMAL(12,2) NOT NULL,
  `plan_name`                VARCHAR(50) NOT NULL,
  `status`                   ENUM('pending','success','failed','refunded','cancelled')
                             NOT NULL DEFAULT 'pending',
  `failure_reason`           VARCHAR(255) DEFAULT NULL,
  `ip_address`               VARCHAR(45) DEFAULT NULL,
  `webhook_received_at`      DATETIME DEFAULT NULL,
  `raw_response`             JSON DEFAULT NULL,
  `created_at`               DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at`               DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_publisher` (`publisher_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_gw_order` (`gateway_order_id`),
  INDEX `idx_gw_payment` (`gateway_payment_id`),
  CONSTRAINT `fk_pay_pub`
    FOREIGN KEY (`publisher_id`) REFERENCES `publisher_user`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- 6. publisher_audit_log — security + change audit trail
-- -----------------------------------------------------
DROP TABLE IF EXISTS `publisher_audit_log`;
CREATE TABLE `publisher_audit_log` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `publisher_id` INT(11) UNSIGNED DEFAULT NULL,
  `action`       VARCHAR(80) NOT NULL,
  `entity_type`  VARCHAR(50) DEFAULT NULL,
  `entity_id`    INT(11) UNSIGNED DEFAULT NULL,
  `ip_address`   VARCHAR(45) DEFAULT NULL,
  `user_agent`   VARCHAR(255) DEFAULT NULL,
  `meta`         JSON DEFAULT NULL,
  `created_at`   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_publisher` (`publisher_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- 7. ALTERS to existing tables (Android backend tables)
-- =====================================================
-- IMPORTANT: run only if these columns don't already exist.
-- Wrap in your migration tool's "if not exists" logic if needed.

-- pub_purchase_history — link to publisher and payment
ALTER TABLE `pub_purchase_history`
  ADD COLUMN `publisher_id`   INT(11) UNSIGNED DEFAULT NULL AFTER `id`,
  ADD COLUMN `payment_id`     INT(11) UNSIGNED DEFAULT NULL AFTER `publisher_id`,
  ADD COLUMN `app_icon_path`  VARCHAR(255) DEFAULT NULL AFTER `app_logo`,
  ADD INDEX `idx_publisher`   (`publisher_id`),
  ADD INDEX `idx_payment`     (`payment_id`);

-- tester_pkg_plans — multi-currency pricing + min testers to start
ALTER TABLE `tester_pkg_plans`
  ADD COLUMN `price_inr`             DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN `price_usd`             DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN `min_testers_to_start`  INT(11) DEFAULT 1,
  ADD COLUMN `is_active`             TINYINT(1) DEFAULT 1;


-- =====================================================
-- 8. Seed data — default plans (adjust as needed)
-- =====================================================
-- (assumes plan_name + total_users + plan_testing_days + coin_per_day exist already)
UPDATE `tester_pkg_plans` SET price_inr =    9.00, price_usd =  0.20, min_testers_to_start = 20 WHERE plan_name = 'Silver';
UPDATE `tester_pkg_plans` SET price_inr = 1499.00, price_usd = 18.00, min_testers_to_start = 40 WHERE plan_name = 'Gold';
UPDATE `tester_pkg_plans` SET price_inr = 2199.00, price_usd = 26.00, min_testers_to_start = 60 WHERE plan_name = 'Platinum';

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- DONE
-- =====================================================
