import { index, integer, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";

export const quoteRequests = sqliteTable("quote_requests", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  reference: text("reference").notNull().unique(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  company: text("company"),
  profileCode: text("profile_code").notNull(),
  signText: text("sign_text").notNull(),
  letterCount: integer("letter_count").notNull(),
  heightCm: integer("height_cm").notNull(),
  widthCm: integer("width_cm").notNull(),
  letterSpacingCm: integer("letter_spacing_cm").notNull().default(4),
  depthMm: integer("depth_mm").notNull(),
  mounting: text("mounting").notNull(),
  facadeKey: text("facade_key"),
  facadeName: text("facade_name"),
  facadeType: text("facade_type"),
  logoKey: text("logo_key"),
  logoName: text("logo_name"),
  logoType: text("logo_type"),
  font: text("font").notNull().default("grotesk"),
  signColor: text("sign_color").notNull().default("#f2eee7"),
  lightColor: text("light_color").notNull().default("#ffb36b"),
  positionX: integer("position_x").notNull().default(50),
  positionY: integer("position_y").notNull().default(45),
  visualScale: integer("visual_scale").notNull().default(100),
  night: integer("night", { mode: "boolean" }).notNull().default(true),
  estimatedPriceCents: integer("estimated_price_cents").notNull(),
  isProfessional: integer("is_professional", { mode: "boolean" }).notNull().default(false),
  status: text("status").notNull().default("new"),
  archived: integer("archived", { mode: "boolean" }).notNull().default(false),
  archivedAt: integer("archived_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("quote_requests_pipeline_idx").on(table.archived, table.status, table.createdAt)]);

export const professionalApplications = sqliteTable("professional_applications", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  company: text("company").notNull(),
  contactName: text("contact_name").notNull(),
  email: text("email").notNull(),
  phone: text("phone").notNull(),
  siret: text("siret").notNull(),
  activity: text("activity").notNull(),
  expectedVolume: text("expected_volume"),
  status: text("status").notNull().default("pending"),
  accessToken: text("access_token").unique(),
  approvedAt: integer("approved_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const textileQuoteRequests = sqliteTable("textile_quote_requests", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  reference: text("reference").notNull().unique(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  company: text("company"),
  profileCode: text("profile_code").notNull(),
  widthCm: integer("width_cm").notNull(),
  heightCm: integer("height_cm").notNull(),
  quantity: integer("quantity").notNull().default(1),
  finish: text("finish").notNull(),
  installation: text("installation").notNull(),
  visualKey: text("visual_key"),
  visualName: text("visual_name"),
  visualType: text("visual_type"),
  estimatedPriceCents: integer("estimated_price_cents").notNull(),
  isProfessional: integer("is_professional", { mode: "boolean" }).notNull().default(false),
  status: text("status").notNull().default("new"),
  archived: integer("archived", { mode: "boolean" }).notNull().default(false),
  archivedAt: integer("archived_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("textile_quote_requests_pipeline_idx").on(table.archived, table.status, table.createdAt)]);

export const neonQuoteRequests = sqliteTable("neon_quote_requests", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  reference: text("reference").notNull().unique(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  company: text("company"),
  neonText: text("neon_text").notNull(),
  font: text("font").notNull(),
  widthCm: integer("width_cm").notNull(),
  heightCm: integer("height_cm").notNull(),
  tubeMm: integer("tube_mm").notNull(),
  color: text("color").notNull(),
  support: text("support").notNull(),
  installation: text("installation").notNull(),
  quantity: integer("quantity").notNull().default(1),
  designKey: text("design_key"),
  designName: text("design_name"),
  designType: text("design_type"),
  estimatedPriceCents: integer("estimated_price_cents").notNull(),
  isProfessional: integer("is_professional", { mode: "boolean" }).notNull().default(false),
  status: text("status").notNull().default("new"),
  archived: integer("archived", { mode: "boolean" }).notNull().default(false),
  archivedAt: integer("archived_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("neon_quote_requests_pipeline_idx").on(table.archived, table.status, table.createdAt)]);

export const manualProjects = sqliteTable("manual_projects", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  reference: text("reference").notNull().unique(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  phone: text("phone"),
  company: text("company"),
  title: text("title").notNull(),
  description: text("description").notNull().default(""),
  widthCm: integer("width_cm"),
  heightCm: integer("height_cm"),
  quantity: integer("quantity").notNull().default(1),
  estimatedPriceCents: integer("estimated_price_cents").notNull(),
  isProfessional: integer("is_professional", { mode: "boolean" }).notNull().default(false),
  status: text("status").notNull().default("new"),
  archived: integer("archived", { mode: "boolean" }).notNull().default(false),
  archivedAt: integer("archived_at", { mode: "timestamp" }),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("manual_projects_pipeline_idx").on(table.archived, table.status, table.createdAt)]);

export const pricingProfiles = sqliteTable("pricing_profiles", {
  code: text("code").primaryKey(),
  factorBp: integer("factor_bp").notNull(),
  basePriceCents: integer("base_price_cents").notNull().default(22000),
  ratePerCmLetterCents: integer("rate_per_cm_letter_cents").notNull().default(450),
  professionalDiscountBp: integer("professional_discount_bp").notNull().default(1800),
  priceMode: text("price_mode").notNull().default("estimate"),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  version: integer("version").notNull().default(1),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const signProfileCatalog = sqliteTable("sign_profile_catalog", {
  code: text("code").primaryKey(),
  name: text("name").notNull(),
  short: text("short").notNull(),
  imageUrl: text("image_url").notNull(),
  description: text("description").notNull().default(""),
  ideal: text("ideal").notNull().default(""),
  specJson: text("spec_json").notNull(),
  active: integer("active", { mode: "boolean" }).notNull().default(true),
  approved: integer("approved", { mode: "boolean" }).notNull().default(false),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const textilePricingProfiles = sqliteTable("textile_pricing_profiles", {
  code: text("code").primaryKey(),
  ratePerM2Cents: integer("rate_per_m2_cents").notNull(),
  basePriceCents: integer("base_price_cents").notNull().default(16000),
  professionalDiscountBp: integer("professional_discount_bp").notNull().default(1800),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  version: integer("version").notNull().default(1),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const neonPricingRules = sqliteTable("neon_pricing_rules", {
  code: text("code").primaryKey(),
  label: text("label").notNull(),
  section: text("section").notNull(),
  value: integer("value").notNull(),
  unit: text("unit").notNull(),
  position: integer("position").notNull().default(0),
  version: integer("version").notNull().default(1),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const commercialQuotes = sqliteTable("commercial_quotes", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  requestType: text("request_type").notNull(),
  requestId: integer("request_id").notNull(),
  productionPriceCents: integer("production_price_cents").notNull(),
  deliveryPriceCents: integer("delivery_price_cents").notNull().default(0),
  installationPriceCents: integer("installation_price_cents").notNull().default(0),
  discountCents: integer("discount_cents").notNull().default(0),
  notes: text("notes"),
  validityDays: integer("validity_days").notNull().default(30),
  publicToken: text("public_token").unique(),
  clientStatus: text("client_status").notNull().default("pending"),
  clientName: text("client_name"),
  clientNote: text("client_note"),
  clientSignature: text("client_signature"),
  respondedAt: integer("responded_at", { mode: "timestamp" }),
  productionFileId: integer("production_file_id"),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [uniqueIndex("commercial_quotes_request_unique").on(table.requestType, table.requestId)]);

export const projectFiles = sqliteTable("project_files", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  requestType: text("request_type").notNull(),
  requestId: integer("request_id").notNull(),
  label: text("label").notNull(),
  category: text("category").notNull().default("production"),
  storageKey: text("storage_key").notNull().unique(),
  fileName: text("file_name").notNull(),
  contentType: text("content_type").notNull(),
  sizeBytes: integer("size_bytes").notNull(),
  factoryVisible: integer("factory_visible", { mode: "boolean" }).notNull().default(false),
  shareToken: text("share_token").notNull().unique(),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("project_files_request_idx").on(table.requestType, table.requestId)]);

export const factoryAccessLinks = sqliteTable("factory_access_links", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  requestType: text("request_type").notNull(),
  requestId: integer("request_id").notNull(),
  token: text("token").notNull().unique(),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [uniqueIndex("factory_access_request_unique").on(table.requestType, table.requestId)]);

export const emailLogs = sqliteTable("email_logs", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  requestType: text("request_type"),
  requestId: integer("request_id"),
  template: text("template").notNull(),
  recipient: text("recipient").notNull(),
  subject: text("subject").notNull(),
  status: text("status").notNull(),
  messageId: text("message_id"),
  errorMessage: text("error_message"),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [
  index("email_logs_project_idx").on(table.requestType, table.requestId, table.createdAt),
  index("email_logs_status_idx").on(table.status, table.createdAt),
]);

export const generatorFonts = sqliteTable("generator_fonts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  slug: text("slug").notNull().unique(),
  name: text("name").notNull(),
  cssFamily: text("css_family").notNull(),
  sourceType: text("source_type").notNull().default("builtin"),
  storageKey: text("storage_key"),
  originalFileName: text("original_file_name"),
  contentType: text("content_type"),
  signEnabled: integer("sign_enabled", { mode: "boolean" }).notNull().default(false),
  neonEnabled: integer("neon_enabled", { mode: "boolean" }).notNull().default(false),
  handwritten: integer("handwritten", { mode: "boolean" }).notNull().default(false),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  factorBp: integer("factor_bp").notNull().default(10000),
  position: integer("position").notNull().default(0),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("generator_fonts_visibility_idx").on(table.enabled, table.signEnabled, table.neonEnabled, table.position)]);

export const cmsContent = sqliteTable("cms_content", {
  key: text("key").primaryKey(),
  label: text("label").notNull(),
  section: text("section").notNull(),
  draftValue: text("draft_value").notNull(),
  publishedValue: text("published_value").notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
  publishedAt: integer("published_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const cmsRevisions = sqliteTable("cms_revisions", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  contentKey: text("content_key").notNull(),
  value: text("value").notNull(),
  action: text("action").notNull(),
  authorEmail: text("author_email"),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
}, (table) => [index("cms_revisions_content_key_idx").on(table.contentKey, table.createdAt)]);

export const cmsPortfolio = sqliteTable("cms_portfolio", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  location: text("location").notNull().default("France"),
  category: text("category").notNull().default("Enseigne"),
  description: text("description").notNull().default(""),
  imageUrl: text("image_url").notNull(),
  altText: text("alt_text").notNull(),
  status: text("status").notNull().default("draft"),
  featured: integer("featured", { mode: "boolean" }).notNull().default(false),
  position: integer("position").notNull().default(0),
  publishedAt: integer("published_at", { mode: "timestamp" }),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});

export const cmsReferences = sqliteTable("cms_references", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  websiteUrl: text("website_url").notNull(),
  logoUrl: text("logo_url").notNull(),
  altText: text("alt_text").notNull(),
  status: text("status").notNull().default("draft"),
  position: integer("position").notNull().default(0),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().$defaultFn(() => new Date()),
});