-- Migration: Add company dates and user soft delete
-- Date: 2025-01-XX
-- Description: Adds usage dates to companies and soft delete to users

-- ===== Extend Companies Table =====
-- Add usage dates and billing date columns
ALTER TABLE companies 
ADD COLUMN usage_start_date DATE DEFAULT NULL COMMENT '利用開始日',
ADD COLUMN usage_end_date DATE DEFAULT NULL COMMENT '利用終了日',
ADD COLUMN last_billing_date DATE DEFAULT NULL COMMENT '最終課金日';

-- Add indexes for new columns
ALTER TABLE companies
ADD INDEX idx_usage_start_date (usage_start_date),
ADD INDEX idx_usage_end_date (usage_end_date),
ADD INDEX idx_last_billing_date (last_billing_date);

-- ===== Extend Users Table =====
-- Add soft delete column to users
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '削除日時（soft delete）';

-- Add index for deleted_at
ALTER TABLE users
ADD INDEX idx_deleted_at (deleted_at);

-- ===== Modify email unique constraint =====
-- Remove unique constraint on email to allow re-registration of deleted users
-- In MySQL, we can't create partial unique indexes, so we'll remove the unique constraint
-- and handle uniqueness checks in application code (only for non-deleted users)
-- 
-- In migration 003, the email column was created with UNIQUE constraint and idx_email index.
-- We need to:
-- 1. Drop the UNIQUE constraint (requires column modification)
-- 2. Keep idx_email as a regular (non-unique) index
--
-- Drop the UNIQUE constraint by modifying the column (removing UNIQUE)
-- This also removes any auto-generated index for the UNIQUE constraint
ALTER TABLE users 
MODIFY COLUMN email VARCHAR(255) NOT NULL;

-- Note: idx_email index from migration 003 will remain as a regular index
-- If it doesn't exist or was already removed, the application will still work
-- because we have application-level uniqueness checks for non-deleted users

-- Note: Application code will check for existing active (non-deleted) users with the same email
-- The email column no longer has a UNIQUE constraint, allowing multiple deleted users with same email

