-- WC360 Database Schema Updates
-- User Approval System & Bug Fixes

-- Missing columns for user_submissions table
ALTER TABLE user_submissions 
ADD COLUMN IF NOT EXISTS comment TEXT,
ADD COLUMN IF NOT EXISTS admin_notes TEXT,
ADD COLUMN IF NOT EXISTS rejection_reason VARCHAR(255),
ADD COLUMN IF NOT EXISTS reviewed_by INT,
ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL;

-- Missing image_path column for toilet_images table
ALTER TABLE toilet_images 
ADD COLUMN IF NOT EXISTS image_path VARCHAR(500);

-- Update existing toilet_images records if needed
-- UPDATE toilet_images SET image_path = url WHERE image_path IS NULL AND url IS NOT NULL;

-- Add foreign key constraints if not exists
-- ALTER TABLE user_submissions ADD CONSTRAINT fk_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL; 