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


// Tabla surveys
export const surveys = t.pgTable(
  'surveys',
  {
    id: t.serial('id').primaryKey(),
    title: t.text('title').notNull(),
    description: t.text('description').notNull(),
    targetAudience: t.varchar('target_audience', { length: 50 }).notNull(),
    closingDate: t.date('closing_date'),
    published: t.boolean('published').notNull(),
    questions: t.jsonb('questions').notNull(),
    ...timestamps,
  },
  (surveys) => ({
    surveysIndex: t
      .index('surveys_index_00')
      .on(surveys.title),
  }),
);

export const answersSurveys = t.pgTable(
  'answers_surveys',
  {
    id: t.serial('id').primaryKey(),
    surveyId: t
      .integer('survey_id')
      .references(() => surveys.id, { onDelete: 'cascade' }),
    userId: t
      .integer('user_id')
      .references(() => users.id, { onDelete: 'cascade' }),
    answers: t.jsonb('answers').notNull(),
    ...timestamps,
  },
  (answers) => ({
    answersIndex: t.index('answers_index_00').on(answers.answers),
    answersIndex01: t.index('answers_index_01').on(answers.surveyId),
    answersIndex02: t.index('answers_index_02').on(answers.userId),
  }),
);



export const viewSurveys = t.pgView('v_surveys', {
  surverId: t.integer('survey_id'),
  title: t.text('title'),
  description: t.text('description'),
  created_at: t.timestamp('created_at'),
  closingDate: t.date('closing_date'),
  targetAudience: t.varchar('target_audience')
}).as(sql`select id as survey_id, title, description, created_at, closing_date, target_audience from surveys
where published = true`);