import { double, int, mysqlTable, text, timestamp, varchar } from "drizzle-orm/mysql-core";

export const clients = mysqlTable("clients", {id:int("id").autoincrement().primaryKey(),company:varchar("company",{length:255}).notNull(),contact:varchar("contact",{length:255}).notNull().default(""),phone:varchar("phone",{length:100}).notNull().default(""),email:varchar("email",{length:255}).notNull().default(""),address:text("address").notNull(),taxId:varchar("tax_id",{length:100}).notNull().default(""),createdAt:timestamp("created_at").defaultNow().notNull()});
export const projects = mysqlTable("projects", {id:int("id").autoincrement().primaryKey(),code:varchar("code",{length:80}).notNull().unique(),name:varchar("name",{length:255}).notNull(),serviceType:varchar("service_type",{length:255}).notNull().default(""),clientId:int("client_id").references(()=>clients.id),contractValue:double("contract_value").notNull().default(0),budget:double("budget").notNull().default(0),progress:double("progress").notNull().default(0),status:varchar("status",{length:100}).notNull().default("Berjalan"),startDate:varchar("start_date",{length:20}).notNull().default(""),endDate:varchar("end_date",{length:20}).notNull().default(""),createdAt:timestamp("created_at").defaultNow().notNull()});
export const financeEntries = mysqlTable("finance_entries", {id:int("id").autoincrement().primaryKey(),date:varchar("date",{length:20}).notNull(),dueDate:varchar("due_date",{length:20}).notNull().default(""),documentNo:varchar("document_no",{length:120}).notNull().default(""),category:varchar("category",{length:100}).notNull(),direction:varchar("direction",{length:30}).notNull(),description:text("description").notNull(),amount:double("amount").notNull().default(0),paidAmount:double("paid_amount").notNull().default(0),notificationDismissed:int("notification_dismissed").notNull().default(0),status:varchar("status",{length:100}).notNull().default("Draft"),projectId:int("project_id").references(()=>projects.id),clientId:int("client_id").references(()=>clients.id),notes:text("notes").notNull(),createdAt:timestamp("created_at").defaultNow().notNull()});
export const assets = mysqlTable("assets", {id:int("id").autoincrement().primaryKey(),code:varchar("code",{length:80}).notNull().unique(),name:varchar("name",{length:255}).notNull(),category:varchar("category",{length:120}).notNull(),acquisitionDate:varchar("acquisition_date",{length:20}).notNull(),acquisitionValue:double("acquisition_value").notNull().default(0),usefulLife:int("useful_life").notNull().default(4),condition:varchar("condition",{length:100}).notNull().default("Baik"),location:varchar("location",{length:255}).notNull().default("Kantor JLBB"),createdAt:timestamp("created_at").defaultNow().notNull()});
export const projections = mysqlTable("projections", {id:int("id").autoincrement().primaryKey(),period:varchar("period",{length:100}).notNull(),scenario:varchar("scenario",{length:100}).notNull().default("Moderat"),projectedIncome:double("projected_income").notNull().default(0),projectedExpense:double("projected_expense").notNull().default(0),notes:text("notes").notNull(),createdAt:timestamp("created_at").defaultNow().notNull()});
export const commercialDocuments = mysqlTable("commercial_documents", {id:int("id").autoincrement().primaryKey(),type:varchar("type",{length:50}).notNull(),documentNo:varchar("document_no",{length:120}).notNull().unique(),date:varchar("date",{length:20}).notNull(),dueDate:varchar("due_date",{length:20}).notNull().default(""),clientId:int("client_id").references(()=>clients.id),projectId:int("project_id").references(()=>projects.id),subject:text("subject").notNull(),itemsJson:text("items_json").notNull(),taxMode:varchar("tax_mode",{length:30}).notNull().default("exclude"),taxRate:double("tax_rate").notNull().default(11),discountType:varchar("discount_type",{length:30}).notNull().default("percent"),discountValue:double("discount_value").notNull().default(0),subtotal:double("subtotal").notNull().default(0),discountAmount:double("discount_amount").notNull().default(0),taxAmount:double("tax_amount").notNull().default(0),total:double("total").notNull().default(0),paidAmount:double("paid_amount").notNull().default(0),status:varchar("status",{length:100}).notNull().default("Draft"),sourceDocumentId:int("source_document_id"),sentAt:varchar("sent_at",{length:20}).notNull().default(""),sentTo:varchar("sent_to",{length:255}).notNull().default(""),paymentDate:varchar("payment_date",{length:20}).notNull().default(""),paymentMethod:varchar("payment_method",{length:100}).notNull().default(""),terms:text("terms").notNull(),notes:text("notes").notNull(),createdAt:timestamp("created_at").defaultNow().notNull()});
export const approvals = mysqlTable("approvals", {id:int("id").autoincrement().primaryKey(),reportType:varchar("report_type",{length:50}).notNull(),period:varchar("period",{length:7}).notNull(),status:varchar("status",{length:30}).notNull().default("Disetujui"),approvedBy:varchar("approved_by",{length:255}).notNull(),approvedAt:timestamp("approved_at").defaultNow().notNull(),notes:text("notes").notNull()});
