-- ResellBD Step 13: manual returns/refunds/support and verified-purchase reviews. Apply ONLY to the NEW ResellBD database after migrations 001-006.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS return_requests (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, order_item_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, reason VARCHAR(1000) NOT NULL, requested_resolution ENUM('refund','exchange','replacement') NOT NULL DEFAULT 'refund', status ENUM('pending','approved','rejected','received','resolved') NOT NULL DEFAULT 'pending', admin_note VARCHAR(1000) NULL, refund_amount_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0, reviewed_by BIGINT UNSIGNED NULL, reviewed_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_returns_status_date(status,created_at), KEY idx_returns_order(order_id), CONSTRAINT fk_returns_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_returns_item FOREIGN KEY(order_item_id) REFERENCES order_items(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_returns_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_returns_reviewer FOREIGN KEY(reviewed_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 support_tickets (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ticket_number VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NULL, order_id BIGINT UNSIGNED NULL, subject VARCHAR(190) NOT NULL, category ENUM('order','payment','delivery','return','product','account','other') NOT NULL DEFAULT 'other', priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal', status ENUM('open','in_progress','waiting_customer','resolved','closed') NOT NULL DEFAULT 'open', 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_ticket_number(ticket_number), KEY idx_ticket_status_date(status,created_at), KEY idx_ticket_user(user_id), CONSTRAINT fk_ticket_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_ticket_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS support_messages (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ticket_id BIGINT UNSIGNED NOT NULL, sender_user_id BIGINT UNSIGNED NULL, sender_type ENUM('customer','staff','system') NOT NULL DEFAULT 'customer', message TEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id), KEY idx_support_messages_ticket(ticket_id,created_at), CONSTRAINT fk_support_message_ticket FOREIGN KEY(ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_support_message_user FOREIGN KEY(sender_user_id) 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 product_reviews (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, product_id BIGINT UNSIGNED NOT NULL, order_item_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, rating TINYINT UNSIGNED NOT NULL, title VARCHAR(190) NULL, review_text TEXT 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_review_order_item_user(order_item_id,user_id), KEY idx_reviews_product_status(product_id,status), CONSTRAINT fk_review_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_review_item FOREIGN KEY(order_item_id) REFERENCES order_items(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_review_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_review_moderator FOREIGN KEY(moderated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT chk_review_rating CHECK(rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;