// shared/schema.ts
// Drizzle ORM schema for ten-sura-game.com game strategy guides
// ⚠️  This schema matches the shared Neon PostgreSQL production database.
//     The 'blog_posts' table and 'categories' table are shared across all sites.
//     The 'site_id' column isolates each site's data within the shared tables.
//     DO NOT run any DROP/ALTER migrations — the DB is already correctly structured.
import {
  pgTable, serial, varchar, text,
  integer, boolean, timestamp, jsonb, uuid
} from 'drizzle-orm/pg-core';

// ─────────────────────────────────────────────────────────────────────────────
// BLOG POSTS TABLE
// Real DB table name: blog_posts
// site_id = 'ten-sura-game.com' for this website
// ─────────────────────────────────────────────────────────────────────────────
export const blogPosts = pgTable('blog_posts', {
  // Core fields
  id: serial('id').primaryKey(),
  externalId: varchar('external_id', { length: 255 }).unique(),
  title: text('title').notNull(),
  slug: varchar('slug', { length: 500 }).notNull().unique(),
  content: text('content'),
  excerpt: text('excerpt'),
  category: varchar('category', { length: 255 }),

  // Site isolation — REQUIRED for shared DB
  siteId: varchar('site_id', { length: 255 }),

  // Images
  cardImage: text('card_image'),
  heroImage: text('hero_image'),

  // Author
  author: varchar('author', { length: 255 }),
  authorProfileUrl: text('author_profile_url'),
  authorBio: text('author_bio'),

  // Dates
  publicationDate: timestamp('publication_date'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),

  // SEO
  metaTitle: varchar('meta_title', { length: 255 }),
  metaDescription: text('meta_description'),
  seoTitle: varchar('seo_title', { length: 255 }),
  snippetIntro: text('snippet_intro'),

  // Metrics
  readingTime: integer('reading_time'),
  wordCount: integer('word_count'),
  viewsCount: integer('views_count').default(0),
  likesCount: integer('likes_count').default(0),

  // Flags
  featured: boolean('featured').default(false),
  isActive: boolean('is_active').default(true),

  // Structured data (JSONB)
  schemaJson: jsonb('schema_json'),
  schemaFaq: jsonb('schema_faq'),
  tableOfContents: jsonb('table_of_contents'),
  faqItems: jsonb('faq_items'),
  keyTakeaways: jsonb('key_takeaways'),
  aioBox: jsonb('aio_box'),
  heroImageData: jsonb('hero_image_data'),
  authorData: jsonb('author_data'),

  // Media
  images: jsonb('images'),
  videos: jsonb('videos'),

  // Metadata
  contentHash: varchar('content_hash', { length: 64 }),
  processingTime: integer('processing_time'),

  // Language
  language: varchar('language', { length: 10 }).default('ja'),
  keywords: text('keywords'),
});

// Keep 'articles' as an alias so any component that still imports it does not break
// TODO: migrate all imports to use 'blogPosts' directly
// export const articles = blogPosts;

export type Article = typeof blogPosts.$inferSelect;
export type InsertArticle = typeof blogPosts.$inferInsert;

// ─────────────────────────────────────────────────────────────────────────────
// CATEGORIES TABLE
// site_id = 'ten-sura-game.com' for this website
// ─────────────────────────────────────────────────────────────────────────────
export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  externalId: varchar('external_id', { length: 255 }).unique(),
  name: varchar('name', { length: 255 }).notNull(),
  japanese: varchar('japanese', { length: 255 }),
  english: varchar('english', { length: 255 }),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  description: text('description'),

  // Site isolation — REQUIRED for shared DB
  siteId: varchar('site_id', { length: 255 }),

  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export type Category = typeof categories.$inferSelect;
export type InsertCategory = typeof categories.$inferInsert;

// ─────────────────────────────────────────────────────────────────────────────
// NEWSLETTER SUBSCRIBERS TABLE
// Real DB table name: subscribers
// site_id = 'ten-sura-game.com' for this website
// ─────────────────────────────────────────────────────────────────────────────
export const newsletterSubscribers = pgTable('subscribers', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),

  // Site isolation — REQUIRED for shared DB
  siteId: varchar('site_id', { length: 100 }),

  subscribedAt: timestamp('subscribed_at').defaultNow(),
  isActive: boolean('is_active').default(true),
  unsubscribedAt: timestamp('unsubscribed_at'),
});

export type NewsletterSubscriber = typeof newsletterSubscribers.$inferSelect;
export type InsertNewsletterSubscriber = typeof newsletterSubscribers.$inferInsert;


// ─────────────────────────────────────────────────────────────────────────────
// HOMEPAGE CONTENT TABLE
// Stores Puck editor state for the homepage visual editor
// ─────────────────────────────────────────────────────────────────────────────
export const homepageContent = pgTable('homepage_content', {
  id: uuid('id').primaryKey().defaultRandom(),
  siteId: varchar('site_id', { length: 255 }).notNull().unique(),
  content: jsonb('content').notNull().default({}),
  draftContent: jsonb('draft_content'),
  updatedAt: timestamp('updated_at').defaultNow(),
  updatedBy: varchar('updated_by', { length: 255 }),
});

export type HomepageContent = typeof homepageContent.$inferSelect;
export type InsertHomepageContent = typeof homepageContent.$inferInsert;

// ─────────────────────────────────────────────────────────────────────────────
// CUSTOM PAGES TABLE
// Real DB table name: custom_pages
// ─────────────────────────────────────────────────────────────────────────────
export const customPages = pgTable('custom_pages', {
  id: serial('id').primaryKey(),
  siteId: varchar('site_id', { length: 255 }).notNull(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  canonicalUrl: varchar('canonical_url', { length: 500 }),
  metaTitle: varchar('meta_title', { length: 255 }),
  metaDescription: text('meta_description'),
  schemaJson: jsonb('schema_json'),
  indexable: boolean('indexable').notNull().default(true),
  showInHeader: boolean('show_in_header').notNull().default(false),
  showInFooter: boolean('show_in_footer').notNull().default(false),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

export type CustomPage = typeof customPages.$inferSelect;
export type InsertCustomPage = typeof customPages.$inferInsert;

// ─────────────────────────────────────────────────────────────────────────────
// NAVIGATION ITEMS TABLE
// Real DB table name: navigation_items
// ─────────────────────────────────────────────────────────────────────────────
export const navigationItems = pgTable('navigation_items', {
  id: serial('id').primaryKey(),
  siteId: varchar('site_id', { length: 100 }).notNull(),
  label: varchar('label', { length: 255 }).notNull(),
  url: text('url'),
  itemType: varchar('item_type', { length: 50 }).notNull(),
  sourceId: varchar('source_id', { length: 255 }),
  menuLocation: varchar('menu_location', { length: 50 }).notNull(),
  parentId: varchar('parent_id', { length: 255 }),
  parentCategory: varchar('parent_category', { length: 255 }),
  sortOrder: integer('sort_order').default(0),
  isActive: boolean('is_active').default(true),
  openInNewTab: boolean('open_in_new_tab').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export type NavigationItem = typeof navigationItems.$inferSelect;
export type InsertNavigationItem = typeof navigationItems.$inferInsert;
