-- Criar tabela notifications
CREATE TABLE notification.notifications (
    id bigint NOT NULL DEFAULT nextval('notification.notifications_id_seq'::regclass),
    notification_code character varying(20) DEFAULT ('NOT'::text || lpad((nextval('notification.notifications_id_seq'::regclass))::text, 8, '0'::text)) NOT NULL,
    company_id bigint,
    user_id bigint,
    template_id bigint,
    type character varying(50) NOT NULL,
    priority notification.priority_level DEFAULT 'NORMAL'::notification.priority_level NOT NULL,
    title character varying(255) NOT NULL,
    message text NOT NULL,
    data jsonb DEFAULT '{}'::jsonb,
    channels character varying(50)[] DEFAULT ARRAY['EMAIL'::character varying],
    status notification.notification_status DEFAULT 'PENDING'::notification.notification_status NOT NULL,
    sent_at timestamp without time zone,
    read_at timestamp without time zone,
    scheduled_for timestamp without time zone,
    expires_at timestamp without time zone,
    retry_count integer DEFAULT 0 NOT NULL,
    max_retries integer DEFAULT 3 NOT NULL,
    error_message text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Adicionar constraints
ALTER TABLE ONLY notification.notifications ADD CONSTRAINT notifications_pkey PRIMARY KEY (id);
ALTER TABLE ONLY notification.notifications ADD CONSTRAINT notifications_notification_code_key UNIQUE (notification_code);

-- Criar índices
CREATE INDEX idx_notifications_company_id ON notification.notifications USING btree (company_id) WHERE (company_id IS NOT NULL);
CREATE INDEX idx_notifications_user_id ON notification.notifications USING btree (user_id) WHERE (user_id IS NOT NULL);
CREATE INDEX idx_notifications_template_id ON notification.notifications USING btree (template_id) WHERE (template_id IS NOT NULL);
CREATE INDEX idx_notifications_status ON notification.notifications USING btree (status);
CREATE INDEX idx_notifications_type ON notification.notifications USING btree (type);
CREATE INDEX idx_notifications_priority ON notification.notifications USING btree (priority);
CREATE INDEX idx_notifications_scheduled_for ON notification.notifications USING btree (scheduled_for) WHERE (scheduled_for IS NOT NULL);
CREATE INDEX idx_notifications_created_at ON notification.notifications USING btree (created_at);
CREATE INDEX idx_notifications_data_gin ON notification.notifications USING gin (data);

-- Criar trigger
CREATE TRIGGER trigger_notifications_updated_at BEFORE UPDATE ON notification.notifications FOR EACH ROW EXECUTE FUNCTION public.trigger_set_updated_at();

-- Adicionar foreign keys
ALTER TABLE ONLY notification.notifications ADD CONSTRAINT notifications_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE CASCADE;
ALTER TABLE ONLY notification.notifications ADD CONSTRAINT notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES core_business.users(id) ON DELETE CASCADE;
ALTER TABLE ONLY notification.notifications ADD CONSTRAINT notifications_template_id_fkey FOREIGN KEY (template_id) REFERENCES notification.notification_templates(id) ON DELETE SET NULL;

-- Criar tabela audit_logs
CREATE TABLE audit.audit_logs (
    id bigint NOT NULL DEFAULT nextval('audit.audit_logs_id_seq'::regclass),
    log_code character varying(20) DEFAULT ('AUD'::text || lpad((nextval('audit.audit_logs_id_seq'::regclass))::text, 8, '0'::text)) NOT NULL,
    user_id bigint,
    company_id bigint,
    session_id bigint,
    action_type audit.action_type NOT NULL,
    entity_type audit.entity_type NOT NULL,
    entity_id bigint NOT NULL,
    action_description text NOT NULL,
    old_values jsonb DEFAULT '{}'::jsonb,
    new_values jsonb DEFAULT '{}'::jsonb,
    ip_address inet,
    user_agent text,
    request_id character varying(255),
    metadata jsonb DEFAULT '{}'::jsonb,
    timestamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Adicionar constraints
ALTER TABLE ONLY audit.audit_logs ADD CONSTRAINT audit_logs_pkey PRIMARY KEY (id);
ALTER TABLE ONLY audit.audit_logs ADD CONSTRAINT audit_logs_log_code_key UNIQUE (log_code);

-- Criar índices
CREATE INDEX idx_audit_logs_user_id ON audit.audit_logs USING btree (user_id) WHERE (user_id IS NOT NULL);
CREATE INDEX idx_audit_logs_company_id ON audit.audit_logs USING btree (company_id) WHERE (company_id IS NOT NULL);
CREATE INDEX idx_audit_logs_action_type ON audit.audit_logs USING btree (action_type);
CREATE INDEX idx_audit_logs_entity ON audit.audit_logs USING btree (entity_type, entity_id);
CREATE INDEX idx_audit_logs_timestamp ON audit.audit_logs USING btree (timestamp);

-- Adicionar foreign keys
ALTER TABLE ONLY audit.audit_logs ADD CONSTRAINT audit_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES core_business.users(id) ON DELETE SET NULL;
ALTER TABLE ONLY audit.audit_logs ADD CONSTRAINT audit_logs_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE SET NULL;
ALTER TABLE ONLY audit.audit_logs ADD CONSTRAINT audit_logs_session_id_fkey FOREIGN KEY (session_id) REFERENCES security.user_sessions(id) ON DELETE SET NULL;
