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



//Tabla de Tipo de categorias
export const categoryType = t.pgTable(
  'category_type',
  {
    id: t.serial('id').primaryKey(),
    group: t.varchar('group', { length: 100 }).notNull(), // grupo pertenece
    description: t.text('description').notNull(), // name
    ...timestamps,
  },
  (categoryType) => ({
    categoryTypeIdx0: t.index('category_typeIx0').on(categoryType.group),
    categoryTypeIdx1: t.index('category_typeIx1').on(categoryType.description),
  }),
);

// Tabla States
export const states = t.pgTable(
  'states',
  {
    id: t.serial('id').primaryKey(),
    name: t.text('name').notNull(),
    ...timestamps,
  },
  (states) => ({
    nameIndex: t.index('states_index_00').on(states.id, states.name),
  }),
);

// Tabla Municipalities
export const municipalities = t.pgTable(
  'municipalities',
  {
    id: t.serial('id').primaryKey(),
    name: t.text('name').notNull(),
    stateId: t
      .integer('state_id')
      .notNull()
      .references(() => states.id, { onDelete: 'cascade' }),
    ...timestamps,
  },
  (municipalities) => ({
    nameStateIndex: t
      .index('municipalities_index_00')
      .on(municipalities.id, municipalities.name, municipalities.stateId),
  }),
);

// Tabla Parishes
export const parishes = t.pgTable(
  'parishes',
  {
    id: t.serial('id').primaryKey(),
    name: t.text('name').notNull(),
    municipalityId: t
      .integer('municipality_id')
      .notNull()
      .references(() => municipalities.id, { onDelete: 'cascade' }),
    ...timestamps,
  },
  (parishes) => ({
    parishIndex: t
      .index('parishes_index_00')
      .on(parishes.id, parishes.name, parishes.municipalityId),
  }),
);

// Tabla Localities
export const localities = t.pgTable(
  'localities',
  {
    id: t.serial('id').primaryKey(),
    stateId: t
      .integer('state_id')
      .notNull()
      .references(() => states.id, { onDelete: 'cascade' }),
    municipalityId: t
      .integer('municipality_id')
      .notNull()
      .references(() => municipalities.id, { onDelete: 'cascade' }),
    parishId: t
      .integer('parish_id')
      .notNull()
      .references(() => parishes.id, { onDelete: 'cascade' }),
    name: t.text('name').unique().notNull(),
    ...timestamps,
  },
  (localities) => ({
    uniqueLocalityIndex: t
      .uniqueIndex('localities_index_03')
      .on(localities.stateId, localities.municipalityId, localities.parishId),
    stateIndex: t.index('localities_index_00').on(localities.stateId),
    municipalityIndex: t
      .index('localities_index_01')
      .on(localities.municipalityId),
    parishIndex: t.index('localities_index_02').on(localities.parishId),
  }),
);

// // Vista LocalitiesView
// export const localitiesView = t.pgView("localities_view", {
//     id: t.integer("id").notNull(),
//     stateId: t.integer("state_id"),
//     state: t.text("state"),
//     municipalityId: t.integer("municipality_id"),
//     municipality: t.text("municipality"),
//     parishId: t.integer("parish_id"),
//     parish: t.text("parish"),
//     fullLocation: t.text("full_location"),
//   });
