PermisosQR Database Schema, Indexes, and Seed Data

const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME || 'permisosqr',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || '',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'admin_operator'
    CHECK (role IN ('super_admin', 'admin_operator')),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS qr_codes (
  id SERIAL PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'available'
    CHECK (status IN ('available', 'active', 'expired', 'disabled')),
  created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS permissions (
  id SERIAL PRIMARY KEY,
  qr_id INTEGER NOT NULL REFERENCES qr_codes(id) ON DELETE CASCADE,
  enabled_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  received_by VARCHAR(150),
  returned_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  allowed_minutes INTEGER NOT NULL DEFAULT 15,
  exit_time TIMESTAMP WITH TIME ZONE,
  return_time TIMESTAMP WITH TIME ZONE,
  time_used_minutes DECIMAL(10, 2),
  delay_minutes DECIMAL(10, 2),
  is_compliant BOOLEAN,
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_qr_codes_status       ON qr_codes(status);
CREATE INDEX IF NOT EXISTS idx_qr_codes_created_by   ON qr_codes(created_by);
CREATE INDEX IF NOT EXISTS idx_permissions_qr_id     ON permissions(qr_id);
CREATE INDEX IF NOT EXISTS idx_permissions_enabled_by ON permissions(enabled_by);
CREATE INDEX IF NOT EXISTS idx_permissions_exit_time  ON permissions(exit_time);
CREATE INDEX IF NOT EXISTS idx_permissions_created_at ON permissions(created_at);

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_qr_codes_updated_at
  BEFORE UPDATE ON qr_codes
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_permissions_updated_at
  BEFORE UPDATE ON permissions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

psql -U postgres -d permisosqr -f base/seed.sql

-- Seed preview (from base/seed.sql)
INSERT INTO users (name, email, password_hash, role) VALUES
  ('Edgar Rojas Apaza',   'admin@permisosqr.com',    '$2b$10$...', 'super_admin'),
  ('Operador Principal',  'operador@permisosqr.com', '$2b$10$...', 'admin_operator')
ON CONFLICT (email) DO NOTHING;

INSERT INTO qr_codes (status, created_by)
SELECT 'available', 1 FROM generate_series(1, 20)
WHERE NOT EXISTS (SELECT 1 FROM qr_codes LIMIT 1);