/*
AIWK Wallet MVP Install SQL
- DB: 기존 yy_db_conn.php의 DB 연결 사용
- Engine: InnoDB 필수. 잔액 변경은 트랜잭션과 SELECT ... FOR UPDATE로 처리한다.
- 금액: BIGINT 정수 unit. float 금지.
*/

CREATE TABLE IF NOT EXISTS `aiwk_wallet_account` (
  `account_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('ACTIVE','BLOCKED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_wallet_balance` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `currency` VARCHAR(20) NOT NULL DEFAULT 'AIWK_CREDIT',
  `bucket` ENUM('PAID','BONUS','LOCKED') NOT NULL DEFAULT 'PAID',
  `balance` BIGINT NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `currency`, `bucket`),
  KEY `ix_currency_bucket` (`currency`, `bucket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_wallet_ledger` (
  `ledger_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tx_id` CHAR(36) NOT NULL,
  `tx_type` VARCHAR(30) NOT NULL,
  `from_user_id` BIGINT UNSIGNED DEFAULT NULL,
  `to_user_id` BIGINT UNSIGNED DEFAULT NULL,
  `amount` BIGINT NOT NULL DEFAULT 0,
  `fee_amount` BIGINT NOT NULL DEFAULT 0,
  `currency` VARCHAR(20) NOT NULL DEFAULT 'AIWK_CREDIT',
  `from_bucket` VARCHAR(20) DEFAULT NULL,
  `to_bucket` VARCHAR(20) DEFAULT NULL,
  `status` ENUM('PENDING','DONE','CANCELLED','FAILED') NOT NULL DEFAULT 'DONE',
  `memo` VARCHAR(255) DEFAULT NULL,
  `ref_type` VARCHAR(50) DEFAULT NULL,
  `ref_id` BIGINT UNSIGNED DEFAULT NULL,
  `ip_addr` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ledger_id`),
  UNIQUE KEY `uk_tx_id` (`tx_id`),
  KEY `ix_from_user` (`from_user_id`, `created_at`),
  KEY `ix_to_user` (`to_user_id`, `created_at`),
  KEY `ix_ref` (`ref_type`, `ref_id`),
  KEY `ix_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_payment_order` (
  `order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `order_no` VARCHAR(50) NOT NULL,
  `pay_amount_krw` INT NOT NULL DEFAULT 0,
  `credit_amount` BIGINT NOT NULL DEFAULT 0,
  `pay_provider` VARCHAR(30) DEFAULT NULL,
  `provider_tx_id` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('READY','PAID','FAILED','CANCELLED','REFUNDED') NOT NULL DEFAULT 'READY',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `ix_user_id` (`user_id`),
  KEY `ix_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_coupon` (
  `coupon_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `coupon_code` VARCHAR(50) NOT NULL,
  `coupon_type` ENUM('FIXED_CREDIT','PERCENT','FREE_JOB') NOT NULL,
  `value` BIGINT NOT NULL DEFAULT 0,
  `max_discount` BIGINT DEFAULT NULL,
  `valid_from` DATETIME DEFAULT NULL,
  `valid_until` DATETIME DEFAULT NULL,
  `transferable` TINYINT NOT NULL DEFAULT 0,
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`coupon_id`),
  UNIQUE KEY `uk_coupon_code` (`coupon_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_user_coupon` (
  `user_coupon_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `coupon_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('ISSUED','USED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'ISSUED',
  `issued_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `used_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`user_coupon_id`),
  KEY `ix_user_status` (`user_id`, `status`),
  KEY `ix_coupon_id` (`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `aiwk_p2p_job` (
  `job_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `requester_user_id` BIGINT UNSIGNED NOT NULL,
  `worker_user_id` BIGINT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `price_credit` BIGINT NOT NULL DEFAULT 0,
  `fee_credit` BIGINT NOT NULL DEFAULT 0,
  `status` ENUM('OPEN','LOCKED','WORKING','SUBMITTED','DONE','CANCELLED','DISPUTE') NOT NULL DEFAULT 'OPEN',
  `submit_memo` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`job_id`),
  KEY `ix_requester` (`requester_user_id`, `status`),
  KEY `ix_worker` (`worker_user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
