-- ResellBD Step 9: cart, COD checkout and atomic stock reservations.
-- Apply only to the NEW ResellBD database, after migrations 001 and 002.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS carts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id BIGINT UNSIGNED NULL,
 session_token CHAR(64) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'BDT',
 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_carts_session(session_token), KEY idx_carts_user(user_id),
 CONSTRAINT fk_carts_user FOREIGN KEY(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 cart_items (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 cart_id BIGINT UNSIGNED NOT NULL,
 variant_id BIGINT UNSIGNED NOT NULL,
 quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
 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_cart_variant(cart_id,variant_id), KEY idx_cart_items_variant(variant_id),
 CONSTRAINT fk_cart_items_cart FOREIGN KEY(cart_id) REFERENCES carts(id) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT fk_cart_items_variant FOREIGN KEY(variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT chk_cart_quantity CHECK(quantity BETWEEN 1 AND 99)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS orders (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_number VARCHAR(32) NOT NULL,
 idempotency_key CHAR(64) NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 customer_name VARCHAR(160) NOT NULL,
 customer_phone VARCHAR(32) NOT NULL,
 customer_email VARCHAR(190) NULL,
 delivery_division VARCHAR(100) NULL,
 delivery_district VARCHAR(100) NOT NULL,
 delivery_area VARCHAR(160) NOT NULL,
 delivery_address VARCHAR(500) NOT NULL,
 delivery_postcode VARCHAR(20) NULL,
 customer_note VARCHAR(1000) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'BDT',
 subtotal_paisa BIGINT UNSIGNED NOT NULL,
 delivery_fee_paisa BIGINT UNSIGNED NOT NULL,
 discount_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 total_paisa BIGINT UNSIGNED NOT NULL,
 payment_method ENUM('cod') NOT NULL DEFAULT 'cod',
 payment_status ENUM('unpaid','paid','refunded','partially_refunded') NOT NULL DEFAULT 'unpaid',
 status ENUM('pending','confirmed','processing','shipped','delivered','cancelled','returned') NOT NULL DEFAULT 'pending',
 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_orders_number(order_number), UNIQUE KEY uq_orders_idempotency(idempotency_key),
 KEY idx_orders_user_created(user_id,created_at), KEY idx_orders_status_created(status,created_at), KEY idx_orders_phone(customer_phone),
 CONSTRAINT fk_orders_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT chk_order_total CHECK(total_paisa = subtotal_paisa + delivery_fee_paisa - discount_paisa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS order_items (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 variant_id BIGINT UNSIGNED NOT NULL,
 product_name_snapshot VARCHAR(240) NOT NULL,
 variant_title_snapshot VARCHAR(190) NOT NULL,
 sku_snapshot VARCHAR(100) NOT NULL,
 unit_price_paisa BIGINT UNSIGNED NOT NULL,
 quantity SMALLINT UNSIGNED NOT NULL,
 line_total_paisa BIGINT UNSIGNED NOT NULL,
 image_path_snapshot VARCHAR(500) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_order_items_order(order_id), KEY idx_order_items_product(product_id),
 CONSTRAINT fk_order_items_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_order_items_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_order_items_variant FOREIGN KEY(variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT chk_order_item_line CHECK(line_total_paisa = unit_price_paisa * quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS stock_reservations (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 variant_id BIGINT UNSIGNED NOT NULL,
 inventory_id BIGINT UNSIGNED NOT NULL,
 quantity SMALLINT UNSIGNED NOT NULL,
 status ENUM('reserved','released','consumed') NOT NULL DEFAULT 'reserved',
 expires_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_reservation_order_variant(order_id,variant_id), KEY idx_reservation_status_expiry(status,expires_at),
 CONSTRAINT fk_reservation_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_reservation_variant FOREIGN KEY(variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_reservation_inventory FOREIGN KEY(inventory_id) REFERENCES inventory(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO settings(setting_key,setting_value,value_type,is_public,description)
VALUES ('guest_checkout_enabled', JSON_EXTRACT('true','$'), 'boolean', 1, 'Allow guest checkout'),
       ('delivery_fee_dhaka_paisa', JSON_EXTRACT('6000','$'), 'number', 1, 'Default delivery fee inside Dhaka in paisa'),
       ('delivery_fee_outside_dhaka_paisa', JSON_EXTRACT('12000','$'), 'number', 1, 'Default delivery fee outside Dhaka in paisa')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);