39 lines
1.9 KiB
SQL
39 lines
1.9 KiB
SQL
-- Enterprise Buy/Sell/Payment Module Migration
|
|
|
|
-- Update cars table for reservations
|
|
ALTER TABLE `cars`
|
|
ADD COLUMN `reserved_by` INT NULL,
|
|
ADD COLUMN `reserved_at` TIMESTAMP NULL,
|
|
ADD COLUMN `reservation_expires_at` TIMESTAMP NULL,
|
|
ADD CONSTRAINT `fk_cars_reserved_by` FOREIGN KEY (`reserved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL;
|
|
|
|
-- Update purchases table for Enterprise Flow
|
|
-- We need to check if existing purchases exist and handle them.
|
|
-- Since this is an upgrade, we'll allow NULL for new columns temporarily if needed,
|
|
-- but the requirement asks for NOT NULL UNIQUE, so we'll be careful.
|
|
ALTER TABLE `purchases`
|
|
ADD COLUMN `transaction_id` CHAR(36) NULL AFTER `id`,
|
|
ADD COLUMN `reference_number` VARCHAR(50) NULL AFTER `transaction_id`,
|
|
ADD COLUMN `verification_token` VARCHAR(64) NULL AFTER `reference_number`,
|
|
ADD COLUMN `base_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
ADD COLUMN `marketplace_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
ADD COLUMN `tax` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
ADD COLUMN `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
ADD COLUMN `payment_method` ENUM('card', 'bank_transfer', 'wallet') NULL,
|
|
ADD COLUMN `escrow_status` ENUM('awaiting_verification', 'held_in_escrow', 'released', 'cancelled') DEFAULT 'awaiting_verification',
|
|
ADD COLUMN `expires_at` TIMESTAMP NULL;
|
|
|
|
ALTER TABLE `purchases`
|
|
MODIFY COLUMN `status` ENUM('initiated', 'processing', 'paid', 'failed', 'refunded', 'chargeback', 'reserved', 'completed', 'cancelled', 'pending', 'approved', 'rejected') DEFAULT 'initiated';
|
|
|
|
-- Settings table for configurable marketplace values
|
|
CREATE TABLE IF NOT EXISTS `settings` (
|
|
`key` VARCHAR(50) PRIMARY KEY,
|
|
`value` VARCHAR(255) NOT NULL,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT IGNORE INTO `settings` (`key`, `value`) VALUES
|
|
('marketplace_fee_percentage', '5'),
|
|
('tax_percentage', '10');
|