pplate/cannamanage
1
0
Fork 0
Code Issues 9 Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
cannamanage/scripts/seed/init.sql
T
Patrick Plate 61b0cd92be feat(sprint8): Phase 5+6 — Integration, schedulers, tier enforcement, testing 
Phase 5 — Integration:
- PaymentReminderScheduler: monthly cron at 9am, sends PAYMENT_REMINDER
  and PAYMENT_OVERDUE (30+ days) notifications, audit logged
- BoardTermScheduler: daily cron at 8am, sends BOARD_TERM_EXPIRING
  notification 30 days before term end (1-day window for single delivery)
- Assembly protocol auto-archive: completeAssembly() generates PDF via
  AssemblyProtocolService and stores it in DocumentService.archiveProtocol()
- PlanTierService: Sprint 8 limits added (Kassenbuch 3mo starter, 1 MV/year
  starter, 100MB/1GB/unlimited document storage)
- Notification wiring: PAYMENT_RECEIVED sent to member on recordPayment(),
  sendToAllMembers() added to NotificationService for assembly invitations
- Seed data: 2 fee schedules (Regular €30, Reduced €15), 4 fee assignments,
  3 sample payments, 2 board positions, 2 board members

Phase 6 — Testing infrastructure:
- V22 migration: protocol_document_id on assemblies table
- Schedulers disabled in test profile (cannamanage.schedulers.enabled=false)
- Scheduler property configurable via SCHEDULERS_ENABLED env var

Additional fixes (pre-existing Phase 4 issues):
- AssemblyProtocolService: fixed Document import ambiguity (OpenPDF vs entity)
- AuditService: added convenience overloads for UUID actorId/clubId
- ReceiptPdfService: fixed getReceiptNumber/getMemberNumber/getPaymentDate
- StaffPermissionChecker: added getUserId/getClubId/getTenantId helpers
- FinanceService: added getPaymentById, exportLedgerCsv methods
2026-06-15 09:22:49 +02:00

200 lines
13 KiB
SQL
Raw Permalink Blame History

-- CannaManage System Test Seed Data
-- This file is mounted into PostgreSQL as /docker-entrypoint-initdb.d/99-seed.sql
-- It runs AFTER Flyway migrations (which use Spring Boot on backend startup).
-- NOTE: Since Flyway runs on backend start (not on DB init), this file will only
-- work if the tables already exist. For Docker test profile, the backend's Flyway
-- creates tables first, then this seed is loaded via the seed container's curl calls.
-- This SQL is kept as a reference and can be loaded manually:
-- docker exec -i cannamanage-db psql -U cannamanage -d cannamanage < scripts/seed/init.sql
-- ============================================================================
-- Test Club
-- ============================================================================
INSERT INTO clubs (id, tenant_id, name, address, license_number, max_members, status, max_prevention_officers)
VALUES (
'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001',
'Grüner Daumen e.V.',
'Hanfstraße 42, 10115 Berlin',
'CSC-BER-2024-001',
500,
'ACTIVE',
2
) ON CONFLICT (license_number) DO NOTHING;
-- ============================================================================
-- Test Admin User (email: admin@test.de, password: test123)
-- BCrypt hash of "test123" generated via: python3 -c "import bcrypt; print(bcrypt.hashpw(b'test123', bcrypt.gensalt(10)).decode())"
-- ============================================================================
INSERT INTO users (id, tenant_id, email, password_hash, role, active)
VALUES (
'b1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001',
'admin@test.de',
'$2a$10$/FgU1KyveJ7MaQ7Xv4kxD.5EIQUHujJfZI4K2E1H7pS6parMHJpeG',
'ROLE_ADMIN',
true
) ON CONFLICT (email, tenant_id) DO NOTHING;
-- ============================================================================
-- Test Members (5 members)
-- ============================================================================
INSERT INTO members (id, tenant_id, club_id, first_name, last_name, email, date_of_birth, membership_date, membership_number, status, is_under_21)
VALUES
('c1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Max', 'Mustermann', 'max@test.de', '1990-05-15', '2024-01-15', 'M-001', 'ACTIVE', false),
('c1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Anna', 'Schmidt', 'anna@test.de', '1985-08-22', '2024-01-20', 'M-002', 'ACTIVE', false),
('c1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Jonas', 'Weber', 'jonas@test.de', '2005-03-10', '2024-02-01', 'M-003', 'ACTIVE', true),
('c1000000-0000-0000-0000-000000000004', 'a1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Lisa', 'Meyer', 'lisa@test.de', '1992-11-30', '2024-02-15', 'M-004', 'ACTIVE', false),
('c1000000-0000-0000-0000-000000000005', 'a1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Tom', 'Fischer', 'tom@test.de', '1988-07-04', '2024-03-01', 'M-005', 'ACTIVE', false)
ON CONFLICT (email, tenant_id) DO NOTHING;
-- ============================================================================
-- Test Strains
-- ============================================================================
INSERT INTO strains (id, tenant_id, name, thc_percentage, cbd_percentage, description)
VALUES
('d1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'Northern Lights', 18.50, 0.80, 'Classic indica strain with relaxing effects'),
('d1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'Amnesia Haze', 22.00, 1.20, 'Potent sativa with cerebral high'),
('d1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'CBD Critical Mass', 5.00, 12.00, 'High-CBD medical strain')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Test Batches (3 batches)
-- ============================================================================
INSERT INTO batches (id, tenant_id, strain_id, quantity_grams, harvest_date, batch_code, status)
VALUES
('e1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'd1000000-0000-0000-0000-000000000001', 500.00, '2024-11-01', 'BATCH-2024-001', 'AVAILABLE'),
('e1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'd1000000-0000-0000-0000-000000000002', 300.00, '2024-11-15', 'BATCH-2024-002', 'AVAILABLE'),
('e1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'd1000000-0000-0000-0000-000000000003', 200.00, '2024-12-01', 'BATCH-2024-003', 'AVAILABLE')
ON CONFLICT (batch_code, tenant_id) DO NOTHING;
-- ============================================================================
-- Test Distributions (some sample handouts)
-- ============================================================================
INSERT INTO distributions (id, tenant_id, member_id, batch_id, quantity_grams, distributed_at, recorded_by, notes)
VALUES
('f1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000001', 'e1000000-0000-0000-0000-000000000001',
5.00, '2024-12-10 10:00:00+01', 'c1000000-0000-0000-0000-000000000001', 'Regular distribution'),
('f1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000002', 'e1000000-0000-0000-0000-000000000001',
3.00, '2024-12-10 11:00:00+01', 'c1000000-0000-0000-0000-000000000001', NULL),
('f1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000003', 'e1000000-0000-0000-0000-000000000003',
2.00, '2024-12-11 09:30:00+01', 'c1000000-0000-0000-0000-000000000001', 'Under-21 member, CBD only')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Monthly Quotas for test members (current month)
-- ============================================================================
INSERT INTO monthly_quotas (id, tenant_id, member_id, year, month, total_distributed, max_allowed)
VALUES
('g1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000001', 2024, 12, 5.00, 50.00),
('g1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000002', 2024, 12, 3.00, 50.00),
('g1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000003', 2024, 12, 2.00, 30.00)
ON CONFLICT (member_id, year, month) DO NOTHING;
-- ============================================================================
-- Stock Movements (audit trail)
-- ============================================================================
INSERT INTO stock_movements (id, tenant_id, batch_id, movement_type, quantity_grams, reason)
VALUES
('h1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'e1000000-0000-0000-0000-000000000001', 'HARVEST_IN', 500.00, 'Initial harvest intake'),
('h1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'e1000000-0000-0000-0000-000000000002', 'HARVEST_IN', 300.00, 'Initial harvest intake'),
('h1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'e1000000-0000-0000-0000-000000000003', 'HARVEST_IN', 200.00, 'Initial harvest intake'),
('h1000000-0000-0000-0000-000000000004', 'a1000000-0000-0000-0000-000000000001',
'e1000000-0000-0000-0000-000000000001', 'DISTRIBUTION', -8.00, 'Distributed to members')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Sprint 8: Fee Schedules (2 tiers)
-- ============================================================================
INSERT INTO fee_schedules (id, tenant_id, club_id, name, amount_cents, interval, is_default, is_active)
VALUES
('f8000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'Regulär', 3000, 'MONTHLY', true, true),
('f8000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'Ermäßigt', 1500, 'MONTHLY', false, true)
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Sprint 8: Fee Assignments (members assigned to fee schedules)
-- ============================================================================
INSERT INTO member_fee_assignments (id, tenant_id, member_id, club_id, fee_schedule_id, valid_from)
VALUES
('f8100000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'f8000000-0000-0000-0000-000000000001', '2024-01-15'),
('f8100000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'f8000000-0000-0000-0000-000000000001', '2024-01-20'),
('f8100000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'f8000000-0000-0000-0000-000000000002', '2024-02-01'),
('f8100000-0000-0000-0000-000000000004', 'a1000000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000004', 'a1000000-0000-0000-0000-000000000001',
'f8000000-0000-0000-0000-000000000001', '2024-02-15')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Sprint 8: Sample Payments
-- ============================================================================
INSERT INTO payments (id, tenant_id, club_id, member_id, amount_cents, payment_method, status, period_from, period_to, paid_at, recorded_by)
VALUES
('f8200000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'c1000000-0000-0000-0000-000000000001',
3000, 'BANK_TRANSFER', 'PAID', '2024-12-01', '2024-12-31',
'2024-12-03 09:00:00+01', 'b1000000-0000-0000-0000-000000000001'),
('f8200000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'c1000000-0000-0000-0000-000000000002',
3000, 'CASH', 'PAID', '2024-12-01', '2024-12-31',
'2024-12-05 14:30:00+01', 'b1000000-0000-0000-0000-000000000001'),
('f8200000-0000-0000-0000-000000000003', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'c1000000-0000-0000-0000-000000000001',
3000, 'BANK_TRANSFER', 'PAID', '2025-01-01', '2025-01-31',
'2025-01-02 10:00:00+01', 'b1000000-0000-0000-0000-000000000001')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Sprint 8: Board Positions (Vorstandspositionen)
-- ============================================================================
INSERT INTO board_positions (id, tenant_id, club_id, title, description, is_active, sort_order)
VALUES
('f8300000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', '1. Vorsitzender',
'Gesetzlicher Vertreter nach §26 BGB', true, 1),
('f8300000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'Kassenwart',
'Verantwortlich für Finanzen und Kassenbuch', true, 2)
ON CONFLICT DO NOTHING;
-- ============================================================================
-- Sprint 8: Board Members (assigned to positions)
-- ============================================================================
INSERT INTO board_members (id, tenant_id, club_id, position_id, member_id, elected_at, term_start, term_end, is_current)
VALUES
('f8400000-0000-0000-0000-000000000001', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'f8300000-0000-0000-0000-000000000001',
'c1000000-0000-0000-0000-000000000001', '2024-01-15', '2024-01-15', '2026-01-15', true),
('f8400000-0000-0000-0000-000000000002', 'a1000000-0000-0000-0000-000000000001',
'a1000000-0000-0000-0000-000000000001', 'f8300000-0000-0000-0000-000000000002',
'c1000000-0000-0000-0000-000000000004', '2024-01-15', '2024-01-15', '2026-01-15', true)
ON CONFLICT DO NOTHING;
Reference in New Issue View Git Blame Copy Permalink