-- ResellBD Step 11: manual payment recording and admin verification. NEW database only.
SET NAMES utf8mb4;
ALTER TABLE orders MODIFY payment_method ENUM('cod','manual_bkash','manual_nagad','manual_rocket','bank_transfer','cash') NOT NULL DEFAULT 'cod';
CREATE TABLE IF NOT EXISTS manual_payment_records (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 payment_method ENUM('bkash','nagad','rocket','bank_transfer','cash') NOT NULL,
 amount_paisa BIGINT UNSIGNED NOT NULL,
 transaction_id VARCHAR(120) NULL,
 sender_account VARCHAR(40) NULL,
 admin_note VARCHAR(1000) NULL,
 verification_status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
 recorded_by BIGINT UNSIGNED NULL,
 verified_by BIGINT UNSIGNED NULL,
 verified_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_manual_payment_order(order_id,created_at), KEY idx_manual_payment_status(verification_status,created_at),
 UNIQUE KEY uq_manual_payment_method_txn(payment_method,transaction_id),
 CONSTRAINT fk_manual_payment_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_manual_payment_recorder FOREIGN KEY(recorded_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT fk_manual_payment_verifier FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS payment_audit_log (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 payment_record_id BIGINT UNSIGNED NOT NULL,
 from_status ENUM('pending','verified','rejected') NULL,
 to_status ENUM('pending','verified','rejected') NOT NULL,
 note VARCHAR(1000) NULL,
 actor_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_payment_audit_record(payment_record_id,created_at),
 CONSTRAINT fk_payment_audit_record FOREIGN KEY(payment_record_id) REFERENCES manual_payment_records(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_payment_audit_actor FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;