const mysql = require('mysql2/promise');

const createMissingTablesSQL = `
-- Add missing tables from Prisma schema

-- Experience table
CREATE TABLE IF NOT EXISTS Experience (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  profileId VARCHAR(191) NOT NULL,
  title VARCHAR(191) NOT NULL,
  company VARCHAR(191) NOT NULL,
  location VARCHAR(191),
  startDate DATETIME(3) NOT NULL,
  endDate DATETIME(3),
  isCurrent BOOLEAN NOT NULL DEFAULT false,
  description TEXT,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  FOREIGN KEY (profileId) REFERENCES Profile(id) ON DELETE CASCADE
);

-- Education table
CREATE TABLE IF NOT EXISTS Education (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  profileId VARCHAR(191) NOT NULL,
  degree VARCHAR(191) NOT NULL,
  institution VARCHAR(191) NOT NULL,
  location VARCHAR(191),
  startDate DATETIME(3) NOT NULL,
  endDate DATETIME(3),
  isCurrent BOOLEAN NOT NULL DEFAULT false,
  description TEXT,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  FOREIGN KEY (profileId) REFERENCES Profile(id) ON DELETE CASCADE
);

-- Portfolio table
CREATE TABLE IF NOT EXISTS Portfolio (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  profileId VARCHAR(191) NOT NULL,
  title VARCHAR(191) NOT NULL,
  description TEXT,
  imageUrl VARCHAR(191) NOT NULL,
  projectUrl VARCHAR(191),
  tags TEXT,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  FOREIGN KEY (profileId) REFERENCES Profile(id) ON DELETE CASCADE
);

-- CategoryProfile table
CREATE TABLE IF NOT EXISTS CategoryProfile (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  categoryId VARCHAR(191) NOT NULL,
  profileId VARCHAR(191) NOT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  FOREIGN KEY (categoryId) REFERENCES Category(id) ON DELETE CASCADE,
  FOREIGN KEY (profileId) REFERENCES Profile(id) ON DELETE CASCADE,
  UNIQUE KEY unique_category_profile (categoryId, profileId)
);

-- Package table
CREATE TABLE IF NOT EXISTS Package (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  description TEXT,
  features TEXT NOT NULL,
  countryId VARCHAR(191) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  duration INT NOT NULL DEFAULT 30,
  categoryLimit INT,
  bidLimit INT,
  isActive BOOLEAN NOT NULL DEFAULT true,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  FOREIGN KEY (countryId) REFERENCES Country(id) ON DELETE CASCADE,
  UNIQUE KEY unique_package_country (name, countryId)
);

-- Notification table
CREATE TABLE IF NOT EXISTS Notification (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  userId VARCHAR(191) NOT NULL,
  type VARCHAR(191) NOT NULL,
  title VARCHAR(191) NOT NULL,
  message VARCHAR(191) NOT NULL,
  data TEXT,
  isRead BOOLEAN NOT NULL DEFAULT false,
  readAt DATETIME(3),
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE
);

-- SiteSettings table
CREATE TABLE IF NOT EXISTS SiteSettings (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  \`key\` VARCHAR(191) NOT NULL UNIQUE,
  value TEXT NOT NULL,
  type VARCHAR(191) NOT NULL,
  description VARCHAR(191),
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
);

-- Update existing tables to match Prisma schema

-- Update Country table to add missing fields
ALTER TABLE Country 
ADD COLUMN IF NOT EXISTS languages TEXT,
ADD COLUMN IF NOT EXISTS paymentGateways TEXT;

-- Update Profile table to match Prisma schema
ALTER TABLE Profile 
ADD COLUMN IF NOT EXISTS title VARCHAR(191),
DROP COLUMN IF EXISTS availability,
DROP COLUMN IF EXISTS portfolio,
DROP COLUMN IF EXISTS experience,
DROP COLUMN IF EXISTS education,
DROP COLUMN IF EXISTS languages;

-- Update Project table to match Prisma schema
ALTER TABLE Project 
ADD COLUMN IF NOT EXISTS duration INT,
ADD COLUMN IF NOT EXISTS publishedAt DATETIME(3),
ADD COLUMN IF NOT EXISTS completedAt DATETIME(3),
ADD COLUMN IF NOT EXISTS selectedBidId VARCHAR(191) UNIQUE,
MODIFY COLUMN status ENUM('DRAFT', 'OPEN', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
DROP COLUMN IF EXISTS currency,
DROP COLUMN IF EXISTS deadline,
DROP COLUMN IF EXISTS freelancerId;

-- Update Bid table to match Prisma schema
ALTER TABLE Bid 
ADD COLUMN IF NOT EXISTS duration INT,
MODIFY COLUMN proposal TEXT NOT NULL,
MODIFY COLUMN status ENUM('PENDING', 'ACCEPTED', 'REJECTED', 'WITHDRAWN') NOT NULL DEFAULT 'PENDING',
DROP COLUMN IF EXISTS currency,
DROP COLUMN IF EXISTS deliveryTime;

-- Update Subscription table to match Prisma schema
ALTER TABLE Subscription 
MODIFY COLUMN status ENUM('ACTIVE', 'EXPIRED', 'CANCELLED') NOT NULL DEFAULT 'ACTIVE',
ADD COLUMN IF NOT EXISTS packageId VARCHAR(191),
ADD COLUMN IF NOT EXISTS autoRenew BOOLEAN NOT NULL DEFAULT true,
DROP COLUMN IF EXISTS amount,
DROP COLUMN IF EXISTS currency,
DROP COLUMN IF EXISTS stripeSubscriptionId;

-- Update Payment table to match Prisma schema
ALTER TABLE Payment 
ADD COLUMN IF NOT EXISTS subscriptionId VARCHAR(191),
ADD COLUMN IF NOT EXISTS paymentMethod VARCHAR(191),
ADD COLUMN IF NOT EXISTS paymentIntentId VARCHAR(191) UNIQUE,
ADD COLUMN IF NOT EXISTS metadata TEXT,
MODIFY COLUMN status ENUM('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED') NOT NULL DEFAULT 'PENDING',
DROP COLUMN IF EXISTS type,
DROP COLUMN IF EXISTS payerId,
DROP COLUMN IF EXISTS payeeId,
DROP COLUMN IF EXISTS stripePaymentIntentId;

-- Update Message table to match Prisma schema
ALTER TABLE Message 
ADD COLUMN IF NOT EXISTS attachments TEXT,
ADD COLUMN IF NOT EXISTS readAt DATETIME(3);

-- Update Review table to match Prisma schema
ALTER TABLE Review 
MODIFY COLUMN comment TEXT NOT NULL,
ADD COLUMN IF NOT EXISTS authorId VARCHAR(191),
ADD COLUMN IF NOT EXISTS recipientId VARCHAR(191),
DROP COLUMN IF EXISTS reviewerId,
DROP COLUMN IF EXISTS revieweeId;
`;

async function createMissingTables() {
  let connection;
  
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'smile24',
      port: 3306,
      multipleStatements: true
    });

    console.log('Connected to smile24 database');

    // Split and execute each statement
    const statements = createMissingTablesSQL.split(';').filter(stmt => stmt.trim());
    
    for (const statement of statements) {
      if (statement.trim()) {
        try {
          await connection.execute(statement);
          console.log('✓ Executed statement successfully');
        } catch (error) {
          if (!error.message.includes('already exists') && !error.message.includes('Duplicate column')) {
            console.error('Error executing statement:', error.message);
            console.error('Statement:', statement.substring(0, 100) + '...');
          }
        }
      }
    }

    console.log('\n✅ Missing tables and columns added successfully!');
    
  } catch (error) {
    console.error('Error creating missing tables:', error.message);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

createMissingTables();
