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

const createTablesSQL = `
-- Users table
CREATE TABLE IF NOT EXISTS User (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  email VARCHAR(191) NOT NULL UNIQUE,
  password VARCHAR(191),
  role ENUM('ADMIN', 'CLIENT', 'FREELANCER') NOT NULL DEFAULT 'CLIENT',
  emailVerified DATETIME(3),
  image TEXT,
  provider VARCHAR(191),
  providerId 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)
);

-- Countries table
CREATE TABLE IF NOT EXISTS Country (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  code VARCHAR(191) NOT NULL UNIQUE,
  domain VARCHAR(191) NOT NULL UNIQUE,
  defaultLanguage VARCHAR(191) NOT NULL,
  currency VARCHAR(191) NOT NULL,
  currencySymbol VARCHAR(191) NOT NULL,
  packagePrice DECIMAL(10,2) NOT NULL,
  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)
);

-- Languages table
CREATE TABLE IF NOT EXISTS Language (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  code VARCHAR(191) NOT NULL UNIQUE,
  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)
);

-- CountryLanguages table
CREATE TABLE IF NOT EXISTS CountryLanguage (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  countryId VARCHAR(191) NOT NULL,
  languageId VARCHAR(191) NOT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  FOREIGN KEY (countryId) REFERENCES Country(id) ON DELETE CASCADE,
  FOREIGN KEY (languageId) REFERENCES Language(id) ON DELETE CASCADE,
  UNIQUE KEY unique_country_language (countryId, languageId)
);

-- Categories table
CREATE TABLE IF NOT EXISTS Category (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  slug VARCHAR(191) NOT NULL UNIQUE,
  description TEXT,
  icon VARCHAR(191),
  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)
);

-- Profiles table
CREATE TABLE IF NOT EXISTS Profile (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  userId VARCHAR(191) NOT NULL UNIQUE,
  firstName VARCHAR(191),
  lastName VARCHAR(191),
  phone VARCHAR(191),
  bio TEXT,
  skills JSON,
  hourlyRate DECIMAL(10,2),
  availability ENUM('AVAILABLE', 'BUSY', 'UNAVAILABLE') DEFAULT 'AVAILABLE',
  profileImage VARCHAR(191),
  portfolio JSON,
  experience JSON,
  education JSON,
  languages JSON,
  countryId 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),
  FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (countryId) REFERENCES Country(id) ON DELETE SET NULL
);

-- Projects table
CREATE TABLE IF NOT EXISTS Project (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  title VARCHAR(191) NOT NULL,
  description TEXT NOT NULL,
  budget DECIMAL(10,2) NOT NULL,
  currency VARCHAR(191) NOT NULL,
  deadline DATETIME(3),
  status ENUM('DRAFT', 'PUBLISHED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
  clientId VARCHAR(191) NOT NULL,
  freelancerId VARCHAR(191),
  categoryId VARCHAR(191) NOT NULL,
  countryId VARCHAR(191) NOT NULL,
  skills JSON,
  attachments JSON,
  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 (clientId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (freelancerId) REFERENCES User(id) ON DELETE SET NULL,
  FOREIGN KEY (categoryId) REFERENCES Category(id) ON DELETE RESTRICT,
  FOREIGN KEY (countryId) REFERENCES Country(id) ON DELETE RESTRICT
);

-- Bids table
CREATE TABLE IF NOT EXISTS Bid (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(191) NOT NULL,
  proposal TEXT NOT NULL,
  deliveryTime INT NOT NULL,
  status ENUM('PENDING', 'ACCEPTED', 'REJECTED', 'WITHDRAWN') NOT NULL DEFAULT 'PENDING',
  projectId VARCHAR(191) NOT NULL,
  freelancerId VARCHAR(191) NOT NULL,
  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 (projectId) REFERENCES Project(id) ON DELETE CASCADE,
  FOREIGN KEY (freelancerId) REFERENCES User(id) ON DELETE CASCADE,
  UNIQUE KEY unique_project_freelancer (projectId, freelancerId)
);

-- Subscriptions table
CREATE TABLE IF NOT EXISTS Subscription (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  userId VARCHAR(191) NOT NULL,
  countryId VARCHAR(191) NOT NULL,
  status ENUM('ACTIVE', 'CANCELLED', 'EXPIRED', 'PENDING') NOT NULL DEFAULT 'PENDING',
  startDate DATETIME(3) NOT NULL,
  endDate DATETIME(3) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(191) NOT NULL,
  stripeSubscriptionId 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),
  FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (countryId) REFERENCES Country(id) ON DELETE RESTRICT
);

-- Messages table
CREATE TABLE IF NOT EXISTS Message (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  content TEXT NOT NULL,
  senderId VARCHAR(191) NOT NULL,
  receiverId VARCHAR(191) NOT NULL,
  projectId VARCHAR(191),
  isRead BOOLEAN NOT NULL DEFAULT false,
  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 (senderId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (receiverId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (projectId) REFERENCES Project(id) ON DELETE SET NULL
);

-- Reviews table
CREATE TABLE IF NOT EXISTS Review (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  rating INT NOT NULL,
  comment TEXT,
  projectId VARCHAR(191) NOT NULL,
  reviewerId VARCHAR(191) NOT NULL,
  revieweeId VARCHAR(191) NOT NULL,
  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 (projectId) REFERENCES Project(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewerId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (revieweeId) REFERENCES User(id) ON DELETE CASCADE,
  UNIQUE KEY unique_project_reviewer (projectId, reviewerId)
);

-- Payments table
CREATE TABLE IF NOT EXISTS Payment (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(191) NOT NULL,
  status ENUM('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED') NOT NULL DEFAULT 'PENDING',
  type ENUM('PROJECT_PAYMENT', 'SUBSCRIPTION', 'REFUND') NOT NULL,
  projectId VARCHAR(191),
  subscriptionId VARCHAR(191),
  payerId VARCHAR(191) NOT NULL,
  payeeId VARCHAR(191),
  stripePaymentIntentId 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),
  FOREIGN KEY (projectId) REFERENCES Project(id) ON DELETE SET NULL,
  FOREIGN KEY (subscriptionId) REFERENCES Subscription(id) ON DELETE SET NULL,
  FOREIGN KEY (payerId) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (payeeId) REFERENCES User(id) ON DELETE SET NULL
);

-- NextAuth tables
CREATE TABLE IF NOT EXISTS Account (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  userId VARCHAR(191) NOT NULL,
  type VARCHAR(191) NOT NULL,
  provider VARCHAR(191) NOT NULL,
  providerAccountId VARCHAR(191) NOT NULL,
  refresh_token TEXT,
  access_token TEXT,
  expires_at INT,
  token_type VARCHAR(191),
  scope VARCHAR(191),
  id_token TEXT,
  session_state 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),
  FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE,
  UNIQUE KEY unique_provider_account (provider, providerAccountId)
);

CREATE TABLE IF NOT EXISTS Session (
  id VARCHAR(191) NOT NULL PRIMARY KEY,
  sessionToken VARCHAR(191) NOT NULL UNIQUE,
  userId VARCHAR(191) NOT NULL,
  expires DATETIME(3) NOT NULL,
  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 (userId) REFERENCES User(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS VerificationToken (
  identifier VARCHAR(191) NOT NULL,
  token VARCHAR(191) NOT NULL UNIQUE,
  expires DATETIME(3) NOT NULL,
  PRIMARY KEY (identifier, token)
);
`;

async function createTables() {
  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 CREATE TABLE statement
    const statements = createTablesSQL.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')) {
            console.error('Error executing statement:', error.message);
          }
        }
      }
    }

    console.log('\n✅ Database schema created successfully!');
    console.log('You can now run: npx prisma db seed');
    
  } catch (error) {
    console.error('Error creating tables:', error.message);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

createTables();
