bmc_hub/app/modules/_template/migrations/001_init.sql

-- Template Module - Initial Migration
-- Opret basis tabeller for template module

-- Items tabel (eksempel)
CREATE TABLE IF NOT EXISTS template_items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Optional: Customers tabel hvis modulet har egne kunder (f.eks. sync fra eksternt system)
-- Kun nødvendigt hvis modulet har mange custom felter eller external sync
-- Ellers brug direkte foreign key til customers.id
CREATE TABLE IF NOT EXISTS template_customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    external_id VARCHAR(100),  -- ID fra eksternt system hvis relevant
    hub_customer_id INTEGER REFERENCES customers(id),  -- VIGTIG: Link til core customers
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for performance
CREATE INDEX IF NOT EXISTS idx_template_items_active ON template_items(active);
CREATE INDEX IF NOT EXISTS idx_template_items_created ON template_items(created_at DESC);

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION update_template_items_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_template_items_updated_at
BEFORE UPDATE ON template_items
FOR EACH ROW
EXECUTE FUNCTION update_template_items_updated_at();

-- Trigger for auto-linking customers (hvis template_customers tabel oprettes)
-- Dette linker automatisk nye kunder til core customers baseret på navn match
CREATE OR REPLACE FUNCTION auto_link_template_customer()
RETURNS TRIGGER AS $$
DECLARE
    matched_hub_id INTEGER;
BEGIN
    -- Hvis hub_customer_id allerede er sat, skip
    IF NEW.hub_customer_id IS NOT NULL THEN
        RETURN NEW;
    END IF;
    
    -- Find matching hub customer baseret på navn
    SELECT id INTO matched_hub_id
    FROM customers
    WHERE LOWER(TRIM(name)) = LOWER(TRIM(NEW.name))
    LIMIT 1;
    
    -- Hvis match fundet, sæt hub_customer_id
    IF matched_hub_id IS NOT NULL THEN
        NEW.hub_customer_id := matched_hub_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_link_template_customer
    BEFORE INSERT OR UPDATE OF name
    ON template_customers
    FOR EACH ROW
    EXECUTE FUNCTION auto_link_template_customer();

-- Indsæt test data (optional)
INSERT INTO template_items (name, description) 
VALUES 
    ('Test Item 1', 'This is a test item from template module'),
    ('Test Item 2', 'Another test item')
ON CONFLICT DO NOTHING;