-- 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_died IS NOT NULL THEN 'D'
        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 = 27
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(185, 'I', 1, 'Member requested temporary suspension');

-- Reactivate member 123
SELECT change_member_state(123, 'A', 1, 'Member reactivation approved');

-- Step 8: Options for "calculated column" - current member state

-- OPTION 1: Add a cached state column with triggers (RECOMMENDED)
ALTER TABLE members ADD COLUMN current_state_code CHAR(1);
ALTER TABLE members ADD COLUMN current_state_changed_at TIMESTAMP;
ALTER TABLE members ADD CONSTRAINT fk_members_current_state 
    FOREIGN KEY (current_state_code) REFERENCES member_states(state_code);

-- Function to update member's current state cache
CREATE OR REPLACE FUNCTION update_member_current_state_cache(p_member_id INTEGER)
RETURNS VOID AS $
DECLARE
    current_state RECORD;
BEGIN
    -- Get the most recent state for this member
    SELECT state_code, changed_at
    INTO current_state
    FROM member_state_log
    WHERE member_id = p_member_id
    ORDER BY changed_at DESC, log_id DESC
    LIMIT 1;
    
    -- Update the cached values in members table
    IF FOUND THEN
        UPDATE members 
        SET current_state_code = current_state.state_code,
            current_state_changed_at = current_state.changed_at
        WHERE member_id = p_member_id;
    END IF;
END;
$ LANGUAGE plpgsql;

-- Trigger to automatically update cache when state changes
CREATE OR REPLACE FUNCTION trigger_update_member_state_cache()
RETURNS TRIGGER AS $
BEGIN
    PERFORM update_member_current_state_cache(NEW.member_id);
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER tr_member_state_log_update_cache
    AFTER INSERT ON member_state_log
    FOR EACH ROW
    EXECUTE FUNCTION trigger_update_member_state_cache();

-- Initialize the cache for existing members
UPDATE members 
SET (current_state_code, current_state_changed_at) = (
    SELECT mcs.state_code, mcs.changed_at
    FROM member_current_state mcs
    WHERE mcs.member_id = members.member_id
);

-- Update the change_member_state function to work with cache
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 (trigger will update cache automatically)
    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;

-- OPTION 2: Generated column using a function (NOT POSSIBLE)
-- PostgreSQL doesn't allow subqueries in generated column expressions
-- This approach won't work:
/*
ALTER TABLE members ADD COLUMN current_state_code_generated CHAR(1) 
GENERATED ALWAYS AS (
    (SELECT state_code 
     FROM member_state_log msl 
     WHERE msl.member_id = members.member_id 
     ORDER BY changed_at DESC, log_id DESC 
     LIMIT 1)
) STORED;
-- ERROR: cannot use subquery in column generation expression
*/

-- OPTION 3: Materialized view (refresh manually or on schedule)
/*
CREATE MATERIALIZED VIEW members_with_current_state AS
SELECT 
    m.*,
    mcs.state_code as current_state_code,
    mcs.state_name as current_state_name,
    mcs.changed_at as current_state_changed_at
FROM members m
LEFT JOIN member_current_state mcs ON m.member_id = mcs.member_id;

CREATE UNIQUE INDEX idx_members_with_current_state_id 
ON members_with_current_state(member_id);

-- Refresh the materialized view (call this after state changes)
-- REFRESH MATERIALIZED VIEW members_with_current_state;
*/

-- Updated common queries using the cached column (OPTION 1)

-- Get all active members (much faster now!)
SELECT m.*, ms.state_name
FROM members m
JOIN member_states ms ON m.current_state_code = ms.state_code
WHERE m.current_state_code = 'A';

-- Get member with current state info
SELECT 
    m.*,
    ms.state_name as current_state_name,
    m.current_state_changed_at
FROM members m
LEFT JOIN member_states ms ON m.current_state_code = ms.state_code
WHERE m.member_id = 123;

-- Count members by current state (very fast)
SELECT 
    ms.state_name,
    COUNT(*) as member_count
FROM members m
JOIN member_states ms ON m.current_state_code = ms.state_code
GROUP BY ms.state_code, ms.state_name
ORDER BY ms.state_code;

-- Step 9: After testing, remove the old column (CAREFUL!)
-- ALTER TABLE members DROP COLUMN member_active;


-- 1) Add boolean column with default true
ALTER TABLE sust.members
ADD COLUMN member_primary boolean DEFAULT true;

-- Optional: ensure all existing rows are true before special handling
UPDATE sust.members
SET member_primary = true
WHERE member_primary IS NULL;

-- 2) Migration: partner-address holders are not primary
UPDATE sust.members
SET member_primary = false
WHERE member_email LIKE '%_partner%';