bmc_hub/migrations/013_timetracking_module.sql

-- ============================================================================
-- Migration 013: Tidsregistrering & Faktureringsmodul (Isoleret)
-- ============================================================================
-- Dette modul er 100% isoleret og kan slettes uden at påvirke eksisterende data.
-- Alle tabeller har prefix 'tmodule_' for at markere tilhørsforhold til modulet.
-- Ved uninstall køres DROP-scriptet i bunden af denne fil.
-- ============================================================================

-- Metadata tabel til at tracke modulets tilstand
CREATE TABLE IF NOT EXISTS tmodule_metadata (
    id SERIAL PRIMARY KEY,
    module_version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
    installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    installed_by INTEGER,  -- Reference til users.id (read-only, ingen FK)
    last_sync_at TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    settings JSONB DEFAULT '{}'::jsonb
);

-- Indsæt initial metadata
INSERT INTO tmodule_metadata (module_version, is_active) 
VALUES ('1.0.0', true)
ON CONFLICT DO NOTHING;

-- ============================================================================
-- KUNDE-CACHE (read-only kopi fra vTiger for isolation)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_customers (
    id SERIAL PRIMARY KEY,
    vtiger_id VARCHAR(50) UNIQUE NOT NULL,  -- vTiger Account ID
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    hub_customer_id INTEGER,  -- Reference til customers.id (OPTIONAL, read-only)
    hourly_rate DECIMAL(10,2),  -- Kan override Hub-rate
    uses_time_card BOOLEAN DEFAULT false,  -- Klippekort - faktureres eksternt
    vtiger_data JSONB,  -- Original vTiger data for reference
    sync_hash VARCHAR(64),  -- SHA256 af data for change detection
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tmodule_customers_vtiger ON tmodule_customers(vtiger_id);
CREATE INDEX idx_tmodule_customers_hub ON tmodule_customers(hub_customer_id);
CREATE INDEX idx_tmodule_customers_synced ON tmodule_customers(last_synced_at);

-- ============================================================================
-- CASE-CACHE (read-only kopi fra vTiger HelpDesk/ProjectTask)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_cases (
    id SERIAL PRIMARY KEY,
    vtiger_id VARCHAR(50) UNIQUE NOT NULL,  -- vTiger HelpDesk/ProjectTask ID
    customer_id INTEGER NOT NULL REFERENCES tmodule_customers(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    status VARCHAR(50),
    priority VARCHAR(50),
    module_type VARCHAR(50),  -- HelpDesk, ProjectTask, etc.
    vtiger_data JSONB,
    sync_hash VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tmodule_cases_vtiger ON tmodule_cases(vtiger_id);
CREATE INDEX idx_tmodule_cases_customer ON tmodule_cases(customer_id);
CREATE INDEX idx_tmodule_cases_status ON tmodule_cases(status);
CREATE INDEX idx_tmodule_cases_synced ON tmodule_cases(last_synced_at);

-- ============================================================================
-- TIDSREGISTRERINGER (read-only kopi fra vTiger ModComments)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_times (
    id SERIAL PRIMARY KEY,
    vtiger_id VARCHAR(50) UNIQUE NOT NULL,  -- vTiger ModComments ID
    case_id INTEGER NOT NULL REFERENCES tmodule_cases(id) ON DELETE CASCADE,
    customer_id INTEGER NOT NULL REFERENCES tmodule_customers(id) ON DELETE CASCADE,
    
    -- Original vTiger data
    description TEXT,
    original_hours DECIMAL(5,2) NOT NULL,
    worked_date DATE,
    user_name VARCHAR(255),  -- vTiger user (read-only)
    
    -- Godkendelsesdata (ændres kun i modulet)
    status VARCHAR(20) DEFAULT 'pending',  -- pending|approved|rejected|billed
    approved_hours DECIMAL(5,2),
    rounded_to DECIMAL(3,1),  -- 0.5, 1.0, etc.
    approval_note TEXT,
    billable BOOLEAN DEFAULT true,
    approved_at TIMESTAMP,
    approved_by INTEGER,  -- Reference til users.id (read-only)
    
    -- Metadata
    vtiger_data JSONB,
    sync_hash VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT check_hours_positive CHECK (original_hours > 0),
    CONSTRAINT check_approved_hours CHECK (approved_hours IS NULL OR approved_hours > 0),
    CONSTRAINT check_status CHECK (status IN ('pending', 'approved', 'rejected', 'billed'))
);

CREATE INDEX idx_tmodule_times_vtiger ON tmodule_times(vtiger_id);
CREATE INDEX idx_tmodule_times_case ON tmodule_times(case_id);
CREATE INDEX idx_tmodule_times_customer ON tmodule_times(customer_id);
CREATE INDEX idx_tmodule_times_status ON tmodule_times(status);
CREATE INDEX idx_tmodule_times_date ON tmodule_times(worked_date);
CREATE INDEX idx_tmodule_times_approved_by ON tmodule_times(approved_by);

-- ============================================================================
-- ORDRER (genereret fra godkendte tider)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES tmodule_customers(id) ON DELETE CASCADE,
    hub_customer_id INTEGER,  -- Reference til customers.id (read-only)
    
    -- Order metadata
    order_number VARCHAR(50),  -- Auto-generated: TT-YYYYMMDD-XXX
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'draft',  -- draft|exported|sent|cancelled
    
    -- Beløb
    total_hours DECIMAL(8,2) NOT NULL DEFAULT 0,
    hourly_rate DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    vat_rate DECIMAL(5,2) DEFAULT 25.00,  -- Danish VAT
    vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    
    -- e-conomic integration
    economic_draft_id INTEGER,
    economic_order_number VARCHAR(50),
    exported_at TIMESTAMP,
    exported_by INTEGER,  -- Reference til users.id (read-only)
    
    -- Metadata
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    created_by INTEGER,  -- Reference til users.id (read-only)
    
    CONSTRAINT check_total_hours CHECK (total_hours >= 0),
    CONSTRAINT check_amounts CHECK (subtotal >= 0 AND vat_amount >= 0 AND total_amount >= 0),
    CONSTRAINT check_status CHECK (status IN ('draft', 'exported', 'sent', 'cancelled'))
);

CREATE INDEX idx_tmodule_orders_customer ON tmodule_orders(customer_id);
CREATE INDEX idx_tmodule_orders_status ON tmodule_orders(status);
CREATE INDEX idx_tmodule_orders_date ON tmodule_orders(order_date);
CREATE INDEX idx_tmodule_orders_economic ON tmodule_orders(economic_draft_id);
CREATE UNIQUE INDEX idx_tmodule_orders_number ON tmodule_orders(order_number) WHERE order_number IS NOT NULL;

-- ============================================================================
-- ORDRE-LINJER (detaljer pr. case eller gruppering)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_order_lines (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES tmodule_orders(id) ON DELETE CASCADE,
    case_id INTEGER REFERENCES tmodule_cases(id) ON DELETE SET NULL,
    
    -- Linje-detaljer
    line_number INTEGER NOT NULL,
    description TEXT NOT NULL,
    quantity DECIMAL(8,2) NOT NULL,  -- Timer
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    
    -- Reference til tidsregistreringer
    time_entry_ids INTEGER[],  -- Array af tmodule_times.id
    
    -- e-conomic mapping
    product_number VARCHAR(50),
    account_number VARCHAR(50),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT check_line_number CHECK (line_number > 0),
    CONSTRAINT check_quantity CHECK (quantity > 0),
    CONSTRAINT check_amounts_line CHECK (unit_price >= 0 AND line_total >= 0)
);

CREATE INDEX idx_tmodule_order_lines_order ON tmodule_order_lines(order_id);
CREATE INDEX idx_tmodule_order_lines_case ON tmodule_order_lines(case_id);

-- ============================================================================
-- AUDIT LOG (fuld sporbarhed af alle handlinger)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tmodule_sync_log (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,  -- sync_started|sync_completed|approval|rejection|export|uninstall
    entity_type VARCHAR(50),  -- time_entry|order|customer|case
    entity_id INTEGER,
    user_id INTEGER,  -- Reference til users.id (read-only)
    
    -- Event-specifik data
    details JSONB,
    
    -- Metadata
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT check_event_type CHECK (event_type IN (
        'sync_started', 'sync_completed', 'sync_failed',
        'approval', 'rejection', 'bulk_approval',
        'order_created', 'order_updated', 'order_cancelled',
        'export_started', 'export_completed', 'export_failed',
        'module_installed', 'module_uninstalled'
    ))
);

CREATE INDEX idx_tmodule_sync_log_event ON tmodule_sync_log(event_type);
CREATE INDEX idx_tmodule_sync_log_entity ON tmodule_sync_log(entity_type, entity_id);
CREATE INDEX idx_tmodule_sync_log_user ON tmodule_sync_log(user_id);
CREATE INDEX idx_tmodule_sync_log_created ON tmodule_sync_log(created_at DESC);

-- ============================================================================
-- TRIGGERS FOR AUTO-UPDATE TIMESTAMPS
-- ============================================================================

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

CREATE TRIGGER tmodule_customers_update 
    BEFORE UPDATE ON tmodule_customers 
    FOR EACH ROW EXECUTE FUNCTION tmodule_update_timestamp();

CREATE TRIGGER tmodule_cases_update 
    BEFORE UPDATE ON tmodule_cases 
    FOR EACH ROW EXECUTE FUNCTION tmodule_update_timestamp();

CREATE TRIGGER tmodule_times_update 
    BEFORE UPDATE ON tmodule_times 
    FOR EACH ROW EXECUTE FUNCTION tmodule_update_timestamp();

CREATE TRIGGER tmodule_orders_update 
    BEFORE UPDATE ON tmodule_orders 
    FOR EACH ROW EXECUTE FUNCTION tmodule_update_timestamp();

-- ============================================================================
-- AUTO-GENERATE ORDER NUMBERS
-- ============================================================================

CREATE OR REPLACE FUNCTION tmodule_generate_order_number()
RETURNS TRIGGER AS $$
DECLARE
    date_prefix VARCHAR(8);
    seq_num INTEGER;
    new_number VARCHAR(50);
BEGIN
    IF NEW.order_number IS NULL THEN
        -- Format: TT-YYYYMMDD-XXX
        date_prefix := TO_CHAR(CURRENT_DATE, 'YYYYMMDD');
        
        -- Find næste sekvensnummer for dagen
        SELECT COALESCE(MAX(
            CAST(SUBSTRING(order_number FROM 'TT-\d{8}-(\d+)') AS INTEGER)
        ), 0) + 1
        INTO seq_num
        FROM tmodule_orders
        WHERE order_number LIKE 'TT-' || date_prefix || '-%';
        
        new_number := 'TT-' || date_prefix || '-' || LPAD(seq_num::TEXT, 3, '0');
        NEW.order_number := new_number;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tmodule_orders_generate_number
    BEFORE INSERT ON tmodule_orders
    FOR EACH ROW EXECUTE FUNCTION tmodule_generate_order_number();

-- ============================================================================
-- VIEWS FOR COMMON QUERIES
-- ============================================================================

-- Oversigt over godkendelsesstatus pr. kunde
CREATE OR REPLACE VIEW tmodule_approval_stats AS
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    c.vtiger_id AS customer_vtiger_id,
    c.uses_time_card AS uses_time_card,
    COUNT(t.id) FILTER (WHERE t.billable = true AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS total_entries,
    COUNT(t.id) FILTER (WHERE t.billable = true AND t.status = 'pending' AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS pending_count,
    COUNT(t.id) FILTER (WHERE t.billable = true AND t.status = 'approved' AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS approved_count,
    COUNT(t.id) FILTER (WHERE t.billable = true AND t.status = 'rejected' AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS rejected_count,
    COUNT(t.id) FILTER (WHERE t.billable = true AND t.status = 'billed' AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS billed_count,
    SUM(t.original_hours) FILTER (WHERE t.billable = true AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS total_original_hours,
    SUM(t.approved_hours) FILTER (WHERE t.billable = true AND t.status = 'approved' AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS total_approved_hours,
    MAX(t.worked_date) FILTER (WHERE t.billable = true AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS latest_work_date,
    MAX(t.last_synced_at) FILTER (WHERE t.billable = true AND t.vtiger_data->>'cf_timelog_invoiced' = '0') AS last_sync
FROM tmodule_customers c
LEFT JOIN tmodule_times t ON c.id = t.customer_id
GROUP BY c.id, c.name, c.vtiger_id, c.uses_time_card;

-- Næste tid der skal godkendes (wizard helper)
CREATE OR REPLACE VIEW tmodule_next_pending AS
SELECT 
    t.*,
    COALESCE(c.vtiger_data->>'case_no', c.title)::VARCHAR(500) AS case_title,
    c.status AS case_status,
    c.vtiger_id AS case_vtiger_id,
    cust.name AS customer_name,
    cust.hourly_rate AS customer_rate
FROM tmodule_times t
JOIN tmodule_cases c ON t.case_id = c.id
JOIN tmodule_customers cust ON t.customer_id = cust.id
WHERE t.status = 'pending' 
  AND t.billable = true  -- Only billable timelogs
  AND t.vtiger_data->>'cf_timelog_invoiced' = '0'  -- Only not-invoiced timelogs
ORDER BY cust.name, c.title, t.worked_date;

-- Order summary med linjer
CREATE OR REPLACE VIEW tmodule_order_details AS
SELECT 
    o.id AS order_id,
    o.order_number,
    o.order_date,
    o.status AS order_status,
    o.total_hours,
    o.total_amount,
    o.economic_draft_id,
    c.name AS customer_name,
    c.vtiger_id AS customer_vtiger_id,
    COUNT(DISTINCT l.id) AS line_count,
    COUNT(DISTINCT t.id) AS time_entry_count
FROM tmodule_orders o
JOIN tmodule_customers c ON o.customer_id = c.id
LEFT JOIN tmodule_order_lines l ON o.id = l.order_id
LEFT JOIN tmodule_times t ON t.id = ANY(l.time_entry_ids)
GROUP BY o.id, o.order_number, o.order_date, o.status, o.total_hours, 
         o.total_amount, o.economic_draft_id, c.name, c.vtiger_id;

-- ============================================================================
-- COMMENTS FOR DOCUMENTATION
-- ============================================================================

COMMENT ON TABLE tmodule_metadata IS 'Metadata og konfiguration for tidsregistreringsmodulet';
COMMENT ON TABLE tmodule_customers IS 'Read-only cache af vTiger kunder (isoleret kopi)';
COMMENT ON TABLE tmodule_cases IS 'Read-only cache af vTiger cases/projekter (isoleret kopi)';
COMMENT ON TABLE tmodule_times IS 'Tidsregistreringer importeret fra vTiger med godkendelsesstatus';
COMMENT ON TABLE tmodule_orders IS 'Genererede ordrer fra godkendte tider';
COMMENT ON TABLE tmodule_order_lines IS 'Ordre-linjer med reference til tidsregistreringer';
COMMENT ON TABLE tmodule_sync_log IS 'Fuld audit log af alle modulhandlinger';

COMMENT ON COLUMN tmodule_times.status IS 'pending=Afventer godkendelse, approved=Godkendt, rejected=Afvist, billed=Faktureret';
COMMENT ON COLUMN tmodule_times.approved_hours IS 'Timer efter brugerens godkendelse og evt. afrunding';
COMMENT ON COLUMN tmodule_times.rounded_to IS 'Afrundingsinterval brugt (0.5, 1.0, etc.)';
COMMENT ON COLUMN tmodule_orders.status IS 'draft=Kladde, exported=Sendt til e-conomic, sent=Sendt til kunde, cancelled=Annulleret';

-- ============================================================================
-- INITIAL DATA LOG
-- ============================================================================

INSERT INTO tmodule_sync_log (event_type, details)
VALUES (
    'module_installed',
    jsonb_build_object(
        'version', '1.0.0',
        'migration', '013_timetracking_module.sql',
        'timestamp', CURRENT_TIMESTAMP
    )
);

-- ============================================================================
-- UNINSTALL SCRIPT (bruges ved modul-sletning)
-- ============================================================================
-- ADVARSEL: Dette script sletter ALLE data i modulet!
-- Kør kun hvis modulet skal fjernes fuldstændigt.
-- 
-- For at uninstalle, kør følgende kommandoer i rækkefølge:
--
-- DROP VIEW IF EXISTS tmodule_order_details CASCADE;
-- DROP VIEW IF EXISTS tmodule_next_pending CASCADE;
-- DROP VIEW IF EXISTS tmodule_approval_stats CASCADE;
-- 
-- DROP TRIGGER IF EXISTS tmodule_orders_generate_number ON tmodule_orders;
-- DROP TRIGGER IF EXISTS tmodule_orders_update ON tmodule_orders;
-- DROP TRIGGER IF EXISTS tmodule_times_update ON tmodule_times;
-- DROP TRIGGER IF EXISTS tmodule_cases_update ON tmodule_cases;
-- DROP TRIGGER IF EXISTS tmodule_customers_update ON tmodule_customers;
-- 
-- DROP FUNCTION IF EXISTS tmodule_generate_order_number() CASCADE;
-- DROP FUNCTION IF EXISTS tmodule_update_timestamp() CASCADE;
-- 
-- DROP TABLE IF EXISTS tmodule_sync_log CASCADE;
-- DROP TABLE IF EXISTS tmodule_order_lines CASCADE;
-- DROP TABLE IF EXISTS tmodule_orders CASCADE;
-- DROP TABLE IF EXISTS tmodule_times CASCADE;
-- DROP TABLE IF EXISTS tmodule_cases CASCADE;
-- DROP TABLE IF EXISTS tmodule_customers CASCADE;
-- DROP TABLE IF EXISTS tmodule_metadata CASCADE;
-- 
-- -- Log uninstall i system log hvis muligt
-- -- (Dette vil fejle hvis tmodule_sync_log er droppet, men det er OK)
-- DO $$
-- BEGIN
--     IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'tmodule_sync_log') THEN
--         INSERT INTO tmodule_sync_log (event_type, details)
--         VALUES ('module_uninstalled', jsonb_build_object('timestamp', CURRENT_TIMESTAMP));
--     END IF;
-- EXCEPTION WHEN OTHERS THEN
--     -- Ignorer fejl - tabellen er måske allerede slettet
--     NULL;
-- END $$;
-- 
-- ============================================================================