-- =====================================================
-- Script: Church Members Module Database Schema (Refactored)
-- Description: Creates tables for work groups and members linked to personas
-- =====================================================

-- Table: grupos_trabajo
CREATE TABLE IF NOT EXISTS public.grupos_trabajo
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    nombre character varying(100) NOT NULL,
    descripcion text,
    activo boolean NOT NULL DEFAULT true,
    creado_en timestamp with time zone NOT NULL DEFAULT NOW(),
    actualizado_en timestamp with time zone NOT NULL DEFAULT NOW(),
    CONSTRAINT grupos_trabajo_pkey PRIMARY KEY (id)
);

-- Table: miembros
-- Drop if exists to handle the schema change during development
DROP TABLE IF EXISTS public.miembros;

CREATE TABLE IF NOT EXISTS public.miembros
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    persona_id bigint NOT NULL,
    bautizado_espiritu_santo boolean NOT NULL DEFAULT false,
    fecha_ingreso_congregacion date,
    telefono_emergencia character varying(20),
    grupo_trabajo_id bigint,
    activo boolean NOT NULL DEFAULT true,
    eliminado boolean NOT NULL DEFAULT false,
    creado_en timestamp with time zone NOT NULL DEFAULT NOW(),
    actualizado_en timestamp with time zone NOT NULL DEFAULT NOW(),
    creado_por character varying(100),
    CONSTRAINT miembros_pkey PRIMARY KEY (id),
    CONSTRAINT fk_miembros_persona FOREIGN KEY (persona_id)
        REFERENCES public.personas (id),
    CONSTRAINT fk_miembros_grupo_trabajo FOREIGN KEY (grupo_trabajo_id)
        REFERENCES public.grupos_trabajo (id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_miembros_persona ON public.miembros(persona_id);
CREATE INDEX IF NOT EXISTS idx_miembros_grupo_trabajo ON public.miembros(grupo_trabajo_id);
CREATE INDEX IF NOT EXISTS idx_miembros_activo ON public.miembros(activo, eliminado);

-- Initial work groups data
INSERT INTO public.grupos_trabajo (nombre, descripcion) VALUES
('Concilio Misionero Femenil', 'Grupo de trabajo de mujeres misioneras'),
('Fraternidad de Varones', 'Grupo de trabajo de varones de la iglesia'),
('Embajadores de Cristo', 'Grupo de jóvenes embajadores')
ON CONFLICT DO NOTHING;