bmc_hub/migrations/070_locations_module.sql

-- Migration: 070_locations_module
-- Created: 2026-01-31
-- Version: Location Module v1.0
-- Description: Establishes complete schema for Location (Lokaliteter) Module

-- Table 1: locations_locations
-- Primary use: Store all physical locations (branches, warehouses, service centers, client sites)
-- Soft deletes: true
CREATE TABLE IF NOT EXISTS locations_locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  location_type VARCHAR(50) NOT NULL CHECK(location_type IN ('branch', 'warehouse', 'service_center', 'client_site')),
  address_street VARCHAR(255),
  address_city VARCHAR(100),
  address_postal_code VARCHAR(20),
  address_country VARCHAR(100) NOT NULL DEFAULT 'DK',
  latitude NUMERIC(10, 8),
  longitude NUMERIC(11, 8),
  phone VARCHAR(20),
  email VARCHAR(255),
  notes TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by_user_id INTEGER,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

CREATE INDEX idx_locations_name ON locations_locations(name);
CREATE INDEX idx_locations_location_type ON locations_locations(location_type);
CREATE INDEX idx_locations_is_active ON locations_locations(is_active);
CREATE INDEX idx_locations_deleted_at ON locations_locations(deleted_at);

-- Table 2: locations_contacts
-- Primary use: Store contact persons associated with each location
-- Soft deletes: true
CREATE TABLE IF NOT EXISTS locations_contacts (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
  contact_name VARCHAR(255) NOT NULL,
  contact_email VARCHAR(255),
  contact_phone VARCHAR(20),
  role VARCHAR(100),
  is_primary BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

CREATE INDEX idx_locations_contacts_location_id ON locations_contacts(location_id);
CREATE INDEX idx_locations_contacts_is_primary ON locations_contacts(is_primary);
CREATE INDEX idx_locations_contacts_deleted_at ON locations_contacts(deleted_at);

-- Table 3: locations_hours
-- Primary use: Store operating hours for each location (day of week basis)
-- Soft deletes: false
CREATE TABLE IF NOT EXISTS locations_hours (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
  day_of_week INTEGER NOT NULL CHECK(day_of_week >= 0 AND day_of_week <= 6),
  open_time TIME,
  close_time TIME,
  is_open BOOLEAN NOT NULL DEFAULT true,
  notes VARCHAR(255)
);

CREATE INDEX idx_locations_hours_location_id ON locations_hours(location_id);
CREATE INDEX idx_locations_hours_day_of_week ON locations_hours(location_id, day_of_week);

-- Table 4: locations_services
-- Primary use: Track services offered at each location
-- Soft deletes: true
CREATE TABLE IF NOT EXISTS locations_services (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
  service_name VARCHAR(255) NOT NULL,
  is_available BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

CREATE INDEX idx_locations_services_location_id ON locations_services(location_id);
CREATE INDEX idx_locations_services_deleted_at ON locations_services(deleted_at);

-- Table 5: locations_capacity
-- Primary use: Track capacity utilization per location (racks, storage, floor space)
-- Soft deletes: false
CREATE TABLE IF NOT EXISTS locations_capacity (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
  capacity_type VARCHAR(100) NOT NULL,
  total_capacity DECIMAL(10, 2) NOT NULL,
  used_capacity DECIMAL(10, 2) NOT NULL DEFAULT 0,
  last_updated TIMESTAMP NOT NULL DEFAULT NOW(),
  CHECK(used_capacity >= 0 AND used_capacity <= total_capacity)
);

CREATE INDEX idx_locations_capacity_location_id ON locations_capacity(location_id);
CREATE INDEX idx_locations_capacity_capacity_type ON locations_capacity(capacity_type);

-- Table 6: locations_audit_log
-- Primary use: Track all changes to locations and related data for compliance and debugging
-- Soft deletes: false
CREATE TABLE IF NOT EXISTS locations_audit_log (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
  event_type VARCHAR(50) NOT NULL,
  user_id INTEGER,
  changes JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_locations_audit_log_location_id ON locations_audit_log(location_id);
CREATE INDEX idx_locations_audit_log_event_type ON locations_audit_log(event_type);
CREATE INDEX idx_locations_audit_log_created_at ON locations_audit_log(created_at);

-- Trigger: Update updated_at column on locations_locations
CREATE TRIGGER update_locations_locations_updated_at
BEFORE UPDATE ON locations_locations
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- Add comments for documentation
COMMENT ON TABLE locations_locations IS 'Main table storing all physical locations (branches, warehouses, service centers, client sites)';
COMMENT ON TABLE locations_contacts IS 'Contact persons associated with each location (managers, technicians, administrators)';
COMMENT ON TABLE locations_hours IS 'Operating hours for each location by day of week (0=Monday, 6=Sunday)';
COMMENT ON TABLE locations_services IS 'Services offered at each location';
COMMENT ON TABLE locations_capacity IS 'Capacity tracking for locations (rack units, square meters, storage boxes, etc.)';
COMMENT ON TABLE locations_audit_log IS 'Audit trail for all location changes (created, updated, deleted, contact_added, service_added, etc.)';

COMMENT ON COLUMN locations_locations.location_type IS 'Type of location: branch, warehouse, service_center, or client_site';
COMMENT ON COLUMN locations_locations.address_country IS 'Country code, defaults to DK (Denmark)';
COMMENT ON COLUMN locations_locations.latitude IS 'GPS latitude with 8 decimal places precision (approx 1.1mm)';
COMMENT ON COLUMN locations_locations.longitude IS 'GPS longitude with 8 decimal places precision (approx 1.1mm)';
COMMENT ON COLUMN locations_locations.deleted_at IS 'Soft delete timestamp; NULL means active location';

COMMENT ON COLUMN locations_contacts.is_primary IS 'TRUE for primary contact person at location';
COMMENT ON COLUMN locations_contacts.deleted_at IS 'Soft delete timestamp; NULL means active contact';

COMMENT ON COLUMN locations_hours.day_of_week IS '0=Monday, 1=Tuesday, 2=Wednesday, 3=Thursday, 4=Friday, 5=Saturday, 6=Sunday';
COMMENT ON COLUMN locations_hours.is_open IS 'TRUE if location is open on this day; FALSE if closed';

COMMENT ON COLUMN locations_services.is_available IS 'TRUE if service is currently available; FALSE if temporarily unavailable';
COMMENT ON COLUMN locations_services.deleted_at IS 'Soft delete timestamp; NULL means service is active at this location';

COMMENT ON COLUMN locations_capacity.capacity_type IS 'Type of capacity: rack_units, square_meters, storage_boxes, parking_spaces, etc.';
COMMENT ON COLUMN locations_capacity.total_capacity IS 'Total available capacity';
COMMENT ON COLUMN locations_capacity.used_capacity IS 'Currently utilized capacity; must be >= 0 and <= total_capacity';

COMMENT ON COLUMN locations_audit_log.event_type IS 'Type of event: created, updated, deleted, contact_added, contact_removed, service_added, service_removed, hours_updated, capacity_updated, etc.';
COMMENT ON COLUMN locations_audit_log.changes IS 'JSONB object storing before/after values of modified fields';

-- Migration 070_locations_module.sql completed successfully