117 lines
3.9 KiB
PL/PgSQL
117 lines
3.9 KiB
PL/PgSQL
-- Step 1: Create the member_states lookup table
|
|
CREATE TABLE member_states (
|
|
state_code CHAR(1) PRIMARY KEY,
|
|
state_name VARCHAR(20) NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Insert the three states
|
|
INSERT INTO member_states (state_code, state_name, description) VALUES
|
|
('A', 'Active', 'Member is currently active'),
|
|
('I', 'Inactive', 'Member is temporarily inactive'),
|
|
('D', 'Deactivated', 'Member has been permanently deactivated');
|
|
|
|
-- Step 2: Create the member_state_log table
|
|
CREATE TABLE member_state_log (
|
|
log_id SERIAL PRIMARY KEY,
|
|
member_id INTEGER NOT NULL,
|
|
state_code CHAR(1) NOT NULL,
|
|
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
changed_by INTEGER, -- Optional: user ID who made the change
|
|
reason TEXT, -- Optional: reason for state change
|
|
FOREIGN KEY (member_id) REFERENCES members(member_id),
|
|
FOREIGN KEY (state_code) REFERENCES member_states(state_code)
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX idx_member_state_log_member_id ON member_state_log(member_id);
|
|
CREATE INDEX idx_member_state_log_changed_at ON member_state_log(changed_at);
|
|
CREATE INDEX idx_member_state_log_state_code ON member_state_log(state_code);
|
|
|
|
-- Step 3: Migration script to populate the log table from existing data
|
|
INSERT INTO member_state_log (member_id, state_code, changed_at)
|
|
SELECT
|
|
member_id,
|
|
CASE
|
|
WHEN member_active = true THEN 'A'
|
|
ELSE 'I'
|
|
END,
|
|
COALESCE(updated_at, created_at, CURRENT_TIMESTAMP)
|
|
FROM members;
|
|
|
|
-- Step 4: Create a view to get current member states (most recent log entry per member)
|
|
CREATE OR REPLACE VIEW member_current_state AS
|
|
SELECT DISTINCT ON (msl.member_id)
|
|
msl.member_id,
|
|
msl.state_code,
|
|
ms.state_name,
|
|
ms.description,
|
|
msl.changed_at,
|
|
msl.changed_by,
|
|
msl.reason
|
|
FROM member_state_log msl
|
|
JOIN member_states ms ON msl.state_code = ms.state_code
|
|
ORDER BY msl.member_id, msl.changed_at DESC, msl.log_id DESC;
|
|
|
|
-- Step 5: Function to change member state (ensures proper logging)
|
|
CREATE OR REPLACE FUNCTION change_member_state(
|
|
p_member_id INTEGER,
|
|
p_new_state_code CHAR(1),
|
|
p_changed_by INTEGER DEFAULT NULL,
|
|
p_reason TEXT DEFAULT NULL
|
|
) RETURNS VOID AS $$
|
|
BEGIN
|
|
-- Validate that the state exists
|
|
IF NOT EXISTS (SELECT 1 FROM member_states WHERE state_code = p_new_state_code) THEN
|
|
RAISE EXCEPTION 'Invalid state code: %', p_new_state_code;
|
|
END IF;
|
|
|
|
-- Validate that the member exists
|
|
IF NOT EXISTS (SELECT 1 FROM members WHERE member_id = p_member_id) THEN
|
|
RAISE EXCEPTION 'Member not found: %', p_member_id;
|
|
END IF;
|
|
|
|
-- Insert the new state log entry
|
|
INSERT INTO member_state_log (member_id, state_code, changed_by, reason)
|
|
VALUES (p_member_id, p_new_state_code, p_changed_by, p_reason);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Step 6: Common queries you'll need
|
|
|
|
-- Get all active members
|
|
SELECT m.*, mcs.state_name, mcs.changed_at as last_state_change
|
|
FROM members m
|
|
JOIN member_current_state mcs ON m.member_id = mcs.member_id
|
|
WHERE mcs.state_code = 'A';
|
|
|
|
-- Get member state history
|
|
SELECT
|
|
msl.changed_at,
|
|
ms.state_name,
|
|
msl.reason,
|
|
msl.changed_by
|
|
FROM member_state_log msl
|
|
JOIN member_states ms ON msl.state_code = ms.state_code
|
|
WHERE msl.member_id = 123 -- Replace with actual member_id
|
|
ORDER BY msl.changed_at DESC;
|
|
|
|
-- Count members by state
|
|
SELECT
|
|
ms.state_name,
|
|
COUNT(*) as member_count
|
|
FROM member_current_state mcs
|
|
JOIN member_states ms ON mcs.state_code = ms.state_code
|
|
GROUP BY ms.state_code, ms.state_name
|
|
ORDER BY ms.state_code;
|
|
|
|
-- Step 7: Example usage of the change_member_state function
|
|
-- Change member 123 to inactive
|
|
SELECT change_member_state(123, 'I', 1, 'Member requested temporary suspension');
|
|
|
|
-- Reactivate member 123
|
|
SELECT change_member_state(123, 'A', 1, 'Member reactivation approved');
|
|
|
|
-- Step 8: After testing, remove the old column (CAREFUL!)
|
|
-- ALTER TABLE members DROP COLUMN member_active; |