DROP FUNCTION IF EXISTS update_updated_at_column();
DROP TYPE IF EXISTS policy_type_enum;
DROP TYPE IF EXISTS policy_status_enum;

GRANT ALL PRIVILEGES ON DATABASE vitalia TO dev_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dev_user;

-- Define ENUM types
CREATE TYPE policy_type_enum AS ENUM ('GMC', 'GTL', 'GPA');
CREATE TYPE policy_status_enum AS ENUM ('WIP', 'Live');

CREATE TABLE policy_configuration (
    policy_id SERIAL PRIMARY KEY,                -- Auto-incrementing ID
    company_name VARCHAR(255) NOT NULL,    -- Company name (adjust length as needed)
    policy_type policy_type_enum NOT NULL, -- Use the ENUM type
    policy_number VARCHAR(255) UNIQUE NOT NULL, -- Unique policy number
    policy_status policy_status_enum NOT NULL, -- Use the ENUM type
    policy_step INTEGER NOT NULL,           -- Policy step (1-5)
    policy_configuration JSONB NOT NULL,   -- JSONB for flexible configuration data
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Creation timestamp
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Update timestamp
);

-- Optional: Create a trigger to automatically update the 'updatedAt' column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_policy_configuration_modtime
BEFORE UPDATE ON policy_configuration
FOR EACH ROW EXECUTE PROCEDURE  update_updated_at_column();
