import * as t from 'drizzle-orm/pg-core';
import { timestamps } from '../timestamps';
import { users } from './auth';
import { sql } from 'drizzle-orm';

export const products = t.pgTable(
  'products',
  {
    id: t.serial('id').primaryKey(),
    title: t.text('title').notNull(),
    description: t.text('description').notNull(),
    price: t.numeric('price').notNull().default('0'),
    stock: t.integer('stock').notNull().default(0),
    address: t.text('address').notNull(),
    urlImg: t.text('url_img').notNull(),
    gallery: t.text('gallery').array().notNull().default(sql`'{}'::text[]`),
    status: t.text('status').notNull().default('BORRADOR'),
    userId: t.integer('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    ...timestamps,
  }
);

export const viewProductsStore = t.pgView('v_product_store', {
  id: t.integer('product_id'),
  title: t.text('title'),
  description: t.text('description'),
  price: t.numeric('price'),
  stock: t.integer('stock'),
  urlImg: t.text('url_img'),
  gallery: t.text('gallery'),
  address: t.text('address'),
  status: t.text('status'),
  userId: t.integer('user_id'),
  fullname: t.text('fullname'),
  email: t.text('email'),
  phone: t.text('phone')
}).as(sql`
  select p.id as product_id, p.title, p.description, p.price, p.stock, p.url_img, p.gallery, p.address, p.status, p.user_id, u.fullname, u.email, u.phone
  from products p
  left join auth.users as u on u.id = p.user_id`);