CREATE TABLE IF NOT EXISTS issued_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    card_code VARCHAR(60) NOT NULL,
    card_type VARCHAR(80) DEFAULT 'beneficiary_card',
    card_title VARCHAR(180) NOT NULL,
    issue_date DATE DEFAULT NULL,
    expires_on DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'issued',
    qr_token VARCHAR(120) DEFAULT '',
    print_count INT DEFAULT 1,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uniq_card_code (card_code),
    INDEX idx_issued_cards_tenant_status (tenant_id, status),
    INDEX idx_issued_cards_beneficiary (beneficiary_id),
    INDEX idx_issued_cards_household (household_id),
    INDEX idx_issued_cards_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(83, 'cards', 'View issued cards and tokens', 'cards.view'),
(84, 'cards', 'Manage issued cards and tokens', 'cards.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,83),(1,84),(2,83),(2,84),(3,83),(3,84),(4,83),(5,83),(6,83),(6,84),(7,83),(7,84),(9,83),(10,83),(11,83);