60 lines
2.7 KiB
MySQL
60 lines
2.7 KiB
MySQL
|
-- =========================================================================
|
||
|
|
-- Migration 016: Pipeline Opportunities (Hub-local)
|
||
|
|
-- =========================================================================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS pipeline_stages (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
default_probability INTEGER NOT NULL DEFAULT 0,
|
||
|
|
color VARCHAR(20) DEFAULT '#0f4c75',
|
||
|
|
is_won BOOLEAN DEFAULT FALSE,
|
||
|
|
is_lost BOOLEAN DEFAULT FALSE,
|
||
|
|
is_active BOOLEAN DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS pipeline_opportunities (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
amount NUMERIC(12, 2) DEFAULT 0,
|
||
|
|
currency VARCHAR(10) DEFAULT 'DKK',
|
||
|
|
expected_close_date DATE,
|
||
|
|
stage_id INTEGER NOT NULL REFERENCES pipeline_stages(id),
|
||
|
|
probability INTEGER NOT NULL DEFAULT 0,
|
||
|
|
owner_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
|
||
|
|
is_active BOOLEAN DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS pipeline_stage_history (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
opportunity_id INTEGER NOT NULL REFERENCES pipeline_opportunities(id) ON DELETE CASCADE,
|
||
|
|
from_stage_id INTEGER REFERENCES pipeline_stages(id),
|
||
|
|
to_stage_id INTEGER NOT NULL REFERENCES pipeline_stages(id),
|
||
|
|
changed_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
|
||
|
|
note TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pipeline_opportunities_customer_id ON pipeline_opportunities(customer_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pipeline_opportunities_stage_id ON pipeline_opportunities(stage_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pipeline_stage_history_opportunity_id ON pipeline_stage_history(opportunity_id);
|
||
|
|
|
||
|
|
-- Seed default stages (Option B)
|
||
|
|
INSERT INTO pipeline_stages (name, description, sort_order, default_probability, color, is_won, is_lost)
|
||
|
|
SELECT * FROM (VALUES
|
||
|
|
('Ny', 'Ny mulighed', 1, 10, '#0f4c75', FALSE, FALSE),
|
||
|
|
('Afklaring', 'Kvalificering og behov', 2, 25, '#1b6ca8', FALSE, FALSE),
|
||
|
|
('Tilbud', 'Tilbud er sendt', 3, 50, '#3282b8', FALSE, FALSE),
|
||
|
|
('Commit', 'Forhandling og commit', 4, 75, '#5b8c5a', FALSE, FALSE),
|
||
|
|
('Vundet', 'Lukket som vundet', 5, 100, '#2f9e44', TRUE, FALSE),
|
||
|
|
('Tabt', 'Lukket som tabt', 6, 0, '#d9480f', FALSE, TRUE)
|
||
|
|
) AS v(name, description, sort_order, default_probability, color, is_won, is_lost)
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM pipeline_stages);
|