init_schema.sql

-- =====================================================================
-- 🐾 SOS Veterinaria — DDL Completo (Extraído de PostgreSQL real)
-- 🐾 SOS Veterinaria — Full DDL (Extracted from real PostgreSQL)
-- =====================================================================
-- Motor / Engine: PostgreSQL 15+
-- Schema: veterinaria
--
-- Ejecuta este script para crear todas las tablas desde cero.
-- Run this script to create all tables from scratch.
--
-- Orden de creación respeta dependencias de FK.
-- Creation order respects FK dependencies.
-- =====================================================================

-- 1. Crear schema
CREATE SCHEMA IF NOT EXISTS veterinaria;

-- =====================================================================
-- TABLAS INDEPENDIENTES (sin FK / no FK)
-- =====================================================================

-- Usuario
CREATE TABLE IF NOT EXISTS veterinaria.usuario (
    idusuario   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cc          VARCHAR(150) NOT NULL,
    nombre      VARCHAR(150) NOT NULL,
    apellidos   VARCHAR(150) NOT NULL,
    usuario     VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    contrasena  VARCHAR(150) NOT NULL,
    telefono    VARCHAR(150) NOT NULL,
    direccion   VARCHAR(150) NOT NULL
);

-- Módulo del sistema
CREATE TABLE IF NOT EXISTS veterinaria.modulo (
    idmodulo        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombremodulo    VARCHAR NOT NULL UNIQUE,
    descripcion     VARCHAR,
    icono           VARCHAR,
    orden           INTEGER
);

-- Cliente
CREATE TABLE IF NOT EXISTS veterinaria.cliente (
    idcliente                   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre                      VARCHAR(150) NOT NULL,
    apellidos                   VARCHAR(150) NOT NULL,
    tipopersona                 VARCHAR(150) NOT NULL,
    cc                          VARCHAR(150) NOT NULL,
    fechanacimiento             DATE         NOT NULL,
    email                       VARCHAR(150) NOT NULL,
    direccion                   VARCHAR(150) NOT NULL,
    telefono                    VARCHAR(150) NOT NULL,
    nombrecontactoemergencia    VARCHAR(150) NOT NULL,
    telefonocontactoemergencia  VARCHAR(150) NOT NULL,
    ciudad                      VARCHAR(150) NOT NULL,
    tipodocumento               VARCHAR(150) NOT NULL
);

-- Marca
CREATE TABLE IF NOT EXISTS veterinaria.marca (
    idmarca     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombremarca VARCHAR(150) NOT NULL,
    descripcion VARCHAR      NOT NULL
);

-- Unidad de Medida
CREATE TABLE IF NOT EXISTS veterinaria.unidadmedida (
    idunidadmedida  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre          VARCHAR(150) NOT NULL
);

-- Tipo de Producto
CREATE TABLE IF NOT EXISTS veterinaria.tipoproducto (
    idtipoproducto  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "nombreTipo"    VARCHAR(150) NOT NULL
);

-- Permiso (legacy — permisos basados en arrays de texto)
CREATE TABLE IF NOT EXISTS veterinaria.permiso (
    idpermiso   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    modulos     TEXT         NOT NULL,
    descripcion VARCHAR(150) NOT NULL
);

-- Proveedor
CREATE TABLE IF NOT EXISTS veterinaria.proveedor (
    idproveedor     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre          VARCHAR(150) NOT NULL,
    apellido        VARCHAR(150) NOT NULL,
    tipopersona     VARCHAR(150) NOT NULL,
    nit_rut         VARCHAR(150) NOT NULL,
    direccion       VARCHAR(150) NOT NULL,
    telefono        VARCHAR(150) NOT NULL,
    email           VARCHAR(150) NOT NULL,
    ciudad          VARCHAR(150) NOT NULL,
    tipodocumento   VARCHAR      NOT NULL
);

-- Servicio
CREATE TABLE IF NOT EXISTS veterinaria.servicio (
    idservicio      INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombreservicio  VARCHAR(150) NOT NULL,
    descripcion     VARCHAR(150) NOT NULL,
    precio          NUMERIC      NOT NULL,
    CONSTRAINT uq_servicio_id UNIQUE (idservicio)
);

-- =====================================================================
-- TABLAS CON FK (nivel 1)
-- =====================================================================

-- Mascota (FK -> cliente, nullable)
CREATE TABLE IF NOT EXISTS veterinaria.mascota (
    idmascota           INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre              VARCHAR(150) NOT NULL,
    raza                VARCHAR(150) NOT NULL,
    especie             VARCHAR(150) NOT NULL,
    sexo                VARCHAR(10)  NOT NULL,
    numerochip          VARCHAR(150) NOT NULL,
    edad                INTEGER      NOT NULL,
    cliente_idcliente   INTEGER,
    CONSTRAINT uq_mascota_id UNIQUE (idmascota),
    CONSTRAINT fk_mascota_cliente1 FOREIGN KEY (cliente_idcliente)
        REFERENCES veterinaria.cliente(idcliente)
);

-- Producto (FK -> marca, usuario, unidadmedida, tipoproducto)
-- Usa herencia por columnas (Single Table Inheritance).
-- Los campos lote, fechaVencimiento, etc. aplican a subtipos
-- Medicamento, Alimento y MaterialQuirurgico; para Accesorio se dejan NULL.
CREATE TABLE IF NOT EXISTS veterinaria.producto (
    idproducto                      INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre                          VARCHAR(150) NOT NULL,
    precio                          NUMERIC      NOT NULL,
    costo                           NUMERIC      NOT NULL,
    referencia                      VARCHAR(150) NOT NULL,
    codigobarras                    VARCHAR(150) NOT NULL,
    stock                           NUMERIC      NOT NULL,
    descripcion                     VARCHAR(150) NOT NULL,
    unidadesingresadas              INTEGER      NOT NULL,
    lote                            VARCHAR(150),
    "fechaVencimiento"              DATE,
    semanaalerta                    INTEGER,
    fraccionado                     BOOLEAN,
    fraccionable                    BOOLEAN,
    contenido                       NUMERIC,
    tipoproducto_idtipoproducto     INTEGER      NOT NULL,
    usuario_idusuario               INTEGER,
    marca_idmarca                   INTEGER      NOT NULL,
    unidadmedida_idunidadmedida     INTEGER,
    imagenproducto                  BYTEA        NOT NULL,
    estado                          VARCHAR      NOT NULL,
    "dosisUnidad"                   VARCHAR,
    CONSTRAINT uq_producto_idproducto UNIQUE (idproducto),
    CONSTRAINT fk_producto_tipoproducto1 FOREIGN KEY (tipoproducto_idtipoproducto)
        REFERENCES veterinaria.tipoproducto(idtipoproducto),
    CONSTRAINT fk_producto_marca1 FOREIGN KEY (marca_idmarca)
        REFERENCES veterinaria.marca(idmarca),
    CONSTRAINT fk_producto_usuario1 FOREIGN KEY (usuario_idusuario)
        REFERENCES veterinaria.usuario(idusuario),
    CONSTRAINT fk_producto_unidadmedida1 FOREIGN KEY (unidadmedida_idunidadmedida)
        REFERENCES veterinaria.unidadmedida(idunidadmedida)
);

-- Venta (FK -> cliente, usuario)
CREATE TABLE IF NOT EXISTS veterinaria.venta (
    idventa             INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    fecha               DATE    NOT NULL,
    totalventa          NUMERIC NOT NULL,
    usuario_idusuario   INTEGER,
    cliente_idcliente   INTEGER NOT NULL,
    CONSTRAINT uq_venta_id UNIQUE (idventa),
    CONSTRAINT fk_venta_cliente1 FOREIGN KEY (cliente_idcliente)
        REFERENCES veterinaria.cliente(idcliente),
    CONSTRAINT fk_venta_usuario FOREIGN KEY (usuario_idusuario)
        REFERENCES veterinaria.usuario(idusuario)
);

-- Historial Clínico (FK -> mascota) — PK compuesta
CREATE TABLE IF NOT EXISTS veterinaria.historialclinica (
    idhistorialclinica  INTEGER GENERATED BY DEFAULT AS IDENTITY,
    observaciones       VARCHAR(150) NOT NULL,
    mascota_idmascota   INTEGER      NOT NULL,
    PRIMARY KEY (idhistorialclinica, mascota_idmascota),
    CONSTRAINT uq_historialclinica_id UNIQUE (idhistorialclinica),
    CONSTRAINT fk_historialclinica_mascota1 FOREIGN KEY (mascota_idmascota)
        REFERENCES veterinaria.mascota(idmascota)
);

-- Password Reset Token (FK -> usuario)
CREATE TABLE IF NOT EXISTS veterinaria.password_reset_token (
    idtoken             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    usuario_idusuario   INTEGER                  NOT NULL,
    email               VARCHAR                  NOT NULL,
    codigo_otp          VARCHAR(6)               NOT NULL,
    fecha_creacion      TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_expiracion    TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    usado               BOOLEAN                  NOT NULL DEFAULT FALSE,
    CONSTRAINT fk_password_reset_usuario FOREIGN KEY (usuario_idusuario)
        REFERENCES veterinaria.usuario(idusuario) ON DELETE CASCADE
);

-- =====================================================================
-- TABLAS PUENTE (Many-to-Many) Y DETALLE
-- =====================================================================

-- Usuario <-> Módulo (permisos modulares)
CREATE TABLE IF NOT EXISTS veterinaria.usuario_modulo (
    idusuario_modulo    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    usuario_idusuario   INTEGER NOT NULL,
    modulo_idmodulo     INTEGER NOT NULL,
    fecha_asignacion    TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_usuario_modulo UNIQUE (usuario_idusuario, modulo_idmodulo),
    CONSTRAINT fk_usuario_modulo_usuario FOREIGN KEY (usuario_idusuario)
        REFERENCES veterinaria.usuario(idusuario) ON DELETE CASCADE,
    CONSTRAINT fk_usuario_modulo_modulo FOREIGN KEY (modulo_idmodulo)
        REFERENCES veterinaria.modulo(idmodulo) ON DELETE CASCADE
);

-- Usuario <-> Permiso (legacy)
CREATE TABLE IF NOT EXISTS veterinaria.usuariopermiso (
    idusuariopermiso    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    permiso_idpermiso   INTEGER NOT NULL,
    usuario_idusuario   INTEGER NOT NULL,
    PRIMARY KEY (idusuariopermiso, permiso_idpermiso, usuario_idusuario),
    CONSTRAINT fk_usuario_has_permiso_permiso1 FOREIGN KEY (permiso_idpermiso)
        REFERENCES veterinaria.permiso(idpermiso),
    CONSTRAINT fk_usuario_has_permiso_usuario1 FOREIGN KEY (usuario_idusuario)
        REFERENCES veterinaria.usuario(idusuario)
);

-- Proveedor <-> Producto (relación N:N)
CREATE TABLE IF NOT EXISTS veterinaria.proveedor_has_producto (
    idproveedor_has_producto INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producto_idproducto      INTEGER NOT NULL,
    proveedor_idproveedor    INTEGER NOT NULL,
    CONSTRAINT fk_proveedor_has_producto_producto1 FOREIGN KEY (producto_idproducto)
        REFERENCES veterinaria.producto(idproducto),
    CONSTRAINT fk_proveedor_has_producto_proveedor1 FOREIGN KEY (proveedor_idproveedor)
        REFERENCES veterinaria.proveedor(idproveedor)
);

-- Historial Clínico <-> Servicio (relación N:N) — Nombres mixtos originales
CREATE TABLE IF NOT EXISTS veterinaria."HistorialClinica_has_Servicio" (
    "idHistorialClinica_has_Servicio"       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    "HistorialClinica_idHistorialClinica"    INTEGER NOT NULL,
    "Servicio_idServicio"                   INTEGER NOT NULL,
    PRIMARY KEY ("idHistorialClinica_has_Servicio", "HistorialClinica_idHistorialClinica", "Servicio_idServicio"),
    CONSTRAINT fk_has_servicio_historialclinica FOREIGN KEY ("HistorialClinica_idHistorialClinica")
        REFERENCES veterinaria.historialclinica(idhistorialclinica),
    CONSTRAINT fk_has_servicio_servicio FOREIGN KEY ("Servicio_idServicio")
        REFERENCES veterinaria.servicio(idservicio)
);

-- Línea de Venta (FK -> venta, producto, servicio)
CREATE TABLE IF NOT EXISTS veterinaria.lineaventa (
    idlineaventa            INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cantidad                INTEGER NOT NULL,
    subtotal                NUMERIC NOT NULL,
    venta_idventa           INTEGER NOT NULL,
    producto_idproducto     INTEGER,
    servicio_idservicio     INTEGER,
    valor                   NUMERIC NOT NULL,
    CONSTRAINT fk_lineaventa_venta FOREIGN KEY (venta_idventa)
        REFERENCES veterinaria.venta(idventa) ON DELETE CASCADE,
    CONSTRAINT fk_lineaventa_producto FOREIGN KEY (producto_idproducto)
        REFERENCES veterinaria.producto(idproducto) ON DELETE SET NULL,
    CONSTRAINT fk_lineaventa_servicio FOREIGN KEY (servicio_idservicio)
        REFERENCES veterinaria.servicio(idservicio) ON DELETE SET NULL
);

-- =====================================================================
-- ✅ Schema creado — Ejecuta seed_data.sql para datos de prueba
-- ✅ Schema created — Run seed_data.sql for sample data
-- =====================================================================