-- ResellBD Step 25: seller commission policy, verified seller reviews and restrictions.
-- Apply ONLY to the NEW ResellBD database after migrations 001-016.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS seller_commission_rules (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 seller_user_id BIGINT UNSIGNED NULL COMMENT 'NULL means global default rule',
 category_id BIGINT UNSIGNED NULL,
 rule_name VARCHAR(190) NOT NULL,
 rate_bps SMALLINT UNSIGNED NOT NULL DEFAULT 1000 COMMENT '100 bps = 1%; 10000 bps = 100%',
 fixed_fee_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 min_commission_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 max_commission_paisa BIGINT UNSIGNED NULL,
 active_from DATETIME NULL,
 active_until DATETIME NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED 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_seller_commission_active(seller_user_id,is_active,active_from,active_until),
 CONSTRAINT fk_seller_commission_user FOREIGN KEY(seller_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_commission_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT chk_seller_commission_bps CHECK(rate_bps <= 10000),
 CONSTRAINT chk_seller_commission_range CHECK(max_commission_paisa IS NULL OR max_commission_paisa >= min_commission_paisa),
 CONSTRAINT chk_seller_commission_dates CHECK(active_until IS NULL OR active_from IS NULL OR active_until >= active_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS seller_reviews (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 seller_user_id BIGINT UNSIGNED NOT NULL,
 customer_user_id BIGINT UNSIGNED NOT NULL,
 parent_order_id BIGINT UNSIGNED NOT NULL,
 suborder_id BIGINT UNSIGNED NOT NULL,
 rating TINYINT UNSIGNED NOT NULL,
 review_text VARCHAR(2000) NULL,
 status ENUM('pending','published','rejected') NOT NULL DEFAULT 'pending',
 moderated_by BIGINT UNSIGNED NULL,
 moderated_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), UNIQUE KEY uq_seller_review_order_customer(suborder_id,customer_user_id),
 KEY idx_seller_reviews_public(seller_user_id,status,created_at),
 CONSTRAINT fk_seller_review_seller FOREIGN KEY(seller_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_review_customer FOREIGN KEY(customer_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_review_order FOREIGN KEY(parent_order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_review_suborder FOREIGN KEY(suborder_id) REFERENCES seller_suborders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_review_moderator FOREIGN KEY(moderated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT chk_seller_review_rating CHECK(rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS seller_restrictions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 seller_user_id BIGINT UNSIGNED NOT NULL,
 restriction_type ENUM('warning','limited','suspended') NOT NULL,
 reason VARCHAR(1000) NOT NULL,
 starts_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 ends_at DATETIME NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NULL,
 lifted_by BIGINT UNSIGNED NULL,
 lifted_at DATETIME NULL,
 lift_note VARCHAR(1000) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_seller_restriction_active(seller_user_id,is_active,ends_at),
 CONSTRAINT fk_seller_restriction_user FOREIGN KEY(seller_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_restriction_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT fk_seller_restriction_lifter FOREIGN KEY(lifted_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT chk_seller_restriction_dates CHECK(ends_at IS NULL OR ends_at >= starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;