const { executeQuery } = require('../config/database');

async function addSubscriptionTables() {
  try {
    console.log('Adding subscription tables and fields...');

    // Add subscription fields to users table
    console.log('Adding subscription fields to users table...');
    await executeQuery(`
      ALTER TABLE users 
      ADD COLUMN subscription_status VARCHAR(20) DEFAULT 'free',
      ADD COLUMN subscription_end_date DATETIME NULL
    `);

    // Create subscriptions table
    console.log('Creating subscriptions table...');
    await executeQuery(`
      CREATE TABLE IF NOT EXISTS subscriptions (
        id INT NOT NULL AUTO_INCREMENT,
        user_id INT NOT NULL,
        platform VARCHAR(20) NOT NULL,
        product_id VARCHAR(255) NOT NULL,
        transaction_id VARCHAR(255) NOT NULL,
        receipt_data TEXT,
        status VARCHAR(20) NOT NULL DEFAULT 'active',
        start_date DATETIME NOT NULL,
        end_date DATETIME NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY unique_transaction (transaction_id),
        KEY idx_user_id (user_id),
        KEY idx_status (status),
        KEY idx_end_date (end_date),
        CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    `);

    console.log('Subscription tables and fields added successfully!');
  } catch (error) {
    console.error('Error adding subscription tables:', error);
    process.exit(1);
  }
}

// Run the migration
if (require.main === module) {
  addSubscriptionTables().then(() => {
    process.exit(0);
  });
}

module.exports = { addSubscriptionTables };