import 'server-only';

import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import {
  mysqlTable,
  text,
  timestamp,
  mysqlEnum,
  serial,
  varchar
} from 'drizzle-orm/mysql-core';
import { count, eq, like, desc } from 'drizzle-orm';
import { createInsertSchema } from 'drizzle-zod';

// Veritabanı bağlantısı (Özel karakterli şifreler için güvenli yöntem)
const poolConnection = mysql.createPool({
  host: process.env.DB_HOST || '176.240.212.57',
  user: process.env.DB_USER || 'buyukhuk_hak',
  password: process.env.DB_PASS || ')cymll7A=;@?',
  database: process.env.DB_NAME || 'buyukhuk_hak',
  port: 3306
});

export const db = drizzle(poolConnection);

// Durum Enum'ı (MySQL için)
export const statusEnum = mysqlEnum('status', ['active', 'inactive', 'archived']);

// Yazılar (Makaleler) Tablosu
export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  imageUrl: varchar('image_url', { length: 255 }),
  status: statusEnum.notNull().default('active'),
  createdAt: timestamp('created_at').defaultNow().notNull()
});

// Hizmetler Tablosu
export const services = mysqlTable('services', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description').notNull(),
  imageUrl: varchar('image_url', { length: 255 }),
  status: statusEnum.notNull().default('active')
});

export type SelectPost = typeof posts.$inferSelect;
export const insertPostSchema = createInsertSchema(posts);

export type SelectService = typeof services.$inferSelect;
export const insertServiceSchema = createInsertSchema(services);

// Yazıları Getir
export async function getPosts(
  search: string,
  offset: number
): Promise<{
  posts: SelectPost[];
  newOffset: number | null;
  totalPosts: number;
}> {
  if (search) {
    return {
      posts: await db
        .select()
        .from(posts)
        .where(like(posts.title, `%${search}%`))
        .orderBy(desc(posts.createdAt))
        .limit(1000),
      newOffset: null,
      totalPosts: 0
    };
  }

  if (offset === null) {
    return { posts: [], newOffset: null, totalPosts: 0 };
  }

  let totalPosts = await db.select({ count: count() }).from(posts);
  let morePosts = await db.select().from(posts).orderBy(desc(posts.createdAt)).limit(5).offset(offset);
  let newOffset = morePosts.length >= 5 ? offset + 5 : null;

  return {
    posts: morePosts,
    newOffset,
    totalPosts: totalPosts[0].count
  };
}

// Hizmetleri Getir
export async function getServices() {
  return await db.select().from(services).where(eq(services.status, 'active'));
}

export async function deletePostById(id: number) {
  await db.delete(posts).where(eq(posts.id, id));
}

export async function deleteServiceById(id: number) {
  await db.delete(services).where(eq(services.id, id));
}
